Recover from aborted transactions and deadlocks

Understand the likely causes

• A constraint, syntax or data error can abort an explicit transaction, leaving subsequent statements unable to proceed. A deadlock is a cycle of conflicting waits; a serialization failure rejects a history that violates the requested isolation. A pool that returns unclean connections can carry a failed transaction into another request and obscure the original error.

Preserve the first error and boundary

Capture the first SQLSTATE, named constraint if supplied, request identifier and transaction start/commit/rollback boundaries from existing application logs. A later 25P02 is normally a consequence, so retrying that later SELECT does not repair the initiating unique violation or invalid input. Redact values that contain personal or secret data. Determine whether the connection was already inside an explicit transaction and whether an exception bypassed the application’s cleanup path. No failing SQL is executed by this guide.

End the failed transaction deliberately

The application should issue ROLLBACK for an invalid overall business unit before returning the connection to its pool. If an expected optional operation was protected by a savepoint, explicit ROLLBACK TO that savepoint can permit continuation under a reviewed contract. Do not issue COMMIT and assume partial earlier work is a valid business result. The connection wrapper should clean up in exception paths and preserve the original error. External side effects require idempotency or an outbox design when the transaction is retried.

Observe waits without terminating sessions

An authorized operator can inspect blocking relationships for the current database. pg_blocking_pids identifies blockers relevant to a backend, while state and wait categories supply context. Details may be privilege-limited. A long transaction is not automatically the guilty one, and a currently empty blocker list does not disprove a deadlock that PostgreSQL already resolved by aborting a participant. Correlate existing deadlock logs and application errors with timestamps rather than manufacturing a new conflict on a live service.

SELECT pid, state, wait_event_type, wait_event, xact_start,
 pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

Retry the complete decision under a bound

For 40P01 or a retryable 40001, roll back and retry the whole idempotent transaction with bounded attempts and suitable backoff. Re-read the facts used to decide the writes. A lock timeout or NOWAIT outcome needs its own service policy and is not proof of a deadlock. Reduce avoidable conflicts by acquiring common keys in a consistent order, keeping transactions short and avoiding network or user waits inside locked intervals. Do not weaken isolation or skip necessary validation merely to silence errors.

SHOW transaction_isolation;
SHOW lock_timeout;
SHOW statement_timeout;

Keep it from recurring

• Centralize transaction ownership and exception cleanup, retain initiating SQLSTATEs and monitor transaction age. Document consistent lock ordering, retry eligibility and idempotency keys. Keep optional savepoint recovery narrow and test pooled-connection reuse after every expected error path.