Implementing FCFS with DB Locks: From FOR UPDATE to Concurrency Testing

Implementing FCFS with DB Locks: From FOR UPDATE to Concurrency Testing

Introduction

In the previous post, we compared 6 implementation strategies for FCFS systems. This post implements the simplest one — DB pessimistic locks (SELECT FOR UPDATE).

We’ll build it in code, test with 100 concurrent buyers, and see exactly where the limits are.

1. Why Start with DB Locks?

DB locks are the most fundamental FCFS implementation.

  • Works with just a database — no extra infrastructure
  • Lets you see concurrency problems firsthand in code
  • Establishes a baseline for understanding why Redis or queues become necessary

To understand why a technology is needed, try building without it first.

2. The Problem: Stock Deduction Without Locks

Two users trying to buy the last item simultaneously:

StepTX1 (Order A)TX2 (Order B)Stock
1SELECT stock FROM products WHERE id = 1 → 11
2SELECT stock FROM products WHERE id = 1 → 11
3stock > 0? yes → UPDATE stock = 00
4COMMIT0
5stock > 0? yes (read 1) → UPDATE stock = -1 💀-1
6COMMIT-1

Stock went negative. Both transactions read the same value (1) and deducted independently — a Lost Update.

3. The Fix: SELECT FOR UPDATE

Adding FOR UPDATE places an exclusive lock on the row. Other transactions can’t read or modify it — they wait.

StepTX1 (Order A)TX2 (Order B)Stock
1SELECT stock FOR UPDATE → 1 (row locked 🔒)1
2SELECT stock FOR UPDATE → waiting ⏳1
3stock > 0 → UPDATE stock = 00
4COMMIT (lock released 🔓)0
5→ 0 (latest value!) → sold out0
6ROLLBACK0

TX2 waits until TX1 finishes, then reads the latest stock (0) and handles it as sold out. No overselling.

4. Implementation with Spring Boot + JPA

4.1 Entity

@Entity
@Table(name = "products")
public class Product {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private int stockQuantity;

    @Enumerated(EnumType.STRING)
    private ProductStatus status; // ON_SALE, SOLD_OUT

    @Version
    private Long version;

    public void decreaseStock(int quantity) {
        if (this.stockQuantity < quantity) {
            throw new RuntimeException("Insufficient stock");
        }
        this.stockQuantity -= quantity;
        if (this.stockQuantity == 0) {
            this.status = ProductStatus.SOLD_OUT;
        }
    }
}

Stock deduction logic lives inside the entity. Throws an exception if stockQuantity < quantity to prevent negative stock.

4.2 Repository: FOR UPDATE Query

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Optional<Product> findByIdForUpdate(@Param("id") Long id);
}

@Lock(LockModeType.PESSIMISTIC_WRITE) — the actual SQL JPA generates:

SELECT * FROM products WHERE id = ? FOR UPDATE

With QueryDSL:

Product product = queryFactory
    .selectFrom(QProduct.product)
    .where(QProduct.product.id.eq(id))
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .fetchOne();

Either way, the result is the same — an exclusive lock on the row.

4.3 Service: Lock + Deduct

@Service
public class PessimisticLockStockService {
    private final ProductRepository productRepository;

    @Transactional
    public void decreaseStock(Long productId, int quantity) {
        // 1. Lock the row + read
        Product product = productRepository.findByIdForUpdate(productId)
            .orElseThrow(() -> new RuntimeException("Product not found"));

        // 2. Deduct stock (throws if insufficient)
        product.decreaseStock(quantity);

        // 3. On transaction commit: UPDATE executes + lock releases
    }
}

The core is 3 lines:

  1. findByIdForUpdate — lock and read the row
  2. decreaseStock — deduct stock (entity method)
  3. When @Transactional ends — JPA dirty checking fires the UPDATE, commit releases the lock

5. Concurrency Testing

“Does it really work when 100 people request simultaneously?” — Let’s verify.

5.1 Test Structure

@SpringBootTest
class PessimisticLockStockConcurrencyTest {

    @Autowired
    PessimisticLockStockService stockService;

    @Autowired
    ProductRepository productRepository;

    @Test
    @DisplayName("100 concurrent purchases: stock becomes exactly 0")
    void concurrentPurchase_100users() throws InterruptedException {
        // Create product with 100 stock
        Product product = productRepository.save(
            new Product("Limited Edition Sneakers", 100, ProductStatus.ON_SALE)
        );

        int threadCount = 100;
        ExecutorService executor = Executors.newFixedThreadPool(32);
        CountDownLatch latch = new CountDownLatch(threadCount);
        AtomicInteger successCount = new AtomicInteger(0);
        AtomicInteger failCount = new AtomicInteger(0);

        long startTime = System.currentTimeMillis();

        for (int i = 0; i < threadCount; i++) {
            executor.submit(() -> {
                try {
                    stockService.decreaseStock(product.getId(), 1);
                    successCount.incrementAndGet();
                } catch (Exception e) {
                    failCount.incrementAndGet();
                } finally {
                    latch.countDown();
                }
            });
        }

        latch.await();
        executor.shutdown();
        long elapsed = System.currentTimeMillis() - startTime;

        Product updated = productRepository.findById(product.getId()).get();

        System.out.println("Success: " + successCount.get());
        System.out.println("Failed: " + failCount.get());
        System.out.println("Final stock: " + updated.getStockQuantity());
        System.out.println("Elapsed: " + elapsed + "ms");

        assertEquals(100, successCount.get());
        assertEquals(0, updated.getStockQuantity());
    }
}

CountDownLatch makes the test wait until all threads finish. A pool of 32 threads processes 100 tasks to simulate concurrent requests.

5.2 Results

=== Pessimistic Lock (FOR UPDATE) Concurrency Test ===
Concurrent requests: 100
Success: 100
Failed: 0
Final stock: 0
Elapsed: 851ms
=====================================================

100 concurrent requests and stock is exactly 0. No overselling, no negative stock.

5.3 Over-Demand Test

What about 150 buyers competing for 100 items?

=== Pessimistic Lock (FOR UPDATE) Over-Demand Test ===
Concurrent requests: 150
Success: 100
Failed (sold out): 50
Final stock: 0
Elapsed: 816ms
=====================================================

Exactly 100 succeed, 50 get sold-out errors. Perfect data consistency.

6. The Limits: Why This Alone Isn’t Enough

Test results look perfect. But in production, three bottlenecks emerge.

6.1 Serialization Bottleneck

FOR UPDATE processes one transaction at a time on that row.

1,000 concurrent users → FOR UPDATE → 1 processes, 999 wait

50ms per transaction × 1,000 = up to 50s wait
200ms per transaction × 10,000 = up to 2,000s (33 min) wait 💀

In our test, 100 users finished in 851ms. But production transactions include payment API calls, order creation, and event publishing. The longer the transaction, the worse the wait.

6.2 Connection Pool Exhaustion

Transactions waiting for locks hold DB connections. HikariCP default pool size is 10:

100 concurrent users → FOR UPDATE → all 10 connections waiting for locks
→ Request #11 → no connection available → HikariCP timeout → Error!

Even normal queries (product listings, user pages) can’t get connections — the entire service slows down.

6.3 Deadlocks

If a single order deducts stock for multiple products:

StepTX1TX2Status
1Lock product A
2Lock product B
3Waiting for product B ⏳
4Waiting for product A ⏳💀 Deadlock!

Deadlock prevention (consistent lock ordering, timeouts) was covered in Part 2.

6.4 Realistic Thresholds

ScenarioDB locks sufficient?
Internal company event (50 concurrent)✅ Fine
Small e-commerce (hundreds concurrent)⚠️ Need connection pool tuning
Limited-edition drop (thousands concurrent)❌ Need Redis
Concert ticketing (tens of thousands)❌ Need queue + Redis

7. Alternative: Atomic UPDATE

Instead of FOR UPDATE, you can use a lock-free atomic UPDATE.

UPDATE products
SET stock_quantity = stock_quantity - 1,
    sales_count = sales_count + 1
WHERE id = 1
AND stock_quantity >= 1
AND status = 'ON_SALE'

The WHERE stock_quantity >= 1 condition prevents negatives. If zero rows are updated, it’s sold out.

@Transactional
public void decreaseStockAtomic(Long productId, int quantity) {
    int updated = productRepository.decreaseStockAtomically(productId, quantity);
    if (updated == 0) {
        throw new RuntimeException("Insufficient stock or product not found");
    }
}

FOR UPDATE vs Atomic UPDATE

AspectFOR UPDATEAtomic UPDATE
Lock typeRow lock (exclusive)No lock (atomic via WHERE clause)
ConcurrencySerial (one at a time)Multiple transactions can attempt simultaneously
Stock readingReads latest value, enables business logicNo need to read current stock
Complex validationCan validate beyond stock countOnly conditions in WHERE clause
PerformanceWait time grows with trafficFaster (no lock waiting)

For simple stock deduction, Atomic UPDATE is more efficient. But when you need to “read stock → run complex business logic → then deduct”, FOR UPDATE is necessary.

Summary

Key PointDetails
FOR UPDATE’s roleLock the row, block other transactions
Implementation core@Lock(PESSIMISTIC_WRITE) + @Transactional
Concurrency test resultsPerfect data consistency with 100 concurrent requests
LimitsSerialization bottleneck, connection pool exhaustion, deadlock risk
Realistic thresholdSuitable for up to a few dozen concurrent users
AlternativeAtomic UPDATE for improved simple deduction performance

DB locks are the starting point for understanding concurrency. The next post goes beyond DB limits to cover handling tens of thousands of requests per second with Redis.

This post is part of the Coupang Partners program, and a commission is earned from qualifying purchases.