Optimistic and Pessimistic Locking in Spring Data JPA

Introduction

This tutorial explains how to stop two users from overwriting each other's changes to the same row, the lost update problem. We will first see the problem, then solve it with optimistic locking, using a @Version field, read the SQL that makes it work, and retry after a conflict. We will then solve it with pessimistic locking, using @Lock on a repository method, and run 4 threads selling from the same stock. Lastly, we will look at lock modes, timeouts and deadlocks, and when to choose which approach.

The lost update problem

A product has 10 keyboards in stock. Ann and Raj both open it: both see 10. Ann sells 3 and saves 7. Raj sells 2 and saves 8, computed from the 10 he saw. The database now says 8, but 5 keyboards were sold: Ann's sale has been lost. No error occurred, and each save was correct on its own. The transaction isolationClick to read tutorial: Spring @Transactional: Propagation and Isolation levels most databases use by default do not prevent this, because the two reads and the two writes happen in separate transactions, or at least do not overlap in a way the database detects.

There are two ways to prevent it -
  • Optimistic locking: let everyone read, and detect at write time that the row changed since it was read. The later writer gets an error instead of silently overwriting.
  • Pessimistic locking: lock the row when reading it, so the second user must wait until the first one has finished.

The example

A Product entity has a stock count and a @Version field, a number Hibernate manages by itself: it is set on insert and incremented on each update. The application never sets it. The field can be a long, an int, their wrappers, or a timestamp, though a number is more reliable. The repository adds one method with a pessimistic lock.
Product.java - a product with a stock count and a version
//Product.java
package com.javabrahman.data.locking;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Version;

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

  @Version
  private long version;

  protected Product() {
  }

  public Product(String name, int stock) {
    this.name = name;
    this.stock = stock;
  }

  public Long getId() { return id; }
  public int getStock() { return stock; }
  public void setStock(int stock) { this.stock = stock; }
  public long getVersion() { return version; }
}
ProductRepository.java - a locking read
//ProductRepository.java
package com.javabrahman.data.locking;

import jakarta.persistence.LockModeType;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;

public interface ProductRepository extends JpaRepository<Product, Long> {

  @Lock(LockModeType.PESSIMISTIC_WRITE)
  Optional<Product> findLockedById(Long id);
}
LockingApp.java - a lost update prevented twice
//LockingApp.java
package com.javabrahman.data.locking;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.transaction.support.TransactionTemplate;

@SpringBootApplication
public class LockingApp {

  @Bean
  CommandLineRunner demo(ProductRepository products, TransactionTemplate tx) {
    return args -> {
      Long id = products.save(new Product("keyboard", 10)).getId();
      System.out.println("start: " + describe(products, id));

      System.out.println("== optimistic: two users edit the same row");
      Product seenByAnn = products.findById(id).orElseThrow();   // both read version 0
      Product seenByRaj = products.findById(id).orElseThrow();
      seenByAnn.setStock(seenByAnn.getStock() - 3);              // Ann sells 3
      products.save(seenByAnn);
      System.out.println("Ann saved: " + describe(products, id));
      seenByRaj.setStock(seenByRaj.getStock() - 2);              // Raj sells 2, from a stale copy
      try {
        products.save(seenByRaj);
      } catch (ObjectOptimisticLockingFailureException e) {
        System.out.println("Raj failed: " + e.getClass().getSimpleName());
        System.out.println("   " + e.getMessage());
      }
      System.out.println("after the conflict: " + describe(products, id));

      System.out.println("== optimistic: retry on a fresh copy");
      for (int attempt = 1; ; attempt++) {
        Product fresh = products.findById(id).orElseThrow();
        fresh.setStock(fresh.getStock() - 2);
        try {
          products.save(fresh);
          System.out.println("Raj saved on attempt " + attempt + ": " + describe(products, id));
          break;
        } catch (ObjectOptimisticLockingFailureException e) {
          System.out.println("attempt " + attempt + " failed, retrying");
        }
      }

      System.out.println("== pessimistic: 4 threads, 25 sales each, from stock 100");
      tx.executeWithoutResult(s -> products.findById(id).orElseThrow().setStock(100));
      AtomicInteger sold = new AtomicInteger();
      List<Thread> threads = new ArrayList<>();
      for (int t = 0; t < 4; t++) {
        threads.add(Thread.ofPlatform().start(() -> {
          for (int i = 0; i < 25; i++) {
            tx.executeWithoutResult(s -> {
              Product p = products.findLockedById(id).orElseThrow();   // select ... for update
              p.setStock(p.getStock() - 1);
              sold.incrementAndGet();
            });
          }
        }));
      }
      for (Thread t : threads) {
        t.join();
      }
      System.out.println("sold " + sold.get() + ": " + describe(products, id));
    };
  }

  private static String describe(ProductRepository products, Long id) {
    Product p = products.findById(id).orElseThrow();
    return "stock " + p.getStock() + ", version " + p.getVersion();
  }

  public static void main(String[] args) {
    SpringApplication.run(LockingApp.class, args);
  }
}
 OUTPUT of the above code
start: stock 10, version 0
== optimistic: two users edit the same row
Ann saved: stock 7, version 1
Raj failed: ObjectOptimisticLockingFailureException
   Row was already updated or deleted by another transaction for entity [com.javabrahman.data.locking.Product with id '1']
after the conflict: stock 7, version 1
== optimistic: retry on a fresh copy
Raj saved on attempt 1: stock 5, version 2
== pessimistic: 4 threads, 25 sales each, from stock 100
sold 100: stock 0, version 103

Explanation of the optimistic part

  • The product starts with stock 10 and version 0; Hibernate set the version on insert.
  • Ann and Raj both read the product at version 0. Ann sold 3 and saved: stock 7, and the version went up to 1. Hibernate increments the version on every update by itself.
  • Raj then sold 2 from his stale copy, which still said stock 10 at version 0. Without a version column, his save would have written stock 8 over Ann's 7, and Ann's sale would be lost. Instead, it failed with ObjectOptimisticLockingFailureException: "Row was already updated or deleted by another transaction". The stock stayed at 7.
  • Optimistic locking does not decide what Raj should do next. Here the application simply retried: read a fresh copy at version 1, sold 2 again, and saved: stock 5, version 2. Other applications show the user the current data and ask them to repeat the change.
The SQL of the conflict part shows how it works -  OUTPUT - the SQL of the conflict
== optimistic: two users edit the same row
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
Hibernate: update product set name=?,stock=?,version=? where id=? and version=?
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
Ann saved: stock 7, version 1
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
Raj failed: ObjectOptimisticLockingFailureException
   Row was already updated or deleted by another transaction for entity [com.javabrahman.data.locking.Product with id '1']
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=?
after the conflict: stock 7, version 1
  • Ann's save: Ann's copy is detached, so save() first reads the row to merge her changes into it, then runs update product set name=?,stock=?,version=? where id=? and version=?. The where clause includes the old version, 0, and the set writes the new one, 1. If another transaction had changed the row in between, the update would match no row, and Hibernate would report the conflict from the count of 0 updated rows. That check and the change happen in one statement, so no other transaction can slip in between.
  • Raj's save: here the conflict was found even earlier. The merge read the row, found version 1 in the database while Raj's copy said 0, and failed without sending an update.
  • The other select statements come from the describe() calls that print the stock.

Pessimistic locking

Optimistic locking assumes conflicts are rare and pays only when they happen. When many transactions update the same row at once - the stock of a popular product during a sale - most of them would fail and retry. Pessimistic locking takes the opposite approach: lock the row when reading it, so that others must wait. In Spring Data, @Lock(LockModeType.PESSIMISTIC_WRITE) on a repository method does it, and the SQL gets a for update clause -  OUTPUT - the locking read
Hibernate: select p1_0.id,p1_0.name,p1_0.stock,p1_0.version from product p1_0 where p1_0.id=? for update
  • select ... for update locks the row until the transaction ends. Another transaction running the same locking read on that row blocks until the first one commits or rolls back, and then reads the committed value.
  • The last line of the program output shows the effect: 4 threads each sold 25 units, in 100 separate transactions, from a stock of 100. All 100 sales were counted, and the stock ended at exactly 0. Each transaction read the stock, decremented it, and committed while the others waited, so no sale was lost and none had to be retried.
  • The version ended at 103: 1 and 2 from the optimistic part, 3 from resetting the stock to 100, and one more for each of the 100 sales. @Version keeps working alongside pessimistic locks.
  • The lock is held only until the transaction ends, so the locking read and the update must be in the same transaction, as each tx.executeWithoutResult() block is here. A locking read in one transaction followed by a save in another protects nothing.

Lock modes and timeouts

  • PESSIMISTIC_WRITE: an exclusive lock, for update in most databases. Use it for read-modify-write on a row.
  • PESSIMISTIC_READ: a shared lock where the database supports one (for share in PostgreSQL). Others may read-lock the row too, but not change it.
  • OPTIMISTIC_FORCE_INCREMENT and PESSIMISTIC_FORCE_INCREMENT: also increment the version, useful to mark a parent entity as changed when only its children were.
  • A waiting transaction should not wait forever. @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "2000")) on the method sets a timeout in milliseconds, where the database supports it. When it expires, the caller gets a PessimisticLockingFailureException.
  • Two transactions that lock the same rows in a different order can deadlock. The database detects it and fails one of them. Locking rows in a fixed order, such as by id, avoids it.

Optimistic or pessimistic?

  • Optimistic by default: add a @Version field to every entity that users can edit. It costs nothing until a conflict happens, holds no locks, and also protects changes that span several requests, such as a form opened at version 4 and submitted ten minutes later.
  • Pessimistic for short, hot transactions on the same rows: stock counts, seat bookings, account balances. It holds database locks, so the transaction must be short, and it cannot span requests.
  • For a single counter, an update query such as update product set stock = stock - 1 where id = ? and stock > 0 may be simpler than either, since the database does the read and write in one statement.

Summary

In this tutorial we prevented the lost update in two ways. With optimistic locking, a @Version field made Hibernate add the version to the where clause of every update, and Raj's save from a stale copy failed with ObjectOptimisticLockingFailureException instead of overwriting Ann's sale; a retry on a fresh copy then succeeded. With pessimistic locking, @Lock(PESSIMISTIC_WRITE) produced select ... for update, and 4 threads made 100 sales with the stock ending at exactly 0. We also saw the lock modes, lock timeouts, deadlocks, and when to choose each approach. The next tutorial is about evolving the database schema itself, safely and repeatably: database migrations with Flyway and LiquibaseClick to read tutorial: Database Migrations with Flyway and Liquibase in Spring Boot.