Skip to content

Postgres Lock Types and Deadlocks

Postgres uses a multi-level locking hierarchy: relation-level locks for DDL and DML coordination, row-level locks for concurrent writes to the same rows, and advisory locks for application-defined mutual exclusion. Most production lock problems are not deadlocks — they're lock waits caused by long-held relation locks, usually from DDL migrations or idle transactions.

Relation-level lock modes

Postgres defines eight relation lock modes, ordered from weakest to strongest. Most DML takes RowExclusiveLock; DDL takes stronger modes. The critical conflicts to know:

  • AccessShareLock — taken by SELECT. Conflicts with AccessExclusiveLock only.
  • RowExclusiveLock — taken by INSERT, UPDATE, DELETE. Conflicts with ShareLock and stronger.
  • ShareUpdateExclusiveLock — taken by VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY. Conflicts with itself and stronger modes, but not with DML.
  • ShareRowExclusiveLock — taken by CREATE TRIGGER, ALTER TABLE ADD FOREIGN KEY. Conflicts with most write operations.
  • AccessExclusiveLock — taken by ALTER TABLE, DROP TABLE, TRUNCATE, plain REINDEX. Conflicts with everything — blocks all reads and writes. The most dangerous lock for production migrations.

Common safe DDL alternatives:

  • CREATE INDEX CONCURRENTLY instead of CREATE INDEX.
  • REINDEX INDEX CONCURRENTLY instead of REINDEX INDEX.
  • Adding a nullable column is instant; adding NOT NULL DEFAULT rewrites the table in older Postgres (use SET NOT NULL + check constraint pattern on PG11 and below).

Row-level locks

Row-level locks are not stored in the lock manager hash table. Instead, Postgres records them in the tuple header (xmax field and infomask bits). This means row-level lock overhead scales with row count, not connection count.

The four row lock modes:

  • FOR KEY SHARE — prevents key columns from being updated or the row from being deleted. Used implicitly by foreign key checks.
  • FOR SHARE — prevents any update or delete. Used by SELECT ... FOR SHARE.
  • FOR NO KEY UPDATE — exclusive lock that allows concurrent FOR KEY SHARE. Taken by UPDATE on non-key columns.
  • FOR UPDATE — fully exclusive row lock. Taken by UPDATE on key columns, DELETE, and SELECT ... FOR UPDATE.

SELECT ... FOR UPDATE SKIP LOCKED is useful for job-queue patterns — it acquires a lock on each qualifying row and skips rows already locked by other transactions, with no blocking:

SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

Reading pg_locks

The pg_locks view shows all currently held and waiting locks. Most useful joined with pg_stat_activity:

SELECT
  blocked.pid                    AS blocked_pid,
  blocked_activity.query         AS blocked_query,
  blocking.pid                   AS blocking_pid,
  blocking_activity.query        AS blocking_query,
  blocked_activity.xact_start    AS blocked_txn_start
FROM pg_locks blocked
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked.pid
JOIN pg_locks blocking
  ON blocking.locktype  = blocked.locktype
 AND blocking.relation  = blocked.relation
 AND blocking.granted   = true
 AND blocked.granted    = false
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking.pid
WHERE NOT blocked.granted;

This returns every blocked query with the blocking query and its PID. The blocking_activity.xact_start tells you how long the blocking transaction has been open — if it's hours old, it's likely an abandoned session.

Lock waits and statement_timeout

A lock wait is not a deadlock — it's one session waiting for another to release. It becomes a production problem when a long-running transaction holds a lock that accumulates a queue of waiters, eventually blocking new connections too.

Set lock_timeout on migrations to prevent them from hanging indefinitely:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN last_login timestamptz;

If the lock isn't acquired within 2 seconds, the statement errors out cleanly rather than blocking all subsequent queries on the table. Retry the migration during lower-traffic windows.

For application queries, statement_timeout caps total execution time. Neither timeout affects already-running statements — they only prevent the current statement from waiting past the threshold.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs. Postgres detects deadlocks automatically (via a background cycle-detection algorithm running every deadlock_timeout, default 1 second) and terminates one of the transactions with:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 98765;
         blocked by process 67890.

Deadlocks in Postgres are almost always caused by inconsistent lock ordering: transaction A locks row 1 then row 2; transaction B locks row 2 then row 1. The fix is to enforce a consistent ordering — always lock rows in the same order (e.g., by primary key ascending) across all code paths.

Frequent deadlocks are logged in postgresql.log. Count them:

grep "deadlock detected" /var/log/postgresql/postgresql*.log | wc -l

Advisory locks

Advisory locks are application-defined locks with no automatic semantics — Postgres acquires and releases them only when explicitly asked. Useful for distributed mutex patterns (e.g., ensuring only one worker processes a given job ID):

-- acquire (returns true if acquired, false if already held)
SELECT pg_try_advisory_lock(12345);

-- release
SELECT pg_advisory_unlock(12345);

-- session-level: held until released or session ends
-- transaction-level: released automatically at commit/rollback
SELECT pg_try_advisory_xact_lock(12345);

Advisory locks use the same lock manager infrastructure as relation locks. They appear in pg_locks with locktype = 'advisory'. They do not survive server restart.

Quick checklist

  • Use CREATE INDEX CONCURRENTLY and REINDEX ... CONCURRENTLY — plain DDL takes AccessExclusiveLock.
  • Set lock_timeout = '2s' on all migration statements.
  • Query pg_locks joined with pg_stat_activity to identify blocking sessions.
  • Kill blocking idle in transaction sessions — they hold locks indefinitely.
  • Fix deadlocks by enforcing consistent row-lock ordering across transactions.
  • Use SELECT ... FOR UPDATE SKIP LOCKED for queue-worker patterns.

Related: Postgres MVCC · Reading EXPLAIN Plans

Postgres Lock Types and Deadlocks | TuneStack