LEARN. PRACTICE. GROW.Concept and mechanism
MVCC lets queries observe row versions consistent with their snapshot. Under Read Committed, successive statements can see different commits; BEGIN does not fix one view for the entire transaction. Serializable adds guarantees, but applications must handle serialization failures such as 40001 by retrying the whole transaction with fresh reads. A deadlock is circular dependency and can arise when operations acquire locks in opposite order. Error 40P01 requires handling the victim; consistent access order reduces recurrence. Do not confuse a long wait queue with proof of deadlock. Collect transaction, application, and dependency-chain evidence to support diagnosis.
Guided application
At a fictional close, dozens of sessions may wait behind an idle-in-transaction transaction the client forgot to end. Identify its owner and effects before cancelling or terminating the session. pg_cancel_backend cancels the statement; pg_terminate_backend terminates the session. Neither function alone demonstrates service recovery. statement_timeout, lock_timeout, and idle_in_transaction_session_timeout target different situations and need compatibility with the pool. After intervention, confirm observed rollback or commit, released locks, and batch processing. Retries should be bounded and external effects protected from repetition, especially when a client response is lost after commit.
40001 calls for a fresh transaction attempt; 40P01 calls for cycle analysis and victim retry. The decision includes effects outside the database.
Common pitfalls
Retrying only the last statement; timeout as rollback proof; killing every waiting session.
Related topics: Connections, identities, and privileges · Plans, indexes, and memory · Vacuum, configuration, and capacity
Act on the observed cause and validate service recovery.
Reference: MVCC and transaction isolation · PostgreSQL 18 reference semantics;18.6 current stable at review