Transactions and locking in depth series: 1. Transactions from the inside · 2. Harmful transactions · 3. Locks in InnoDB · 4. Optimistic, pessimistic and the booking lab
When many requests change the same row, should you lock first (pessimistic), detect conflicts at write time (optimistic), fold the check into the write (conditional update), or queue requests with a distributed lock? This post measures all four on a 100-seat flight with 400 concurrent requests, so you can pick by numbers rather than by gut feeling. Read part 3 (locks and contention) and transactions, isolation levels and locking (retrying correctly) first.
At a glance:
flowchart TD
A{"Contention level?"} -->|"Low"| B["Optimistic: check version"]
A -->|"Hot row"| C["Pessimistic: others wait"]
A -->|"Seat counter"| D["Conditional UPDATE"]
A -->|"Many instances"| G["Distributed lock with TTL"]
B --> E["Fail, then retry"]
C --> F["Lock in fixed id order"]
G --> H["Add version as fencing"]
1. Pessimistic and optimistic locking
Key idea: pessimistic locking locks before acting, so the second request waits; optimistic locking doesn’t lock on read and only checks at write time whether someone changed the row first, so the second request fails and retries.
- Pessimistic (assume conflicts will happen):
SELECT ... FOR UPDATE/FOR SHAREin the database,synchronizedorReentrantLockin Java. - Optimistic (assume conflicts are rare): a compare-and-swap (compare first, change only if it still matches) on a
versioncolumn.
SELECT available_seats, version FROM flight WHERE id = 1; -- 100, version 7
UPDATE flight SET available_seats = 99, version = version + 1
WHERE id = 1 AND version = 7; -- 1 row: you won; 0 rows: someone got there first
With JPA all you need is a @Version field. Hibernate adds AND version = ? to the UPDATE and throws OptimisticLockException when 0 rows are affected (Spring translates it into ObjectOptimisticLockingFailureException).
Why not use a timestamp as the version
The slides also stress: database locks are not programming-language locks. synchronized only works inside one JVM; two instances behind a load balancer means two independent locks, and the race condition is back.
2. Best practices, each one explained
Key idea: lock as little and as briefly as possible, through the primary key; use optimistic locking when contention is low; when you add a distributed lock, still keep version in the database as the last line of defence.
- Subtract first, add later. In a transfer, the conditional debit (
amount >= x) runs first: if there isn’t enough money you stop before touching the receiving account, fewer locks are held for nothing, and money never “appears” at B before leaving A. - Lock through the primary key. Part 3 measured it: an equality match on the primary key takes a single record lock at both RR and RC.
- Avoid
UPDATEs that filter on a non-unique secondary index. They take two sets of locks plus gap locks (locks on the gaps between values, part 3). A common pattern: find the primary keys with a plainSELECT, thenUPDATE ... WHERE id IN (...)and repeat the business condition in theWHERE, because data may change between the two steps. It’s an optimization for hot tables, not a ban. - Examine the real locks. Before putting a statement on a hot path, run it in a transaction and read
data_locks. - Prefer optimistic locking when contention is low (editing a profile, editing an unpaid order).
- Combine a distributed lock with optimistic locking. The lock (Redis, for example) queues requests before they reach the database and protects work outside the DB too. It can expire mid-flight (a GC pause, a short TTL, a slow network), so
versionacts as a fencing token: it rejects the write of whoever lost the lock. The exercise measures exactly this; the post reviewing a Redis distributed lock analyses how to implement the Redis lock.
Exercises
Exercise 1: Flight booking with several locking strategies
Task (paraphrased): implement booking in two steps: create a booking and decrement the flight’s available seats. The basic version uses pessimistic locking in the database; the advanced version combines optimistic locking (a version column) with a distributed lock.
Setup: 7 strategies on the same harness: a 100-seat flight, 400 requests each booking 1 seat, 32 threads, one connection per thread; MySQL 8.4.11 and Redis 7 in Docker, Java 21 + JDBC. The invariant checked at the end: bookings + seats left = 100 and seats left >= 0.
Results of one run (numbers vary between runs; the trends are stable):
| Strategy | Bookings | Seats left | Invariant | Sold out | Gave up | Retries | Time |
|---|---|---|---|---|---|---|---|
| 1. Naive read-then-write | 400 | 87 | BROKEN | 0 | 0 | 0 | 571 ms |
2. Pessimistic FOR UPDATE |
100 | 0 | OK | 300 | 0 | 0 | 266 ms |
3. Conditional UPDATE |
100 | 0 | OK | 300 | 0 | 0 | 203 ms |
| 4. Optimistic (5 attempts) | 100 | 0 | OK | 124 | 176 | 1,028 | 484 ms |
| 5. Redis lock + optimistic | 100 | 0 | OK | 300 | 0 | 0 | 472 ms |
| 6. Redis TTL 5 ms, no version | 311 | 0 | BROKEN | 89 | 0 | 0 | 1,997 ms |
| 7. Redis TTL 5 ms + version | 100 | 0 | OK | 89 | 211 | 1,257 | 2,351 ms |
Three things to remember: naive sold 400 tickets for 100 seats, optimistic wrongly turned away 176 customers, and when the lock expires mid-flight (6, 7) only version keeps the invariant.
Solution to Exercise 1: JDBC code for the strategies and a row-by-row analysis
// 2. Pessimistic: lock the flight row while deciding
static Outcome pessimistic(Connection c, long flightId, long userId) throws SQLException {
c.setAutoCommit(false);
try {
int available = queryInt(c, "SELECT available_seats FROM flight WHERE id = ? FOR UPDATE", flightId);
if (available <= 0) { c.rollback(); return Outcome.SOLD_OUT; }
update(c, "UPDATE flight SET available_seats = available_seats - 1 WHERE id = ?", flightId);
update(c, "INSERT INTO booking(flight_id, user_id) VALUES (?, ?)", flightId, userId);
c.commit();
return Outcome.BOOKED;
} catch (SQLException e) { c.rollback(); throw e; }
}
// 4. Optimistic: compare the version on write, retry a bounded number of times
static Outcome optimistic(Connection c, long flightId, long userId, int maxAttempts, long workMillis)
throws SQLException, InterruptedException {
c.setAutoCommit(false);
for (int attempt = 1; attempt <= maxAttempts; attempt++) {
try {
int[] row = queryTwoInts(c, "SELECT available_seats, version FROM flight WHERE id = ?", flightId);
int available = row[0], version = row[1];
if (available <= 0) { c.rollback(); return Outcome.SOLD_OUT; }
if (workMillis > 0) Thread.sleep(workMillis); // pricing, a slow service call...
int rows = update(c,
"UPDATE flight SET available_seats = ?, version = version + 1 WHERE id = ? AND version = ?",
available - 1, flightId, version);
if (rows == 1) {
update(c, "INSERT INTO booking(flight_id, user_id) VALUES (?, ?)", flightId, userId);
c.commit();
return Outcome.BOOKED;
}
c.rollback(); // someone else won: re-read
Thread.sleep(ThreadLocalRandom.current().nextLong(1, 5L * attempt));
} catch (SQLException e) { c.rollback(); throw e; }
}
return Outcome.GAVE_UP;
}
// 5. Distributed lock (Redis) around it, with the version as a fencing token
static Outcome lockedOptimistic(Connection c, Redis redis, long flightId, long userId,
long ttlMillis, long workMillis) throws Exception {
String key = "lock:flight:" + flightId, token = UUID.randomUUID().toString();
long deadline = System.currentTimeMillis() + 5_000;
while (!redis.setNxPx(key, token, ttlMillis)) { // SET key token NX PX ttl
if (System.currentTimeMillis() > deadline) return Outcome.GAVE_UP;
Thread.sleep(ThreadLocalRandom.current().nextLong(1, 4));
}
try {
return optimistic(c, flightId, userId, 5, workMillis);
} finally {
redis.releaseIfOwner(key, token); // Lua: DEL only if the value is still our token
}
}
The other strategies:
- (1) naive: read, then write
available_seats = <value read> - 1. - (3) conditional:
UPDATE flight SET available_seats = available_seats - 1 WHERE id = ? AND available_seats > 0, then check the affected rows. - (6) a Redis lock with a 5 ms TTL while the work takes 10 ms, without a version check. (7) like (6) but with the version check.
The Redis client is a minimal RESP class I wrote (SET ... NX PX plus a compare-and-delete Lua script).
Row by row:
- Naive sold 400 tickets and still reported 87 seats left: overselling and a lost update on the counter at the same time.
- Pessimistic is correct and simple; 32 threads queue on one row, but each holds the lock very briefly.
- Conditional update is the fastest because the check and the write are one statement, and the lock is held only for the
UPDATE+INSERT. For a counter, this is the default choice. - Optimistic: every winner leaves about 31 losers behind. 176 customers were told “try again later” while seats were still available (only 124 got a genuine “sold out”). Exactly what the slides say: optimistic locking is for low contention.
- Redis lock + optimistic: zero retries because Redis queued requests first, but twice as slow as the conditional update, and one more system to run. Worth it when the work inside the lock includes things outside the database (calling a partner airline, holding inventory at a third party).
- 6 and 7: when the lock expires before the work is done, two requests both “hold” it; without a version, 311 tickets were sold. With a version, the database stops it: I counted 324 cases where a request holding the lock (believing it was exclusive) was still rejected by the version check.
The same conditional update prevents overselling stock in the cart and checkout post: UPDATE ... WHERE stock >= :qty runs in the same transaction that creates the order, and running out rolls the whole order back.
The retry for optimistic locking must wrap the transaction from the outside (a loop calling TransactionTemplate, or @Retryable on another bean wrapping the @Transactional method), as the retry section of transactions and locking explains.
A Spring Boot 3 version of pessimistic locking and the conditional update
Written with Spring Data JPA, not run; the equivalent logic was run with JDBC above.
public interface FlightRepository extends JpaRepository<Flight, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE) // SELECT ... FOR UPDATE
@Query("select f from Flight f where f.id = :id")
Optional<Flight> findForUpdate(@Param("id") long id);
@Modifying
@Query("update Flight f set f.availableSeats = f.availableSeats - 1 where f.id = :id and f.availableSeats > 0")
int decrementIfAvailable(@Param("id") long id); // conditional update
}
@Entity
class Flight {
@Id Long id;
int availableSeats;
@Version int version; // optimistic: Hibernate adds "and version = ?"
}
Exercise 2: The four conditions for deadlock and a simple way to avoid it
Task: state the four conditions for a deadlock and derive a simple way to avoid it.
Short answer: a deadlock needs all four Coffman conditions; breaking one is enough, and the simplest is locking in a fixed order so there is no circular wait.
Solution to Exercise 2: the four Coffman conditions and how to break them in a database
The four conditions: mutual exclusion (a lock is held by one party), hold and wait (holding one lock while requesting another), no preemption (nobody can take a lock away from its holder), and circular wait.
| Condition | How to break it in a database |
|---|---|
| Circular wait | Lock in a fixed order: SELECT ... WHERE id IN (?, ?) ORDER BY id FOR UPDATE, transfers always lock the lower id first. The simplest fix. |
| Hold and wait | Take all locks in one statement at the start of the transaction, or use a single conditional update instead of read-lock-write in several steps |
| No preemption | The database does this: InnoDB’s deadlock detector picks a victim and rolls it back (error 1213); NOWAIT/a lock wait timeout lets the waiter give up |
| Mutual exclusion | MVCC: plain reads need no locks, so they never take part in a deadlock |
Example, a connecting-flight booking (two legs are two flight rows): lock both with one ORDER BY id FOR UPDATE statement, then decrement both. Still keep a retry loop for error 1213, because some other code path might lock in a different order. The post on race conditions, volatile and deadlocks applies the same four conditions to locks in Java.
Remember
- Explain the core difference between pessimistic and optimistic locking: wait, or fail and retry.
- Know when a conditional update beats both, and why optimistic locking wrongly rejects users on a hot row.
- Explain why a distributed lock needs
versionas a fencing token, with the 311-tickets number. - Know why
synchronizedand timestamps aren’t enough for optimistic locking across several instances. - State the four deadlock conditions and why “subtract first, add later” doesn’t prevent deadlocks.
Interview questions
Junior
Why can’t synchronized fix a race condition on database writes?
Because synchronized only locks within one JVM, while the data lives in a database shared by every instance.
Two instances behind a load balancer have two independent locks, so two requests still read and write together. Protect it in the database (conditional statements, FOR UPDATE, versions) or with a distributed lock.
What is the core difference between pessimistic and optimistic locking?
On conflict, pessimistic locking makes the second request wait; optimistic locking makes it fail and retry.
Pessimistic locking locks before acting. Optimistic locking doesn’t lock on read and only checks at write time (usually with a version column). One pays in waiting time, the other in redone work.
Mid
How does optimistic locking with a version column work?
Write with a condition that version still equals the value you read, and bump it; zero affected rows means someone changed it first.
On write: UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?. One row means you won; zero means re-read and retry (or tell the user). With JPA you just add @Version, and Hibernate throws OptimisticLockException when zero rows are affected.
Why shouldn’t you use a timestamp instead of a version for optimistic locking?
Because timestamps can collide or go backwards, so conflicts slip through.
Time columns often have only one-second precision, so two updates in the same second get the same value. If the application sets the time, machine clocks differ and can go backwards. An ever-increasing integer version has none of these problems.
Senior
When do you choose optimistic and when pessimistic? Give an example with numbers.
By contention: when it’s low, optimistic is nearly free; on a hot row, use pessimistic or, better, a conditional update.
Editing a user profile: optimistic. A flight’s seat counter: in my experiment with 400 requests for 100 seats, optimistic took over 1,000 retries and 176 requests gave up while seats were still available, whereas FOR UPDATE needed no retries. A single conditional update beats both.
Why do you need both a distributed lock and optimistic locking, not just one?
The lock reduces conflicts and protects work outside the database; the version rejects the write of whoever lost the lock when it expires mid-flight.
A distributed lock has a TTL, so a GC pause or a slow network can leave two parties both believing they hold it. The version in the database is a fencing token. In my experiment with a TTL shorter than the work: without a version, 311 tickets were sold for 100 seats; with a version, exactly 100.
Does the “subtract first, add later” rule for transfers prevent deadlocks?
No. Preventing deadlocks needs a fixed lock order (lower id first), plus a retry loop for error 1213.
Two transfers in opposite directions, A→B and B→A, both debiting first, each lock one account and then wait for each other. “Subtract first” only lets you stop early on insufficient funds and never shows money at the receiver before it leaves the sender.
Useful phrases for interviews
- “I pick the locking strategy by contention: optimistic when conflicts are rare, pessimistic or a conditional update when one row is hot.”
- “For a counter, I fold the check into the UPDATE and look at the affected row count.”
- “Optimistic locking still takes a row lock during the UPDATE; it just doesn’t hold one while the user is thinking.”
- “On a hot row, optimistic locking turned into a retry storm and rejected customers while seats were still available.”
- “A Redis lock can expire under a GC pause, so I keep a version column as a fencing token in the database.”
- “Lock ordering by id breaks the circular-wait condition, which is the simplest way to avoid deadlocks.”
- “synchronized only protects one JVM; shared data in the database needs a database-level guarantee.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.