SQL / TRANSACTIONS AND CONCURRENCY
Isolation levels and the anomalies they allow
Name the anomaly each isolation level still allows, set the level correctly per transaction, and tell which read-then-write patterns need SERIALIZABLE.
What you will learn
- Map dirty reads, non-repeatable reads and phantoms to the levels that allow them
- Set the level right after BEGIN, before the first query takes a snapshot
- Check the default with SHOW transaction_isolation or SELECT @@transaction_isolation
- Retry a transaction that fails with SQLSTATE 40001 instead of reporting an error
Understanding Isolation levels and the anomalies they allow
The four levels in the SQL standard are defined not by how an engine works internally but by which wrong answers it is still allowed to give you. A dirty read is seeing a row another transaction wrote but has not committed; a non-repeatable read is reading the same row twice in one transaction and getting different values because someone committed in between; a phantom read is running the same range query twice and getting a different set of rows. READ UNCOMMITTED permits all three, READ COMMITTED forbids dirty reads only, REPEATABLE READ also forbids non-repeatable reads, and SERIALIZABLE forbids all three. That table is a floor, not a description: an engine may prevent more than the level requires, which is why PostgreSQL's REPEATABLE READ has no phantoms at all.
What makes the levels predictable is asking when your transaction's view of the data is allowed to move. Under READ COMMITTED, PostgreSQL takes a fresh snapshot at the start of every statement, so two identical SELECTs in one transaction can legitimately disagree; the world moved between them. Under REPEATABLE READ and SERIALIZABLE a single snapshot is taken at the first query and reused to the end, which is exactly why the level cannot be changed once a query has run: the snapshot is already frozen. Lock-based engines reach the same guarantees by holding read locks until commit instead, which is why the same level name means 'you wait' on SQL Server and 'you get an error' on PostgreSQL.
The standard's three anomalies are not the whole list. Under snapshot isolation two transactions can each read the same rows, each confirm a condition that is still true in its own snapshot, and each write a different row: two doctors both going off call because each saw the other still on call. Nothing conflicts at the row level, so REPEATABLE READ commits both and the invariant is gone; this is write skew, and only SERIALIZABLE catches it, by tracking read sets (PostgreSQL SSI) or by locking the range that was read (two-phase locking engines). The price is that SERIALIZABLE can abort a transaction that did nothing individually wrong with SQLSTATE 40001, so the level is only usable if the caller replays the whole transaction.
-- PostgreSQL, run in psql
CREATE TABLE seat (id int PRIMARY KEY, taken boolean NOT NULL);
INSERT INTO seat VALUES (1, false), (2, false);
SHOW transaction_isolation; -- level of this single-statement transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
SELECT count(*) AS free_seats FROM seat WHERE NOT taken; -- first query: snapshot fixed here
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- too late for this transaction
COMMIT;
SHOW transaction_isolation; -- new transaction, back to the session defaultAn isolation level is a contract about which concurrency anomalies the engine may still let you see, so picking one is deciding which wrong answers your transaction can tolerate or must prevent by other means.
Worked examples
Session default versus one-transaction override
Shows the difference between the level new transactions inherit and the level requested for a single transaction.
-- PostgreSQL
SET default_transaction_isolation = 'repeatable read';
BEGIN;
SHOW transaction_isolation;
COMMIT;
BEGIN ISOLATION LEVEL READ COMMITTED;
SHOW transaction_isolation;
COMMIT;
SHOW default_transaction_isolation;Example explained
Line 1SET default_transaction_isolation changes the level that every later transaction in this connection starts with; it does not change a transaction already running.
Line 2BEGIN ISOLATION LEVEL READ COMMITTED requests a level for that one transaction, and it is the safest form because the request cannot arrive after the first query.
Line 3The final SHOW proves the override was local: the session default is untouched, so the next BEGIN is repeatable read again.
Line 4ALTER ROLE app SET default_transaction_isolation = 'repeatable read' pins this per login role, so you do not depend on every client remembering to ask.
The read that a lower level lets go stale
A check-then-act block that is correct alone and wrong when a second session runs it at the same moment.
-- PostgreSQL
CREATE TABLE inventory (sku text PRIMARY KEY, qty int NOT NULL CHECK (qty >= 0));
INSERT INTO inventory VALUES ('mug', 1);
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT qty FROM inventory WHERE sku = 'mug'; -- decision: one left, sell it
UPDATE inventory SET qty = qty - 1 WHERE sku = 'mug';
COMMIT;
SELECT * FROM inventory;Example explained
Line 1Run alone the block is correct, and that is the trap: nothing in this output warns you that the SELECT result is only true for as long as nobody else commits.
Line 2With two sessions at READ COMMITTED both SELECTs return 1, the second UPDATE waits for the row lock, then re-evaluates qty = qty - 1 against the newly committed version and computes -1, so CHECK (qty >= 0) rejects it.
Line 3At REPEATABLE READ the second UPDATE fails earlier and differently, with 'could not serialize access due to concurrent update', because it tries to write a row that changed after its snapshot was taken.
Line 4Adding FOR UPDATE to the first SELECT fixes the same race at any level by making the second session wait before it decides, instead of after.
MySQL starts one level higher than PostgreSQL
Reads the session and global isolation levels on MySQL, where the shipped default is REPEATABLE READ.
-- MySQL 8.0 client
SELECT @@transaction_isolation AS session_level,
@@global.transaction_isolation AS global_level;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation AS session_level;Example explained
Line 1The same application code sees fewer non-repeatable reads on MySQL than on PostgreSQL, Oracle or SQL Server, purely because InnoDB's default is REPEATABLE READ and theirs is READ COMMITTED.
Line 2@@transaction_isolation is this connection's level while @@global.transaction_isolation is what new connections inherit, so changing the session value leaves other clients as they were.
Line 3SET SESSION TRANSACTION ISOLATION LEVEL applies from the next transaction onward; the bare SET TRANSACTION form applies to only the next transaction, and inside an open transaction it fails with 'Transaction characteristics can't be changed while a transaction is in progress'.
Line 4InnoDB's REPEATABLE READ blocks phantoms for locking reads with gap locks rather than by snapshot alone, another reminder that the level name does not tell you the mechanism.
Important notes
PostgreSQL accepts READ UNCOMMITTED but runs it as READ COMMITTED, so a dirty read is impossible there; SQL Server's READ UNCOMMITTED really does return uncommitted rows, and a scan under it can also return a row twice or skip one if pages split while it runs.
A higher level does not mean more locks for you in an MVCC engine: your reads still do not block writers. PostgreSQL enforces REPEATABLE READ and SERIALIZABLE by aborting the offending transaction, so handling 40001 is part of using those levels, not a sign of a bug.
Common mistakes
Reaching for REPEATABLE READ to make a check-then-act safe: both transactions pass the check in their own snapshot, each writes a different row, no row-level conflict is ever detected, and the invariant is silently broken.
Sending SET TRANSACTION ISOLATION LEVEL after the first SELECT: PostgreSQL raises 'must be called before any query' and leaves the whole transaction aborted, while MySQL rejects the bare form inside a transaction and, outside one, applies it to only the next transaction rather than all of them.
Switching to SERIALIZABLE without a retry path: the first serialization failure reaches the user as a failed request, even though the correct response is to run the transaction again.
Try it yourself
Change, predict, then run
In a PostgreSQL editor, create a two-row table, then run BEGIN; SELECT count(*) FROM t; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; and read the error. Move the SET to the line directly after BEGIN, rerun, and confirm SHOW transaction_isolation now reports repeatable read.
Open the SQL workspaceCheck your understanding
Two sessions run the same block at PostgreSQL's REPEATABLE READ: each counts the doctors with on_call = true (both see 2), each then sets a different doctor's on_call to false, and both commit. What is the result?
- The second COMMIT fails with a serialization error, because REPEATABLE READ re-checks the rows each transaction read.
- The second session blocks until the first commits, then re-reads the count as 1 and skips its update.
- Both commit and no doctor is left on call: snapshot isolation keeps each transaction's reads consistent but does not detect write skew.
- Both commit, but each transaction's count is silently refreshed to 1 before its UPDATE runs.
Show answer
The two UPDATEs touch different rows, so there is no row-level conflict to detect, and each transaction is perfectly consistent with its own snapshot even though no serial order of the two produces this outcome. The first option is tempting because REPEATABLE READ does raise 'could not serialize access due to concurrent update', but only when two transactions write the same row; noticing a conflict between what one transaction read and what another wrote requires SERIALIZABLE.