Implementing Optimistic and Pessimistic Locking in Spring Boot and Spring Data JPA: A Proof of Concept

The source code of this tutorial is available over on GitHub.

Let's suppose there is a bank account and a microservice to handle withdrawals and deposits. Since this service is going to be horizontally scaled up/down depending on the load, It could have several instances trying to update the account balance either with a withdrawal or a deposit.

For these two use cases, when a deposit is done the service must ensure that only one instance updates the balance account at a time to avoid lost updates. However, withdrawals must only be executed if the account has enough money and there are no ongoing deposits. If there is an ongoing deposit, the application must throw an exception.

To ensure data integrity and prevent conflicts during account balance updates, the service uses a combination of optimistic and pessimistic locking, as described below.

For deposits, the service uses pessimistic locking to lock the row, allowing only one instance at a time to modify the account balance. Other instances attempting to modify the same row will be blocked until the lock is released.

For withdrawals, optimistic locking is used. If the account is currently locked due to a deposit or withdrawal in progress, the application will return an error message, and the user must try the withdrawal again later.

The microservice will be scaled horizontally into a Kubernetes cluster having four instances to provide the right conditions to demonstrate the behavior described before. The following diagram depicts the Kubernetes cluster.

In order to run several requests at the same time I created a JMeter test plan that will send 14 requests at the same time going through a CSV file that provides the data for each of them, this will be helpful to visualize the behavior of the service and the results on each scenario.

The JMeter test plan and the CSV file can be found under the JMeter folder in the repository.

First test without using any locking approach

This is the content of the CSV file to test the first scenario. It should update the account balance, with an initial value of 0, and at the end of the test, it should be 320.

90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20        

After the test plan was completed successfully, the account balance is 40. Why? This is a concurrency error named "lost update", which occurs when two or more transactions attempt to update the same data simultaneously, and one of them overwrites the changes made by the other. 

In this case, several instances are querying the account balance from the database at the same time and consequently, several instances are adding 20 to the same value, this behavior can be identified by checking the application logs.

The JMeter results show that multiple requests are using the same data.

Second test with optimistic and pessimistic locking

In order to use optimistic and pessimistic locking as described above, it is necessary to run this logic within a transaction scope, Spring framework provides the @Transacctional annotation which manages the transaction using AOP and commits or rolls back the transaction depending on the outcome.

More information about Spring transaction management and the @Transactional annotation

BanckAccountService.java

@Transactional
public AccountTransaction accountTransaction(AccountTransaction transaction) {

    BankAccount e;
    double newBalance;

    switch (transaction.type()) {
        case DEBIT -> {
            e = repository.findByAccountNumberAndName(transaction.accountNumber(), transaction.name())
                    .orElseThrow();
            newBalance = e.getBalance() - transaction.amount();
            if (newBalance < 0) {
                throw new IllegalArgumentException("Insufficient funds");
            }
        }
        case CREDIT -> {
            e = repository.findByAccountNumber(transaction.accountNumber())
                    .orElseThrow();
            System.out.println("Old value " + e.getBalance());
            newBalance = e.getBalance() + transaction.amount();
        }
        default -> throw new IllegalArgumentException("Invalid transaction type");
    }
    System.out.println("New value " + newBalance);
    e.setBalance(newBalance);
    repository.save(e);
    return new AccountTransaction(transaction.accountNumber(), transaction.name(), transaction.type(), newBalance);
}        

BankAccountRespository.java

@Lock(LockModeType.PESSIMISTIC_WRITE
Optional<BankAccount> findByAccountNumber(String accountNumber);

@Lock(LockModeType.OPTIMISTIC)
Optional<BankAccount> findByAccountNumberAndName(String accountNumber, String name);)        

This is the content of the CSV file to test the second scenario. It should update the account balance with deposits and withdrawals, the initial account balance will be 100  (if a DEBIT is executed first, it will avoid the 'Insufficient funds' exception).

90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,DEBIT,20
90898329,Julio,DEBIT,20
90898329,Julio,CREDIT,20
90898329,Julio,DEBIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,CREDIT,20
90898329,Julio,DEBIT,20
90898329,Julio,DEBIT,20
90898329,Julio,DEBIT,20        

After the test plan was executed, 14 CREDITS (Deposits) and 2 DEBITS (Withdrawals) were saved successfully, and the account balance is 340 which is the expected value using pessimistic locking for deposits and optimistic locking for withdrawals.

(Account balance + successfully deposits) – successfully withdrawals.

(100 + 280) – 40 = 340

This time the JMeter test plan shows that some DEBIT requests were not processed further.

The withdrawals use optimistic locking to ensure that the record's field version is tracked when updating the data. This ensures that the most up-to-date value is used and in case any other instance has updated the data, an exception would be thrown.

The application logs show the behavior of the service using the locking techniques to prevent conflicts, it is worth noting old and new values of the account balance and when they are throwing the exception.

Conclusions

In conclusion, the use of pessimistic and optimistic locking in spring is fairly easy and helps to avoid the lost update problem, However, both approaches have some trade-offs that must be considered and managed carefully depending on the context and the application needs.

Pessimistic locking

Advantages:

• Ensures data consistency by preventing concurrent transactions to modify data simultaneously.
• It is straightforward to implement.
• Reduced risk of conflicts since only one transaction can access and modify the data.
• Supported by most relational database systems.

Disadvantages:

• Reduced concurrency by blocking concurrent transactions.
• Increased contention since the transactions that require access to the data must wait until the lock is released.
• Potential deadlocks where several transactions are waiting for lock release.
• Higher resource utilization due to locks must be held for the duration of the transaction.

Optimistic locking

Advantages:

• Reduced contention by allowing multiple transactions to access the data (other transactions could access the data but not update it in case any transaction had already been updated it).
• Improve scalability since it does not block concurrent access to the data.
• Reduced overhead since locks don't need to be acquired and released explicitly.

Disadvantages:

• Risk of conflicts since optimistic locking relies on the assumption that conflicts between transactions are rare.
• Additional complexity due to the additional code that is required to handle rollbacks or retries.
• Limited support, some databases do not support or have limited support for optimistic locking.