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/

How is this done in springboot?

Optimistic Locking

Optimistic locking in Spring JPA is a concurrency control mechanism that doesn’t lock database records. Instead, it uses a version number or a timestamp to check for concurrent modifications. It assumes that multiple transactions can complete without affecting each other. It’s more suitable for applications with low data contention.

How it works:

  1. A transaction reads a record, including its version number.
  2. The transaction holds onto the version number while performing its work.
  3. When the transaction tries to update the record, it checks if the version number in the database is the same as the one it read.
  4. If the version numbers match, the update proceeds, and the version number is incremented.
  5. If they don’t match, it means another transaction modified the record, and the update fails. The application must then handle this OptimisticLockException, typically by retrying the transaction.

Implementation in Spring JPA:

You can implement optimistic locking by adding a version field to your JPA entity. Use the @Version annotation on a field, which can be of type int, Integer, long, Long, or java.sql.Timestamp. JPA will automatically manage this field.

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private double price;

    @Version
    private int version; // JPA manages this field automatically
}

Pessimistic Locking

Pessimistic locking is a concurrency control strategy that prevents other transactions from modifying a record once it has been selected for an update. This approach assumes that conflicts are likely and is more suited for environments with high data contention.

How it works:

  1. A transaction explicitly locks a record (or a row in a table) before reading or updating it.
  2. Once a record is locked, no other transaction can access or modify it until the lock is released.
  3. The lock is typically held until the transaction is committed or rolled back.

Implementation in Spring JPA:

You can implement pessimistic locking by using the @Lock annotation on a repository method. The type of lock is specified by the LockModeType enum.

  1. LockModeType.PESSIMISTIC_READ: Acquires a shared lock. This allows other transactions to read the data but prevents them from modifying it.
  2. LockModeType.PESSIMISTIC_WRITE: Acquires an exclusive lock. This prevents other transactions from reading or writing the data.
public interface ProductRepository extends JpaRepository<Product, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Optional<Product> findByIdForUpdate(Long id);
}

Links to this note