Optimistic and Pessimistic Locking in JPA

Locking is a mechanism that allows parallel work with the same data in the database. When more than one transaction tries to access the same data at the same time, locks come into play, which ensures that only one of these transactions will change the data. JPA supports two types of locking mechanisms: optimistic model and pessimistic model.

Let's consider the airline database as an example. The flights table stores information about flights, and tickets stores information about booked tickets. Each flight has its own capacity, which is stored in the flights.capacity column. Our application should control the number of tickets sold and should not allow purchasing a ticket for a fully filled flight. To do this, when booking a ticket, we need to get the capacity of the flight and the number of tickets sold from the database, and if there are empty seats on the flight, sell the ticket, otherwise, inform the user that the seats have run out. If each user request is processed in a separate thread, data inconsistency may occur. Suppose there is one empty seat on the flight and two users book tickets at the same time. In this case, two threads simultaneously read the number of tickets sold from the database, check that there is still a seat left, and sell the ticket to the client. In order to avoid such collisions, locks are applied.

Simultaneous changes without locking

We will use Spring Data JPA and Spring Boot. Let’s create entities, repositories, and other classes:

@Entity
@Table(name = "flights")
public class Flight {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String number;

    private LocalDateTime departureTime;

    private Integer capacity;

    @OneToMany(mappedBy = "flight")
    private Set<Ticket> tickets;

    // ...
    // getters and setters
    // ...

    public void addTicket(Ticket ticket) {
        ticket.setFlight(this);
        getTickets().add(ticket);
    }

}

public interface FlightRepository extends CrudRepository<Flight, Long> { }

@Entity
@Table(name = "tickets")
public class Ticket {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "flight_id")
    private Flight flight;

    private String firstName;

    private String lastName;

    // ...
    // getters and setters
    // ...
}

public interface TicketRepository extends CrudRepository<Ticket, Long> { }

DbService performs transactional changes:

@Service
public class DbService {

    private final FlightRepository flightRepository;

    private final TicketRepository ticketRepository;

    public DbService(FlightRepository flightRepository, TicketRepository ticketRepository) {
        this.flightRepository = flightRepository;
        this.ticketRepository = ticketRepository;
    }

    @Transactional
    public void changeFlight1() throws Exception {
        // the code of the first thread
    }

    @Transactional
    public void changeFlight2() throws Exception {
        // the code of the second thread
    }

}

An application class:

import org.apache.commons.lang3.function.FailableRunnable;

@SpringBootApplication
public class JpaLockApplication implements CommandLineRunner {

    @Resource
    private DbService dbService;

    public static void main(String[] args) {
        SpringApplication.run(JpaLockApplication.class, args);
    }

    @Override
    public void run(String... args) {
        ExecutorService executor = Executors.newFixedThreadPool(2);
        executor.execute(safeRunnable(dbService::changeFlight1));
        executor.execute(safeRunnable(dbService::changeFlight2));
        executor.shutdown();
    }

    private Runnable safeRunnable(FailableRunnable<Exception> runnable) {
        return () -> {
            try {
                runnable.run();
            } catch (Exception e) {
                e.printStackTrace();
            }
        };
    }
}

We will use this database state in every following run of the application

flights table:

id	number	departure_time	capacity
1	FLT123	2022-04-01 09:00:00+03	2
2	FLT234	2022-04-10 10:30:00+03	50

tickets table:

id	flight_id	first_name	last_name
1	1	Paul	Lee

Let's write a code simulating the simultaneous purchase of tickets without locking.

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception {
        if (flight.getCapacity() <= flight.getTickets().size()) {
            throw new ExceededCapacityException();
        }
        var ticket = new Ticket();
        ticket.setFirstName(firstName);
        ticket.setLastName(lastName);
        flight.addTicket(ticket);
        ticketRepository.save(ticket);
    }

    @Transactional
    public void changeFlight1() throws Exception {
        var flight = flightRepository.findById(1L).get();
        saveNewTicket("Robert", "Smith", flight);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        var flight = flightRepository.findById(1L).get();
        saveNewTicket("Kate", "Brown", flight);
        Thread.sleep(1_000);
    }

}

public class ExceededCapacityException extends Exception { }

Calling Thread.sleep(1_000); makes sure that transactions started by both threads will overlap in time. The result of executing this example in the database:

id	flight_id	first_name	last_name
1	1	Paul	Lee
2	1	Kate	Brown
3	1	Robert	Smith

As you can see, three tickets were booked, although the capacity of the FLT123 flight is two passengers.

Optimistic locking

Now, look at how optimistic blocking works. Let's start with a more straightforward example - a simultaneous capacity of the flight change. In order to use optimistic locking, a persistent property with an annotation @Version must be added to the entity class. This property can be of type int, Integer, short, Short, long, Long, or java.sql.Timestamp. Version property is managed by the persistence provider, you do not need to change its value manually. If the entity is changed, the version number is increased by 1 (or the timestamp is updated if the field with the @Version annotation has the java.sql.Timestamp type). And if the original version does not match the version in the database when saving the entity, an exception is thrown.

Add the version property to the Flight entity

@Entity
@Table(name = "flights")
public class Flight {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String number;

    private LocalDateTime departureTime;

    private Integer capacity;

    @OneToMany(mappedBy = "flight")
    private Set<Ticket> tickets;

    @Version
    private Long version;

    // ...
    // getters and setters
    //

    public void addTicket(Ticket ticket) {
        ticket.setFlight(this);
        getTickets().add(ticket);
    }

}

Add the version column to the flights table

id	name	departure_time	capacity	version
1	FLT123	2022-04-01 09:00:00+03	2	0
2	FLT234	2022-04-10 10:30:00+03	50	0

Now we change flight capacity in both threads:

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    @Transactional
    public void changeFlight1() throws Exception {
        var flight = flightRepository.findById(1L).get();
        flight.setCapacity(10);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        var flight = flightRepository.findById(1L).get();
        flight.setCapacity(20);
        Thread.sleep(1_000);
    }

}

Now when executing our application we will get an exception

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=?, departure_time=?, number=?, version=? where id=? and version=?

Thus, in our example, one thread saved the changes, and the other thread could not save the changes because there are already changes in the database. Thanks to this, simultaneous changes of the same flight are prevented. In the exception message, we see that id and version columns are used in the where clause.

Keep in mind that the version number does not change when changing the @OneToMany and @ManyToMany collections with the mappedBy attribute. Let's restore the original DbService code and check it out:

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception {
        if (flight.getCapacity() <= flight.getTickets().size()) {
            throw new ExceededCapacityException();
        }
        var ticket = new Ticket();
        ticket.setFirstName(firstName);
        ticket.setLastName(lastName);
        flight.addTicket(ticket);
        ticketRepository.save(ticket);
    }

    @Transactional
    public void changeFlight1() throws Exception {
        var flight = flightRepository.findById(1L).get();
        saveNewTicket("Robert", "Smith", flight);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        var flight = flightRepository.findById(1L).get();
        saveNewTicket("Kate", "Brown", flight);
        Thread.sleep(1_000);
    }

}

The application will run successfully and the result in the tickets table will be as follows

id	flight_id	first_name	last_name
1	1	Paul	Lee
2	1	Robert	Smith
3	1	Kate	Brown

Again, the number of tickets exceeds the flight capacity.

JPA makes it possible to forcibly increase the version number when loading an entity using the @Lock annotation with the OPTIMISTIC_FORCE_INCREMENT value. Let's add the findWithLockingById method to the FlightRepository class. In Spring Data JPA, any text between find and By can be added to the method name, and if it does not contain keywords such as Distinct, the text is descriptive, and the method is executed as a regular find…By…:

public interface FlightRepository extends CrudRepository<Flight, Long> {

    @Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)
    Optional<Flight> findWithLockingById(Long id);

}

Use the findWithLockingById method in DbService

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception {
        // ...
    }

    @Transactional
    public void changeFlight1() throws Exception {
        var flight = flightRepository.findWithLockingById(1L).get();
        saveNewTicket("Robert", "Smith", flight);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        var flight = flightRepository.findWithLockingById(1L).get();
        saveNewTicket("Kate", "Brown", flight);
        Thread.sleep(1_000);
    }

}

When the application starts, one of the two threads throws ObjectOptimisticLockingFailureException. The state of the tickets table is

id	flight_id	first_name	last_name
1	1	Paul	Lee
2	1	Robert	Smith

We see that this time only one Ticket has been saved to the database.

If it is impossible to add a new column to the table, but there is a need to use optimistic locking, you can apply Hibernate annotations OptimisticLocking and DynamicUpdate. The type value in the OptimisticLocking annotation can take the following values:

• ALL - perform locking based on all fields
• DIRTY - perform locking based on only changed fields fields
• VERSION - perform locking using a dedicated version column
• NONE - don’t perform locking

We will try the DIRTY optimistic locking type in the changing flight capacity example.

@Entity
@Table(name = "flights")
@OptimisticLocking(type = OptimisticLockType.DIRTY)
@DynamicUpdate
public class Flight {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String number;

    private LocalDateTime departureTime;

    private Integer capacity;

    @OneToMany(mappedBy = "flight")
    private Set<Ticket> tickets;

    // ...
    // getters and setters
    // ...

    public void addTicket(Ticket ticket) {
        ticket.setFlight(this);
        getTickets().add(ticket);
    }

}

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    @Transactional
    public void changeFlight1() throws Exception {
        var flight = flightRepository.findById(1L).get();
        flight.setCapacity(10);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        var flight = flightRepository.findById(1L).get();
        flight.setCapacity(20);
        Thread.sleep(1_000);
    }

}

An exception will be thrown

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=? where id=? and capacity=?

Now id and cpacity columns are utilized in the where clause. If you change the lock type to ALL, such an exception will be thrown

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=? where id=? and capacity=? and departure_time=? and number=?

Now all columns are used in the where clause.

Pessimistic locking

With pessimistic locking, table rows are locked at the database level. Let's change the blocking type of the FlightRepository#findWithLockingById method to PESSIMISTIC_WRITE

public interface FlightRepository extends CrudRepository<Flight, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Flight> findWithLockingById(Long id);

}

and rerun the booking tickets example. One of the threads will throw ExceededCapacityException and only two tickets will be in the tickets table.

id	flight_id	first_name	last_name
1	1	Paul	Lee
2	1	Kate	Brown

Now the thread that first loaded the flight has exclusive access to the row in the flights table, so the second thread suspends its work until the lock is released. After the first thread commits the transaction and releases the lock, the second thread will get monopole access to the row, but at this point, the flight capacity will already be exhausted, because the changes made by the first thread will get into the database. As a result, the controlled ExceededCapacityException exception will be thrown.

There are three types of pessimistic locking in JPA:

• PESSIMISTIC_READ - acquire a shared lock, and the locked entity cannot be changed before a transaction commit.
• PESSIMISTIC_WRITE - acquire an exclusive lock, and the locked entity can be changed.
• PESSIMISTIC_FORCE_INCREMENT - acquire an exclusive lock and update the version column, the locked entity can be changed

If many threads are locking the same row in the database, it may take a long time to get the lock. You can set a timeout to receive a lock:

public interface FlightRepository extends CrudRepository<Flight, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="10000")})
    Optional<Flight> findWithLockingById(Long id);

}

If the timeout expires, CannotAcquireLockException will be thrown. It is important to note that not all persistence providers support the hint javax.persistence.lock.timeout. For example, Oracle's persistence provider supports this hint, whereas it does not for PostgreSQL, MS SQL Server, MySQL, and H2.

Now we consider a deadlock situation.

@Service
public class DbService {

    // ...
    // autowiring
    // ...

    private void fetchAndChangeFlight(long flightId) throws Exception {
        var flight = flightRepository.findWithLockingById(flightId).get();
        flight.setCapacity(flight.getCapacity() + 1);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight1() throws Exception {
        fetchAndChangeFlight(1L);
        fetchAndChangeFlight(2L);
        Thread.sleep(1_000);
    }

    @Transactional
    public void changeFlight2() throws Exception {
        fetchAndChangeFlight(2L);
        fetchAndChangeFlight(1L);
        Thread.sleep(1_000);
    }

}

We will get the following stack trace from one of the threads

org.springframework.dao.CannotAcquireLockException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not extract ResultSet
...
Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
...

The database detected that this code leads to a deadlock. However, there may be situations when the database will not be able to do this and threads will suspend their execution until the timeout ends.

Conclusion

Optimistic and pessimistic locking are two different approaches. Optimistic locks are suitable for situations where an exception that has been thrown can be easily handled and either notify the user or try again. At the same time, rows at the database level are not blocked, which does not slow down the operation of the application. If it was possible to get a block, pessimistic locks give great guarantees for the execution of queries to the database. However, using pessimistic locking, you need to carefully write and check the code because there is a possibility of deadlocks, which can become floating errors that are difficult to find and fix.