Optimistic Locking vs Pessimistic Locking

Optimistic locking and pessimistic locking are concurrency control techniques in database management and software development:

• Optimistic Locking:

  • This approach assumes that conflicts between multiple users are rare.

  • It allows users to read data without any locks. When they want to update data, they check if it has been modified by someone else in the meantime.

  • If no changes have occurred, the update is allowed. If changes were made, it typically results in an error or requires conflict resolution.

  • Optimistic locking is often more scalable and suitable for scenarios with low contention.

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back.

When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session.

In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

• Pessimistic Locking:

  • Pessimistic locking is based on the assumption that conflicts are more likely.

  • It involves locking data when a user starts to work with it. This prevents other users from accessing or modifying the same data until the lock is released.

  • Pessimistic locking is more conservative and suitable for high contention situations where conflicts are expected.

  • It ensures data consistency but may reduce concurrency.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it.

It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then reconnect using that ID.

The DBMS maintains the locks and allows you to pick the session back up through the TxID. This is how distributed transactions using two-phase commit protocols (such as XA or COM+ Transactions) work.