Database: Optimistic locking and Pessimistic locking techniques

Problem

The problem happens when multiple transactions try to access same resource and need (read / update) this resource concurrently.

Solution

To handle this problem we use locking (Optimistic locking / Pessimistic locking).

Example

In E-Commerce Application if there is product A in inventory with Quantity 1 and there are two customers, each one tries to buy this product in parallel. After these two transactions are finished the quantity in inventory will be -1 which is an incorrect value.

Customer A: select * from inventory where product_id = 1;
Customer B: select * from inventory where product_id = 1;
Customer A: check quantity (quantity= 1)
Customer B: check quantity (quantity= 1)
Customer A: update inventory set quantity = quantity - 1 where product_id = 1;
Customer B: update inventory set quantity = quantity - 1 where product_id = 1;        

Solutions

There are two mechanisms to solve this problem Using Pessimistic locking / Optimistic locking.

Pessimistic locking

“Prevents conflicts between concurrent business transactions by allowing only one business transaction at a time to access data” by David Rice

In pessimistic locking we acquire an exclusive lock for the resource that will be updated and this lock will be released after the update is finished. Between acquiring the lock and releasing, another transaction will wait until the running transaction is finished and the lock is released.

In our example

Using Pessimistic locking these two transactions will be run in series by acquiring exclusive locks for this product.

The customer A will acquire lock for this product then update the quantity with value 0 and in same time the customer B will try to acquire lock on same product but will wait until the Customer A’s transaction is finished after thet Customer B will lock the product and check the quantity and found the value is zero so the lock will be released.

Customer A: start transaction 
Customer A: select * from inventory where product_id = 1 for update;
Customer B: start transaction 
Customer B: select * from inventory where product_id = 1 for update; // will be blocked until the customer A release the lock
Customer A: check quantity (quantity = 1)
Customer A: update inventory set quantity = quantity - 1 where product_id = 1;
Customer A: commit transaction // lock is released and customer B's transaction will be resumed 
Customer B: check quantity (quantity = 0) // there is no quantity 
Customer B: rollback transaction. // The lock is released        

Drawbacks

The resource will be exclusively locked before any update and another transaction will be blocked until the lock is released and this may affect performance and may cause deadlock.

Optimistic locking

“Prevents conflicts between concurrent business transactions by detecting a conflict and rolling back the transaction” by David Rice

In Optimistic locking there is no exclusive lock acquired instead we check if there is another transaction update the resource before commit the update if the resource is not updated, the update will be committed successfully and if the resource is updated we will roll back the transaction and throw exception (the conflict will be detected in commit phase). We can check if the resource is updated or not using version or timestamp.

In our example the customer A will read the current version of the product then update the quantity of the product with check the read version. And customer B can make these steps also but one of them will succeed and another will rollback the transaction.

Customer A: start transaction
Customer A: select * from inventory where product_id = 1;
Customer B: start transaction
Customer B: select * from inventory where product_id = 1;
Customer A: check quantity (quantity = 1)
Customer A: update inventory set quantity = quantity - 1 , version = version+1 where product_id = 1 and version= $version; // the number of updated row 1 so the transaction completed successfully 
Customer A: commit transaction
Customer B: check quantity (quantity= 1)
Customer B: update inventory set quantity = quantity - 1 , version = version+1 where product_id = 1 and version= $version; // the number of updated row 0 so there is another transaction update the read version 
Customer B: rollback transaction // throw exception        

Drawbacks

If there are many concurrent transactions and all of them access the same resource, many of them will fail.

Conclusion

Using optimistic lock or Pessimistic lock will be according to the application and use case if there are rare concurrent transactions accessing same resource or there are many concurrent transactions accessing same resource and there is no problem if most of them failed and we don’t want effect the performance we should optimistic lock otherwise the Pessimistic may be used. So to choose between two techniques you should know the frequency of concurrent transactions and what are the performance requirements.