Module A-13·25 min read

Concurrency bugs are almost never about wrong SQL — they are about wrong locking assumptions. The complete PostgreSQL locking matrix.

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

Q: A Senior DBA executes ALTER TABLE users ADD COLUMN email TEXT; on a heavily trafficked users table. Concurrently, a long-running analytics query is executing SELECT COUNT(*) FROM users;. Shortly after the ALTER TABLE command is issued, new application requests attempting SELECT * FROM users WHERE id = 1; begin to time out, eventually leading to a connection pool exhaustion and a full outage. Which of the following best explains the root cause of this outage?

B. The ALTER TABLE was waiting for an ACCESS EXCLUSIVE lock, and its position in the lock queue caused all subsequent, normally compatible ACCESS SHARE requests to also queue behind it. — The correct answer is B. The lesson explicitly states that an ALTER TABLE attempts to acquire an ACCESS EXCLUSIVE lock. If a long-running SELECT (holding an ACCESS SHARE lock) is active, the ALTER TABLE will queue. The critical insight is that this waiting ACCESS EXCLUSIVE lock blocks ALL subsequent lock acquisitions on that table, even those (like new SELECTs) that would normally be compatible with the existing ACCESS SHARE lock. This leads to a cascading queue, connection pool exhaustion, and an outage. Option A is incorrect because the ALTER TABLE waits if a conflicting lock is held, it doesn't immediately acquire it. Option C is incorrect because a simple SELECT COUNT(*) acquires an ACCESS SHARE lock, not a ROW EXCLUSIVE lock, and the primary issue is queue blocking, not a deadlock in this specific scenario. Option D is incorrect as ALTER TABLE does not implicitly trigger VACUUM FULL.

Q: Consider a banking application where transfer_funds(sender_id, receiver_id, amount) is implemented. To prevent race conditions and ensure atomicity, the accounts table rows for both sender and receiver are locked. If two concurrent transactions attempt to transfer funds between the same two accounts (e.g., A -> B and B -> A), which of the following strategies is most effective in preventing deadlocks while maintaining data integrity?

D. Always acquire FOR UPDATE locks on the account rows in a consistent, predetermined order (e.g., by id ascending). — The correct answer is D. The lesson explicitly states that deadlocks are prevented by always acquiring locks in the same order across all code paths. If all transactions lock row IDs in ascending order, deadlocks are impossible. FOR UPDATE is the correct lock mode for rows that will be modified. Option A is incorrect because FOR SHARE prevents updates from other transactions but doesn't prevent deadlocks if the lock acquisition order is inconsistent, and FOR UPDATE is needed for modification. Option B is a good strategy for failing fast on contention but does not prevent the underlying deadlock condition if the lock order is inconsistent. Option C is incorrect because while advisory locks can be used for coordination, they don't automatically protect row modifications, and acquiring two advisory locks in an inconsistent order could still lead to an advisory lock deadlock. Row-level FOR UPDATE locks are the direct mechanism for protecting the data.

Q: A microservices architecture needs to ensure that a critical background job, process_daily_reports, runs on only one instance at a time across multiple application servers. This job also performs complex, multi-step database operations within a single transaction. Which locking mechanism is the most appropriate and why?

C. Utilize pg_try_advisory_xact_lock() with a unique job ID, leveraging its atomicity with database transactions and automatic cleanup. — The correct answer is C. pg_try_advisory_xact_lock() is ideal for this scenario because it provides a cooperative, application-defined lock that is scoped to the transaction. This ensures atomicity with the multi-step database operations and automatic release of the lock upon transaction commit or rollback (even in case of a crash), which is crucial for reliable singleton job execution. Option A is incorrect because LOCK TABLE ACCESS EXCLUSIVE would block all operations on the entire table, leading to severe contention. Option B is suitable for work queues where multiple workers process distinct items, not for ensuring a single instance of a specific job. Option D is a valid distributed lock strategy, but the lesson highlights that for coordination that also modifies database state, advisory locks win on atomicity and simplicity compared to external systems like Redis, which would require separate coordination and manual cleanup (e.g., TTLs).

What this module covers: Concurrency bugs in production are almost never about wrong SQL — they are about wrong locking assumptions. This module covers the complete PostgreSQL locking matrix from first principles: what every lock mode protects, how row-level locks differ from table-level locks, the precise semantics of FOR UPDATE vs FOR SHARE vs FOR NO KEY UPDATE, how ALTER TABLE can silently queue behind a single open transaction and take down your application, and how advisory locks let you build distributed coordination primitives directly inside Postgres.

Why Locking Is Hard to Reason About

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

Table-level locks — protect the table structure and relation-level operations
Row-level locks — protect individual tuple modifications
Page-level locks — internal, mostly transparent (buffer pins)
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

Lock Mode	Acquired By	Conflicts With
`ACCESS SHARE`	`SELECT`	`ACCESS EXCLUSIVE` only
`ROW SHARE`	`SELECT FOR UPDATE/SHARE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`	`SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE UPDATE EXCLUSIVE`	`VACUUM`, `ANALYZE`, `CREATE INDEX CONCURRENTLY`, `ALTER TABLE ... VALIDATE`	`SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE`	`CREATE INDEX` (non-concurrent)	`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE ROW EXCLUSIVE`	`CREATE TRIGGER`, some `ALTER TABLE`	`ROW EXCLUSIVE` and above
`EXCLUSIVE`	Rare — some replication operations	Everything except `ACCESS SHARE`
`ACCESS EXCLUSIVE`	`ALTER TABLE`, `DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, `LOCK TABLE`	Everything including `SELECT`

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:

text

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)

	Advisory Locks	Redis SETNX
Atomicity with DB transaction	Yes — lock and data modification in one TX	No — separate systems
Automatic cleanup on crash	Yes — session ends, lock released	Requires TTL
No additional infrastructure	Yes	No — requires Redis
Lock acquisition overhead	~microseconds	~1ms network RTT
Works without Postgres connection	No	Yes

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

Concept	Key Takeaway
`ACCESS EXCLUSIVE`	Acquired by `ALTER TABLE`, `TRUNCATE`, `DROP`. Blocks everything including `SELECT`. Always set `lock_timeout`.
Lock queue cascading	A waiting `ACCESS EXCLUSIVE` blocks all subsequent lock requests, even compatible ones.
`FOR UPDATE`	Exclusive row lock. Prevents concurrent modification. Released on `COMMIT`/`ROLLBACK`.
`FOR NO KEY UPDATE`	Like `FOR UPDATE` but doesn't block foreign key checks. Prefer when not updating key columns.
`FOR SHARE`	Shared row lock. Prevents updates but allows concurrent reads.
`SKIP LOCKED`	Skip locked rows. Correct pattern for work queues.
`NOWAIT`	Fail immediately on lock contention. Prevents queue buildup.
Deadlock prevention	Always acquire locks in consistent order across all code paths.
Advisory locks	Application-defined cooperative locks. Ideal for distributed job deduplication.
`lock_timeout`	Set it on every DDL statement on a live table.

Knowledge Check

A Senior DBA executes ALTER TABLE users ADD COLUMN email TEXT; on a heavily trafficked users table. Concurrently, a long-running analytics query is executing SELECT COUNT(*) FROM users;. Shortly after the ALTER TABLE command is issued, new application requests attempting SELECT * FROM users WHERE id = 1; begin to time out, eventually leading to a connection pool exhaustion and a full outage. Which of the following best explains the root cause of this outage?

Consider a banking application where transfer_funds(sender_id, receiver_id, amount) is implemented. To prevent race conditions and ensure atomicity, the accounts table rows for both sender and receiver are locked. If two concurrent transactions attempt to transfer funds between the same two accounts (e.g., A -> B and B -> A), which of the following strategies is most effective in preventing deadlocks while maintaining data integrity?

A microservices architecture needs to ensure that a critical background job, process_daily_reports, runs on only one instance at a time across multiple application servers. This job also performs complex, multi-step database operations within a single transaction. Which locking mechanism is the most appropriate and why?

Test your knowledge with more question sets

PreviousModule A-12: Production Operations: Monitoring, Migration, and the Runbooks That Matter Next Module A-14: Advanced Partitioning: Pruning, Maintenance, and pg_partman

Discussion

Join the discussion

Loading comments...