Locks, contention and deadlocks

What waits for a lock

Go back to the last blue mug. P7 has stock 1. Mina’s purchase 103 subtracts one mug, and Tariq’s purchase 104 tries to do the same. The guarded change keeps the stock test beside the subtraction:

UPDATE products
SET stock = stock - 1
WHERE product_id = 'P7' AND stock >= 1
RETURNING stock;

The UPDATE takes a row lock as part of changing P7. There is no need for a preceding locking read just to repeat this one-row test. Suppose Mina’s update returns while her transaction remains open. Tariq’s next operation encounters a changed row whose transaction has not yet finished.

Mina’s lock survives the end of her UPDATE. It is released when her transaction ends. If Mina commits, Tariq’s waiting guarded change checks the condition against stock 0 and returns no row. His caller must treat that result as a refused purchase and roll back its work. Waiting for the lock did not reserve a successful outcome.

A locking read asks for a lock explicitly:

BEGIN;
SELECT stock
FROM products
WHERE product_id = 'P7'
FOR UPDATE;
-- Check the returned stock and do the protected work.
ROLLBACK;

FOR UPDATE makes the returned row a resource that this transaction holds against conflicting writers and lockers. An ordinary SELECT stock does not ask for that row lock. In PostgreSQL it can read committed stock 1 while Mina’s uncommitted change is 0. That makes it useful for a display, but it has not secured a mug for a later purchase.

The distinction is between conflicting operations, not simply reads and writes. A locking SELECT can wait for a row; an ordinary snapshot read can proceed. Table locks, including those used for schema changes, add another kind of conflict, to which we will return.

Choose the row that governs the decision

A product row was a natural authority for its own stock. A total across several rows needs more thought. Start a separate scenario after order 103 exists. Its agreed lines are one mug at 20.00 and two teas at 4.50 each: 29.00 in all. It has no payments.

Suppose the shop requires total recorded payments not to exceed that line value. Two requests, A and B, each want to record 20.00. Either payment fits on its own. Both together make 40.00, which does not.

Locking rows does not lock a condition

A tempting first step is to lock the payments that will be summed:

SELECT payment_id, amount
FROM payments
WHERE order_id = 103
FOR UPDATE;

It returns no rows, so there is no payment row to hold. More fundamentally, the protected fact is a condition over every payment for the order, including a payment that does not exist yet. Even when some rows exist, their row locks alone do not stand for that whole condition.

Order 103 already has one stable parent row. Make that row the shared authority: every operation that could invalidate the payment limit must acquire its conflicting lock before making its decision. That serialises the decisions even when the payment set is empty.

With the order lock, A and B cannot be in the protected decision together. A commits 20.00; B then reads 20.00 and refuses another 20.00. With only existing payment rows locked, both can decide from zero and both can commit. The schema’s distinct payment identifiers do not express a limit on their sum.

The parent row works because of the protocol around it. A plain insert into payments is not automatically a correct payment-limit check. The protocol must cover payment creation and amount changes, moving a payment between orders, and any edit to order lines that could reduce the allowed total. A move needs both orders’ authorities. Each path must leave the rule true when it commits.

Where a declarative constraint expresses the rule, prefer its enforcement across callers. Where the rule needs a protocol, put that protocol at a boundary every writer must use. An unprotected administrative script can break a rule that all web requests respect. Constraints and data integrity develops that choice.

Read the decision after acquiring the lock

The successful payment run used a particular order of statements at Read Committed. First acquire order 103’s lock. Only after that statement returns, start the query that reads the line value and the paid amount. Keep the lock through the decision, insertion and commit.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT order_id
FROM orders
WHERE order_id = 103
FOR UPDATE;
-- Require exactly one returned order before continuing.

SELECT
  (SELECT sum(quantity * unit_price)
   FROM order_items WHERE order_id = 103) AS line_value,
  (SELECT coalesce(sum(amount), 0)
   FROM payments WHERE order_id = 103) AS paid;
-- First attempt: line_value 29.00, paid 0.00.
-- Waiting second attempt: line_value 29.00, paid 20.00.

The two sums use separate input sets, so order lines and payments do not multiply each other through a join. coalesce makes an empty payment sum zero. For this scenario the order is required to have its two lines; an absent line total is a refused operation, not permission to accept a payment.

The caller accepts only a positive proposed amount whose addition keeps paid + amount <= line_value. A, seeing zero, can continue on the same connection and transaction:

INSERT INTO payments (payment_id, order_id, amount, method)
VALUES (601, 103, 20.00, 'card');
COMMIT;

B must make the decision again from its own result. It sees 20.00, so another 20.00 fails the test. It inserts nothing and sends ROLLBACK. The committed payment total stays 20.00, leaving 9.00 unpaid. This example records payments; it does not call a payment provider or change order 103 to paid.

The decisive interval starts when the authority is acquired and ends with the transaction. Every relevant writer respects that interval, and the decision read begins inside it. Moving the read before acquisition would leave B using the earlier zero even though B eventually gets its turn.

A lock does not refresh a snapshot

Now keep the same order lock but change when B takes its snapshot. At PostgreSQL Read Committed a later statement can include A’s intervening commit. At Repeatable Read, B retains the snapshot established by its first query. B’s first locking SELECT can establish that snapshot before waiting.

Start again with no payments. A holds order 103, records 20.00 and commits while B waits for the same unchanged order row. Whether B’s later total includes that payment now depends on its snapshot, not just on the lock.

In the Repeatable Read comparison, the ownership order is correct: A has the guard, then B. The payment decision is still wrong. B’s snapshot excludes A’s payment, so B sees zero and can add another 20.00. Locking an unchanged guard is not the same as updating it: the concurrent-update failure that can protect a changed row does not arise just because two transactions lock it in turn.

Combining the lock and the aggregate into one Read Committed statement also removes the promised later snapshot. A subquery or lock-first CTE is still inside a statement that began before the wait ended. SQL text order is no substitute for the boundary between these two statements.

This protocol depends on Read Committed with a separate decision statement. Other designs can make the authority carry a conflicting write, or use Serializable with complete transaction retries. Their visibility and failure behaviour need to support the whole invariant; Transactions and isolation compares those choices.

Choose lock scope and strength

Order 103 is a smaller authority than the whole payments table. Two attempts for that order compete, as they must. A payment for another order can use that order’s own authority. Replacing them with one shop-wide guard would force unrelated orders through the same turn.

PostgreSQL table locks have modes too: a table lock need not block every operation. Commands take table locks automatically alongside their row work. An ACCESS EXCLUSIVE table lock, used by operations such as TRUNCATE, also conflicts with ordinary reads. “The query only reads” therefore does not mean it can never wait.

Advisory locks let an application assign a lock to a key of its own. The database does not infer that the key governs payment changes. A transaction-level advisory lock ends with the transaction; a session-level one can survive a rollback and remain with a pooled connection. Choose its lifetime deliberately, and make participation part of the same protocol as the decision.

Strength decides which operations conflict. Two FOR UPDATE requests on order 103 exclude each other. Two FOR SHARE requests can coexist, so replacing the first with the second would let both payment attempts enter the decision together. A weaker lock is useful only when it still excludes everything that could invalidate the protected work. PostgreSQL’s row-lock compatibility table is the reference for choosing one; names alone are not enough.

Keep the protected work short

Once A holds order 103, every competing attempt for that order depends on A ending. Asking the customer a question or calling a remote service during that interval extends every waiter’s delay. Prepare input first, then acquire, decide, change and end. Keep the reads that justify the decision inside the protected interval.

A statement can finish while its transaction sits idle, still holding the lock. The holder need not be using a processor to hold up everyone else. Inspect the transaction’s lifetime, not only the duration of the last SQL command.

If one authority remains hot despite short transactions, adding connections only creates more contenders for its turn. Changing the business unit of serialisation may help, but it requires a new argument that the rule still holds. How much work to admit and where to queue it belongs to Connections, overload and capacity.

How a deadlock forms

A waiter can make progress when its holder can finish. A deadlock forms when the dependencies close a cycle: each transaction needs a resource that another transaction in the cycle holds, and none can reach the step that releases it.

Use a new, lock-only scenario with P3 and P7. No purchase is being made and no stock changes. A locks P3; B locks P7. A then asks for P7, and B asks for P3. Each now holds the resource the other needs.

The first wait, A → B, was not a deadlock. B could still finish and release P7. B’s later request added B → A and closed the cycle. Waiting longer cannot make either next statement finish by itself.

PostgreSQL breaks the cycle by aborting a transaction. Either can be the victim; application correctness must not depend on a particular one losing. A deadlock error has SQLSTATE 40P01. If the application retries, it must restart the entire aborted work, including its decisions, rather than continue from the statement that failed. Retries and uncertain commit outcomes develops that recovery and the different case where a commit’s reply is lost.

Acquire locks in a consistent order

With P3 before P7 on both paths, B waits before taking P7. A can acquire P7 and end, then B can do the same. The ordering prevented the cycle by preventing B from holding the resource A needed while B waited for A.

BEGIN;
SELECT product_id FROM products
WHERE product_id = 'P3' FOR UPDATE;
SELECT product_id FROM products
WHERE product_id = 'P7' FOR UPDATE;
-- Protected work, then COMMIT or ROLLBACK.
ROLLBACK;

For a variable set of product keys, sort them with one agreed ordering and acquire them in that order on every path. An unordered IN ('P3', 'P7') names a set; its written order does not establish lock acquisition order. Product identifiers used to order acquisition must remain stable during this work.

The agreement has to include less frequent paths: cancellation, repair, imports and administrative changes. It also needs to account for locks acquired implicitly by writes, foreign-key checks and triggers. Ordering only the explicit FOR UPDATE statements is not a proof about all locks in a larger operation.

Acquire the strength the protected work needs early enough to avoid competing upgrades. If A and B first take compatible shared locks and then both ask to strengthen them, each can end up waiting for the other’s shared lock. Consistent resource ordering and suitable strength address different ways of making a cycle.

Follow the waiter to the blocker

When a request is stuck, “waiting for a lock” describes only one edge. Reconstruct what its transaction requests, who blocks it, and what that transaction is doing. The blocker may itself be waiting.

A can also wait for B while B waits for C, with no return dependency. Suppose B holds P7 and needs P3, while C holds P3 in an idle open transaction. If C ends, B can proceed and eventually release A. There is no cycle here, however long C has been idle. A deadlock detector does not supply a deadline for this wait.

Look at transaction age, current state and requested locks together. A long-lived transaction may have acquired its blocking lock much earlier than its last visible query. In PostgreSQL, pg_blocking_pids() identifies blocking sessions, including a conflicting request ahead in the wait queue; pg_stat_activity supplies transaction and query times and whether a session is idle in a transaction. A deadlock report records the cycle that the server found.

Use that evidence to find the protocol to repair: an unexpectedly long protected interval, an unnecessary shared guard, an inconsistent acquisition order or a competing upgrade. Cancelling an arbitrary waiter does not explain why the holder is still open. Native monitoring queries and bounded interventions belong to Operating and diagnosing PostgreSQL; establishing whether this wait accounts for the request’s delay belongs to Diagnosing a database incident.

Put a bound on waiting

A correct protocol can still wait too long for its caller. Choose what a busy resource means for the operation: wait for a bounded interval, refuse immediately, or select some other eligible work. The choice must preserve the meaning of the result.

Skipping can serve a queue consumer whose question is “give me an available job”. It cannot serve this payment decision by treating a busy authority as an empty payment total. If the operation requires order 103, failure to acquire it means the protected decision has not been made. NOWAIT and SKIP LOCKED apply to the selected row locks; the statement can still encounter a table-lock wait.

PostgreSQL’s lock_timeout bounds each lock-acquisition wait. statement_timeout bounds one statement, including its execution. An application request deadline has a wider lifetime, including time before the server gets the SQL. For example:

BEGIN ISOLATION LEVEL READ COMMITTED;
SET LOCAL lock_timeout = '250ms';
SET LOCAL statement_timeout = '2s';

SELECT order_id FROM orders
WHERE order_id = 103
FOR UPDATE;
-- Continue with the separate decision read only on success.
-- On failure, ROLLBACK the failed transaction.

These are invented budgets, not recommended universal settings. Several waits can each fit under 250 ms while their combined duration exceeds it. Several statements can each fit under 2 seconds while the request exceeds its own deadline. Set the bounds around the work the caller can still use, and end failed work cleanly before returning the connection.

deadlock_timeout serves another purpose: it delays the server’s deadlock check, with a default of one second. It is not a general maximum lock wait. The long chain ending at idle C can remain blocked beyond it because there is no cycle to break.

The final decision is the same one the payment example exposed. A timeout, an omitted row or an eventual lock grant is not proof that a business action is allowed. Acquiring the right authority, reading the right view, checking the rule and ending the protected work are the protocol.