Full isolation is expensive, so SQL defines four levels that trade correctness for concurrency. Each level forbids a different set of anomalies.

The anomalies

Anomaly	What happens	Example
Dirty read	T2 reads data T1 has written but not committed	T1 debits 200, T2 reads 800, T1 rolls back — T2 used a value that never existed
Non-repeatable read	T1 reads a row twice and gets different values because T2 committed an update in between	Balance 1000, then 800 in the same report
Phantom read	T1 re-runs a range query and sees new/removed rows committed by T2	`COUNT(*) WHERE dept_id = 1` is 4, then 5
Lost update	T1 and T2 both read 1000, both write back their own result — one update disappears	Two withdrawals of 100 leave 900 instead of 800
Write skew	Two transactions each check a condition, then write different rows, together breaking the rule	Two on-call doctors each go off duty because “the other is on call”

The four standard levels

Level	Dirty read	Non-repeatable read	Phantom	Notes
`READ UNCOMMITTED`	Possible	Possible	Possible	Rarely used; PostgreSQL treats it as Read Committed
`READ COMMITTED`	No	Possible	Possible	Default in PostgreSQL, SQL Server, Oracle
`REPEATABLE READ`	No	No	Possible (standard) — prevented by snapshots in PostgreSQL/MySQL	Default in MySQL InnoDB
`SERIALIZABLE`	No	No	No	As if transactions ran one at a time; may abort with serialization errors — retry

PostgreSQL / MySQL / SQL Server

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- SQL Server / MySQL (next transaction)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- PostgreSQL

Seeing it: two sessions on one SQLite file

SQLite in WAL mode gives each reading transaction a snapshot (like Repeatable Read/Serializable) and allows one writer at a time. Two connections play two users:

Python + SQLite — isolation

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "bank.db")
with sqlite3.connect(path) as db:
    db.execute("PRAGMA journal_mode = WAL")
    db.executescript("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER);"
                     "INSERT INTO accounts VALUES (1, 1000);")

a = sqlite3.connect(path, isolation_level=None, timeout=0)   # session A
b = sqlite3.connect(path, isolation_level=None, timeout=0)   # session B
read = lambda s: s.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]

b.execute("BEGIN")                  # B starts a read transaction…
print("B first read:", read(b))     # B first read: 1000

a.execute("BEGIN IMMEDIATE")        # A takes the write lock
a.execute("UPDATE accounts SET balance = balance - 200 WHERE id = 1")
print("A sees its change:", read(a))       # A sees its change: 800
print("B, A uncommitted:", read(b))        # B, A uncommitted: 1000   ← no dirty read
a.execute("COMMIT")

print("B, A committed:", read(b))          # B, A committed: 1000     ← same snapshot: repeatable read
b.execute("COMMIT")
print("B, new transaction:", read(b))      # B, new transaction: 800

Preventing lost updates

What isolation level would you use?

What is a phantom read?