Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 90 ยท Transactions, MVCC and Concurrency

Isolation Levels and Anomalies

Learn PostgreSQL isolation levels with two-session examples: Read Committed, Repeatable Read, Serializable (SSI), write skew and how to retry serialization failures.

When two transactions run at the same time, each one wants to behave as if it were alone. Perfect isolation is expensive, so SQL defines levels that trade some protection for speed. PostgreSQL offers three effective levels, and the differences between them come down to one question from the MVCC lesson: when is the snapshot taken?

In this lesson you will see what each level guarantees, which strange results (anomalies) each one still allows, and how to write application code that survives the errors PostgreSQL uses to protect you. For the general theory and a locking overview, see Isolation and Locking in SQL; here we focus on how PostgreSQL behaves.

What runs live here. Isolation needs two simultaneous sessions, and the in-browser SQLite engine has just one. The two-session scripts are therefore shown as read-only timelines. The live examples simulate snapshots and a retry loop with plain SQL and Python, so you can watch the logic, not the server.

Three levels in PostgreSQL

Read Committed The default. A new snapshot for every statement.
Repeatable Read One snapshot for the whole transaction.
Serializable Behaves as if transactions ran one after another.

Read Uncommitted exists as a name, but PostgreSQL runs it as Read Committed.

1 / 2

Choosing a level

You choose the level when a transaction begins, or as a default:

SQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- or, as the first statement of an already-started transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- change the default for a role or database:
ALTER ROLE reporting SET default_transaction_isolation = 'repeatable read';

The level can only be set before the first query of the transaction runs.

The anomalies

An anomaly is a result that could not happen if the transactions had run one at a time. The classic ones:

Anomaly What goes wrong
Dirty read You see data another transaction has not committed.
Non-repeatable read You read a row twice and get different values, because someone committed in between.
Phantom read You run the same query twice and the set of matching rows changes.
Lost update Two read-modify-write cycles overwrite each other.
Write skew Two transactions read overlapping data, then each writes something different, and together they break a rule neither broke alone.

What each PostgreSQL level allows:

Level Dirty read Non-repeatable read Phantom read Write skew and other serialization anomalies
Read Committed No Possible Possible Possible
Repeatable Read No No No (PostgreSQL's snapshot prevents them) Possible
Serializable No No No No

PostgreSQL never allows dirty reads at any level. Its Repeatable Read is also stricter than the SQL standard requires (the standard allows phantoms there). PostgreSQL's Repeatable Read is what the literature calls snapshot isolation.

Read Committed (the default)

Each statement sees the data committed before that statement started. Two statements in one transaction can therefore see different data.

TEXT
Session A                                  Session B
---------                                  ---------
BEGIN;
SELECT price FROM items WHERE id = 1;      -- 10
                                           BEGIN;
                                           UPDATE items SET price = 12 WHERE id = 1;
                                           COMMIT;
SELECT price FROM items WHERE id = 1;      -- 12   (non-repeatable read)
COMMIT;

For most OLTP code this is fine, but it matters when your code reads, thinks, and writes. Consider a read-modify-write cycle in application code:

TEXT
Session A: SELECT stock FROM items WHERE id = 1;     -- 5
Session B: SELECT stock FROM items WHERE id = 1;     -- 5
Session A: UPDATE items SET stock = 4 WHERE id = 1;  -- (5 - 1, computed in app)
Session B: UPDATE items SET stock = 4 WHERE id = 1;  -- also 4: one sale is lost

Two sales happened but the stock fell by one. This is the lost update. The fix at this level is to do the arithmetic in the database, where the second UPDATE waits for the first to commit and then recomputes against the committed row:

SQL
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;

When an UPDATE, DELETE or SELECT ... FOR UPDATE hits a row that a concurrent transaction has just changed, Read Committed waits for that transaction. If it commits, PostgreSQL re-reads the new version of the row, checks your WHERE clause against it again, and continues only if it still matches. That is why the statement above is safe, and why WHERE stock > 0 can never push the stock below zero.

When you do need to read first and then decide, lock the row you read: SELECT ... FOR UPDATE (see Locks, Row Locking and Deadlocks).

Here is a live simulation of the first timeline. The "snapshot" is a copy of the table, standing in for what a Repeatable Read transaction would keep:

SQL Non-repeatable read, simulated

Press Run (or Ctrl + Enter) to see the result here.

The live-data read returns 12 and the snapshot read still returns 10. That is the whole difference between the two lower levels.

Repeatable Read

A Repeatable Read transaction takes its snapshot with its first query, and all later queries use it. Re-reading a row always gives the same answer, and so does re-running a range query (no phantoms).

TEXT
Session A: BEGIN ISOLATION LEVEL REPEATABLE READ;
Session A: SELECT count(*) FROM orders WHERE status = 'open';   -- 7
Session B: INSERT INTO orders (status) VALUES ('open'); COMMIT;
Session A: SELECT count(*) FROM orders WHERE status = 'open';   -- 7 (the new row is not in A's snapshot)

This is the right level for reports and exports that run several queries and need them to agree with each other. Because the transaction stays on one snapshot, it also holds back VACUUM for its whole duration, so keep these transactions as short as you can.

The price is conflict detection. If A tries to change a row that another transaction changed and committed after A's snapshot, A cannot simply continue (it would be modifying a row it never saw). PostgreSQL stops it:

TEXT
Session A: BEGIN ISOLATION LEVEL REPEATABLE READ;
Session A: SELECT balance FROM accounts WHERE id = 1;           -- 100
Session B: UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
Session A: UPDATE accounts SET balance = balance - 10 WHERE id = 1;
ERROR:  could not serialize access due to concurrent update

The error has SQLSTATE 40001 (serialization_failure). The transaction is now aborted, and the correct reaction is to roll back and run the whole transaction again with a fresh snapshot.

Write skew and Serializable

Snapshot isolation has one famous hole, write skew. Two transactions read the same data, make a decision based on it, and update different rows. Neither update conflicts with the other, so both commit, yet the rule they were protecting is broken.

The standard example is a hospital rule: at least one doctor must stay on call. Alice and Bob are both on call, and both ask to go off call at the same moment:

TEXT
Session A (Alice)                                 Session B (Bob)
-----------------                                 ---------------
BEGIN ISOLATION LEVEL REPEATABLE READ;            BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;  -- 2 SELECT count(*) FROM doctors WHERE on_call;  -- 2
-- "two is enough, I can leave"                   -- "two is enough, I can leave"
UPDATE doctors SET on_call = false                UPDATE doctors SET on_call = false
 WHERE name = 'Alice';                             WHERE name = 'Bob';
COMMIT;                                           COMMIT;   -- both succeed. Nobody is on call.

The two updates touch different rows, so there is no write conflict. Run the two transactions one after another and the second would have seen only one doctor on call and refused. Here is the same story as a live simulation, with each session's snapshot taken before either one writes:

SQL Write skew, simulated

Press Run (or Ctrl + Enter) to see the result here.

With separate snapshots both updates fire and nobody is on call (0). Run one after another, the second check sees one doctor and the update is skipped, leaving 1. A serializable database has to behave like the second case.

Serializable does exactly that. PostgreSQL implements it as Serializable Snapshot Isolation (SSI): it works like Repeatable Read, but additionally tracks "read-write dependencies" between concurrent transactions with lightweight predicate locks (they never block anyone). If it finds a pattern of dependencies that could not have come from any serial order, it aborts one of the transactions when it tries to commit (or earlier):

TEXT
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

In the doctors example, one of the two sessions gets this error and, when retried, sees only one doctor on call and declines to leave.

Things to know about Serializable in PostgreSQL:

  • False positives happen. SSI is conservative, so it may abort a transaction even when no real anomaly exists. Expect retries.
  • Every transaction taking part must use it. Mixing Serializable with Read Committed transactions on the same data gives no guarantee.
  • Read-only transactions can use BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;. It waits until it can get a snapshot that is safe, then runs without any risk of being aborted. This is useful for long reports.
  • Index scans and sequential scans create predicate locks. Good indexes mean smaller lock footprints and fewer false conflicts.
  • It is not available on hot-standby replicas.

An alternative to Serializable is to protect the rule explicitly: lock the rows the decision depends on with SELECT ... FOR UPDATE, use a constraint that makes the rule enforceable by the database, or serialize the critical section with an advisory lock. For many rules, one of these is simpler and cheaper than a global Serializable policy.

Retrying serialization failures

Whenever you use Repeatable Read or Serializable, retrying is part of the contract. PostgreSQL reports transient conflicts with these SQLSTATE codes, and your code must handle them:

The rules for a correct retry loop:

  1. Retry the whole transaction from BEGIN, not just the failed statement.
  2. Put the transaction in a function that you can call again, with its inputs. It must not rely on values read in an earlier attempt.
  3. Do side effects (emails, HTTP calls) only after a successful commit.
  4. Cap the number of attempts, and wait a little (with jitter) between them.

The simulation below uses Python's built-in sqlite3 module in the browser. The "database error" is fake (we raise it ourselves), but the loop is the one you would write around psycopg or any other driver:

Python A retry loop for serialization failures

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first two attempts fail with the simulated 40001 error and wait 0.05 s and 0.10 s (the simulation does not really sleep). The third attempt commits and the balance is 70. The ValueError is a business error that is not retried, because running it again could not change the outcome. Only the transient failure codes are retried.

Which level should you use?

  • Read Committed for most application traffic, with atomic UPDATE ... SET x = x + 1 statements and explicit row locks for read-then-write steps.
  • Repeatable Read for reports and exports that issue several queries needing a consistent view, and for read-modify-write code you are willing to retry.
  • Serializable when correctness rules span several rows and tables and you would rather let the database enforce them than reason about every interleaving, as long as every writer retries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL's default isolation level, when does a statement take its snapshot?

    Read Committed takes a new snapshot for every statement. This is why two SELECTs in one transaction can return different data.
  2. 2. Which anomaly can still happen under Repeatable Read in PostgreSQL?

    Repeatable Read is snapshot isolation. It prevents dirty, non-repeatable and phantom reads, but two transactions can read overlapping data and write different rows, which is write skew. Serializable prevents it.
  3. 3. You get ERROR 40001 could not serialize access. What should your code do?

    The transaction is aborted. Re-run it from BEGIN so it takes a fresh snapshot and re-reads its data. Cap the attempts and add a small delay.
  4. 4. Why is UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0 safe at Read Committed?

    After waiting for the other transaction to commit, PostgreSQL re-evaluates the row's new version against the WHERE clause. The decrement happens against the current value, so no sale is lost.
  5. 5. What is true about Serializable in PostgreSQL?

    SSI tracks read-write dependencies and aborts one transaction when it cannot prove a serial order. It is conservative, so retries are a normal part of using it.

Summary

  • PostgreSQL has three real isolation levels: Read Committed (the default, new snapshot per statement), Repeatable Read (one snapshot per transaction) and Serializable (SSI).
  • Dirty reads never happen. Non-repeatable reads and phantoms need Read Committed; write skew needs Serializable to be prevented.
  • At Read Committed, do arithmetic in SQL and lock rows you read first; the re-check after a concurrent update protects your WHERE clause.
  • Repeatable Read and Serializable can abort with SQLSTATE 40001; retry the whole transaction, with a cap and a small delay.
  • Use READ ONLY DEFERRABLE for long serializable reports, and keep snapshot-holding transactions short.

Next, look at the explicit locks behind all this in Locks, Row Locking and Deadlocks.