Locking and Deadlocks

Locks make concurrent transactions wait for each other instead of corrupting shared data. In PostgreSQL, MVCC lets plain SELECT statements run without waiting; locks matter for writes, for explicit locking reads, and for schema changes.

Multi-session examples show sessions A, B, C (and D) whose output was captured from real psql sessions on PostgreSQL 16. A statement marked [blocks] did not return until another session finished. The tables:

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  owner   text NOT NULL,
  balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ben', 300);

CREATE TABLE jobs (
  id      serial PRIMARY KEY,
  payload text NOT NULL,
  status  text NOT NULL DEFAULT 'queued'
);
INSERT INTO jobs (payload) VALUES ('email-1'), ('email-2'), ('email-3');

Example 1: Row locks and table locks

Locks exist at two levels:

Each statement takes a table-level lock whose mode depends on the statement. Row locks are recorded on the row itself, not in the lock table, so locking a million rows does not use a million lock entries.

BEGIN;
SELECT * FROM accounts WHERE id = 1;
SELECT mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'accounts'::regclass;
ROLLBACK;

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'accounts'::regclass;
ROLLBACK;

BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'accounts'::regclass;
ROLLBACK;

BEGIN;
ALTER TABLE accounts ADD COLUMN note text;
SELECT mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'accounts'::regclass;
ROLLBACK;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
(1 row)

      mode       
-----------------
 AccessShareLock
(1 row)

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
(1 row)

     mode     
--------------
 RowShareLock
(1 row)

       mode       
------------------
 RowExclusiveLock
(1 row)

        mode         
---------------------
 AccessExclusiveLock
(1 row)

The table lock for each statement, from weakest to strongest:

SELECT and UPDATE do not conflict at table level; two UPDATE statements conflict only when they touch the same row.

Example 2: Exclusive row locks and the lock queue

An UPDATE takes an exclusive lock on each row it changes, held until the transaction ends. Other writers to that row wait in a queue, in order. Sessions B and C both update row 1 while A holds it:

-- Session A (holds the row lock)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B (first in the queue) [blocks]
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;

-- Session C (second in the queue) [blocks]
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;

-- Session A
SELECT 'A commits' AS step;
COMMIT;

   step    
-----------
 A commits
(1 row)

-- Session B: the blocked statement now completes

-- Session B
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

 balance 
---------
  470.00
(1 row)

-- Session C: the blocked statement now completes

-- Session C
COMMIT;
SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  440.00
(1 row)

The three decrements (10, 20, 30) all applied: 500 - 60 = 440. Waiting writers ran one after another, and B's transaction stayed open until it committed, so C waited for B too.

Example 3: Shared and exclusive locks

SELECT ... FOR SHARE takes a shared row lock: many transactions can hold it together, and it blocks writers. FOR UPDATE and UPDATE take an exclusive lock, which conflicts with everything.

Row-level lock modes in PostgreSQL, weakest to strongest:

Two sessions hold a shared lock at once; a third session's UPDATE waits for both to finish:

-- Session A (shared lock)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
(1 row)

-- Session B (shared locks are compatible)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
(1 row)

-- Session C (needs an exclusive lock) [blocks]
BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;

-- Session A (C still waits: B holds a shared lock)
COMMIT;

-- Session B (last shared lock released)
COMMIT;

-- Session C: the blocked statement now completes

-- Session C
ROLLBACK;

Example 4: SELECT FOR UPDATE, NOWAIT and SKIP LOCKED

SELECT ... FOR UPDATE locks the selected rows so that a read-then-write cannot be interrupted (see isolation levels). Two options change what happens when a row is already locked:

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

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
(1 row)

-- Session B (fails immediately)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

ERROR:  could not obtain lock on row in relation "accounts"

-- Session B (skips the locked row)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

 id | owner | balance 
----+-------+---------
(0 rows)

-- Session A
ROLLBACK;

NOWAIT returned SQLSTATE 55P03 (lock_not_available). SKIP LOCKED returned zero rows because the only matching row was locked. MySQL 8.0 and Oracle also support both options; SQL Server offers READPAST for the skip behaviour.

Example 5: A job queue with SKIP LOCKED

Workers claim the next queued job. SKIP LOCKED makes each worker skip jobs that another worker is holding, so workers do not wait for each other and never pick the same job:

-- Session A (worker 1 claims a job)
BEGIN;
SELECT id, payload FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

 id | payload 
----+---------
  1 | email-1
(1 row)

-- Session B (worker 2 gets the next job)
BEGIN;
SELECT id, payload FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

 id | payload 
----+---------
  2 | email-2
(1 row)

-- Session A
UPDATE jobs SET status = 'done' WHERE id = 1;
COMMIT;

-- Session B
UPDATE jobs SET status = 'done' WHERE id = 2;
COMMIT;
SELECT id, payload, status FROM jobs ORDER BY id;

 id | payload | status 
----+---------+--------
  1 | email-1 | done
  2 | email-2 | done
  3 | email-3 | queued
(3 rows)

Worker 1 got job 1 and worker 2 got job 2 without waiting. In production the claim and the status update are usually one statement:

UPDATE jobs SET status = 'running'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload, status;

 id | payload | status  
----+---------+---------
  1 | email-1 | running
(1 row)

Job 1 is now running (the statement ran in autocommit mode). Concurrent workers running the same statement each get a different job.

Example 6: Viewing locks and waiting sessions

pg_stat_activity shows what each session runs and whether it waits; pg_locks lists the locks held and requested; pg_blocking_pids(pid) returns the sessions blocking a given one. Session A holds a row lock and B waits for it; Session C inspects:

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B (blocks) [blocks]
UPDATE accounts SET balance = balance - 20 WHERE id = 1;

-- Session C (monitoring)
SELECT a.query, a.wait_event_type, cardinality(pg_blocking_pids(a.pid)) AS blockers
FROM pg_stat_activity a
WHERE a.datname = current_database() AND a.pid <> pg_backend_pid() AND a.state = 'active'
ORDER BY a.query;

                          query                           | wait_event_type | blockers 
----------------------------------------------------------+-----------------+----------
 UPDATE accounts SET balance = balance - 20 WHERE id = 1; | Lock            |        1
(1 row)

-- Session C (who holds and who waits)
SELECT a.query, l.locktype, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.datname = current_database() AND a.pid <> pg_backend_pid()
  AND l.locktype IN ('relation', 'transactionid')
ORDER BY a.query, l.locktype DESC, l.granted DESC;

                          query                           |   locktype    |       mode       | granted 
----------------------------------------------------------+---------------+------------------+---------
 UPDATE accounts SET balance = balance - 10 WHERE id = 1; | transactionid | ExclusiveLock    | t
 UPDATE accounts SET balance = balance - 10 WHERE id = 1; | relation      | RowExclusiveLock | t
 UPDATE accounts SET balance = balance - 10 WHERE id = 1; | relation      | RowExclusiveLock | t
 UPDATE accounts SET balance = balance - 20 WHERE id = 1; | transactionid | ExclusiveLock    | t
 UPDATE accounts SET balance = balance - 20 WHERE id = 1; | transactionid | ShareLock        | f
 UPDATE accounts SET balance = balance - 20 WHERE id = 1; | relation      | RowExclusiveLock | t
 UPDATE accounts SET balance = balance - 20 WHERE id = 1; | relation      | RowExclusiveLock | t
(7 rows)

-- Session A
COMMIT;

-- Session B: the blocked statement now completes

Reading the result:

• wait_event_type = Lock and one blocker: the - 20 update is waiting on another session.
• A row with granted = f is the wait. A row lock wait appears as a ShareLock request on the transaction ID of the lock holder (transactionid): a waiting session queues on the holder's transaction, not on the row.
• Two RowExclusiveLock rows per session: one on the table and one on its primary key index.

A common query to find the root blocker is SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;. pg_terminate_backend(pid) ends a blocking session.

Example 7: Table locks and lock_timeout

LOCK TABLE and DDL take ACCESS EXCLUSIVE, which blocks even plain reads. lock_timeout makes a statement give up after a fixed wait instead of queueing forever.

-- Session A
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

-- Session B (reader waits, then gives up)
SET lock_timeout = '300ms';
SELECT count(*) FROM accounts;

ERROR:  canceling statement due to lock timeout
LINE 1: SELECT count(*) FROM accounts;
                             ^

-- Session A
ROLLBACK;

-- Session B
SELECT count(*) FROM accounts;

 count 
-------
     2
(1 row)

Related settings:

SHOW lock_timeout;
SHOW deadlock_timeout;
SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;

 lock_timeout 
--------------
 0
(1 row)

 deadlock_timeout 
------------------
 1s
(1 row)

 statement_timeout 
-------------------
 0
(1 row)

 idle_in_transaction_session_timeout 
-------------------------------------
 0
(1 row)

lock_timeout is off (0) by default. deadlock_timeout (1 second) is how long a session waits before the server checks for a deadlock. Migrations that need ACCESS EXCLUSIVE set lock_timeout so they fail fast instead of stalling production traffic.

Example 8: The lock queue effect of DDL

A waiting ACCESS EXCLUSIVE request blocks every request that arrives after it, even ones that would be compatible with the current holder. One idle transaction that has read the table is enough:

-- Session A (a reader whose transaction stays open)
BEGIN;
SELECT count(*) FROM accounts;

 count 
-------
     2
(1 row)

-- Session B (needs ACCESS EXCLUSIVE, waits for A) [blocks]
ALTER TABLE accounts ADD COLUMN note text;

-- Session C (new reader queues behind the ALTER) [blocks]
SELECT count(*) FROM accounts;

-- Session D (monitoring)
SELECT a.query, a.wait_event_type, cardinality(pg_blocking_pids(a.pid)) AS blockers
FROM pg_stat_activity a
WHERE a.datname = current_database() AND a.pid <> pg_backend_pid() AND a.state = 'active'
ORDER BY a.query;

                   query                    | wait_event_type | blockers 
--------------------------------------------+-----------------+----------
 ALTER TABLE accounts ADD COLUMN note text; | Lock            |        1
 SELECT count(*) FROM accounts;             | Lock            |        1
(2 rows)

-- Session A
COMMIT;

-- Session B: the blocked statement now completes

-- Session C: the blocked statement now completes

 count 
-------
     2
(1 row)

Session A only read the table, but the queued ALTER TABLE made the later SELECT in Session C wait as well. After A committed, B ran and C returned. A long-running read, a waiting DDL statement and application traffic together cause outages this way.

Example 9: Deadlock

A deadlock is a cycle of waits: A waits for B and B waits for A. Neither can proceed. PostgreSQL detects the cycle after deadlock_timeout and aborts one transaction with SQLSTATE 40P01; the other continues.

-- Session A (locks row 1)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B (locks row 2)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;

-- Session A (waits for B) [blocks]
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Session B (waits for A: deadlock)
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

ERROR:  deadlock detected
DETAIL:  Process 5247 waits for ShareLock on transaction 3301; blocked by process 5244.
Process 5244 waits for ShareLock on transaction 3302; blocked by process 5247.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

-- Session A: the blocked statement now completes

-- Session A
COMMIT;

-- Session B
ROLLBACK;

-- Session A
SELECT * FROM accounts ORDER BY id;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  490.00
  2 | Ben   |  310.00
(2 rows)

Session B was chosen as the victim: its transaction was aborted and the ROLLBACK cleared it. Session A's UPDATE then completed and its changes were committed (Asha 490, Ben 310). The process and transaction numbers in the message differ on every run, and which session is aborted is not predictable. The application must catch 40P01 and retry the whole transaction. See transactions and ACID.

Example 10: Preventing deadlocks

The transfer from Example 9, with both sessions taking the lower id first, waits instead of deadlocking:

-- Session A (locks the lower id first)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B (also starts with the lower id: waits) [blocks]
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

-- Session A
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- Session B: the blocked statement now completes

-- Session B
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
COMMIT;
SELECT * FROM accounts ORDER BY id;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
  2 | Ben   |  300.00
(2 rows)

B waited for A's lock on row 1 and then proceeded. Both transfers applied (Asha 500, Ben 300 after 10 out and 10 back in each direction).

Example 11: Optimistic versus pessimistic locking, and two-phase locking

The optimistic version-column pattern is shown in isolation levels.

Two-phase locking (2PL) is the classic protocol behind lock-based isolation: a transaction acquires locks during a growing phase and releases them only during a shrinking phase, and never acquires a lock after releasing one. Databases use strict 2PL: all write locks are held until commit or rollback, which is why an exclusive row lock in Example 2 lasts until COMMIT.

flowchart LR
  A["BEGIN"] --> B["growing phase<br/>acquire locks as statements run"]
  B --> C["COMMIT or ROLLBACK"]
  C --> D["shrinking phase<br/>all locks released at once"]

PostgreSQL uses MVCC for reads (no read locks) and 2PL-style row and table locks for writes. SQL Server at its default level and MySQL InnoDB locking reads use locks for reads too.

Example 12: Advisory locks

Advisory locks are locks on an application-chosen number rather than on a table or row. The database enforces them but attaches no meaning to them, so they suit "only one worker runs this task" and mutual exclusion around external resources.

-- Session A (session A takes lock 42)
SELECT pg_try_advisory_lock(42) AS got_lock;

 got_lock 
----------
 t
(1 row)

-- Session B (session B is refused)
SELECT pg_try_advisory_lock(42) AS got_lock;

 got_lock 
----------
 f
(1 row)

-- Session A
SELECT pg_advisory_unlock(42) AS released;

 released 
----------
 t
(1 row)

-- Session B (now B gets it)
SELECT pg_try_advisory_lock(42) AS got_lock;

 got_lock 
----------
 t
(1 row)

-- Session B
SELECT pg_advisory_unlock(42) AS released;

 released 
----------
 t
(1 row)

pg_try_advisory_lock returns immediately with true or false; pg_advisory_lock waits. These are session-level locks and last until released or the session ends. pg_advisory_xact_lock(key) is released automatically at the end of the transaction. MySQL offers the same idea as GET_LOCK().

Interview points

• Row lock versus table lock, and which statement takes which. ALTER TABLE takes ACCESS EXCLUSIVE and blocks even SELECT.
• Shared versus exclusive locks and the compatibility matrix.
• SELECT FOR UPDATE, and what NOWAIT and SKIP LOCKED change. SKIP LOCKED is the standard answer for a database-backed job queue.
• How a deadlock forms (two transactions, opposite lock order), how the database detects it (wait-for cycle after deadlock_timeout), what it does (aborts one with 40P01), and how to prevent it (consistent lock order, short transactions).
• Why a waiting ALTER TABLE can stall all traffic, and the mitigation (lock_timeout and retry, no long transactions).
• How to find the blocker: pg_stat_activity, pg_locks, pg_blocking_pids(); MySQL uses performance_schema.data_lock_waits or SHOW ENGINE INNODB STATUS.
• Optimistic versus pessimistic locking and when to choose each.
• Two-phase locking and strict 2PL: locks held until commit.
• InnoDB takes gap and next-key locks in REPEATABLE READ locking reads, which causes deadlocks and waits between inserts that PostgreSQL does not have.
• Lock escalation (row locks converted to table locks) exists in SQL Server and Db2; PostgreSQL and InnoDB do not escalate.

Summary

• Readers do not block writers in PostgreSQL; writers to the same row queue behind the exclusive row lock until the holder commits or rolls back.
• FOR UPDATE locks rows for a read-then-write, NOWAIT fails instead of waiting, and SKIP LOCKED enables concurrent job queues.
• DDL takes ACCESS EXCLUSIVE, and a queued DDL statement blocks later readers; use lock_timeout and short transactions.
• Deadlocks are cycles of waits, resolved by aborting one transaction with 40P01; prevent them with consistent lock order and retry when they occur.
• Use pg_stat_activity, pg_locks and pg_blocking_pids() to find who blocks whom; advisory locks give application-level mutual exclusion.