How to Solve Race Conditions in a Booking System

Database isolation refers to the level of isolation between concurrent transactions in a database. Isolation levels control the visibility and accessibility of data to concurrent transactions and can affect the occurrence of race conditions in a database. If your isolation level is not “serializable” - there is a possibility of race conditions.

The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently

Race conditions

Example of Race condition, Hotel room booking and movie ticket booking etc

CREATE TABLE Room (
  id INT PRIMARY KEY AUTO_INCREMENT,
  room_number INT,
  available BOOLEAN
);

CREATE TABLE Booking (
  id INT PRIMARY KEY AUTO_INCREMENT,
  room_id INT,
  start_date DATE,
  end_date DATE
);

insert into room(id, room_number, available) values(123, 123, true);

Two users, Alice and Bob, try to book the same room for overlapping dates at the same time.

Alice:

UPDATE Room
SET available = FALSE
WHERE id = 123;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-01', '2022-01-07');

Bob:

UPDATE Room
SET available = FALSE
WHERE id = 123;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-03', '2022-01-10');

The final result (the room being booked for both Alice and Bob) is different from what would be expected if the statements were executed sequentially (either Alice's booking or Bob's booking being rejected)

Pessimistic concurrency control

Pessimistic concurrency control is a technique used to prevent race conditions in a database by locking the data that is being accessed or updated. This ensures that only one user can access the data at a time, and other users have to wait until the lock is released before they can access it.

In SQL, pessimistic concurrency control can be implemented using the "SELECT ... FOR UPDATE" statement. This statement allows a user to lock the rows of a table that are being accessed and prevents other users from updating or locking those rows until the lock is released.

To implement pessimistic concurrency control for the "Booking" table, a user can execute the following SQL statement:

SELECT * FROM Room WHERE id = 123 FOR UPDATE;

This statement will lock the row with the ID 123 in the "Book" table, and prevent other users from accessing or updating that row until the lock is released.

To release the lock, the user can commit or roll back the transaction:

COMMIT;  -- releases the lock
ROLLBACK;  -- releases the lock and discards any changes made to the data

Optimistic concurrency control

Optimistic concurrency control, on the other hand, allows multiple users to access and update the data concurrently, but checks for conflicts before committing the changes. If a conflict is detected, the user is notified and the changes are not applied.

One way to implement optimistic concurrency control in a booking system is to use a "version" column in the "Room" table. This column can be used to store a "version number" for each booking, which is incremented each time the booking is updated.

ALTER TABLE Room
ADD version INT DEFAULT 1;

Then we need to update SQL statements for booking a room. Now Alice's statement will look like this:

UPDATE Room
SET available = FALSE, version = version + 1
WHERE room_number = 123 AND version = 1;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-01', '2022-01-07');

And Bob’s will look like this:

UPDATE Room
SET available = FALSE, version = version + 1
WHERE id = 123 AND version = 1;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-03', '2022-01-10');

If both of these statements are executed concurrently, the first UPDATE statement to be executed will increment the "version" of the room with ID 123 to 2, and the second UPDATE statement will fail, as the "version" in the WHERE clause is 1 (so zero rows will be updated with the second transaction).

This will prevent the race condition from occurring, as only one booking will be inserted into the "Booking" table, and the availability of the room will not be incorrectly updated.

Bonus

Is MySQL's auto_increment really monotonic?

Why you shouldn't rely on auto_increment feature in some cases.

If a race condition between two MySQL transactions appears, the row with ID = N may appear in the database BEFORE another row with ID < N

We need three terminals here, let’s call them A, B and C.
Create Table in T-A

create table foo (
  id int not null auto_increment,
  value char(30) not null,
  created_at timestamp default current_timestamp,
  primary key (id)
  );

Query OK, 0 rows affected (0,08 sec)

select * from foo;

Empty set (0,00 sec)

T-B Start transaction, insert row. but don't commit yet

start transaction;
insert into foo (value) values ('alfa');

select * from foo;

Empty set (0,00 sec)

insert into foo (value) values ('bravo');

Query OK, 1 row affected (0,01 sec)

select * from foo;
+----+-------+---------------------+
| id | value | created_at          |
+----+-------+---------------------+
|  2 | bravo | 2018-04-09 13:13:51 |
+----+-------+---------------------+
1 row in set (0,00 sec)

It’s there! As you can notice, it got assigned the ID = 2, because ID = 1 had been previously allocated for the alfa row. But hey, where is it?!

Uncommited! Hanging in the air!

commit;

Query OK, 0 rows affected (0,02 sec)

select * from foo;

+----+-------+---------------------+
| id | value | created_at          |
+----+-------+---------------------+
|  1 | alfa  | 2018-04-09 13:13:21 |
|  2 | bravo | 2018-04-09 13:13:51 |
+----+-------+---------------------+
2 rows in set (0,01 sec)

The Issue

This is a edge case.

Consider that we query the db during this short between-commit phase:

we retrieve a single row, ID = 2,
we process this batch of one row,
we store the ID = 2 state for the next batch, telling it you should add where ID > 2 to your query.
We’ve just ommited the alfa row!

Solution ideas

Mark the already pushed/updated rows with a flag, query with where completed = false (add additional column to the table)
Exploit the updated_at column to improve the situation (add additional column to the table)

Reference - https://www.boldare.com/tech-blog/is-mysql-autoincrement-really-monotonic/

Optimistic and Pessimistic Locking in JPA

https://hackernoon.com/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

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:

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);
    }

}

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

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);
    }

}

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

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);
}

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.

valarpirai/Database race condition.md