What is it? #
Isolation is the "I" in ACID: how much one transaction can see of another's work while both are running.
Perfect isolation — every transaction behaving as if it were alone on the server — is the safest and the slowest. Weaker isolation allows more concurrency but permits certain anomalies. Isolation levels let you choose where on that scale you sit.
There are three anomalies to know: a dirty read (seeing uncommitted data), a non-repeatable read (the same row changing between two reads in one transaction) and a phantom read (new rows appearing in a repeated query).
PostgreSQL offers three usable levels. Read Committed is the default and is right for most work. The other two matter when a transaction reads data, makes a decision based on it, and then writes.
Think of it like this #
Several people editing one shared spreadsheet.
Read Committed — you see each cell as it is at the moment you look at it. Look twice, and the value may have changed in between, because someone saved an edit. Fine for most work, occasionally surprising.
Repeatable Read — you take a photograph of the entire sheet when you start, and work from the photograph. Everything you see stays consistent with everything else you see. Others' changes are invisible to you until you finish.
Serializable — the same photograph, plus a referee who watches for the case where two people, both working from their own photographs, make decisions that could not both be valid. The referee cancels one of them and asks them to start again.
Simple example #
Two people book the last seat on a flight at the same moment.
Both transactions check "how many seats are left?" and both see 1. Both then book. Under the default isolation level, both succeed and the flight is oversold. That specific failure is why the stricter levels exist.
Code #
---------- The three anomalies ----------
DIRTY READ
Transaction B reads data that Transaction A has written but NOT committed.
If A then rolls back, B acted on data that never existed.
PostgreSQL NEVER allows this, at any isolation level.
NON-REPEATABLE READ
B reads a row, A commits a change to it, B reads the SAME row again
and gets a different value. The row changed underneath B.
PHANTOM READ
B runs "SELECT count(*) WHERE status='pending'" and gets 5.
A inserts a new pending row and commits.
B runs the SAME query again and gets 6. New rows appeared.---------- What each level allows ----------
Level Dirty read Non-repeatable Phantom Notes
-------------------------------------------------------------------------
Read Uncommitted no* possible possible *PostgreSQL treats
Read Committed no possible possible this as Read Committed
^ DEFAULT
Repeatable Read no no no** **stricter than the
Serializable no no no SQL standard requires
PostgreSQL's Repeatable Read prevents phantoms too, because it uses a true
snapshot. The SQL standard only requires Serializable to do this.-- ---------- Setting the level ----------
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... statements ...
COMMIT;
-- or, after BEGIN:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check the current default:
SHOW default_transaction_isolation; -- "read committed"-- ---------- READ COMMITTED (default): each statement sees fresh data ----------
-- Session A -- Session B
BEGIN;
SELECT balance FROM accounts
WHERE id = 1; -- 1000
BEGIN;
UPDATE accounts SET balance = 500
WHERE id = 1;
COMMIT;
SELECT balance FROM accounts
WHERE id = 1; -- 500 <-- CHANGED within the same transaction
COMMIT;
-- Each STATEMENT gets its own snapshot of committed data.
-- That is a non-repeatable read. Usually harmless; sometimes not.-- ---------- REPEATABLE READ: one snapshot for the whole transaction ----------
-- Session A -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts
WHERE id = 1; -- 1000
BEGIN;
UPDATE accounts SET balance = 500
WHERE id = 1;
COMMIT;
SELECT balance FROM accounts
WHERE id = 1; -- 1000 <-- UNCHANGED. Same snapshot.
COMMIT;
-- Consistent view. But if A tries to UPDATE that row itself:
-- ERROR: could not serialize access due to concurrent update
-- A must ROLLBACK and RETRY. Your application must handle that.-- ---------- The oversold flight, and three ways to fix it ----------
-- THE BUG (under Read Committed, both sessions succeed):
BEGIN;
SELECT seats_left FROM flights WHERE id = 7; -- both see 1
-- application decides: "there is a seat, book it"
UPDATE flights SET seats_left = seats_left - 1 WHERE id = 7;
COMMIT;
-- Result: seats_left = -1. The flight is oversold.-- FIX 1 — Let the database do the arithmetic, and add a constraint.
-- Simplest and usually best.
ALTER TABLE flights ADD CONSTRAINT seats_non_negative CHECK (seats_left >= 0);
UPDATE flights SET seats_left = seats_left - 1
WHERE id = 7 AND seats_left > 0;
-- Check the affected row count: 0 means there was no seat.
-- No read-then-decide gap exists, because the check is inside the UPDATE.-- FIX 2 — Lock the row while you decide (see the locks lesson).
BEGIN;
SELECT seats_left FROM flights WHERE id = 7 FOR UPDATE; -- others now WAIT here
-- decide safely: nobody else can modify this row until we commit
UPDATE flights SET seats_left = seats_left - 1 WHERE id = 7;
COMMIT;-- FIX 3 — SERIALIZABLE: let PostgreSQL detect the conflict.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT seats_left FROM flights WHERE id = 7;
UPDATE flights SET seats_left = seats_left - 1 WHERE id = 7;
COMMIT;
-- One of the two transactions fails at COMMIT with:
-- ERROR: could not serialize access due to read/write dependencies
-- among transactions
-- HINT: The transaction might succeed if retried.
-- YOUR APPLICATION MUST CATCH THIS AND RETRY.# ---------- Retry logic: required for Repeatable Read and Serializable ----------
import psycopg
from psycopg import errors
def book_seat(conn, flight_id, attempts=3):
for attempt in range(attempts):
try:
with conn.transaction():
conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
seats = conn.execute(
"SELECT seats_left FROM flights WHERE id = %s", (flight_id,)
).fetchone()[0]
if seats < 1:
return False
conn.execute(
"UPDATE flights SET seats_left = seats_left - 1 WHERE id = %s",
(flight_id,),
)
return True
except errors.SerializationFailure:
if attempt == attempts - 1:
raise # give up after the last attempt
continue # otherwise retry the whole transaction
# The transaction must be safe to run twice — that is what retryable means.How it works #
PostgreSQL implements isolation with MVCC — multiversion concurrency control. An update does not overwrite a row; it writes a new version and marks the old one as superseded. Each transaction then reads the versions visible in its snapshot.
The isolation level determines when that snapshot is taken, and that single fact explains all the behaviour.
Under Read Committed, a new snapshot is taken at the start of each statement. So every statement sees everything committed up to the moment it began — including work committed by other transactions since your previous statement. That is exactly why the same SELECT can return different values twice in one transaction.
Under Repeatable Read, the snapshot is taken once at the start of the transaction and used for everything within it. Your view is internally consistent from beginning to end. PostgreSQL's implementation is stricter than the SQL standard requires: because it is a genuine snapshot, phantom reads are prevented too, which the standard only demands of Serializable.
The trade-off arrives when a Repeatable Read transaction tries to modify a row that another transaction changed after your snapshot was taken. PostgreSQL cannot let you write based on a stale view, so it raises a serialization failure and you must retry.
Serializable adds conflict detection on top of the snapshot. PostgreSQL tracks the dependencies between concurrent transactions and, at commit time, checks whether the observed outcome could have been produced by running them one after another in some order. If not, one is aborted. Notably, this catches problems involving rows a transaction only read — which is precisely the oversold-flight case, where both transactions read the seat count and then acted on it.
The crucial consequence: Repeatable Read and Serializable require retry logic in your application. A serialization failure is not a bug, it is the mechanism working correctly, and the transaction is expected to be attempted again. A transaction that cannot safely be run twice is not suitable for these levels.
"Read Uncommitted" exists in the SQL standard and would permit dirty reads. PostgreSQL accepts the syntax but treats it as Read Committed — it never allows reading uncommitted data at any level.
Real-world use #
Read Committed is the default and is correct for the large majority of application work. Single-statement updates, inserts and ordinary reads are all fine under it.
The pattern that genuinely needs more care is read, decide, write: check a balance then debit it, check stock then reserve it, check for an existing row then insert. That gap between reading and writing is where two concurrent transactions can both make a decision that was only valid for one of them.
For most of those cases, the best fix is not a stricter isolation level at all — it is removing the gap. Letting the database do the arithmetic (SET seats_left = seats_left - 1 WHERE seats_left > 0) with a CHECK constraint behind it is simpler, faster and impossible to get wrong through a race. Prefer this whenever the logic can be expressed in a single statement.
When the decision is too complex for one statement, SELECT ... FOR UPDATE locks the specific rows while you decide. That is the locks lesson, and it is the common approach in application code.
Serializable is the right tool when correctness across multiple rows and tables genuinely matters — financial ledgers, inventory with complex rules, anything where an incorrect outcome is more costly than a retry. It has real throughput cost under contention and demands retry logic, so it is a deliberate choice rather than a default.
Whichever stricter level you use, test the retry path. Serialization failures appear under concurrency, which typically means they first appear in production, in traffic, at the worst moment. Generate concurrent load in a test environment and confirm your retries actually work before relying on them.
Common mistakes #
- Using Repeatable Read or Serializable without implementing retry logic for serialization failures.
- Reaching for a stricter isolation level when a single UPDATE with a CHECK constraint would remove the race entirely.
- Assuming Read Committed prevents the read-then-write race — it does not.
- Writing retryable transactions that are not safe to run twice.
- Never testing the retry path under real concurrency, so it first fails in production.
Practice #
Open two psql sessions side by side. In session A, BEGIN under Read Committed and select a row; in session B, update and commit that row; then re-select in A and watch the value change. Repeat the whole exercise with REPEATABLE READ and confirm the value does not change. Finally, reproduce the oversold-flight bug with two concurrent sessions, then fix it three ways — a single UPDATE with a guard condition, SELECT FOR UPDATE, and SERIALIZABLE — and observe what each one does differently.