MVCC and isolation

MVCC keeps enough version information for a transaction to read the rows visible under its snapshot while other transactions create newer versions. Visibility need not mean the newest tuple. PostgreSQL's Read Committed ordinarily takes a new snapshot for each command, whereas Repeatable Read uses a transaction-level snapshot. A transaction's own writes have special visibility. These rules affect repeated SELECT results.

Readers can avoid many conflicts with writers, but MVCC does not abolish locks, write conflicts, deadlocks, or cleanup. Long snapshots can delay version cleanup and increase storage pressure. An UPDATE also performs concurrency checks and may wait for another updater. Transactional statements do not automatically make a multi-statement read-modify-write sequence atomic. A single conditional update can express some invariants more directly.

Begin with workers A and B active. Transaction T1 reads both and plans to disable A if B is active. T2 reads both and plans to disable B if A is active. Under a snapshot-isolation style execution, each sees the initial valid state and writes a different row. Neither overwrites the other's row, so ordinary same-row conflict detection need not reject either. The final state violates the invariant even though both transactions obeyed their local rule.

A serial execution could not produce that result if each transaction re-evaluates the rule against the preceding committed state. This is the meaning of serializability: the committed outcome corresponds to some serial order, not necessarily that transactions literally ran one at a time. PostgreSQL's Serializable isolation detects dangerous dependency patterns and can abort transactions. Alternatively, at Read Committed, acquire a FOR UPDATE row lock on one common guard row, then read the predicate in a subsequent command. Every decision path must follow that protocol; a preexisting Repeatable Read snapshot can remain stale after waiting for a lock.

A serialization failure means the transaction must be retried from an appropriate beginning with fresh reads. Repeating only its final UPDATE preserves a decision based on an invalid snapshot. Put retryable database work inside a bounded attempt loop with jitter and a deadline. Do not automatically repeat an external email, payment, or irreversible action inside that loop; record an intent transactionally and arrange idempotent downstream processing when external effects are needed.

Calculate retry load before choosing an aggressive policy. Assume independent attempts with constant failure probability only for the simple estimate below; real contention can correlate failures and worsen as retries increase traffic. Track conflict rate, retries per operation, and tail latency. Lock ordering, shorter transactions, and better contention ownership may solve the cause more effectively than adding attempts. Test the actual concurrent history, including deletion and insertion paths that could change the predicate's meaning.