Using locking to control database concurrency

Imagine you are working on an e-commerce system and thousands of people try to buy the last remaining product at the same time. However, many of them could proceed to the checkout and finish the order. When you check your stock, you have a product with a negative quantity. How was this possible, and how can you solve this?

Let's code! The first thing you might think is to check the stock before the checkout. Maybe something like this:

public void validateAndDecreaseSolution(long productId, int quantity {
    Optional stockByProductId = 
 stockRepository.findStockByProductId(productId);

    int stock = stockByProductId.orElseThrow().getStock();
    int possibleStock = stock - quantity;

    if (stock 



You can use this validation, but when we talk about hundreds, thousands, millions, or even dozens of requests per second, this validation will not be enough. When 10 requests reach this piece of code at the exact same time and the database returns the same value for stockByProductId, your code will break. You need a way to block other requests while we do this verification.


  
  
  First solution - FOR UPDATE


Add a lock statement on your SELECT. In this example I did this using  FOR UPDATE with Spring Data. As PostgreSQL documentation says


FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends.




@Query(value = "SELECT * FROM stocks s WHERE s.product_id = ?1 FOR UPDATE", nativeQuery = true)
Optional findStockByProductIdWithLock(Long productId);






public void validateAndDecreaseSolution1(long productId, int quantity) {
    Optional stockByProductId = stockRepository.findStockByProductIdWithLock(productId);

    // ... validate

    stockRepository.decreaseStock(productId, quantity);
}




All requests to stocks table using the product ID will wait until the actual transaction finishes. The objective here is to ensure you get the last updated value of the stock.


  
  
  Second solution - pg_advisory_xact_lock


This solution is similar to the previous one, but you can select what is the lock key. We'll lock the entire transaction until we finish all the processing of validation and stock decrement.



public void acquireLockAndDecreaseSolution2(long productId, int quantity) {
    Query nativeQuery = entityManager.createNativeQuery("select pg_advisory_xact_lock(:lockId)");
    nativeQuery.setParameter("lockId", productId);
    nativeQuery.getSingleResult();

    Optional stockByProductId = stockRepository.findStockByProductId(productId);

    // check stock and throws exception if it is necessary

    stockRepository.decreaseStock(productId, quantity);
}




The next request will only interact with a product with the same ID after this transactions ends.


  
  
  Third solution - WHERE clause


In this case, we'll not lock our row or transaction. Let's permit this transaction to continue until the update statement. Notice the last condition: stock > 0. This will not permit our stock be less than zero. So if two people try to buy at the same time, one of them will receive an error because our database will not allow stock 


@Transactional
@Modifying
@Query(nativeQuery = true, value = "UPDATE stocks SET stock = stock - :quantity WHERE product_id = :productId AND stock > 0")
int decreaseStockWhereQuantityGreaterThanZero(@Param("productId") Long productId, @Param("quantity") int quantity);





  
  
  Conclusion


The first and second solutions use pessimistic locking as a strategy. The third is optimistic locking. The pessimistic locking strategy is used when you want restrictive access to a resource while you perform any task involving this resource. The target resource will be locked for any other access until you finish your process. Be careful with deadlocks!

With optimistic locking, you can perform various queries on the same resource without any block. It's used when conflicts are not likely to happen. Usually, you will have a version related to your row, and when you update this row, the database will compare your row version with the row version in the database. If both are equal, the change will be successful. If not, you have to retry. As you can see, I don't use any version row in this article, but my third solution doesn't block any requests and controls concurrency using the stock > 0 condition.

If you want to see the full code, you can check on my GitHub.

There are many other strategies to implement pessimistic and optimistic locking, you can search more about FOR UPDATE WITH SKIP LOCKED for example.