Module 13 — Locking Internals: Row Locks, Table Locks, and Advisory Locks

Why Locking Is Hard to Reason About

Postgres has multiple, overlapping lock systems that operate at different granularities simultaneously:

1. Table-level locks — protect the table structure and relation-level operations
2. Row-level locks — protect individual tuple modifications
3. Page-level locks — internal, mostly transparent (buffer pins)
4. Advisory locks — application-defined locks with no automatic semantics

Every DML statement acquires locks at multiple levels simultaneously. An UPDATE acquires a RowExclusiveLock on the table and a row-level exclusive lock on each modified tuple. The two systems interact: some table-level operations must wait for all row-level locks to be released before they can proceed.

Understanding which operations conflict — and which do not — is the difference between a schema migration that completes in 30 seconds and one that causes a 20-minute outage.

Table-Level Lock Modes

Postgres defines 8 table-level lock modes, ordered from weakest to strongest. Each mode conflicts with some modes and is compatible with others.

The Full Lock Matrix

The critical insight: ACCESS EXCLUSIVE conflicts with every other lock mode, including plain SELECT. This is why ALTER TABLE on a busy table causes an outage.

The ALTER TABLE Outage Pattern

This is one of the most common causes of production incidents. Here is the exact mechanism:

sql
-- Timeline:
-- t=0: Application connection A runs a long SELECT (acquires ACCESS SHARE)
-- t=1: DBA runs: ALTER TABLE transactions ADD COLUMN notes TEXT;
--       Postgres tries to acquire ACCESS EXCLUSIVE
--       ACCESS EXCLUSIVE conflicts with A's ACCESS SHARE → queues
-- t=2: New application connections try to SELECT from transactions
--       They try to acquire ACCESS SHARE
--       ACCESS SHARE is compatible with ACCESS SHARE, BUT...
--       The queued ACCESS EXCLUSIVE lock blocks ALL subsequent lock acquisitions
--       New SELECTs queue behind the ALTER TABLE
-- t=3: Connection queue fills up. New connections refused. Outage.

The ALTER TABLE does not hold a lock — it is waiting for one. But its waiting position in the lock queue blocks all subsequent requests, even ones that would normally be compatible with each other.

sql
-- See the lock queue in real time
SELECT
  pid,
  locktype,
  relation::regclass,
  mode,
  granted,
  ROUND(EXTRACT(EPOCH FROM (now() - query_start))::numeric, 1) AS wait_seconds,
  LEFT(query, 80) AS query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation = 'transactions'::regclass
ORDER BY granted DESC, wait_seconds DESC;

The Correct ALTER TABLE Pattern

sql
-- Step 1: Set a lock_timeout so the ALTER fails fast rather than queuing
SET lock_timeout = '2s';
ALTER TABLE transactions ADD COLUMN notes TEXT;
-- If it can't get the lock in 2s, it fails. Retry during a quiet moment.

-- Step 2: For operations that MUST complete:
-- Kill long-running queries before running the ALTER
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - INTERVAL '30 seconds'
  AND query NOT LIKE 'autovacuum%';

-- Then immediately run the ALTER
SET lock_timeout = '500ms';
ALTER TABLE transactions ADD COLUMN notes TEXT;

The window between killing queries and running the ALTER must be short — new long-running queries can start in the gap.

Lock Modes in Practice

sql
-- What lock does each statement acquire?
-- ACCESS SHARE (compatible with everything except ACCESS EXCLUSIVE):
SELECT * FROM transactions WHERE id = 1;

-- ROW SHARE (used with explicit row locking):
SELECT * FROM transactions WHERE id = 1 FOR UPDATE;

-- ROW EXCLUSIVE (normal writes):
INSERT INTO transactions (...) VALUES (...);
UPDATE transactions SET status = 'confirmed' WHERE id = 1;
DELETE FROM transactions WHERE id = 1;

-- SHARE UPDATE EXCLUSIVE (long-running maintenance, allows reads/writes):
VACUUM transactions;
ANALYZE transactions;
CREATE INDEX CONCURRENTLY idx_new ON transactions (sender);

-- ACCESS EXCLUSIVE (blocks everything):
ALTER TABLE transactions ADD COLUMN x TEXT;
TRUNCATE transactions;
DROP TABLE transactions;
LOCK TABLE transactions;  -- explicit full lock

Row-Level Locks

Table-level locks protect the schema. Row-level locks protect individual tuples from concurrent modification. They are separate systems.

The Four Row Lock Modes

FOR UPDATE

• Strongest row lock. Marks the row as being updated.
• Blocks: other FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE
• Use when: you intend to update or delete the row, or you need to prevent any concurrent modification

FOR NO KEY UPDATE

• Like FOR UPDATE but does not block FOR KEY SHARE
• Use when: updating non-key columns (foreign keys to this row remain unblocked)
• Postgres uses this internally for UPDATE statements that don't modify key columns

FOR SHARE

• Prevents other transactions from updating or deleting the row, but allows other shared locks
• Blocks: FOR UPDATE, FOR NO KEY UPDATE
• Does not block: other FOR SHARE, FOR KEY SHARE
• Use when: reading a row that must not change for the duration of your transaction (e.g., verifying a foreign key reference)

FOR KEY SHARE

• Weakest row lock. Prevents deletion or key-column updates only.
• Blocks: FOR UPDATE, FOR NO KEY UPDATE
• Does not block: FOR SHARE, other FOR KEY SHARE
• Postgres uses this automatically for foreign key checks

sql
-- Compatibility matrix for row locks:
--                    FOR KEY SHARE  FOR SHARE  FOR NO KEY UPDATE  FOR UPDATE
-- FOR KEY SHARE           ✓            ✓              ✗               ✗
-- FOR SHARE               ✓            ✓              ✗               ✗
-- FOR NO KEY UPDATE       ✗            ✗              ✗               ✗
-- FOR UPDATE              ✗            ✗              ✗               ✗

FOR UPDATE — The Correct Pattern

sql
-- Atomic debit: read the balance, verify it, update it — no concurrent modification
BEGIN;

SELECT balance
FROM accounts
WHERE id = $1
FOR UPDATE;            -- holds exclusive row lock until transaction ends

-- Application checks: if balance >= amount, proceed
UPDATE accounts
SET balance = balance - $amount
WHERE id = $1;

COMMIT;
-- Row lock released on COMMIT

The FOR UPDATE lock prevents any other transaction from modifying this row between your SELECT and your UPDATE. Without it, two concurrent debits can both read the same balance and both proceed.

But note: with PG18's RETURNING OLD/NEW (Module 9), this two-step pattern is often unnecessary. Prefer atomic single-statement operations where possible.

SKIP LOCKED: Non-Blocking Queue Processing

SKIP LOCKED skips rows that are currently locked by another transaction. This enables efficient work queue patterns:

sql
-- Worker claims the next available pending job without blocking other workers
BEGIN;

SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;   -- skip rows locked by other workers

-- If a row is returned, claim it
UPDATE jobs SET status = 'processing', worker_id = $1 WHERE id = $returned_id;

COMMIT;

Without SKIP LOCKED, multiple workers queue up on the same row. With SKIP LOCKED, each worker instantly claims a different row. This is the correct pattern for implementing a job queue in Postgres.

NOWAIT: Fail Fast on Lock Contention

sql
-- Fail immediately if the row is locked (don't queue)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"
-- → application retries or returns "try again" to client

NOWAIT is preferable to waiting when the application can handle the retry — it keeps connection hold time short and prevents lock queue buildup.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs.

sql
-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- ... waiting for row 2 ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- locks row 2
-- ... waiting for row 1 ...
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- DEADLOCK: A waits for B's lock on row 2, B waits for A's lock on row 1

Postgres detects deadlocks automatically (every deadlock_timeout milliseconds, default 1s) and terminates one transaction:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890;
        blocked by process 54321.
        Process 54321 waits for ShareLock on transaction 12345;
        blocked by process 12345.
HINT: See server log for query details.

Preventing deadlocks: always acquire locks in the same order across all code paths. If every transaction locks row IDs in ascending order, deadlocks are impossible.

sql
-- Safe: always lock lower ID first
BEGIN;
SELECT * FROM accounts WHERE id = LEAST($1, $2) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST($1, $2) FOR UPDATE;
-- ... proceed with updates ...
COMMIT;

sql
-- Monitor deadlock frequency
SELECT deadlocks, conflicts FROM pg_stat_database WHERE datname = current_database();

Advisory Locks: Application-Level Distributed Locks

Advisory locks are cooperative locks with no automatic semantic — Postgres acquires and releases them only when you explicitly call the advisory lock functions. They are:

• Lightweight (stored in shared memory, not in system tables)
• Scoped to either a transaction or a session
• Identified by a 64-bit integer (or two 32-bit integers)
• Not tied to any table or row

Transaction-Level Advisory Locks

sql
-- Acquire lock for this transaction (released automatically on COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);
-- ... critical section ...
COMMIT;  -- lock released automatically

-- Try to acquire without blocking:
SELECT pg_try_advisory_xact_lock(12345);
-- returns true if acquired, false if already held

Session-Level Advisory Locks

sql
-- Acquire lock for this session (persists until explicitly released or session ends)
SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);  -- must release explicitly

Session-level locks survive transaction boundaries. Use them for long-running operations that span multiple transactions.

Production Pattern: Distributed Singleton Job

The most common use case: ensuring only one instance of a background job runs at a time across multiple application servers.

sql
-- Background job startup:
DO $$
BEGIN
  -- Try to acquire advisory lock for job ID 1001
  IF pg_try_advisory_lock(1001) THEN
    -- We are the only instance running this job
    PERFORM run_daily_settlement();
    PERFORM pg_advisory_unlock(1001);
  ELSE
    RAISE NOTICE 'Job 1001 already running on another instance, skipping';
  END IF;
END;
$$;

sql
-- Application-level mutual exclusion for a resource
-- Hash the resource identifier to a bigint
SELECT pg_advisory_xact_lock(
  hashtext('user_export_' || $user_id::text)
);
-- Only one export for this user can run at a time
-- Lock released automatically when transaction ends

Viewing Active Advisory Locks

sql
SELECT
  pid,
  locktype,
  classid,
  objid,
  mode,
  granted
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY pid;

Advisory Locks vs Application-Level Locks (Redis SETNX)

For application-level coordination that also modifies database state, advisory locks win on atomicity and simplicity. For coordination between processes that do not share a Postgres connection (microservices), Redis or a distributed lock service is appropriate.

The Production Incident: ALTER TABLE Queue Cascade

Context: A team ran ALTER TABLE transactions ADD COLUMN region TEXT NOT NULL DEFAULT 'us-east' during a low-traffic window — but did not set lock_timeout.

What happened:

A background analytics query had been running for 8 minutes (no statement_timeout was set). It held ACCESS SHARE on the transactions table. The ALTER TABLE queued waiting for ACCESS EXCLUSIVE.

Over the next 30 seconds, 400 application connections tried to query transactions. Each one queued behind the ALTER TABLE. PgBouncer's connection pool filled. New application requests began failing with connection pool full. The analytics query ran for another 4 minutes.

Root cause: one 12-minute analytics query + no lock_timeout on the ALTER TABLE = 430 blocked connections.

The fix and prevention:

sql
-- Always set both timeouts before any DDL on a live table
SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER TABLE transactions ADD COLUMN region TEXT NOT NULL DEFAULT 'us-east';

ini
# Set statement_timeout globally to prevent the underlying cause
statement_timeout = '300s'
idle_in_transaction_session_timeout = '60s'

Summary