Locks & Deadlocks

Transactions & Isolation introduced SELECT ... FOR UPDATE as a way to lock a row on purpose. Postgres actually has two separate locking systems — row-level and table-level — plus an application-level escape hatch, and knowing which one is holding things up is most of debugging a "why is this query just hanging" incident.

Row-level locks

Row locks come in a few flavors, from weakest to strongest:

• FOR UPDATE — locks rows as if for UPDATE; blocks other FOR UPDATE, FOR SHARE, UPDATE, and DELETE on the same rows
• FOR NO KEY UPDATE — like FOR UPDATE but doesn't conflict with FOR KEY SHARE (used internally for updates that don't touch a foreign key's referenced columns)
• FOR SHARE — locks rows for reading; multiple transactions can hold it concurrently, but it blocks concurrent UPDATE/DELETE
• FOR KEY SHARE — the weakest; only conflicts with locks that would change a row's key values

-- session A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- session B, at the same time
UPDATE accounts SET balance = 0 WHERE id = 1;
-- blocks until session A commits or rolls back

Table-level lock modes

Every statement takes a table-level lock too, even a plain SELECT (ACCESS SHARE, the weakest mode — it only conflicts with ACCESS EXCLUSIVE). The eight modes form a conflict matrix from weakest to strongest:

ACCESS SHARE            -- SELECT
ROW SHARE                -- SELECT FOR UPDATE/SHARE
ROW EXCLUSIVE            -- UPDATE, DELETE, INSERT
SHARE UPDATE EXCLUSIVE   -- VACUUM (non-full), CREATE INDEX CONCURRENTLY
SHARE                    -- CREATE INDEX (non-concurrent)
SHARE ROW EXCLUSIVE      -- some CREATE TRIGGER / constraint forms
EXCLUSIVE                -- rarely used directly
ACCESS EXCLUSIVE         -- DROP TABLE, TRUNCATE, most ALTER TABLE, VACUUM FULL

Deadlocks

A deadlock happens when two transactions each hold a lock the other is waiting for:

-- Session A                    -- Session B
BEGIN;                          BEGIN;
UPDATE accounts SET ...
WHERE id = 1;
                               UPDATE accounts SET ...
                                 WHERE id = 2;
UPDATE accounts SET ...
WHERE id = 2;  -- waits on B
                               UPDATE accounts SET ...
                                 WHERE id = 1;  -- waits on A
                               -- DEADLOCK

Postgres runs a deadlock detector (checking every second by default) that finds the cycle and aborts one of the transactions with ERROR: deadlock detected, letting the other proceed. The fix is almost always at the application level: always lock rows (or tables) in a consistent order across every code path.

Advisory locks

Sometimes you want to coordinate application logic that has nothing to do with a specific row — a cron job that shouldn't run twice concurrently, for example. Advisory locks are arbitrary integer locks Postgres tracks for you without attaching them to any table:

-- session-level: held until explicitly released or the session ends
SELECT pg_advisory_lock(12345);
-- ... do work ...
SELECT pg_advisory_unlock(12345);

-- transaction-level: released automatically at COMMIT/ROLLBACK
SELECT pg_advisory_xact_lock(12345);

They're cheap, application-defined, and never conflict with real table or row locks — a useful tool for distributed coordination when you already have a Postgres connection open.

Locking controls what happens when queries collide. Indexing, next, is about avoiding unnecessary collisions in the first place by making queries touch far fewer rows.