Locking and Concurrency¶

Locks in a database¶

Even with MVCC letting readers proceed without blocking writers, databases still use locks — to serialize conflicting writes to the same row, to protect tables from being altered or dropped while in use, and to let applications coordinate explicitly. Most production incidents involving "the database is stuck" are lock problems: a long transaction holding a lock, a migration waiting in a lock queue, or a deadlock between code paths.

Every behavior on this page was measured on PostgreSQL 16.2.

Database locks protect logical objects (rows, tables) for the duration of a transaction. They are different from latches, the short-lived internal mutexes that protect in-memory structures such as buffer pool pages for microseconds (see Buffer Pool and B+Tree).

Row locks¶

Writes lock the rows they modify until the transaction ends:

Statement	Row lock taken (PostgreSQL)	Blocks
`UPDATE` (key columns changed), `DELETE`	`FOR UPDATE`	All other row locks on that row
`UPDATE` (no key column changed)	`FOR NO KEY UPDATE`	Other updates, but not foreign-key checks (`FOR KEY SHARE`)
`SELECT ... FOR UPDATE`	`FOR UPDATE`	Same as an update
`SELECT ... FOR SHARE`	`FOR SHARE`	Writers, but not other `FOR SHARE`
Foreign-key check on insert into a child table	`FOR KEY SHARE` on the parent row	Deleting the parent or changing its key
Plain `SELECT`	None	Nothing — it reads a snapshot

PostgreSQL stores row locks in the row itself (in the tuple's xmax and flag bits), not in a lock table in memory, so locking millions of rows costs no shared memory. A waiting transaction waits on the transaction ID of the holder, which is why pg_locks shows a transactionid lock for every running writer.

What happens when two transactions update the same row (measured under Read Committed): B's UPDATE blocks until A commits or rolls back. If A commits, B re-reads the newest committed version of the row, re-evaluates its WHERE clause against it, and applies its change; if A rolls back, B proceeds on the original row.

Table locks¶

Every statement also takes a table-level lock, which mostly conflicts only with DDL:

Lock mode	Taken by	Conflicts with (most important)
`ACCESS SHARE`	`SELECT`	`ACCESS EXCLUSIVE` only
`ROW SHARE`	`SELECT ... FOR UPDATE/SHARE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`	`SHARE` and stronger
`SHARE UPDATE EXCLUSIVE`	`VACUUM`, `ANALYZE`, `CREATE INDEX CONCURRENTLY`	Itself and stronger
`SHARE`	`CREATE INDEX` (non-concurrent)	Writers
`ACCESS EXCLUSIVE`	Most `ALTER TABLE`, `DROP`, `TRUNCATE`, `VACUUM FULL`	Everything, including plain `SELECT`

Measured: a transaction holding row locks from SELECT ... FOR UPDATE SKIP LOCKED on jobs showed these entries in pg_locks:

relation   jobs        AccessShareLock   granted
relation   jobs        RowShareLock      granted
relation   jobs_pkey   AccessShareLock   granted
relation   jobs_pkey   RowShareLock      granted
transactionid          ExclusiveLock     granted   ← its own XID: others wait on this

The lock queue: how a migration takes down an application¶

Lock requests on a table wait in a FIFO queue. A new request conflicting with a waiting request waits behind it, even if it doesn't conflict with the current holder. Measured:

Session 1 runs SELECT * FROM acct inside a transaction and leaves it open (holds ACCESS SHARE).
Session 2 runs ALTER TABLE acct ADD COLUMN note text — needs ACCESS EXCLUSIVE, conflicts with session 1, waits.
Session 3 runs a plain SELECT count(*) FROM acct — its ACCESS SHARE doesn't conflict with session 1, but does conflict with session 2's queued ACCESS EXCLUSIVE, so it also waits.

after 1s: ALTER waiting: True | new SELECT waiting: True
pg_stat_activity: ALTER TABLE ...  blocked by [session 1]
                  SELECT count(*)  blocked by [session 2 (the ALTER)]
after session 1 commits: ALTER finished in 1.33s, SELECT in 1.02s

In production, every query on the table queues behind the migration; connection pools fill; the application goes down — because of one idle transaction and a "fast" ADD COLUMN. The standard defense:

SET lock_timeout = '2s';          -- give up instead of blocking everyone behind us
ALTER TABLE acct ADD COLUMN note text;
-- on failure: retry later, after finding and ending the long transaction

Explicit locking patterns¶

`SELECT ... FOR UPDATE`: pessimistic read-modify-write¶

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- lock now
-- application decides based on balance
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Concurrent transactions doing the same wait at the SELECT, so each sees the previous one's result. This prevents lost updates and — when the locked rows are exactly the ones the decision depends on — write skew.

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.

`NOWAIT` and `SKIP LOCKED`¶

SELECT ... FOR UPDATE NOWAIT;        -- fail immediately instead of waiting
SELECT ... FOR UPDATE SKIP LOCKED;   -- silently skip rows others have locked

Measured: with worker A holding locks on jobs 1–3, worker B's SELECT id FROM jobs WHERE NOT done ORDER BY id LIMIT 3 FOR UPDATE SKIP LOCKED returned 4, 5, 6; a FOR UPDATE NOWAIT on job 1 failed immediately with 55P03 could not obtain lock on row in relation "jobs".

SKIP LOCKED turns a table into a correct concurrent job queue: each worker claims different rows without blocking or double-processing.

Advisory locks¶

SELECT pg_advisory_xact_lock(hashtext('nightly-report'));   -- released at commit
SELECT pg_try_advisory_lock(42);                            -- session-level, non-blocking

Locks on arbitrary application-chosen integers, not tied to any row: leader election among app instances, ensuring one migration runner, or serializing work per customer ID.

Deadlocks¶

Measured: A updated account 1, B updated account 2; then A tried to update 2 and B tried to update 1.

A: ERROR 40P01 deadlock detected   (after 1.00 s)
B: UPDATE succeeded                 (after 0.90 s — once A was aborted)

PostgreSQL does not check for deadlocks on every wait. A waiter sleeps for deadlock_timeout (default 1 s); only then does it search the wait-for graph, and if it finds a cycle, it aborts itself. That is why A's error came at exactly 1.00 s. InnoDB checks immediately on each wait (by default) and rolls back the transaction that has modified the fewest rows. The general theory is in Deadlock.

Application rules:

Acquire row locks in a consistent order (sort IDs before updating several rows; see the pitfall below).
Keep transactions short; don't wait for user input or remote calls while holding locks.
Retry on 40P01 — a deadlock abort is normal, not a bug report.

Optimistic concurrency control¶

Instead of locking, detect conflicts at write time:

-- version column incremented on every write
UPDATE documents SET body = :body, version = version + 1
WHERE id = :id AND version = :version_read;
-- rows affected = 0 → someone else changed it; reload and retry (or report a conflict)

	Pessimistic (`FOR UPDATE`)	Optimistic (version check)
Conflict handling	Wait	Retry / report
Best when	Contention is high, retries are expensive	Contention is low; work spans user think time (an edit form)
Risk	Deadlocks, lock waits	Livelock under heavy contention

Serializable isolation (SSI) is optimistic concurrency control done by the database for whole transactions. See Isolation Levels and Anomalies.

Diagnosing lock problems¶

-- Who is waiting, and on whom?
SELECT pid, wait_event_type, wait_event, state, left(query, 60),
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Long or idle transactions (the usual root cause)
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Useful settings: log_lock_waits = on (log any wait longer than deadlock_timeout), lock_timeout for DDL and batch jobs, idle_in_transaction_session_timeout to kill sessions that hold a transaction open while doing nothing.

Common pitfall¶

Acquiring row locks in different orders on different code paths. Path A updates account 1 then 2; path B (a transfer in the other direction) updates 2 then 1. Each is correct alone; together they deadlock whenever they interleave — exactly the measured case above, where one of them was aborted after a full second of waiting. The fix is mechanical, which is why it survives refactoring: lock rows in sorted order (SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE, or update IDs in ascending order), so every code path takes locks in the same global order regardless of the business direction of the operation.