Locking

Whenever multiple transactions try to read and modify the same rows at the same time, PostgreSQL needs a way to keep them from stepping on each other. It does most of this automatically through MVCC (covered on its own page), but for a class of problems — "I read a row, I'm about to update it based on what I read, and I need to make sure nobody else changes it in between" — MVCC alone isn't enough. That's what explicit locking is for.

PostgreSQL supports locking at several different granularities. The two you'll run into most often as an application developer are row-level locks and table-level locks.

Row-level vs Table-level Locks

Lock Level	What It Protects	Typical Trigger
Row-level	A single row (or a specific set of rows)	`UPDATE`, `DELETE`, `SELECT ... FOR UPDATE`, `SELECT ... FOR SHARE`
Table-level	The entire table, including its structure	`LOCK TABLE`, `ALTER TABLE`, `TRUNCATE`, `VACUUM FULL`

Row-level locks are the ones you'll deal with directly in everyday application code. Table-level locks matter mostly for DDL (schema changes) and maintenance operations, since they can block every query touching the table while held.

SELECT ... FOR UPDATE

The classic race condition: you read a row's value, do some logic on the application side, then write a new value back. If two transactions do this concurrently, the second write can silently clobber the effect of the first (a "lost update"). SELECT ... FOR UPDATE fixes this by locking the selected rows for writing — any other transaction that tries to lock or modify those same rows will wait until the current transaction commits or rolls back.

A worked example: two people trying to redeem the same discount coupon at the same time. Without locking, both requests could read uses_remaining = 1 and both decide it's safe to proceed:

race-condition-safe-update.sql

SQL

-- Transaction A and Transaction B both run this concurrently

BEGIN;

-- Lock the row for update. If another transaction already holds
-- a lock on this row, this statement BLOCKS until it's released.
SELECT id, uses_remaining
FROM coupons
WHERE code = 'SAVE10'
FOR UPDATE;

-- Now that we hold the lock, it's safe to check and decrement.
-- No other transaction could have changed uses_remaining underneath us.
UPDATE coupons
SET uses_remaining = uses_remaining - 1
WHERE code = 'SAVE10'
  AND uses_remaining > 0;

COMMIT;

Whichever transaction runs the SELECT ... FOR UPDATE first gets the lock immediately; the second transaction blocks on its own SELECT ... FOR UPDATE until the first one commits (releasing the lock) — at which point it sees the updated uses_remaining and makes the correct decision. Without FOR UPDATE, both transactions could read the same stale value and both decrement it, over-redeeming the coupon.

SELECT ... FOR SHARE

FOR SHARE is the weaker sibling of FOR UPDATE. It also locks the selected rows, but multiple transactions can hold a FOR SHARE lock on the same row at once — it only blocks other transactions from acquiring a FOR UPDATE lock or modifying the row. This is useful when you want to guarantee a row won't be changed or deleted out from under you while you're relying on it (say, checking a foreign key relationship manually), but you don't need exclusive write access yourself.

SQL

-- Multiple transactions can hold this simultaneously
SELECT * FROM products WHERE id = 42 FOR SHARE;

Deadlocks

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 proceed. Classic example:

Transaction A locks row 1, then tries to lock row 2.
Transaction B has already locked row 2, then tries to lock row 1.
A is waiting on B, and B is waiting on A — neither can ever finish.

PostgreSQL doesn't just hang forever in this situation. It runs a deadlock detector that periodically checks for these circular waiting patterns. When it finds one, it picks one of the transactions involved and aborts it with an error, letting the other proceed. The aborted transaction gets a deadlock detected error and is rolled back automatically.

Applications must handle deadlock errors

PostgreSQL breaking a deadlock is not optional and not something you can fully prevent through query design alone in a busy system. Your application code should catch the deadlock error (SQLSTATE `40P01`) and retry the aborted transaction from the beginning. Treating it as a fatal, unrecoverable error will cause otherwise-normal contention to surface as user-facing failures.

The best defense against deadlocks is consistency: if every transaction in your application always acquires locks on multiple rows/tables in the same order, circular waits become far less likely.

Inspecting locks with pg_locks

The `pg_locks` system view shows every lock currently held or awaited in the database, including which process (`pid`) holds it and which relation/row it applies to. Joining it against `pg_stat_activity` is the standard way to diagnose "why is this query stuck?" — for example: `SELECT * FROM pg_locks WHERE NOT granted;` shows every lock request that is currently blocked.

Note

Locking is a tool for correctness, not a substitute for good transaction design. Keep transactions that acquire row locks as short as possible — the longer a lock is held, the more other transactions pile up waiting behind it.