Locking, MVCC & Deadlocks
This is how isolation (Chapter 04) is actually implemented under the hood. It's also where "it worked in dev, it deadlocked in production under load" bugs are born and understood.
◆ Story — two customers, one concert ticket
Two customers try to buy the last ticket for a concert at the exact same moment. Two fundamentally different philosophies exist for handling this:
| Pessimistic locking | Optimistic locking | |
|---|---|---|
| Philosophy | "Conflicts are likely — lock the row the instant I read it for update, so nobody else can touch it until I'm done." | "Conflicts are rare — proceed without locking, but check at the end whether anyone else changed it first." |
| Mechanism | SELECT... FOR UPDATE — blocks other transactions from acquiring the same lock | A version column checked and incremented on write; fails if the version changed since you read it |
| Best for | High-contention resources (concert tickets, flash sales) where conflicts are the common case | Low-contention resources (most typical CRUD updates) where conflicts are rare and retrying is cheap |
| Cost | Reduces concurrency — the second customer's transaction simply waits | Higher concurrency, but requires handling the failure/retry case explicitly |
BEGIN; SELECT * FROM tickets WHERE id = 501 FOR UPDATE; -- locks this row; any other transaction's -- own FOR UPDATE on row 501 now BLOCKS and waits UPDATE tickets SET status = 'SOLD' WHERE id = 501; COMMIT; -- the lock is released; the waiting transaction proceeds and sees status='SOLD'
public interface TicketRepository extends JpaRepository<Ticket, Long> { @Lock(LockModeType.PESSIMISTIC_WRITE) @Query("SELECT t FROM Ticket t WHERE t.id = :id") Optional<Ticket> findByIdForUpdate(@Param("id") Long id); }
💻 Code example
BEGIN; SELECT * FROM tickets WHERE id = 501 FOR UPDATE; -- locks this row; any other transaction's -- own FOR UPDATE on row 501 now BLOCKS and waits UPDATE tickets SET status = 'SOLD' WHERE id = 501; COMMIT; -- the lock is released; the waiting transaction proceeds and sees status='SOLD'
@Entity public class Ticket { @Id @GeneratedValue private Long id; private String status; @Version // Hibernate manages this automatically private Long version; }
@Transactional public void buyTicket(Long ticketId) { Ticket ticket = ticketRepository.findById(ticketId).orElseThrow(); // reads version=3, say ticket.setStatus("SOLD"); // on flush/commit, Hibernate runs: // UPDATE tickets SET status='SOLD', version=4 WHERE id=? AND version=3 // if another transaction already bumped version to 4 first, 0 rows match — // Hibernate throws OptimisticLockException instead of silently overwriting }
▲ Common mistake
Catching OptimisticLockException and simply ignoring it (or logging and swallowing it) leaves the user's action silently lost with no feedback — the whole point of optimistic locking is to detect a conflict, not prevent it; you must handle the exception explicitly, typically by retrying the operation against fresh data or informing the user someone else changed the resource first.
💻 Code example
@Entity public class Ticket { @Id @GeneratedValue private Long id; private String status; @Version // Hibernate manages this automatically private Long version; }
◆ Under the hood
MVCC is the reason PostgreSQL readers never block writers and writers never block readers. Instead of locking a row for a read, every row physically stores which transaction created it and (if applicable) which transaction deleted/superseded it. When your transaction reads a row, it doesn't ask "what's the current value" — it asks "what did this row look like as of my transaction's snapshot," filtering out versions created by transactions that started after you did, or that haven't committed. This is why a long SELECT in one transaction never blocks an UPDATE in another: they're working with different versions of the row entirely, never contending for the same lock.
MVCC keeps multiple versions of a row simultaneously — each transaction reads the version consistent with its own snapshot, so readers and writers never block each other.
◆ Story
Transaction A locks Account 1, then tries to lock Account 2. At the exact same moment, Transaction B locks Account 2, then tries to lock Account 1. Each is now waiting for a lock the other one holds, and neither will ever release its own lock until it gets the other — a permanent standoff. This is a deadlock.
-- Transaction A -- Transaction B BEGIN; BEGIN; UPDATE accounts SET ... UPDATE accounts SET ... WHERE id = 1; WHERE id = 2; -- A now holds lock on row 1 -- B now holds lock on row 2 UPDATE accounts SET ... UPDATE accounts SET ... WHERE id = 2; -- BLOCKS WHERE id = 1; -- BLOCKS -- waiting for B to release row 2 -- waiting for A to release row 1 — DEADLOCK
◆ Under the hood — deadlock detection
The database runs a background deadlock detector that periodically builds a "wait-for graph" (who's waiting for whom) and looks for a cycle. When it finds one, it doesn't wait forever — it picks one of the two transactions as the "victim," forcibly rolls it back, and lets the other proceed with its lock now free. The victim's application code receives a deadlock exception and must handle it (usually by retrying the whole transaction).
@Retryable(retryFor = DeadlockLoserDataAccessException.class, maxAttempts = 3, backoff = @Backoff(delay = 100)) @Transactional public void transfer(Long fromId, Long toId, BigDecimal amount) { // if this transaction is picked as the deadlock victim, Spring Retry re-attempts // the whole method automatically, up to 3 times }
▲ Common mistake — and the actual fix
The most common real-world cause of deadlocks isn't bad luck — it's inconsistent lock ordering: two different code paths locking the same two rows in a different order (A-then-B in one place, B-then-A in another). The reliable fix isn't retry logic (a mitigation) but always locking rows in a consistent order — e.g. always lock the account with the lower ID first, everywhere in the codebase, which makes the cyclic wait structurally impossible rather than just recoverable.
💻 Code example
-- Transaction A -- Transaction B BEGIN; BEGIN; UPDATE accounts SET ... UPDATE accounts SET ... WHERE id = 1; WHERE id = 2; -- A now holds lock on row 1 -- B now holds lock on row 2 UPDATE accounts SET ... UPDATE accounts SET ... WHERE id = 2; -- BLOCKS WHERE id = 1; -- BLOCKS -- waiting for B to release row 2 -- waiting for A to release row 1 — DEADLOCK
Want a visual for this concept?
Generate a diagram tailored to “Locking, MVCC & Deadlocks” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.
Sign in to generate a visual →