Locking and concurrency control

The transactions lesson named Isolation as one of ACID's four guarantees without explaining the actual mechanism. This is that mechanism — locks, and the real choice between blocking a conflicting transaction versus detecting the conflict after the fact.

Advanced

5 min read

The concrete problem Isolation was naming abstractly

-- Two transactions, running at the same time, both against account id=1:
-- Transaction A: reads balance (500), plans to subtract 100
-- Transaction B: reads balance (500), plans to subtract 200
-- Both read 500 BEFORE either write — if both proceed naively, one update
-- silently overwrites the other, and 300 total gets lost instead of 500

This is the exact "lost update" scenario the transactions lesson's Isolation section described in the abstract — two concurrent transactions reading the same stale value and one overwriting the other's change. Locking is the actual mechanism databases use to prevent this from happening.

Row-level locks: the basic mechanism

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- explicitly locks this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;   -- lock released here

SELECT ... FOR UPDATE explicitly locks the selected row for the rest of the transaction — any other transaction trying to lock or update that same row has to wait until this transaction commits or rolls back and releases the lock. This is pessimistic locking: it assumes a conflict is likely, and prevents it upfront by blocking the second transaction entirely until the first one finishes, rather than letting both proceed and discovering a conflict afterward.

Why pessimistic locking has a real cost: blocked transactions wait

Transaction A: BEGIN; SELECT ... FOR UPDATE; -- (holds the lock)
Transaction B: BEGIN; SELECT ... FOR UPDATE; -- BLOCKS here until A commits/rolls back

The blocking is the entire point — it's what prevents the lost-update scenario — but it's also the real cost: Transaction B doesn't fail, it waits, potentially for a long time if Transaction A is slow or holds its lock longer than necessary. Under heavy concurrent load, this waiting can cascade — many transactions all queued up waiting for the same row — which is exactly why locks should be held for as short a time as possible, and why SELECT ... FOR UPDATE is deliberately explicit rather than automatic: locking more than genuinely necessary directly costs concurrency.

Deadlocks: two transactions waiting on each other, forever

Transaction A: locks row 1, then tries to lock row 2 (held by B) -- waits
Transaction B: locks row 2, then tries to lock row 1 (held by A) -- waits
-- Neither can ever proceed; each is waiting on the other

A deadlock happens when two transactions each hold a lock the other one needs, and each is waiting for the other to release it — neither can ever make progress. Databases detect this specific situation automatically and resolve it by forcibly rolling back one of the two transactions (returning a deadlock error to that transaction's caller), letting the other proceed. The practical takeaway: application code that catches transactions must be prepared to catch a deadlock error and retry, and acquiring locks in a consistent order across every code path (always lock the lower id first, for example) is the standard way to prevent deadlocks from happening in the first place.

Optimistic locking: assume no conflict, check before committing

-- The row has a version column
UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;   -- only succeeds if version hasn't changed since it was read
 
-- Application code checks how many rows were actually updated:
-- 0 rows updated => someone else modified this row first; handle the conflict
-- 1 row updated  => success, no conflict occurred

Optimistic locking takes the opposite bet: assume conflicts are rare, let both transactions proceed without blocking each other at all, and check for a conflict only at the moment of writing — a version column (or a timestamp) incremented on every update lets the final UPDATE include a WHERE version = ... clause naming the version that was originally read, which only matches (and succeeds) if nobody else updated the row in the meantime. If someone else did, the WHERE matches zero rows, the update silently does nothing, and the application code checking the affected-row count knows to handle the conflict — typically by re-reading the current state and retrying.

Pessimistic vs. optimistic: the actual trade-off

Pessimistic (SELECT ... FOR UPDATE):
  - Conflicts genuinely can't happen — the lock prevents them
  - Cost: transactions wait, sometimes a long time, under real contention

Optimistic (version column + conditional UPDATE):
  - No waiting at all — every transaction proceeds immediately
  - Cost: a real conflict is only discovered after the fact, requiring a retry

Pessimistic locking is the right choice when conflicts on the same row are genuinely frequent — waiting is cheaper than repeatedly discovering conflicts and retrying. Optimistic locking is the right choice when conflicts are rare — most transactions never actually collide, so paying zero waiting cost on the common case is worth occasionally having to retry the rare one. This mirrors a familiar shape from elsewhere in this app: it's the same kind of upfront-prevention-versus-detect-and-recover trade the idempotency lesson makes when comparing preventing a duplicate request outright versus detecting and handling one after the fact.