PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 12

Locks, deadlocks and aborted transactions

Coordinate two writers and recover from errors without partial business work. Distinguish waiting, deadlock, serialization failure and the PostgreSQL aborted-transaction state.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Acquire necessary row locks in a consistent order.
Explain 25P02 cleanup, savepoints and bounded full retries.

MVCC does not remove write conflicts

Ordinary readers can often read an earlier committed version while a writer holds a row lock, but conflicting writers must coordinate. SELECT ... FOR UPDATE requests row-level protection within a transaction and also acquires relevant table-level locks. Locks are released at transaction end, so avoid network calls, user interaction and unexplained pauses inside the protected interval. DDL may require stronger table locks and can wait behind a long transaction even when its planned physical change is small.

Make account lock order explicit

Assume accounts 10 and 20 exist in a disposable fixture. Two separate point lookups make the intended increasing lock order explicit before a transfer decision. The application must check that both rows exist and the source has sufficient funds before continuing. Every competing transfer must follow the same ordering policy. The example stops with rollback and does not claim that merely selecting two rows automatically implements account authorization, money movement or a complete transfer invariant.

sql Illustrative SQL; expected results are reasoned, not measured.

BEGIN;
SELECT account_id, balance FROM quay_lab.accounts
WHERE account_id = 10 FOR UPDATE;
SELECT account_id, balance FROM quay_lab.accounts
WHERE account_id = 20 FOR UPDATE;
-- Application validates both rows and the business preconditions.
ROLLBACK;

A conflicting writer waits while the relevant row lock is held, subject to timeout and deadlock handling.

The first error matters more than 25P02

Inside an explicit transaction, many statement errors put the transaction into an aborted state. Later ordinary commands report current transaction is aborted, commands ignored until end of transaction block, with SQLSTATE 25P02. That message is a consequence, not the original constraint or syntax failure. Roll back the transaction before reusing the connection. A pool must return a clean connection; returning an aborted or idle transaction leaks state into the next request and can obscure the initiating error.

Use a savepoint for an expected local failure

A savepoint can preserve earlier valid work when one optional statement has an expected error, provided the application explicitly rolls back to the savepoint before continuing. This intentionally broken division illustrates the control flow; it must not be run as a normal production health check. After rollback to before_optional, the next SELECT can return one and the outer transaction can end normally. For an invalid overall business decision, use full rollback instead of pretending every error is locally recoverable.

sql Illustrative SQL; expected results are reasoned, not measured.

BEGIN;
SAVEPOINT before_optional;
SELECT 1 / 0; -- intentional division_by_zero (22012)
ROLLBACK TO SAVEPOINT before_optional;
SELECT 1 AS usable_again;
ROLLBACK;

Expected output

Expected sequence: division_by_zero; rollback to savepoint; usable_again = 1.

A script client may stop on the intentional error; the application must issue recovery deliberately.

Different failures need different decisions

A deadlock is a cycle of waits; PostgreSQL aborts a participant with 40P01 so another can proceed. Serialization failures use 40001 and also require reconsidering the complete transaction. A lock timeout or NOWAIT failure can be a contention outcome without proving a deadlock. SKIP LOCKED can support queue consumers but returns an intentionally inconsistent subset and is not a general reporting fix. Bound retries, preserve request idempotency and inspect blockers through monitoring rather than blindly terminating the longest-running backend.

Common errors and fixes

current transaction is aborted (25P02)

Why it happens: An earlier statement failed and its transaction was not cleaned up.
How to fix it: Find the first error and rollback or deliberately recover to a savepoint.
Prevent it next time: Use transaction wrappers that always clean pooled connections on exceptions.

deadlock detected (40P01)

Why it happens: Transactions acquired conflicting locks in a wait cycle.
How to fix it: Rollback and retry the whole bounded idempotent operation.
Prevent it next time: Use consistent lock order and shorter transaction scopes.

A queue technique loses report rows

Why it happens: SKIP LOCKED was used where a complete consistent result was required.
How to fix it: Choose a reporting snapshot or explicit wait policy instead.
Prevent it next time: Document which consumers may intentionally skip locked work.

Check your understanding

1. A unique violation is followed by several 25P02 messages. Which error is the root cause and what must happen before reuse?

Show worked solution

The original unique violation explains why the transaction failed; 25P02 only reports its aborted state. Roll back, retain the original SQLSTATE and named constraint, and decide whether the request is a duplicate or conflicting data. Do not retry later statements on the same failed transaction or return that connection to a pool without cleanup.

2. A locks row 10 then wants 20; B locks 20 then wants 10. Explain the remedy and its limit.