Locking in Practice

PostgreSQL uses locks to coordinate concurrent access. Understanding lock types, when they are acquired, and how to debug them is essential for any production system.

Row-Level Locks

Row-level locks are the most common and the least intrusive. They only affect the specific rows being locked.

FOR UPDATE

Locks the selected rows for modification. Other transactions that try to UPDATE, DELETE, or SELECT FOR UPDATE the same rows will block until the lock is released.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- This row is now locked. Other transactions wait if they try to modify it.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

This is the standard pattern for read-then-write operations at Read Committed isolation.

FOR NO KEY UPDATE

A weaker version of FOR UPDATE. Blocks other FOR UPDATE and FOR NO KEY UPDATE but does not block operations that only reference the row through a foreign key.

BEGIN;
SELECT * FROM orders WHERE id = 42 FOR NO KEY UPDATE;
-- Allows concurrent inserts into order_items that reference this order
UPDATE orders SET status = 'processing' WHERE id = 42;
COMMIT;

Allows multiple transactions to hold the lock simultaneously, but blocks any transaction that tries to modify the row.

BEGIN;
SELECT * FROM products WHERE id = 10 FOR SHARE;
-- Other transactions can also SELECT FOR SHARE
-- But no one can UPDATE or DELETE this row until all FOR SHARE locks are released
COMMIT;

Use FOR SHARE when you need to ensure a row is not modified while you read it, but other readers are fine.

The weakest row lock. Only prevents the row's key columns from being changed. This is what PostgreSQL acquires automatically when checking foreign key constraints.

NOWAIT & SKIP LOCKED

-- Fail immediately instead of waiting
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"

-- Skip rows that are already locked
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED is essential for job queue patterns (covered in the concurrency patterns subtopic).

Table-Level Locks

Every SQL statement acquires a table-level lock. Most are compatible with each other and you never notice them. The problems start with DDL.

Lock Compatibility (Key Levels)

From weakest to strongest:

ACCESS SHARE: acquired by SELECT. Compatible with everything except ACCESS EXCLUSIVE.
ROW SHARE: acquired by SELECT FOR UPDATE/SHARE. Compatible with most locks.
ROW EXCLUSIVE: acquired by INSERT, UPDATE, DELETE. Compatible with ACCESS SHARE and ROW SHARE.
SHARE: acquired by CREATE INDEX (non-concurrent). Blocks writes but allows reads.
ACCESS EXCLUSIVE: acquired by ALTER TABLE, DROP TABLE, VACUUM FULL, REINDEX. Blocks everything — no reads, no writes.

The DDL Lock Problem

-- This takes an ACCESS EXCLUSIVE lock on the table
ALTER TABLE orders ADD COLUMN priority int DEFAULT 0;

While this runs, every query against the orders table blocks. On a busy table, even a fast ALTER can cause a pileup because it has to wait for existing transactions to finish before it can acquire the lock.

The real danger: the ALTER waits in the lock queue, and while it waits, all new queries also queue behind it.

-- Safer: set a lock timeout so the ALTER fails quickly if it can't get the lock
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN priority int;

If it times out, retry during a quieter period.

Creating Indexes Without Blocking

-- Standard CREATE INDEX blocks writes
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- CONCURRENTLY does not block writes (takes longer, uses more resources)
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

CONCURRENTLY cannot run inside a transaction and may fail, leaving an INVALID index. Check for invalid indexes:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

Drop invalid indexes and retry.

Advisory Locks

Application-level locks that PostgreSQL manages but does not tie to any particular table or row. Useful for coordinating application logic.

Session-Level Advisory Locks

-- Acquire a lock identified by a bigint
SELECT pg_advisory_lock(12345);

-- Do exclusive work (e.g., ensure only one process runs a migration)

-- Release the lock
SELECT pg_advisory_unlock(12345);

Session-level locks persist until explicitly released or the session ends.

Transaction-Level Advisory Locks

BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Lock is held until the transaction ends
COMMIT;  -- Lock automatically released

Try Variants (Non-Blocking)

-- Returns true if lock was acquired, false if not
SELECT pg_try_advisory_lock(12345);

Two-Key Advisory Locks

-- Lock identified by two integers — useful for (entity_type, entity_id) patterns
SELECT pg_advisory_lock(1, 42);  -- entity type 1, entity id 42

Common Use Cases

Singleton processes: ensure only one instance of a cron job runs at a time.
Rate limiting: lock a resource ID before processing.
Migration coordination: prevent multiple app instances from running migrations simultaneously.

Deadlock Detection

A deadlock occurs when two transactions each wait for a lock held by the other.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Holds lock on row 1

-- Session B
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Holds lock on row 2

-- Session A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Waits for Session B's lock on row 2

-- Session B
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Waits for Session A's lock on row 1
-- DEADLOCK!

PostgreSQL detects this (default check interval: 1 second, controlled by deadlock_timeout) and aborts one of the transactions:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 9012.
        Process 9012 waits for ShareLock on transaction 1234;
        blocked by process 1234.
HINT: See server log for query details.

Avoiding Deadlocks

Lock resources in a consistent order. If every transaction that needs to update multiple accounts does so in ascending ID order, deadlocks cannot occur.

-- Always lock in ascending order
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);
COMMIT;

Or more explicitly:

BEGIN;
-- Lock both rows in a consistent order first
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Now update safely
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Debugging Locks with pg_locks

-- View current locks with useful context
SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       l.relation::regclass,
       a.query,
       a.state,
       a.wait_event_type,
       a.wait_event,
       age(now(), a.query_start) AS query_duration
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
ORDER BY a.query_start;

Finding Blocked Queries

-- Queries waiting for locks
SELECT a.pid,
       a.query,
       a.wait_event_type,
       a.wait_event,
       age(now(), a.state_change) AS waiting_since
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY a.state_change;

Finding What Is Blocking Them

-- The blocking chain
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks bk ON bk.relation = bl.relation
  AND bk.granted AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid;

Terminating a Blocking Session

-- Ask the backend to cancel its current query
SELECT pg_cancel_backend(1234);

-- Force-terminate the backend (last resort)
SELECT pg_terminate_backend(1234);

Common Pitfalls

Holding locks while doing slow operations. Never make HTTP calls, send emails, or do heavy computation inside a transaction that holds row locks.
Forgetting that DDL takes ACCESS EXCLUSIVE locks. A simple ALTER TABLE on a busy table can cause a cascade of blocked queries. Always set lock_timeout.
Not using CONCURRENTLY for index creation in production. Standard CREATE INDEX blocks all writes on the table.
Releasing advisory locks in the wrong order or not at all. Use transaction-level advisory locks (pg_advisory_xact_lock) to ensure automatic release.
Debugging deadlocks without looking at the PostgreSQL log. The log contains the full query details for both sides of the deadlock.
Using FOR UPDATE when FOR SHARE would suffice. FOR UPDATE blocks other readers that also use FOR UPDATE. If you only need to prevent modification, FOR SHARE is less restrictive.

Key Takeaways

Row-level locks (FOR UPDATE, FOR SHARE) are the primary tool for coordinating concurrent writes. They only block conflicting operations on the same rows.
Table-level locks from DDL (ALTER TABLE, CREATE INDEX) can block entire workloads. Use lock_timeout and CREATE INDEX CONCURRENTLY.
Advisory locks provide application-level locking without tying to specific rows or tables. Prefer transaction-level variants for automatic cleanup.
Deadlocks are detected and resolved automatically. Prevent them by locking resources in a consistent order.
pg_locks and pg_stat_activity are your primary debugging tools for lock issues.
Keep transactions short to minimize lock duration and contention.