Pessimistic and Optimistic concurrency control

There are 2 different concurrency control mechanisms
1. Pessimistic concurrency control
2. Optimistic concurrency control

What is the difference between optimistic and pessimistic concurrency control


Pessimistic concurrency involves locking rows to prevent other users from modifying the same data at the same time. Until the lock is released by the lock owner, no other users will be able to access that data. Pessimistic locking can very easily lead to performance bottle necks in an application.

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.




Optimistic concurrency does NOT involve locking rows when reading. Instead, this model checks if two users tried to update the same record at the same time. If that happens one user's changes are committed and the other user's changes are discarded and an exception will be thrown to notify the user.

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back.

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.