Concurrency Control With Locking (Optimistic and Pessimistic With HANDS ON)
Table Of Contents
- Introduction
1.1 What is Flash Sale - Optimistic and Pessimistic Locking
2.1 Optimistic Locking
2.2 Pessimistic Locking - Hands-on
3.1 Testcase 1 — The Lost update anomaly
3.2 Testcase 2 — Optimistic Locking
3.3 Testcase 3 — Pessimistic Locking - Conclusion
- Useful resources
Introduction
In distributed systems, there are various scenarios where we need to take care of data consistency while maintaining high degree of parallelism.
Few such examples are
* Amazon/Flipkart flash sale
* IRCTC, MakeMyTrip ticket booking
* Banking transactions.
Such systems maintain relational database as the data is structured and high concurrency is required.
In order to handle data consistency and concurrency, applications have to implement some kind of locking mechanism.
For more context on different kinds of locking such as shared/exclusive locks, MVCC and different transaction isolation levels, have a look at below articles:
1. https://medium.com/how-the-web-works/how-does-a-database-server-handle-thousands-of-concurrent-requests-d54352310183
2. https://www.gojek.io/blog/on-concurrency-control-in-databases
In this article, we will try to solve the data consistency problem in Flash Sale system design, using optimistic and pessimistic locking.
What is Flash Sale ?
Flash sale is sale of a product which has a huge demand in the market and the number of stocks are quite less. The item would be out of stock soon after it is available for sale due to high number of buy requests. So, it becomes very important that demands of customers are met fairly and without any chance of inconsistency.
Optimistic and Pessimistic Locking
Optimistic Locking
In optimistic locking, applications allow multiple users to update the record at same time. The record is validated against any conflicts while committing the transaction.
If multiple users try to update the same record, only one of the transactions will succeed and rest all will fail. Example below
It can be implemented by keeping an extra column in table for versioning purpose. Hibernate also provides “Version” annotation to implement it.
Advantages: The systems are fast and there is no overhead of acquiring locks.
Disadvantage: There can be lot of optimistic locking exceptions, which can degrade user experience. Applications need to implement internal retry logic to provide better experience.
Pessimistic Locking
In pessimistic locking, application tries to gain exclusive lock on all the resources it wants to update. It keeps the hold on the lock until the transaction is completed.
Other transactions wanting to update the record have to wait until lock is released or it will fail if lock timeout limit is exceeded.
It can be implemented by using @Lock annotation during select query.
Note that all select and update must occur within same transaction.
Advantage : It helps in maintaining high data integrity and also provide better user experience, since the database does the heavy lifting and chances of failures are less.
Disadvantage: It makes the system relatively slower. There are also chances of deadlock, so all locking scenarios must be well thought. The systems should lock the resources in the same order throughout.
Example: Let’s say there are two resources R1 and R2. Transaction T1 tries to acquire the locks in order R1->R2 and Transaction T2 tries to acquire in order R2->R1. If T1 has acquired R1 and T2 has acquired R2, both transaction will be waiting to acquire locks on resources R1 and R1 respectively.
Hands-on
Reference code can be found in GitHub repo. Follow the read-me and set it up in your local for better understanding.
As described above, we are try to replicate the scenarios in a Flash Sale system design.
We will have 2 tables.
1. stock_table( Id(pk), name, count, version ) — Stores the available count of given item Id
2. audit_table(Id(pk), count ) — This is just for auditing purpose. All successful orders will have an entry here.
Let’s say you have 40 items in stock and for each successful order, you create audit entry in another table. If 36 orders are placed, the available stock would reduce to 4. The audit table should also have details for the 36 orders only.
So, in ideal scenario, the decrease in count in stock_table should be equal to entries in audit_table.
We will test 3 scenarios
1. Update the records currently without having optimistic/pessimistic locking in place. It will help in reproducing The Lost Update anomaly encountered in various systems.
2. Update the records concurrently with optimistic locking.
3. Update the records concurrently with pessimistic locking.
We will insert below entry in stock_table
insert into stock_table (id, name, count, version) values(‘id1’, ‘Samsung Galaxy S22’, 400, 0)
Notice that count is 400.
We will place 1000 purchaseOrder requests using thread pool of 40 concurrent requests.
All testcases are in https://github.com/sks1995/sql-locking/blob/master/src/test/java/com/practice/locking/sqllocking/SqlLockingApplicationTests.java
Testcase 1 — The Lost update anomaly
SqlLockingApplicationTests#test_failureScenario
You can notice that count in stock_table decreased from 400 to 353 i.e. only 47 orders. But the audit_table has 1000 entries.
It should have been 400 and audit entries also should have been 400.
Instead 1000 orders are successful and system has accounted for only 47 orders in stock_table. It will be bad user experience.
Testcase 2 — Optimistic Locking
SqlLockingApplicationTests#test_OptimisticLockingScenario
The count in stock_table decreased from 400 to 316 i.e. 84 orders.
Also, the audit_table has 84 entries.
Notice that version field in stock_table, which is used for optimistic locking check has also incremented to 84 from 0.
So, out of 1000, 84 orders were successful and rest of them threw OptimisticLockingException.
Database consistency is intact and rest 916 users out of 1000 faced errors. Same can be seen in logs as well.
Testcase 3— Pessimistic Locking
SqlLockingApplicationTests#test_PessimisticLockingScenario
The count in stock_table decreased from 400 to 0 i.e. 400 orders.
Also, the audit_table has 400 entries.
Hence, all 400 orders were successful.
Notice that the test execution will take more time than testcase-2 due to exclusive locking at database.
Also notice that out of 1000 requests, 400 were successful i.e. equal to the number of stock, while in optimistic locking, success rate was very low.
You can check the sql statement fired in case of pessimistic locking by enabling spring.jpa.show-sql flag in application.properties.
Conclusion
Pessimistic locking provides better user experience at the cost of latency while Optimistic locking provides performance at cost of user experience.
We noticed that
1. In optimistic locking, out of 1000 order requests, 84 were successful while 916 failed. Items left in stock are 316.
2. In pessimistic locking, out of 1000 order requests, 400 were successful, while 600 failed. Items left in stock is 0.
Useful resources
1. https://vladmihalcea.com/optimistic-vs-pessimistic-locking/
2. https://baeldung-cn.com/java-jpa-transaction-locks
3. https://www.cockroachlabs.com/docs/stable/select-for-update.html