We store the information in databases so that it can be utilized later for different purposes like reading or updating. While we perform these operations on a record in a database there are good chances of conflicts or collisions. There can be two ways of dealing with such situations:
- Let the conflict occur and detect it upon the commit
- Avoid the conflict altogether and let one transaction complete at a time
Let’s try to understand these two options with an example. We will use the famous problem of Alice and Bob who have a joint account in the bank with an amount of $50 in it.
- Alice reads the account balance, and the value is $50
- Bob checks the balance at the same time and withdraws $20 from the account with a commit
- Alice, whose transaction is still running tries to withdraw $40 which will leave the account balance negative $10
Now, how we can handle this situation to make sure the balance is not overdrawn?
Pessimistic locking
We’ve seen this approach in programming as well where we synchronize a piece of code so that only one thread can access the resource at a time. In the database, the transaction is delayed if there is a conflict among transactions for a set of records at a later stage. During this time the database records are locked and others can have read-only access to these records.
So, if we take our previous example to understand the pessimistic locking approach.
- Alice walks in and acquires a lock on the account to check the balance
- Bob also checks the balance and now shares the lock with Alice
- Both of them cannot withdraw from the account because to write/update a record we need an exclusive lock
For this reason, Alice’s updates are blocked until Bob releases the shared lock acquired previously.
When using the pessimistic approach, the validate operation is carried out first, and read, compute, and write activities are carried out if the validation results are compatible with the compatibility of the lock.
Optimistic locking
In the optimistic approach, until a transaction is committed, it can be carried out without any issues or restrictions. A conflict check can be performed at the end thanks to the optimistic method, which also permits the transactions to move forward in an unsynchronized manner.
Only read and compute operations are performed during optimistic execution, and the transaction is only validated just before the appropriate operation. Let’s try to see how optimistic locking can help Alice and Bob. To achieve the optimistic locking version column is maintained in the database.
- Alice and Bob check the account balance. Both get a balance of $50 (current version = 1)
- Bob places the withdrawal $30 request with version = 1 and the balance is updated (updated version = 2)
- Alice places the withdrawal $30 request with version = 1 but this fails because the version is updated
- Alice reads the balance again and the updated version and balance are fetched
- Alice places the withdrawal $20 request with version = 2 and the balance is updated (updated version = 3)
Conclusion
In this article, we understood the two approaches to transaction handling in a database. Both these approaches have their own pros and cons and we should be able to choose one based on our requirements.
When the cost of retrying a transaction is very high or when there is plenty of contention that many transactions would wind up rolling back, pessimistic locking is appropriate.
Optimistic locking is preferred otherwise because it works across multiple transactions as it does not involve locking.