Reduce row-lock contention
Lock contention turns independent writers into a serial queue. Each transaction holds a row lock until commit, so a long transaction on a hot row stalls every writer behind it. Shrink the lock window, order the locks, or avoid the lock entirely.
Procedure
- Find the contention:
SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity
WHERE wait_event_type = 'Lock' GROUP BY 1,2;
SELECT relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;
- Shorten the transaction: do network calls, sleeps, and I/O before
BEGINor outside the transaction, so locks are held for microseconds. - Lock rows in a consistent order (e.g. ascending id) everywhere, so two transactions touching the same rows cannot deadlock.
- For a queue, use
FOR UPDATE SKIP LOCKEDso workers take different rows instead of blocking:
SELECT id FROM jobs WHERE status = 'queued'
ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 10;
- For counters, drop
SELECT ... FOR UPDATE; UPDATEin favour of one atomic statement that holds the lock only for the update:
UPDATE counters SET n = n + 1 WHERE id = $1;
- For low-conflict updates, use optimistic concurrency and retry on zero rows affected:
UPDATE docs SET body=$2, version=version+1 WHERE id=$1 AND version=$3;
- Use
NOWAITwhen a caller can do something better than wait, converting a block into a fast failure it handles.
Pitfalls
- A transaction that makes a network call or waits on a user between
BEGINandCOMMIT, holding locks for seconds. - Inconsistent lock ordering, producing deadlocks the database resolves by killing one transaction.
SELECT ... FOR UPDATEover a wide set, locking rows the transaction then discards.SKIP LOCKEDon anything that needs every row; it silently skips and returns partial results.- Expecting an index to remove contention when the same hot row is still locked — the contention is logical, not I/O.
- A long analytics query holding
AccessShareLockand blockingALTER TABLE; run DDL with a shortlock_timeout.
Verification
psql -c "SELECT count(*) FROM pg_locks WHERE NOT granted;"
psql -c "SELECT wait_event, count(*) FROM pg_stat_activity WHERE wait_event_type='Lock' GROUP BY 1;"
# pass: ungranted locks near zero under load, no lock-wait pileup
Report the contended relation or row, the lock window before and after, and whether the fix was lock ordering, an atomic update, or SKIP LOCKED.