Database locking

The problem

Transaction A starts. It updates a row in the orders table where order_id = 1001, acquiring an exclusive row lock. Transaction B starts simultaneously. It updates a row in the shipments table where shipment_id = 5, acquiring an exclusive row lock. Then:

• Transaction A needs to update the shipment row for order 1001. Blocked. That row is locked by Transaction B.
• Transaction B needs to update order 1001's status. Blocked. That row is locked by Transaction A.

Neither will ever proceed. PostgreSQL detects the cycle after about one second and kills one transaction with ERROR: deadlock detected. The application retries. The exact same interleaving happens again, because both retry paths acquire locks in the same order. By the time the on-call engineer wakes up, hundreds of write queries are queued and p99 latency is measured in seconds.

Deadlocks are the most common write-contention failure in production databases. They are entirely preventable once you understand why they form.

What database locking is

Database locking is the mechanism that prevents two transactions from making conflicting changes to the same data simultaneously. A lock is an exclusive claim on a resource (a row, a page, or a table) that blocks other transactions from claiming it in an incompatible mode until the first transaction commits or rolls back.

Think of it like single-occupancy restrooms in a small restaurant. One person is inside (holds the lock). Everyone else queues outside (waits). When the person inside leaves (commits or rolls back), the first person in the queue enters. Multiple people can look through the window at the same time (read locks), but only one can occupy the room (write lock).

How database locking works

Every lock request goes through four stages: request, grant or queue, hold for the duration of the transaction, and release on commit or rollback. The deadlock scenario happens when two transactions form a cycle in the wait-for graph.

Step by step:

1. T1 acquires an exclusive lock on orders row 1001. Succeeds immediately.
2. T2 acquires an exclusive lock on shipments row 5. Succeeds immediately.
3. T1 requests a lock on shipments row 5. Held by T2, so T1 is queued.
4. T2 requests a lock on orders row 1001. Held by T1, so T2 is queued.
5. The lock manager detects the cycle (T1 waits for T2, T2 waits for T1). It aborts T2 as the victim and releases its locks. T1 proceeds.

// Pseudocode: deadlock detection via wait-for graph traversal

function check_deadlock(requesting_txn, blocking_txn):
    visited = {}
    queue  = [blocking_txn]

    while queue is not empty:
        node = queue.pop()

        if node == requesting_txn:
            return DEADLOCK_DETECTED     // cycle found

        if node in visited: continue
        visited.add(node)

        for each lock in node.waiting_for:
            queue.append(lock.holder)   // follow the wait chain

    return NO_DEADLOCK

PostgreSQL runs this check after deadlock_timeout (default: 1 second) has elapsed, not on every lock request. This means a deadlock takes at least 1 second to detect even if the cycle formed immediately.

The wait-for graph for the example above:

spawnSync d2 ENOENT

Lock types and the compatibility matrix

Not all lock modes conflict with each other. Reads can proceed alongside other reads. Intent locks allow the manager to check higher-level conflicts without scanning all row locks.

Intent locks (IS, IX) are taken on a table or page before acquiring row-level locks. This lets the lock manager detect table-level conflicts cheaply. For example: if Transaction A holds an Intent Exclusive lock on the orders table (meaning it will write some rows), Transaction B cannot acquire an Exclusive lock on the orders table (needed for TRUNCATE TABLE).

In PostgreSQL, you can observe live locks directly:

-- All locks currently held
SELECT pid, mode, granted, relation::regclass
FROM pg_locks
WHERE relation IS NOT NULL;

-- Which transactions are blocking which
SELECT
    blocked.pid    AS blocked_pid,
    blocking.pid   AS blocking_pid,
    blocked.query  AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Lock granularity: row-level vs table-level

The granularity at which locks are acquired determines how much concurrency is possible.

Row-level locking maximizes concurrency: two transactions writing different rows of the same table never conflict. The cost is overhead: a bulk UPDATE modifying 100,000 rows holds 100,000 simultaneous row locks. Each lock consumes memory in the lock table, and other transactions touching those rows must wait for every one of them to release.

Pessimistic locking: SELECT FOR UPDATE and variants

Pessimistic locking assumes conflicts will happen. It acquires a lock at read time, before any modification, so other transactions cannot modify the row until the first transaction commits or rolls back. Use it when you always need to write what you just read and the cost of a conflicting write would be incorrect business state.

-- Acquire an exclusive row lock at read time
BEGIN;
SELECT quantity FROM inventory
WHERE product_id = 7
FOR UPDATE;
-- No other transaction can modify this row until COMMIT or ROLLBACK.
-- quantity = 5, confirmed exclusively.
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT;

PostgreSQL provides four row-level lock modes for SELECT:

FOR UPDATE is the default. FOR NO KEY UPDATE is appropriate when you modify non-primary-key columns of a row with foreign key dependents, to avoid unnecessary conflicts with FK validators. FOR SHARE lets multiple transactions hold a shared read lock that blocks writers.

Two modifiers control what happens when the target row is already locked:

-- Raise an error immediately if the row is already locked (do not queue)
SELECT quantity FROM inventory WHERE product_id = 7 FOR UPDATE NOWAIT;

-- Skip rows currently locked by other transactions (for job queues)
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

NOWAIT is correct when queuing behind a lock is never acceptable: reject the request now rather than wait. SKIP LOCKED is the standard pattern for concurrent job queues where each worker should claim uncontested work rather than queue behind another worker claiming the same job.

Optimistic locking: version columns and retry