Database Internals - Optimistic vs Pessimistic

This article deep dives into optimistic locking vs pessimistic locking and which one to use when!

When your application deals with many transactions doing concurrent reads and writes to the database, you need concurrency control mechanisms to ensure that your database is always in a consistent state before and after processing reads and writes requests.

Why? Consider the example below:

Alice and Bob are trying to buy a product (let’s say an iPhone) from Amazon. Also, imagine that only one iPhone is left in Amazon’s inventory.

Now, the sequence of operations happens like this:

1. Alice opens Amazon and reads that there is an iPhone available for purchase.

2. Bob opens Amazon and reads that there is an iPhone available for purchase.

3. Both users click on the “Buy now“ button and make the payment simultaneously.

4. Just for the sake of an example: assume that Bob’s payment was made a few milliseconds earlier than Alice’s.

5. In this case, Alice is also able to pass through their transaction though it should be marked as failed.

So, we need some concurrency control or locking mechanism to make sure we get the expected output and don’t do over-debit in case the inventory is empty.

When it comes to concurrency control, there are two major kinds of locking implementations:

1. Optimistic Locking

2. Pessimistic Locking

Optimistic locking or Pessimistic locking are just approaches in concurrency control or strategies in software development, but they're not specific keywords in the database query language sense.

Optimistic Locking

Optimistic locking says a record is locked only when changes are committed to the database.

Optimistic locking is based on the concept that conflicts between transactions are extremely rare and most transactions can be completed without interference.

One way to implement Optimistic locking is using the version number. You need a column called “version” in the table and increment it every time an UPDATE or DELETE command is committed.

In the diagram below here is the sequence of operations:

1. Alice requests to view the iPhone on the Amazon website and gets the iPhone product as a display.

2. Bob requests to view the iPhone on the Amazon website and gets the iPhone product as a display.

3. Bob tries to purchase the iPhone and simultaneously the version number is incremented in the UPDATE operation.

4. Bob purchases the iPhone successfully.

5. When Alice tries to buy the iPhone, she will try to update the record where the version number is 1, but there won’t be any rows found because the version has already changed at the time of Bob’s transaction.

Thus, if the version read at the time of writing is different than the version in the table row, then there is a conflict and the user can restart the transaction. In case of conflicts, there will be “0 rows affected” in the transaction sent to the database and this output can be used by the application to retry the transaction.

Pessimistic Locking

Pessimistic Locking says a record is locked while it is edited

Pessimistic locking is based on the concept that conflicts are likely and the transactions need to lock the data on which the reads and writes are being performed.

In the diagram below here is the sequence of operations:

1. Alice requests to view the iPhone on the Amazon website.

2. Bob requests to view the iPhone on the Amazon website.

3. Bob gets the response back successfully and then tries to buy the product.

4. While Alice’s read transaction is in progress, the UPDATE made by Bob will be blocked. This is because while a shared read lock is in progress, an exclusive write lock cannot be acquired.

5. Thus, when Alice’s read transaction is committed and the shared read lock is released, Bob’s UPDATE command gets the exclusive write lock and then the transaction is committed.

Since the write operation requires the write(exclusive) lock and there can be no read(shared) locks in progress, that’s why there cannot be simultaneous updates for the same record.

Conclusion

The main thing between which one to choose among Optimistic locking vs Pessimistic locking depends on how likely is it to get conflicts among your transactions and how much effort is required to roll back the transactions if there are conflicts.

Pessimistic locking is useful when you have a large number of transactions facing conflicts and thus end up getting rolled back. The cost of retrying the transaction is very high and thus pessimistic locking ensures that the transaction should pass through.

Whereas, if conflicts are less likely to occur, then go for optimistic locking because it uses a less strict isolation level and thus allows you to have more throughput from the database.

---

That’s it, folks for this edition of the newsletter. Please consider liking and sharing with your friends as it motivates me to bring you good content for free.

Resources

Optimistic vs Pessimistic Locking by Vlad Mihalcea
IBM - optimistic vs pessimistic locking
Locking in Databases and Isolation Mechanisms by Denny Sam