Database

Transaction A holds row 1 and needs row 2, while B holds row 2 and needs row 1. Directed waits form a cycle. PostgreSQL may reject either transaction with 40P01.

Deadlocks happen when two writers wait on each other

A deadlock requires a cycle of incompatible waits. If one transaction holds a row while another waits, the holder can still finish and release it. If both hold a resource the other needs, neither can complete that path. PostgreSQL detects the cycle and aborts one transaction, allowing the other to continue. The application must distinguish that rejection from an ordinary slow wait. ...

A retained Repeatable Read snapshot includes old row 1 and A’s own newly inserted row 3. After A commits, a fresh read includes new row 1, B’s row 2 and A’s row 3.

MVCC selects row versions through visibility rules

Multi-version concurrency control lets PostgreSQL keep different row versions and decide which ones a query may see. An ordinary reader can continue seeing an older committed version while another transaction writes a newer one. This reduces conflicts between reading and writing, but does not give every transaction one permanently frozen view. Snapshot lifetime depends on isolation, and a transaction can see its own earlier changes even when other transactions cannot. ...