Transactions, isolation, and lock waits

Follow one unit of work

A transaction groups database changes so they can be committed or rolled back together. ROLLBACK abandons its changes. A savepoint allows a narrower recovery inside a transaction. Keep the transaction short and avoid waiting on an external HTTP request while holding locks. Transaction tutorial↗.

ACID provides a useful checklist: atomic work, maintained constraints, a defined isolation model, and durability under the configured storage/commit guarantees. Those guarantees do not combine two separate databases and a message broker into one atomic operation. Revisit MicroBank's distributed boundaries.

Expand ACID into the questions it answers: atomicity means the transaction's changes commit together or are abandoned; consistency means the database enforces its declared rules, not every unstated business rule; isolation defines interaction among concurrent transactions; durability concerns committed data surviving failures under the configured guarantees. Multi-version concurrency control (MVCC) retains row versions so readers can use snapshots rather than treating all concurrent writes as unreadable data.

Visibility is not the same as locking

PostgreSQL uses MVCC so readers can see a consistent snapshot without treating every writer as a reason to block. At the default Read Committed level, each statement gets a fresh snapshot. Repeatable Read uses a transaction snapshot; Serializable also detects executions inconsistent with serial ordering. Applications must handle retryable failures rather than assume a stronger isolation setting eliminates all errors. Isolation levels↗.

Two writers updating the same row can still wait on each other. Consistent lock ordering and shorter transactions reduce deadlock opportunities. A deadlock victim or serialization failure requires a retry of the complete transaction with bounded retry behavior, not just repeating the final statement.

Observe a bounded lock conflict

Open two interactive psql sessions using:

docker exec -it learnwithsk-postgres psql -X -U postgres -d study

Session A holds a lock on a fixture account; the timeout ensures abandoned idle work is eventually closed:

SET application_name = 'study-lock-holder';
SET idle_in_transaction_session_timeout = '60s';
BEGIN;
UPDATE study.accounts SET label = 'demo-a-held' WHERE account_id = 1;

Before 60 seconds pass, session B runs:

SET application_name = 'study-lock-waiter';
BEGIN;
SET LOCAL lock_timeout = '3s';
UPDATE study.accounts SET label = 'demo-a-waiter' WHERE account_id = 1;
ROLLBACK;

Expect a lock-timeout error in B, not a changed label. Run ROLLBACK; in A, then verify account 1 is still demo-a. If A timed out first, record what happened and repeat the exercise so you can observe B waiting for the lock.

A third session can inspect the wait during those three seconds:

SELECT pid, application_name, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE application_name IN ('study-lock-holder', 'study-lock-waiter');

This is a contention exercise, not a deadlock: only B is waiting. Do not terminate arbitrary sessions to “fix” it. Explicit locking↗, session timeouts↗, monitoring activity↗.

Checkpoint

Explain why an application can be slow while server CPU stays low. Describe the difference between a lock timeout and a caller giving up after a commit whose response was lost. The latter requires idempotent recovery, not an assumption that no write happened.

Check the two-session result

Session A holds an uncommitted update. Session B needs a conflicting row lock, waits, then reaches its configured timeout. Neither session commits, so account 1 remains demo-a. A separate ordinary read can still see the previously committed row under the default isolation behavior. Low CPU during a lock wait is therefore unsurprising.

A lost response after a successful commit is different: rollback in a later client attempt cannot erase a transaction that already committed. Use the durable operation identifier and idempotency contract to discover the result. With that distinction clear, inspect query execution plans next; a wait and an inefficient plan need different remedies.