How PostgreSQL Prevents Transaction Interference

When two PostgreSQL sessions work at the same time, one can read a row while the other updates it. A transaction is a group of database operations that PostgreSQL treats as a unit. PostgreSQL uses multiversion concurrency control, or MVCC, which stores multiple versions of rows so many transactions can proceed without making every reader wait for every writer.

With MVCC, PostgreSQL keeps row versions and gives each query a snapshot: the set of committed data that the query is allowed to see. The transaction’s isolation level determines how that view behaves and how PostgreSQL handles conflicting changes. Understanding those choices helps you decide when the default is enough, when to lock rows, and when to retry a transaction.

MVCC lets readers use row versions

Suppose one session reads a row while another session updates it. PostgreSQL can show the reader the row version visible to its snapshot while the writer creates a newer version. The read and update can proceed without waiting on each other in many ordinary cases. PostgreSQL describes this approach in its MVCC introduction.

MVCC reduces interference, but transactions can still wait or conflict. When transactions update the same row, PostgreSQL coordinates their changes. Some operations that affect a whole table block other access. Explicit locks also let a transaction deliberately reserve rows or coordinate work. PostgreSQL’s concurrency behavior therefore depends on both the versions a transaction can see and the locks its statements acquire.

Isolation determines what each transaction sees

PostgreSQL accepts four standard isolation-level names, but READ UNCOMMITTED behaves like READ COMMITTED. The default is READ COMMITTED. The levels differ in how long PostgreSQL keeps a transaction’s view stable and what anomalies it prevents, as described in the transaction isolation documentation.

Choose READ COMMITTED when each statement can act on the latest committed data it sees. Choose REPEATABLE READ when several statements need to make decisions from the same snapshot. Choose SERIALIZABLE when a business rule depends on the combined effect of concurrent reads and writes.

A stable snapshot alone does not guarantee that concurrent transactions produce the same result as running one at a time. For example, PostgreSQL documents a case in which transactions read sums from different groups and then write those sums into the opposite groups. Under REPEATABLE READ, both transactions can commit even though their combined result cannot be produced by any serial order. That kind of result is a serialization anomaly.

Serializable detects unsafe read-write patterns

PostgreSQL implements SERIALIZABLE with Serializable Snapshot Isolation, or SSI. SSI tracks dependencies between transactions, including cases where one transaction reads data that another changes. PostgreSQL uses these dependencies to detect patterns that could produce a serialization anomaly and aborts a transaction when needed. The original PostgreSQL SSI paper describes how this approach preserves snapshot-based concurrency while enforcing serializable results.

To track some read dependencies, PostgreSQL uses predicate locks. These record which rows a query examined and which spans of values its conditions covered. PostgreSQL uses this information to detect when another transaction’s write could affect the query’s results. Predicate locks support conflict detection; they do not block writers or cause deadlocks. The query plan partly determines how PostgreSQL tracks these reads. PostgreSQL can combine detailed tracking into broader locks to manage memory.

SERIALIZABLE trades some transaction retries and monitoring overhead for a stronger guarantee. It does not have a universal performance advantage over other approaches; the result depends on the workload and its contention. Treat a transaction’s result as final only after PostgreSQL successfully commits it.

Use row locks for deliberate coordination

Use a row lock when a transaction must claim or reserve a particular row before another transaction changes it. For example, SELECT ... FOR UPDATE locks selected rows until the transaction ends, blocking other transactions that try to update those rows or request conflicting row locks. This creates a deliberate wait when another transaction needs the same row. PostgreSQL’s explicit locking documentation describes row and table lock behavior.

Locks also apply to tables. An ordinary SELECT takes a table-level lock that permits normal concurrent reads and writes, while TRUNCATE takes an ACCESS EXCLUSIVE lock that blocks other access to that table. A lock’s name describes its mode, and compatibility between modes determines which operations can proceed together.

Advisory locks let transactions coordinate over resources that do not map neatly to rows. PostgreSQL grants these locks only when code requests them, so every code path that needs to coordinate over the same resource must request the same lock. Transaction-level advisory locks end with the transaction; session-level locks remain until explicitly released or the database session ends.

Retry the whole transaction after a concurrency failure

When PostgreSQL reports SQLSTATE 40001, it signals a serialization failure, so the code that ran the transaction should rerun it from the start, including the logic that chose its queries and values. Retrying only the last statement can reuse a decision based on an earlier snapshot. PostgreSQL’s serialization failure guidance explains why the database cannot safely replay that application logic on its own.

A deadlock occurs when transactions wait on locks held by one another, so the code that ran them needs to handle the resulting failure. PostgreSQL detects the cycle and aborts one participant with SQLSTATE 40P01. Keep transactions short and acquire multiple locks in a consistent order to reduce these conflicts. Distinguish transient concurrency errors from persistent failures, such as a constraint violation that reflects invalid data.

A unique constraint, for example, makes PostgreSQL enforce uniqueness even when concurrent transactions attempt conflicting inserts. Use constraints for rules the database can enforce directly, and use the appropriate isolation level or locking strategy for rules that depend on a broader set of concurrent reads and writes.

Keep old row versions manageable

MVCC depends on retaining old row versions while active snapshots might still need them. Updates and deletes leave versions that PostgreSQL later cleans up or makes reusable through VACUUM. Autovacuum handles routine maintenance in many installations, though operators may need to adjust its settings for their workloads. PostgreSQL explains this work in its routine vacuuming documentation.

Long-running transactions can keep older snapshots active, delaying cleanup of row versions those snapshots might still need; keep transactions short and investigate long-lived transactions when database maintenance falls behind. PostgreSQL does not roll back every kind of change along with row versions: sequence values, for example, remain advanced even if the transaction that requested them aborts.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com