Isolation Levels & Their Anomalies
Advanced · 24 min read · ▶ live playground · ✦ checkpoint
Isolation levels decide what one transaction is allowed to see while another transaction is working. They do not make concurrency disappear — they choose which surprises are allowed, which are blocked, and which make you retry.
The course playground has one database session, so it cannot truly run two transactions at the same time. This lesson uses live SQL for setup and syntax, then clearly labeled two-session transcripts for the concurrent behavior that needs real PostgreSQL auditing.
The small table for the live parts
Start with a tiny cafe stock table. The live fences prove the setup, the default isolation level, and the safe single-statement stock decrement shape.
CREATE TABLE stock_items (
sku text PRIMARY KEY,
item_name text NOT NULL,
stock integer NOT NULL CHECK (stock >= 0)
);
INSERT INTO stock_items (sku, item_name, stock)
VALUES
('beans', 'Espresso beans', 5),
('cups', 'Paper cups', 12),
('kit', 'Pour-over kit', 1);
SELECT sku, item_name, stock
FROM stock_items
ORDER BY sku;CREATE TABLE
INSERT 0 3
sku | item_name | stock
-------+----------------+-------
beans | Espresso beans | 5
cups | Paper cups | 12
kit | Pour-over kit | 1
(3 rows)PostgreSQL's ordinary default is READ COMMITTED. You can set the level for the current transaction, but do it before the first real query or write in that transaction.
SHOW transaction_isolation;
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW transaction_isolation;
COMMIT; transaction_isolation
-----------------------
read committed
(1 row)
BEGIN
SET
transaction_isolation
-----------------------
read committed
(1 row)
COMMITPostgreSQL also accepts the standard name READ UNCOMMITTED, but it behaves like READ COMMITTED in PostgreSQL. That means dirty reads are still not allowed.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
COMMIT;BEGIN
SET
transaction_isolation
-----------------------
read uncommitted
(1 row)
COMMITSHOW reports the requested name, but the behavior is still PostgreSQL's Read Committed behavior: no dirty reads.
The stronger names are live syntax too:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
COMMIT;
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;
COMMIT;BEGIN
SET
transaction_isolation
-----------------------
repeatable read
(1 row)
COMMIT
BEGIN
SET
transaction_isolation
-----------------------
serializable
(1 row)
COMMITThe anomaly vocabulary
An anomaly is a concurrent result that would surprise you if you pictured transactions running one at a time.
- Dirty read: you read another transaction's uncommitted change. PostgreSQL prevents this at every isolation level it implements, even when you request
READ UNCOMMITTED. - Non-repeatable read: you read the same row twice in one transaction and see a different committed value the second time.
- Phantom read: you re-run a predicate query, such as "all rows with stock above zero," and the set of matching rows changes because another transaction committed a matching insert, update, or delete.
- Lost update: two workers read the same old value, both compute a replacement, and one replacement overwrites the other worker's business event.
- Serialization anomaly: a set of committed transactions produces a result that cannot be explained by any one-at-a-time ordering.
SERIALIZABLEprevents that by making one transaction fail instead of committing the impossible story.
READ COMMITTED: each statement gets a fresh view
READ COMMITTED is useful and common because it keeps you away from uncommitted data without freezing your whole transaction's view of the world. The trade is that two SELECTs in the same transaction can see different committed data.
The next block is not run by the course checker. It is an annotated two-session transcript for real PostgreSQL audit.
Real-Postgres audit transcript — READ COMMITTED non-repeatable read
Session A | Session B
---------------------------------------------+---------------------------------------------
BEGIN; |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET |
SELECT sku, stock |
FROM stock_items |
WHERE sku = 'beans'; |
sku | stock |
-------+------ |
beans | 5 |
(1 row) |
| BEGIN;
| SELECT sku, stock
| FROM stock_items
| WHERE sku = 'beans';
| sku | stock
| -------+------
| beans | 5
| (1 row)
| UPDATE stock_items
| SET stock = stock + 3
| WHERE sku = 'beans';
| UPDATE 1
| COMMIT;
| COMMIT
SELECT sku, stock |
FROM stock_items |
WHERE sku = 'beans'; |
sku | stock |
-------+------ |
beans | 8 |
(1 row) |
COMMIT; |
COMMIT |
Audit note: Session A did not see dirty data. It saw Session B's change only after Session B committed.That is a non-repeatable read. It is not a PostgreSQL bug. It is the contract of READ COMMITTED: each statement sees rows committed before that statement began.
The same fresh-view rule allows phantom reads. If Session A runs SELECT sku FROM stock_items WHERE stock > 0 ORDER BY sku;, Session B commits a new positive-stock item, and Session A repeats the query, the second result can include a new row. That new matching row is the phantom.
Lost update: the stale replacement bug
The dangerous lost-update shape often starts in application code, not in a fancy SQL feature. Two checkouts read stock 1, both decide the new stock should be 0, then both write the absolute replacement 0.
Real-Postgres audit transcript — lost-update shape under READ COMMITTED
Session A | Session B
---------------------------------------------+---------------------------------------------
BEGIN; | BEGIN;
SELECT sku, stock | SELECT sku, stock
FROM stock_items | FROM stock_items
WHERE sku = 'kit'; | WHERE sku = 'kit';
sku | stock | sku | stock
-----+------ | -----+------
kit | 1 | kit | 1
(1 row) | (1 row)
-- app computes replacement stock = 0 | -- app computes replacement stock = 0
UPDATE stock_items |
SET stock = 0 |
WHERE sku = 'kit'; |
UPDATE 1 |
COMMIT; |
COMMIT |
| UPDATE stock_items
| SET stock = 0
| WHERE sku = 'kit';
| UPDATE 1
| COMMIT;
| COMMIT
Audit note: both UPDATEs had a nearby SELECT, but the write used a stale absolute replacement.
The table says stock 0 after two checkouts from stock 1. One sale disappeared from the stock math.The fix is not "never use READ COMMITTED." It is to move the decision into one guarded statement so PostgreSQL applies it to the current row version.
SELECT sku, item_name, stock
FROM stock_items
WHERE sku = 'kit';
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE stock_items
SET stock = stock - 1
WHERE sku = 'kit'
AND stock > 0;
SELECT sku, item_name, stock
FROM stock_items
WHERE sku = 'kit';
COMMIT; sku | item_name | stock
-----+---------------+-------
kit | Pour-over kit | 1
(1 row)
BEGIN
SET
UPDATE 1
sku | item_name | stock
-----+---------------+-------
kit | Pour-over kit | 0
(1 row)
COMMITIn a real two-session race, a second checkout using the same guarded UPDATE would either wait and then update zero rows, or see zero rows affected right away. Your application treats that as "sold out, retry the cart or report failure." The retry is the feature: the database gives you a truthful signal instead of letting stale code invent stock.
REPEATABLE READ: stable view, stronger than the standard here
REPEATABLE READ gives one transaction a stable snapshot. The first real query or write fixes the view; later SELECTs in that transaction keep seeing that same committed world, plus the transaction's own writes.
In the SQL standard, Repeatable Read may still allow phantom reads. PostgreSQL's implementation is stronger: it prevents phantom reads at REPEATABLE READ.
Real-Postgres audit transcript — PostgreSQL Repeatable Read prevents the phantom
(continuing the audit session from above: beans is 8 after the first transcript,
and kit was sold down to 0 in the lost-update transcript — so only beans and cups
have stock above zero when Session A begins)
Session A | Session B
---------------------------------------------+---------------------------------------------
BEGIN; |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET |
SELECT sku |
FROM stock_items |
WHERE stock > 0 |
ORDER BY sku; |
sku |
------- |
beans |
cups |
(2 rows) |
| BEGIN;
| INSERT INTO stock_items (sku, item_name, stock)
| VALUES ('filters', 'Paper filters', 6);
| INSERT 0 1
| COMMIT;
| COMMIT
SELECT sku |
FROM stock_items |
WHERE stock > 0 |
ORDER BY sku; |
sku |
------- |
beans |
cups |
(2 rows) |
COMMIT; |
COMMIT |
Audit note: a READ COMMITTED transaction could see filters on the second SELECT. PostgreSQL Repeatable Read keeps Session A on its original snapshot.The price is retry handling. REPEATABLE READ can raise SQLSTATE 40001 when it cannot safely apply an update against a row changed after the transaction's snapshot. When that happens, retry the whole transaction, not just the last statement.
SERIALIZABLE: commit only if the story can be serial
SERIALIZABLE is the strictest level. PostgreSQL lets transactions run concurrently, but only commits a set of transactions if their final effect can be explained as some serial order — as if one finished before the other began.
When PostgreSQL detects a serialization anomaly, one transaction fails with SQLSTATE 40001. That is not corruption. It is the database refusing to commit a story that has no valid one-at-a-time explanation.
Real-Postgres audit transcript — SERIALIZABLE retry signal
Session A | Session B
---------------------------------------------+---------------------------------------------
BEGIN; | BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;| SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET | SET
SELECT count(*) AS low_stock_items | SELECT count(*) AS low_stock_items
FROM stock_items | FROM stock_items
WHERE stock <= 1; | WHERE stock <= 1;
low_stock_items | low_stock_items
----------------- | -----------------
1 | 1
(1 row) | (1 row)
-- each session decides to add one reserve row based on that count
INSERT INTO stock_items (sku, item_name, stock)
VALUES ('reserve-a', 'Reserve kit A', 1); |
INSERT 0 1 |
| INSERT INTO stock_items (sku, item_name, stock)
| VALUES ('reserve-b', 'Reserve kit B', 1);
| INSERT 0 1
COMMIT; |
COMMIT |
| COMMIT;
| ERROR: could not serialize access due to read/write dependencies among transactions
| [SQLSTATE 40001 serialization_failure]
Audit note: the application retries Session B from BEGIN, including the SELECT that made the decision.That retry rule is the practical takeaway for both REPEATABLE READ and SERIALIZABLE: transaction code must be able to run again from the top when PostgreSQL returns 40001. Retrying only the final COMMIT is not enough because the earlier reads made the decision.
Choosing a level
Use READ COMMITTED when each statement is already honest on its own: guarded updates, unique constraints, foreign keys, and short transactions. It is PostgreSQL's default for a reason.
Use REPEATABLE READ when one transaction needs a stable view for a multi-step decision or report, and your code can retry 40001 failures. Remember the PostgreSQL accent: it prevents phantoms even though the SQL standard would allow them at this level.
Use SERIALIZABLE when the business rule is easiest to state as "this must behave as if transactions ran one at a time." Pay for that clarity with full-transaction retry handling.
Do not use this lesson as a locking cookbook. 12.3 opens that box: row locks, deadlocks, and the MVCC idea behind why readers and writers often coexist in PostgreSQL.
Isolation levels tell you which concurrent surprises are allowed and which become retries. Next, 12.3 shows the machinery you have been politely avoiding so far: locks, deadlocks, and MVCC.
Checkpoint
Answer all three to mark this lesson complete