How to Prevent Overselling with Pessimistic Locks in Spring Boot 3

1. Introduction

Assume an online bookstore where 200 copies of a popular novel are in stock. During a live signing event, 1500 users may try to purchase the book simultaneously, leading to overselling if concurrent requests read the same stock value.

@Transactional
public void buy(Long id, Integer quantity) {
    Product product = productRepository.findById(id).get();
    if (product.getQuantity() >= quantity) {
        product.setQuantity(product.getQuantity() - quantity);
        productRepository.save(product);
    } else {
        throw new RuntimeException("库存不足");
    }
}

The above code looks correct but suffers from a race condition: multiple threads can read the stock before any update, causing duplicate deductions.

2. Why Optimistic Lock May Not Be Enough

Using @Version for optimistic locking works in many cases, but under heavy load (e.g., 500 requests within 2 seconds) it triggers frequent OptimisticLockException and forces retries or failures.

3. Solution: Pessimistic Lock

A pessimistic lock assumes high conflict probability and locks the database row as soon as it is read, preventing other transactions from reading or modifying it.

3.1 Define Entity

@Entity
@Table(name = "t_product")
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private BigDecimal price;
    private int quantity;
    // getters, setters
}

3.2 Repository with @Lock

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Transactional
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = ?1")
    Product findByIdWithLock(Long id);
}

Calling findByIdWithLock generates SQL with FOR UPDATE, locking the selected row.

3.3 Testing Concurrency

@Transactional
public Product queryProduct(Long id) {
    System.err.printf("[%d] %s - start...%n", System.currentTimeMillis(), Thread.currentThread().getName());
    Product product = this.productRepository.findByIdWithLock(id);
    System.err.printf("[%d] %s - 锁定数据【%d】成功...%n", System.currentTimeMillis(), Thread.currentThread().getName(), id);
    TimeUnit.SECONDS.sleep(10);
    System.err.printf("[%d] %s - end...%n", System.currentTimeMillis(), Thread.currentThread().getName());
    return product;
}

When two threads invoke the method simultaneously, the second thread waits until the first releases the lock.

3.4 Lock Timeout Configuration

Configure lock timeout with @QueryHints:

@Transactional
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({ @QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000") }) // 3 seconds
@Query("SELECT p FROM Product p WHERE p.id = ?1")
Product findByIdWithLock(Long id);

Testing on Oracle shows a timeout exception after 3 seconds, while MySQL ignores the hint.

MySQL’s lock wait timeout is controlled by the innodb_lock_wait_timeout variable (default 50 s). It can be changed temporarily:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SET GLOBAL innodb_lock_wait_timeout = 3;

Or permanently by adding to my.cnf:

[mysqld]
innodb_lock_wait_timeout = 3

3.5 Graceful Handling of PessimisticLockingFailureException

Use Spring Retry to retry the operation and fall back when retries are exhausted.

<dependency>
    <groupId>org.springframework.retry</groupId>
    <artifactId>spring-retry</artifactId>
</dependency>

@SpringBootApplication
@EnableRetry
public class App {}

@Transactional
public void buyProcess(Long id, Integer quantity) {
    Product product = productRepository.findByIdWithLock(id)
        .orElseThrow(() -> new ProductNotFoundException("商品不存在"));
    try {
        System.err.printf("[%d] %s - 锁定数据【%d】成功...%n", System.currentTimeMillis(), Thread.currentThread().getName(), id);
        TimeUnit.SECONDS.sleep(13);
    } catch (InterruptedException e) { Thread.currentThread().interrupt(); }
    if (product.getQuantity() < quantity) {
        throw new RuntimeException("库存不足");
    }
    product.setQuantity(product.getQuantity() - quantity);
    productRepository.save(product);
}

@Recover
public void recover(PessimisticLockingFailureException e, Long id, Integer quantity) {
    // log, alert, or throw business exception
    throw new BusinessException("系统繁忙, 请稍后重试");
}

Running two threads shows successful retries after lock contention, and after exceeding retry limits the custom exception is thrown.

Thus, by applying database‑level pessimistic locks, configuring appropriate timeouts, and handling lock failures with retries, overselling can be reliably prevented in high‑concurrency Spring Boot applications.