How PostgreSQL Transaction Isolation Levels Affect Your Code

PostgreSQL defaults to READ COMMITTED. A transaction groups statements that should succeed or fail together. Under READ COMMITTED, each SQL statement sees committed data as other requests change the database; the isolation level determines what its statements see while other transactions run.

Each level controls what a statement or transaction sees while other transactions run, and which problems PostgreSQL prevents. The default gives each statement a fresh view of committed data. Stronger levels keep the view more consistent throughout a transaction. At SERIALIZABLE, PostgreSQL can reject a transaction when concurrent work produces an outcome that could not happen if transactions ran one at a time. PostgreSQL describes these rules in its transaction isolation documentation.

READ COMMITTED gives each statement a fresh view

Under READ COMMITTED, each SELECT sees a snapshot of rows committed before that query began. A snapshot is the set of committed data a query can see at a particular moment. PostgreSQL uses multi-version concurrency control (MVCC), which keeps multiple versions of each row. This lets queries read a consistent snapshot while other transactions change the data.

As a result, two SELECT statements in one transaction can return different results. For example, assume inventory contains one row with quantity = 10:

-- Session A
BEGIN;
SELECT quantity FROM inventory WHERE id = 1;

-- Session B
BEGIN;
UPDATE inventory SET quantity = 9 WHERE id = 1;
COMMIT;

-- Session A
SELECT quantity FROM inventory WHERE id = 1;

Session A’s first query returns 10; its second returns 9. Session B committed between the two queries. This is a non-repeatable read: a transaction reads the same row twice and sees a value another transaction changed and committed.

READ COMMITTED also allows a phantom read, where repeating a query returns a different set of rows because another transaction inserted or deleted rows that match its condition. It prevents dirty reads, which would expose changes that another transaction has not committed. PostgreSQL’s READ UNCOMMITTED setting behaves exactly like READ COMMITTED, so it does not enable dirty reads.

Writes have an additional detail. If an UPDATE, DELETE, or row-locking query such as SELECT FOR UPDATE encounters a row another transaction is changing, PostgreSQL waits for that transaction. It then checks the condition against the updated row version. The update can therefore use the row’s latest committed value, even if an earlier query saw a different value.

For a simple inventory rule, update the quantity only if it is still above zero. One conditional update does both the check and the change:

UPDATE inventory
SET quantity = quantity - 1
WHERE id = 1 AND quantity > 0
RETURNING quantity;

The statement changes the quantity only if it is still above zero. Check whether it returned a row to tell whether the update succeeded or the item was unavailable.

REPEATABLE READ keeps one snapshot for the transaction

REPEATABLE READ gives the transaction a stable snapshot. If Session A reads a row, Session B changes and commits it, and Session A reads again, Session A still sees the earlier value. The same snapshot also keeps repeated queries from gaining new matching rows, so PostgreSQL prevents phantom reads at this level.

To see that behavior, change Session A’s transaction start in the earlier example:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM inventory WHERE id = 1;

After Session B updates and commits, Session A’s next SELECT still returns the value from its original snapshot. Session A ends that view with COMMIT or ROLLBACK.

A stable snapshot does not prevent every problem. For example, two transactions can each read that two doctors are on call, then turn off a different doctor. Because each transaction updates a different row, both can commit under REPEATABLE READ, leaving no doctor on call. This is a serialization anomaly: the result could not happen if the transactions ran one at a time in either order.

PostgreSQL can also abort a REPEATABLE READ transaction if it tries to update a row that another transaction changed after its snapshot began. Code must handle the resulting serialization failure; a transaction is not guaranteed to commit.

SERIALIZABLE rejects outcomes that cannot happen one at a time

SERIALIZABLE gives transactions a consistent snapshot and detects conflicts that could produce a serialization anomaly. PostgreSQL makes sure the committed transactions have the same effect as if they ran one at a time. If concurrent transactions cannot all commit under that rule, PostgreSQL aborts one and reports a serialization failure. The PostgreSQL documentation on isolation anomalies and serializable transactions describes these guarantees and failure behavior.

For the on-call example, assume on_call has two active rows, one for Ada and one for Grace. Start both transactions before either updates:

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE active;
UPDATE on_call SET active = false WHERE doctor = 'Ada';
COMMIT;

-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE active;
UPDATE on_call SET active = false WHERE doctor = 'Grace';
COMMIT;

If both sessions see two active doctors and try to turn off different rows, PostgreSQL aborts one transaction rather than commit a result with no active doctor. After a serialization failure, the client must retry the whole transaction so its queries and decisions use a new snapshot. Retrying only the final UPDATE would reuse decisions based on old data.

Choose the level around the invariant

An invariant is a rule your data must keep true. READ COMMITTED fits many web requests where each statement can use the latest committed data. Use atomic updates, database constraints, or row locks when they directly protect the rule your code must preserve. If a rule depends on several rows, choose between explicit locks and SERIALIZABLE based on the workload.

A stronger isolation level does not automatically make queries slower or prevent more bugs. REPEATABLE READ takes one snapshot per transaction; READ COMMITTED takes one per statement. At SERIALIZABLE, PostgreSQL checks for conflicts and may abort transactions that the client must retry. The cost depends on the queries and how often transactions conflict. Transaction duration and retry frequency also affect it. Measure your workload to find the actual cost.

Set the transaction’s isolation level before issuing its first query or changing data:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Read and write within the transaction
COMMIT;

Run SHOW transaction_isolation; to see the active level. PostgreSQL’s default comes from default_transaction_isolation. A level set for one transaction does not carry over to the next.

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