Locking & Concurrency

What is it: Locks control concurrent access to shared data. When two transactions try to modify the same row simultaneously, locking prevents them from interfering.

Why: Without locks, two transactions could each read a balance, compute a new value, and write back — with the second write overwriting the first (lost update). Locks serialize conflicting operations.

Row-level vs table-level lock: Row-level locks are acquired per row on UPDATE, DELETE, SELECT FOR UPDATE. Only the specific rows being modified are locked — other transactions can freely modify other rows in the same table. Table-level locks (LOCK TABLE) prevent all access to the table, blocking all concurrent writers. PostgreSQL uses row-level locks by default; table locks are typically used for schema changes (DDL operations like ALTER TABLE take an ACCESS EXCLUSIVE lock).

Shared (read) vs exclusive (write) lock: A shared lock (S lock) allows multiple transactions to hold it simultaneously — multiple readers can coexist. An exclusive lock (X lock) requires no other lock to be held — one writer, no concurrent readers or writers on the same row. MVCC means readers in PostgreSQL never take shared locks — they read the snapshot version. SELECT FOR UPDATE takes an exclusive row lock, preventing other SELECT FOR UPDATE and UPDATE on the same row.

What happens when multiple users update the same row: The second UPDATE blocks until the first transaction commits or rolls back. On commit of T1: T2's UPDATE proceeds against the now-current version. On rollback of T1: T2's UPDATE proceeds against the original version. Under Read Committed, T2 re-reads the current committed value of the row before applying its change — this is "read-latest-committed" semantics, which prevents one class of anomalies but allows non-repeatable reads.

Deadlock:

graph LR T1["T1: holds lock on Row A"] -->|"waiting for"| B["Row B (locked by T2)"] T2["T2: holds lock on Row B"] -->|"waiting for"| A["Row A (locked by T1)"]

T1 locks row A and waits for row B; T2 locks row B and waits for row A. PostgreSQL detects deadlocks by periodically checking the lock dependency graph (after a deadlock_timeout, default 1 second). On detection, it aborts one of the transactions (the one with lower priority, or the one whose abort is cheapest). The aborted transaction receives error code 40P01 and must retry.

Prevention: always acquire locks in a consistent global order. If every transaction acquires row A before row B, deadlocks between A and B are impossible.

Optimistic concurrency control: Instead of locking upfront, attempt the update and check if any conflict occurred. In SQL: UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance = 500 — if another transaction changed the balance, this matches 0 rows. The application checks rows_affected == 0 and retries. No blocking, no deadlock risk. Better for low-contention workloads; worse for high-contention (many retries).

Real-world usage: In gRPC-wallet, debit operations use SELECT FOR UPDATE to serialize concurrent transfers on the same account, preventing lost updates on balance without resorting to table-level locks.

Common pitfall

Acquiring row locks in different orders across different code paths is the single most common cause of production deadlocks — code path A does UPDATE accounts WHERE id=1 then id=2; code path B (a transfer in the other direction) does id=2 then id=1. Individually each path is correct; run concurrently, they deadlock roughly as often as both paths happen to interleave. The fix from the "Prevention" note above is concrete: always sort the IDs being locked (ORDER BY id ASC) before issuing the updates, so every code path acquires locks in the same global order regardless of which "direction" the business operation is conceptually going.