PostgreSQL Transactions and Isolation Levels

Concurrency is the norm in databases, and it is also fertile ground for bugs. When two transactions update the same row simultaneously, one reads while another writes, or one runs an aggregate while another inserts, a lack of control can produce dirty reads, phantom reads, and lost updates: the kinds of problems that get you paged in the middle of the night.

PostgreSQL takes a pragmatic but rigorous approach: the default Read Committed is usually enough, you can move up to Serializable when strong consistency is required, and there is an underrated Repeatable Read level in between. To choose the right level, you first need to understand exactly what each one prevents, what it does not prevent, and how PostgreSQL implements MVCC.

1. What Actually Goes Wrong Without Concurrency Control?

First, consider four classic concurrency anomalies. Once you understand them, isolation levels become a checklist of what they prevent and what they allow.

1. Dirty Read: Transaction A reads data that transaction B has not yet committed. If B rolls back, A has read data that never actually existed. This is the most serious anomaly, and nearly all databases prevent it by default.

2. Non-repeatable Read: Transaction A reads the same row twice. Between those reads, transaction B modifies and commits the row, so A gets different results. The key point is that the same row changed.

3. Phantom Read: Transaction A executes the same range query twice. Between those queries, transaction B inserts or deletes matching rows, so the second query contains additional or missing "phantom" rows. The key point is that the set of rows changed.

4. Lost Update: Transactions A and B both calculate a new value from the same stale value and then write it back. The later write overwrites the earlier one, silently losing A's update. Concurrent debits using balance = balance - 100, inventory reductions, and counter increments have all encountered this classic problem.

Of these four problems, the first three concern the visibility of reads, while the fourth concerns write conflicts. Isolation levels primarily address the first three; lost updates often require additional safeguards.

2. The I in ACID Governs Concurrent Visibility

In ACID, A (Atomicity) relies on undo/redo logs, D (Durability) relies on persisting the WAL, and C (Consistency) represents the constraints the database must enforce. I (Isolation) determines what concurrent transactions can see of one another.

Isolation is a spectrum. Fully serial execution, where one transaction finishes before the next begins, provides the strongest isolation but unacceptable performance. No isolation at all offers the best performance but allows data to become inconsistent. The SQL standard defines four isolation levels, which essentially trade consistency for concurrency: the higher the level, the more anomalies it prevents, but the greater the performance cost.

The key insight is this: an isolation level defines the rules for a transaction's "snapshot of the world"; it is not simply a switch for locks. Once you understand that, MVCC follows naturally.

3. The Four Isolation Levels and the Anomalies They Allow

The four isolation levels defined by the SQL standard allow the following anomalies from the standard's perspective:

It may sound like a tongue twister, but there is a pattern: each step up prevents one additional anomaly. The lowest level allows all of them, while the highest allows none. But note that this is the SQL standard, not PostgreSQL's actual behavior. PostgreSQL makes its own implementation tradeoffs, as explained below.

4. PostgreSQL's MVCC: Multiversion Concurrency Control

PostgreSQL implements isolation using MVCC (Multiversion Concurrency Control). Its core idea is: each row retains multiple versions, reads use the old version visible to their own snapshot, writes create new versions, reads do not block writes, and writes do not block reads.

Each row on disk, specifically in its tuple header, has two important transaction IDs:

• xmin: the ID of the transaction that inserted or created the row
• xmax: the ID of the transaction that deleted or updated the row, or 0 if it has not been deleted

When a transaction reads a row, it uses its snapshot to determine whether that row's xmin and xmax are "visible" to it. Was this row version committed before I started? Was it deleted only after I started? If the version is visible, the transaction reads it. Otherwise, it skips that version and follows the version chain to find the next visible one.

Updates do not modify rows in place. PostgreSQL marks the old row with xmax and inserts a new row with a new xmin. A PostgreSQL UPDATE is therefore essentially a DELETE plus an INSERT. This is why frequently updated large tables become bloated and require VACUUM to remove "dead tuples."

MVCC provides an enormous benefit: reads and writes do not block each other. Long-running queries do not stall writes, and writes do not wait merely because someone is running a SELECT. The tradeoff is accumulated row versions and the need for VACUUM, but it is a worthwhile exchange.

5. PostgreSQL Does Things Its Own Way: RU Equals RC, and RR Prevents Phantom Reads

PostgreSQL's isolation implementation differs from the SQL standard in two important ways. These differences are common interview questions and frequent sources of mistakes in production:

Difference 1: Read Uncommitted is equivalent to Read Committed in PostgreSQL. PostgreSQL simply does not allow dirty reads. If you select READ UNCOMMITTED, PostgreSQL internally runs the transaction as READ COMMITTED. The documentation states it plainly: PostgreSQL does not allow dirty reads. Its lowest effective level is therefore Read Committed, which is good news for developers.

Difference 2: Repeatable Read already prevents phantom reads in PostgreSQL. PostgreSQL's RR uses SI (Snapshot Isolation). It takes a snapshot when the transaction begins and uses that snapshot throughout the transaction, so it does not see new rows committed by other sessions. Under PostgreSQL's RR, running the same range query twice in one transaction produces exactly the same result. Phantom reads that are "possible" under the standard are "impossible" in PostgreSQL.

What does PostgreSQL's RR fail to prevent? Serialization anomalies such as write skew. Two transactions can each read a consistent state from their snapshots and make individually valid changes that violate a business constraint when combined.

Consider the classic example of an on-call rule requiring "at least one person to remain on duty." A and B are both on duty. A's transaction sees that B is on duty and marks A as off duty. B's transaction sees that A is on duty and marks B as off duty. Both transactions commit successfully, leaving nobody on duty. RR cannot prevent this because the transactions write different rows, so there is no write conflict for MVCC to detect.

PostgreSQL's actual concurrency table therefore looks like this:

This is the table you actually need to remember when working with PostgreSQL.

6. Serializable: PostgreSQL Uses SSI for True Serializability

SERIALIZABLE provides the strongest isolation. PostgreSQL 9.1+ implements it using SSI (Serializable Snapshot Isolation). In addition to snapshots, SSI tracks read/write dependencies between transactions and detects "dangerous dependency structures" at runtime. If it finds an execution sequence that could cause a serialization anomaly, it aborts one of the transactions and raises a could not serialize access due to read/write dependencies error.

It is unquestionably strong, but the costs are clear:

1. Performance overhead: SSI must maintain predicate locks (SIREAD) and a dependency graph, significantly reducing throughput under high write concurrency.
2. Retries are mandatory: Your code must catch SQLSTATE 40001, roll back the entire transaction, and restart it from the beginning. Using an ORM or application layer without explicit retry logic turns these failures into HTTP 500 errors in production.

Configuration:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or for a single transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Business SQL...
COMMIT;  -- If it fails, catch the exception and retry the entire BEGIN...COMMIT

A practical rule: Serializable is unbeatable when used correctly; when used incorrectly, it means slowness plus errors. Unless your business truly requires strong consistency, such as financial reconciliation or strict inventory constraints, do not blindly select the highest level.

7. Choosing a Level: Default Read Committed Fits Most Workloads

My recommendations are straightforward:

• Use the default Read Committed for the vast majority of applications. It is PostgreSQL's default and is sufficient for web applications, CRUD operations, list queries, and analytical reports. Each statement sees a consistent snapshot as of the start of that statement, and dirty reads are impossible.
• Use Repeatable Read when multiple reads within a transaction must remain consistent, such as for exports, reports, or reconciliation. It provides snapshot consistency without requiring retries like Serializable. Remember that it cannot prevent write skew.
• Use Serializable or explicit locking for finance, inventory, and critical consistency constraints. Choose either Serializable with application-level retries or explicit locking with SELECT ... FOR UPDATE.
• Do not blindly move to the highest level. Serializable has a real throughput cost under high write concurrency and requires supporting retry logic. If the team is not prepared to implement retries, upgrading the isolation level merely introduces failures.

Changing the isolation level:

-- Global default
ALTER DATABASE mydb SET default_transaction_isolation = 'read committed';
-- Current session
SET session characteristics as transaction isolation level repeatable read;

8. Preventing Lost Updates: SELECT FOR UPDATE and Optimistic Locking

Isolation levels primarily govern read visibility, but lost updates can occur at any level except Serializable and require separate protection. There are two common approaches:

Option 1: Pessimistic locking with SELECT ... FOR UPDATE. Lock the row when reading it so other transactions must wait.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- Lock this row
-- Have the application calculate the new value from the balance it read
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Concurrent transactions queue at SELECT FOR UPDATE, serializing updates to the same row. This is simple and reliable, but transactions may spend time waiting under high concurrency.

Option 2: Optimistic locking with a version column and CAS. Add a version column to the table, include it in the update condition, and retry if the update fails.

-- Add a version column to the table
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 0;

-- Apply a conditional update
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = :read_version;
-- affected_rows = 0 means another transaction changed it; retry the entire read-compute-write cycle

Optimistic locking is suitable for workloads with few conflicts, typically read-heavy workloads. It avoids lock waits and provides high throughput, but frequent conflicts make retries expensive. Pessimistic locking is better when conflicts are common. Neither is a silver bullet; choose based on your conflict rate.

PostgreSQL 9.1+ also provides the more specialized SELECT ... FOR NO KEY UPDATE, which locks updates to non-key columns without affecting other sessions that query the primary key. It is worth understanding when finer-grained locking is useful.

Summary

1. There are four concurrency anomalies: Dirty reads, non-repeatable reads, and phantom reads concern read visibility; lost updates concern write conflicts. Isolation levels primarily address the first three, while lost updates need separate protection.
2. PostgreSQL's actual isolation table differs from the SQL standard: Read Uncommitted equals Read Committed because PostgreSQL does not allow dirty reads. Repeatable Read uses SI and already prevents phantom reads, but it cannot prevent write skew.
3. MVCC is the foundation of PostgreSQL isolation: Each row has xmin and xmax, transactions read from their own snapshots, and reads and writes do not block each other. The cost is version bloat that requires VACUUM.
4. Serializable uses SSI and is strong but expensive: You must be ready to catch 40001 errors and retry the entire transaction. Otherwise, do not use it.
5. Default Read Committed is usually sufficient. Use Repeatable Read for reconciliation and Serializable or explicit locking only for strong consistency constraints. Protect against lost updates with SELECT FOR UPDATE for pessimistic locking or a version column for optimistic locking.

The best isolation level is not the highest one; it is the most appropriate one. Default Read Committed is a PostgreSQL developer's source of confidence, while Serializable is a luxury that only makes sense when paired with retry logic.