🔒Lock contention
Queues, not parallelism
Lock contention happens when transactions wait on each other's locks, symptoms are queueing, timeouts, and rising p99 latencies. Locks serialize conflicting operations on tuples, pages, or predicates. Hot rows (global counters, single parent records) serialize updates; long reads under SERIALIZABLE may block writers; metadata locks block DDL. Shard hot counters into many rows, use queues, or move aggregates to OLAP, do not let one row become a global mutex.
Mitigations include sharding counters and using SELECT … FOR UPDATE SKIP LOCKED for work queues, or redesigning invariants so writers do not pile onto the same key.
PostgreSQL: who blocks whom
SELECT blocked_locks.pid AS blocked_pid,
blocking_locks.pid AS blocking_pid,
blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;| blocked_pid | blocking_pid | blocked_query |
|---|---|---|
| 4821 | 4799 | UPDATE wallets SET balance = balance - 10 WHERE user_id = 9 |
Common mistakes
- Polling UPDATE counters in tight loops.
- Running migrations that need ACCESS EXCLUSIVE during traffic.
- Using SELECT FOR UPDATE without ordering locks consistently.
Operational habits
- Visualize waits with engine dashboards (
pg_locks,sys.dm_tran_locks). - Batch updates to hot keys or use approximate counts where acceptable.
- Standardize lock acquisition order across modules.
- Alert on wait time percentiles, not only deadlocks.
🏗️Lock Hierarchy: Intent Locks
How the engine avoids checking 1,000,000 rows for a table lock.
Intent Shared (IS) and Intent Exclusive (IX)
If you want to lock a row, the engine first places an Intent lock on the Table and Page. IX means "I intend to update a row in this table." If some other process wants a SHARE lock on the whole table (e.g., for an index build), it sees the IX and waits. This prevents the "Scan all rows" problem for the Lock Manager.
| Lock Type | Compatibility | Mechanical Purpose |
|---|---|---|
| Shared (S) | Compatible with S, IS | Read access. Multiple readers can share. |
| Exclusive (X) | None | Write access. Blocks everything else. |
| Intent (IS/IX) | Compatible with other Intents | Flags that a lower-level (row) lock exists. |
🕸️Deadlock Detection: The Wait-For Graph
Circular dependencies are fatal.
When Transaction A waits for B, and B waits for A, they are in a deadlock. The DB lock manager periodically scans the wait-for graph for cycles. When it finds one, it aborts one transaction (often the one that has done the least work, or the most recent) to break the cycle.
FOR UPDATE SKIP LOCKED
For work queues, SELECT … FOR UPDATE SKIP LOCKED lets workers skip rows another session already locked, scaling consumers without piling up on the same keys.
TL;DR
Lock contention happens when concurrent transactions need the same rows or index ranges and one must wait (or abort). Symptoms: rising wait events, queue depth on hot keys, timeouts under load. Mitigations: shorten transactions, index access paths to reduce scan locks, avoid random hot-row updates, batch work, and tune isolation, measure waits, do not guess.