Locks & Deadlocks

Most important commands to remember

• SELECT … FOR UPDATE — hold a row lock for the transaction.
• NOWAIT — report an unavailable row lock instead of waiting for it.
• COMMIT / ROLLBACK — finish the transaction and release its locks.

Commands and flags

Command or syntax	Meaning
psql -X -d lab	Open the test database without psql startup customizations.
CREATE TABLE / INSERT	Create the dedicated fixture and its single row.
PRIMARY KEY	Require a unique, nonnull row identifier.
BEGIN	Keep subsequent operations inside one transaction.
SELECT * … WHERE id = 1 FOR UPDATE	Read the row and acquire its update lock.
NOWAIT	Fail immediately if the row lock is unavailable.
COMMIT / ROLLBACK	Commit / abandon the current transaction.
DROP TABLE / \q	Remove the test table / exit psql.

-- A1 labels the session and step order. Semicolons end SQL statements. An expected lock error leaves B’s explicit transaction aborted until ROLLBACK.

The concepts that matter

1. Locks coordinate incompatible operations

A lock prevents operations that cannot safely proceed together from acting at the same time. Databases use different lock modes and granularities, including table-level and row-level coordination.

A PostgreSQL row lock does not mean all readers are stopped. Ordinary snapshot reads can often continue while a writer holds the row. The important question is which requested operation conflicts with which held lock, not merely whether a lock exists.

2. The transaction owns the waiting time

Many database locks remain held until the transaction ends. A quick update followed by a long pause before commit can therefore block other work for much longer than the update itself took.

Keep transactions focused and avoid waiting for a person or slow external service while holding locks. An idle connection inside a transaction can still be a blocker even though it is using almost no CPU.

3. A deadlock is a cycle, not just a long wait

If transaction A holds row 1 and needs row 2, while B holds row 2 and needs row 1, neither can progress. That circular dependency is a deadlock. One transaction waiting for another that can still finish is ordinary blocking.

PostgreSQL detects deadlocks and aborts one participant so the others can proceed. The application must handle the failed transaction. Merely increasing a timeout does not resolve a dependency cycle.

4. Consistent order reduces conflicts, retries handle the remainder

When transactions need several resources, acquiring them in a consistent order reduces deadlock opportunities. Short transaction duration also reduces the overlap in which conflicts can arise.

Immediate-failure options and timeouts bound waiting but transfer responsibility to the caller. A caller must decide whether to retry the whole operation, return a useful conflict, or abandon the work. Retrying endlessly under heavy contention can make the system less responsive.

One small example

Optional: open the first command in terminals A and B with the same database and schema. Run A1, B1, A2, then B2. Execute B1’s ROLLBACK after its deliberately expected error. Stop if the table already exists; do not operate on that existing table.

psql -X -d lab

-- A1
CREATE TABLE btc_lock_lab (id integer PRIMARY KEY);
INSERT INTO btc_lock_lab VALUES (1);
BEGIN;
SELECT * FROM btc_lock_lab WHERE id = 1 FOR UPDATE;

-- B1
BEGIN;
SELECT * FROM btc_lock_lab WHERE id = 1 FOR UPDATE NOWAIT;
ROLLBACK;

-- A2
COMMIT;

-- B2
BEGIN;
SELECT * FROM btc_lock_lab WHERE id = 1 FOR UPDATE NOWAIT;
COMMIT;
DROP TABLE btc_lock_lab;

\q

B1 should fail to obtain the row lock while A’s transaction is open. After A2 commits, B2 should acquire the lock and return row 1. No row value changes: selecting FOR UPDATE is enough to demonstrate ownership and conflict.

This is a lock-conflict example, not a deliberately created deadlock. DROP removes the fixture after both transactions finish; then quit both sessions. If interrupted, close or roll back open transactions before removing only the table you created.

Keep this idea: A lock wait has a blocker; a deadlock has a cycle. Transaction boundaries determine how long the conflict can last.