The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Web applications and performance

Stock decrement and locks

What if many orders arrive at once for 3 items in stock?

Several requests run "check the stock, subtract 1, create an order" at the same time. With no safeguard, more items are sold than are in stock, or the stock number no longer matches reality. A lock is the typical safeguard against such concurrent changes, and this lab compares the results of several ways of locking and of preventing the problem without a lock.

Checked against real execution · All five methods were run in 45 combinations on MySQL 8.4.11 and Redis and checked against the results

Concepts in this lab

Pessimistic locking

An approach that assumes a conflict will happen and locks the data first, at the time it is read. When you lock a row with SELECT ... FOR UPDATE, another transaction that tries to modify the row waits until the lock is released. It is reliable when conflicts are frequent, but it brings waiting and the possibility of deadlock.

Optimistic locking (@Version)

An approach that assumes conflicts are rare and takes no database lock. Instead, at update time, it uses a version number to check whether another transaction updated the data first in the meantime. If you put a @Version field on an entity, the version is compared on update, and an exception is thrown if it has already changed. When conflicts are not frequent, it keeps data consistent without lock waits.

Shared locks and exclusive locks

A shared lock (S) is a lock for reading, and several transactions can hold it at the same time. An exclusive lock (X) is a lock for writing, and only one transaction can hold it. While data has an exclusive lock, no other transaction can take a shared lock or an exclusive lock on it.

Distributed lock

A lock that lets only one process at a time perform a specific task among processes that run on several servers. Java's synchronized is valid only inside one server, so with several servers it is implemented by placing a "lock marker" in a store that all of them access (such as Redis). It is given an expiration time so that it is released even if the side that took it runs into a problem.

Race condition

This is a situation in which two or more threads read and write the same data at the same time, and the result depends on the order of execution. count++ consists of three steps: read the value, add 1, and write it back. If the steps of two threads are interleaved, one of the increments is lost. It typically stays hidden in tests and appears under load.

Try these

• Default settings — No safeguard. With 3 items in stock, all 4 orders succeed.
• Pessimistic locking — Only one request at a time reads and changes the stock row. The others wait in line.
• Optimistic locking — Everyone goes ahead, and when saving, a request fails if someone else changed the row first.
• UPDATE with a condition — The check and the decrement are combined into one SQL statement.
• Many orders with optimistic locking — 6 requests compete. Look at the number of retries.

Running with the default settings

1. Request 1: Reads the stock. It is 3. This is greater than 0, so the request goes ahead with the order.
2. Request 2: Reads the stock. It is 3. This is greater than 0, so the request goes ahead with the order. The earlier requests had not saved yet, so this one read the same value.
3. Request 3: Reads the stock. It is 3. This is greater than 0, so the request goes ahead with the order. The earlier requests had not saved yet, so this one read the same value.
4. Request 4: Reads the stock. It is 3. This is greater than 0, so the request goes ahead with the order. The earlier requests had not saved yet, so this one read the same value.
5. Request 1: Saves the value it read (3) minus 1 (2) as the stock, and creates the order.
6. Request 2: Saves the value it read (3) minus 1 (2) as the stock, and creates the order. It overwrote the value that an earlier request saved with the same value. The stock did not go down, but one more order was created.
7. Request 3: Saves the value it read (3) minus 1 (2) as the stock, and creates the order. It overwrote the value that an earlier request saved with the same value. The stock did not go down, but one more order was created.
8. Request 4: Saves the value it read (3) minus 1 (2) as the stock, and creates the order. It overwrote the value that an earlier request saved with the same value. The stock did not go down, but one more order was created.

Result of this run

• 4 orders were created, but the stock in the DB went down by only 1, from 3 to 2. 1 order was sold beyond the stock.
• This happens because another request can cut in between the read and the update. @Transactional alone does not prevent it. When requests that read the same value overwrite each other's changes, some of the changes are lost.

Code

@Transactional
public void order(long productId) {
    Product p = productRepository.findById(productId).orElseThrow();
    if (p.getStock() <= 0) throw new SoldOutException();
    p.setStock(p.getStock() - 1);        // subtract 1 from the value that was read
    orderRepository.save(new Order(productId));
}

Assumptions of this model

The model assumes the worst possible overlap of the requests (the order in which everyone reads first and then writes). In practice, only some requests overlap, so how far the result goes wrong is different every time. For optimistic locking, the retries assume that the requests that failed all try again at the same time. All five methods were run on a real MySQL 8.4.11, with a separate session opened for each order, in 45 combinations (stock of 1, 3, and 5 items; 2, 4, and 6 orders), and the number of orders created, the stock left, and the numbers of waits and retries were confirmed to match the screen (the execution order was arranged to produce the same "worst order" as the screen). The distributed lock was run with SET NX on a real Redis. The optimistic locking was run without going through JPA, using the same SQL that @Version produces (WHERE version = ?).

Previous lab: Connection pool and OSIV · Next lab: Querying directly into a DTO