Optimistic locking vs pessimistic record locking

Table of Contents

TODO

  1. https://stackoverflow.com/a/41029731
  2. https://stackoverflow.com/questions/27865992/why-use-select-for-update-mysql/73841540#73841540

Lost Update anomaly

Lost Update in Database Systems

The problem

Alice believes she can withdraw 40 from her account but does not realize that Bob has just changed the account balance, and now there are only 20 left in this account.

The solution

When dealing with conflicts to rows, we have two options:

  1. We can try to avoid the conflict, and that’s what Pessimistic Locking does. The record is locked while it is edited.
  2. Or, we could allow the conflict to occur, but we need to detect it upon committing your transactions, and that’s what Optimistic Locking does. The record is locked only when changes are committed to the database.

In both data-locking models, the lock is released after the changes are committed to the database.

Pessimistic locking

Both Alice and Bob will acquire a read lock on the account table row that both users have read. The database acquires these locks on SQL Server when using Repeatable Read or Serializable.

Because both Alice and Bob have read the account with the PK value of 1, neither of them can change it until one user releases the read lock. This is because a write operation requires a write/exclusive lock acquisition, and shared/read locks prevent write/exclusive locks.

Only after Alice has committed her transaction and the read lock was released on the account row, Bob UPDATE will resume and apply the change. Until Alice releases the read lock, Bob’s UPDATE blocks.

Source: https://www.ibm.com/docs/en/rational-clearquest/7.1.0?topic=clearquest-optimistic-pessimistic-record-locking

Source: https://stackoverflow.com/a/129397

http://en.wikipedia.org/wiki/Lock_(database)

http://en.wikipedia.org/wiki/Deadlock

http://en.wikipedia.org/wiki/Client-server

http://www.opengroup.org/bookstore/catalog/c193.htm

http://msdn.microsoft.com/en-us/library/ms687120(VS.85).aspx

Drawbacks

Going to the “ticketing” scenario:

We could lock all the options as you read them. This is pessimistic scenario. You see why it sucks. Your system can be brought down by a single clown who simply starts a reservation and goes smoking. Nobody can reserve anything before he finishes. Your cash flow drops to zero. That is why, optimistic reservations are used in reality. Those who dawdle too long have to restart their reservation at higher prices.

Scenarios

  1. e-commerce applications where multiple applications work with the data related to orders.
  2. Financial data where interest earnings need to be added to each account at the end of each month.
  3. Ticket reservation system where many clients reserve the same rooms/seats at the same time.

Optimistic locking

With optimistic locking, we use an additional version column. The version column is incremented every time an UPDATE or DELETE is executed, and it is also used in the WHERE clause of the UPDATE and DELETE statements. For this to work, we need to issue the SELECT and read the current version prior to executing the UPDATE or DELETE, as otherwise, we would not know what version value to pass to the WHERE clause or to increment.

In the optimistic approach, we have to record all the data that we read and come to the commit point with our version of data (The user wants to buy shares at the price we displayed in this quote, not the current price). At this point, ANSI transaction is created, which locks the DB, checks if nothing is changed and commits/aborts the operation. IMO, this is effective emulation of MVCC (https://en.wikipedia.org/wiki/Multiversion_concurrency_control), which is also associated with Optimistic CC and also assumes that our transaction restarts in case of abort. That is, the user will make a new reservation. A transaction here involves a human user decisions.

http://en.wikipedia.org/wiki/Optimistic_locking

Source: https://www.ibm.com/docs/en/rational-clearquest/7.1.0?topic=clearquest-optimistic-pessimistic-record-locking

Source: https://stackoverflow.com/a/129397

Drawbacks

Going to the “ticketing” scenario:

With the pessimistic locking approach, we let the users browse the offers, fill in the form with lots of available options and current prices. That process takes a lot of time and meanwhile, the options can become obsolete, all the prices can become invalid between the user started to fill the form and presses “I agree” button. Because there was no lock on the data the user has accessed and somebody else, more agile, can intefere changing all the prices and the user needs to restart with new prices.

Which one is right for me?

To select proper locking mechanism you have to estimate the amount of reads and writes and plan accordingly.

To understand which strategy is best for you, think not about the “Transactions Per Second your DB has” but the duration of a single transaction. Normally, you open a transaction, perform an operation and close the transaction. This is a short, classical transaction ANSI had in mind and for these, it is fine to get away without locking.

It’s often worth setting your transactions to read-committed or no-lock to avoid deadlocks while reading.

Optimistic locking Pessimistic locking
Optimistic assumes that nothing’s going to change while we are reading a row (or working with data from a row). Pessimistic assumes that something will change and so locks the row.
Exclusive lock is not used when reading a row Exclusive lock is used when reading a row
The application doesn’t need to maintain a connection with the database for the session The application needs a direct connection to the database.
Used in three-tier architectures Used in two-tier architectures
Works best when we don’t expect too many “collisions”, “lost updates”, or “write skews” Works best when collisions are anticipated. “Lost updates” and “write skews” are prevented.
Faster because of no locking. As long as there are no collisions, the cost is less. Gives more accurate data but much slower
Performs better when contention is high because it prevents the work rather than discard it and start over.
This strategy is ugly compared to optimistic locking but it avoids the need for a dedicated version column, in cases where you aren’t able to modify the schema.
When there are collisions, the transaction needs to be aborted and started over. The work done on the transaction needs to be discarded. Conflicting transactions are “rolled back”. The transactions which would violate synchronization are simply blocked.
Use this strategy when it is not essential that the data is perfectly read i.e., when “dirty” reads are ok (most web aplications are ok with this) Use this when exact data is mandatory (financial transactions)
Will not cause “deadlocks”
For optimistic locking to work, every participant in data modification must agree in using this kind of locking. But if someone modifies the data without taking care about the version column, this will spoil the whole idea of the optimistic locking. Pessimistic locking wouldn’t allow it in the first place.

References

  1. https://www.ibm.com/docs/en/rational-clearquest/7.1.0?topic=clearquest-optimistic-pessimistic-record-locking
  2. https://www.tothenew.com/blog/optimistic-locking-in-spring-boot/
  3. https://www.baeldung.com/jpa-optimistic-locking
  4. https://medium.com/slalom-build/optimistically-locking-your-spring-boot-web-services-187662eb8a91
  5. https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
  6. https://vladmihalcea.com/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena/

Links to this note