Databases III: Transactions and Isolation
ACID honestly, and the lies databases tell — dirty reads, lost updates, write skew, and why serializable isn't the default anywhere.
🎯 By the end you'll understand
What ACID really guarantees (and what it doesn't), the four standard isolation levels and the specific anomalies each permits, how locks and MVCC enforce isolation, what a deadlock looks like in practice, and how TraceLab v2's concurrent telemetry writers expose a genuine lost-update race that transactions fix. You'll reproduce each anomaly by hand against the runs table before threads arrive next lesson — so the hazard is already familiar.
KEY INSIGHTA transaction is not a unit of speed — it is a unit of correctness
Every isolation level below serializable is a documented, signed agreement to be occasionally wrong. The database is not hiding this — it's in the manual. You just didn't read it.
Your robotics fleet is running experiments. Twelve concurrent processes are writing telemetry into a Postgres table. You assumed the database would "handle it." It will — but what it handles depends entirely on one configuration choice most teams never consciously make: the isolation level. The choice is not about performance. It is about which lies your application is willing to accept.
A transaction groups multiple reads and writes into a single unit that either fully commits or fully rolls back. That's atomicity. Isolation is the promise about what one transaction is allowed to see while other transactions are in flight. The four standard levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — form a dial: turn it clockwise for more correctness, pay more in throughput and complexity. The dangerous move is not picking the wrong level on purpose — it is not knowing you've picked one at all. Postgres defaults to READ COMMITTED. MySQL's InnoDB defaults to REPEATABLE READ. Your application is already living at one of these levels, whether or not you know it.
CORE CONCEPTACID, honestly — what each letter actually buys you
ACID is one of computing's most repeated acronyms and most selectively understood properties. Let's go through each one without the textbook gloss.
Atomicity — all or nothing, not "fast"
If any statement in your transaction fails, the database rolls back every change made so far in that transaction. The canonical example: transferring money by UPDATE accounts SET balance = balance - 100 WHERE id = 1 and UPDATE accounts SET balance = balance + 100 WHERE id = 2. If the second statement crashes, atomicity guarantees the first is also undone. What atomicity does not guarantee: that the combined result is logically consistent, that no other transaction ran in between, or that either statement was fast.
Consistency — your job, not the database's
This is the one that trips people up. "Consistency" in ACID means the database enforces constraints you declare: NOT NULL, FOREIGN KEY, CHECK constraints, unique indexes. It does not mean "the data makes semantic sense in your domain." If you transfer $100 from account A to account B but the logic is wrong (you multiply instead of subtract), the database commits happily. Consistency ≠ correctness. Application-level consistency is your job.
Isolation — a promise with asterisks
This is the one this lesson is actually about. Isolation controls what a transaction is allowed to see while concurrent transactions are in flight. Perfect isolation would mean each transaction runs as if it were the only one — serializable isolation. But serializable is expensive, so databases offer weaker levels with documented holes. The anomalies below are those holes, by name.
Durability — committed means survived
Once a transaction commits, its changes survive a crash. Practically: Postgres writes to a write-ahead log (WAL) before reporting success; a power outage after the WAL write but before the data file update is survivable by replaying the log on restart. This guarantee is expensive: every commit involves at least one fsync() — a synchronous write to durable storage. Systems that skip fsync for speed (Redis in some modes, SQLite with PRAGMA synchronous = OFF) are trading durability for throughput. Know what you traded.
You'll hear "BASE" (Basically Available, Soft state, Eventually consistent) described as the NoSQL alternative to ACID. This is marketing wearing an acronym costume. ACID and BASE describe different things at different layers — many NoSQL systems offer ACID transactions at the document level (MongoDB since 4.0, DynamoDB transactions), and many SQL databases offer eventual consistency for replicated reads. The real question is always: which specific guarantees does this specific operation need?
HOW IT WORKSThe isolation dial — four levels, four different sets of permitted lies
The SQL standard defines four isolation levels. From weakest to strongest:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Write Skew | Lost Update |
|---|---|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible | possible | possible |
| READ COMMITTED (Postgres default) | prevented | possible | possible | possible | possible |
| REPEATABLE READ (MySQL InnoDB default) | prevented | prevented | possible* | possible | prevented† |
| SERIALIZABLE | prevented | prevented | prevented | prevented | prevented |
* Postgres's REPEATABLE READ also prevents phantom reads (stronger than the SQL standard requires). † Lost updates under REPEATABLE READ in Postgres: prevented by snapshot isolation in most cases, but write-skew still possible.
Each row is a signed agreement to accept specific anomalies. The question is whether your application can tolerate each one. Let's name them precisely so you can make that call.
DEFINITIONThe anomalies, named and explained
These are not hypothetical edge cases. They are documented behaviors of production databases running at default settings. Learn each one by what it looks like, not by its label.
T1 reads a row that T2 has modified but not yet committed. If T2 then rolls back, T1 has acted on data that never existed. Postgres prevents this even at READ COMMITTED — so dirty reads are mostly a curiosity for SQL Server and MySQL at their lowest level. But the mental model matters: without any isolation, every in-progress write is immediately visible.
T1 reads a row, T2 commits an update to that row, T1 reads the same row again — and gets a different value. The same query, within the same transaction, returns different results. This breaks any logic that assumes "I already checked this value." At READ COMMITTED, this is permitted and common.
T1 reads a set of rows matching a condition (e.g., WHERE experiment_id = 42), T2 inserts a new row matching that condition and commits, T1 runs the same query and now sees an extra row that wasn't there before. Like a non-repeatable read, but for the set of matching rows rather than the value of a single row.
T1 and T2 both read a row (value: 10), both compute a new value (T1: 10+1=11, T2: 10+5=15), both write back. One write overwrites the other. Final value is either 11 or 15 — never 16, which is what a correct serial execution would produce. This is the canonical TraceLab bug: two robot processes update the same experiment's run count simultaneously.
The sneaky one. T1 and T2 each read some set of rows and make a decision based on what they read. Their individual writes don't conflict — they touch different rows — but the combined result violates a constraint that both transactions checked as true before acting. Classic example: a hospital system where the constraint is "at least one doctor must be on call." T1 reads "two on call," takes doctor A off call. T2 reads "two on call," takes doctor B off call. Both commit. Zero doctors on call. Neither write was a conflict in the traditional sense — they touched different rows — but the result is logically corrupt. Write skew requires SERIALIZABLE to prevent.
The hierarchy: dirty reads → non-repeatable reads → phantoms → write skew. Each higher level prevents the previous anomaly but adds enforcement overhead.
SEE ITTransaction interleaving simulator
The best way to understand isolation anomalies is to reproduce them by hand. This simulator runs two concurrent transactions against TraceLab's runs table. Pick an anomaly scenario, pick an isolation level, then step through each operation one at a time. Watch the database state change — and watch the level either prevent or permit the anomaly.
Three things to notice as you play. The lost-update scenario at READ COMMITTED shows both transactions reading the same value and writing back different increments — only one update survives. Switching to REPEATABLE READ or SERIALIZABLE either aborts one transaction (serializable in Postgres uses SSI, Serializable Snapshot Isolation) or prevents the phantom read. Write skew only disappears at SERIALIZABLE — the individual writes look fine to a lock-based system because they touch different rows, but the combined result is wrong.
HOW IT WORKSHow isolation is actually enforced — locks and MVCC
There are two dominant implementation strategies, and most production databases use a combination.
Two-phase locking (2PL) is the classical approach. Transactions acquire locks as they touch data (growing phase) and release them all at commit or rollback (shrinking phase). Reads take shared locks; writes take exclusive locks. This prevents anomalies by ensuring conflicting transactions can't proceed simultaneously — but it blocks. If T1 holds a write lock on a row and T2 wants to read it, T2 waits. The cost of strong isolation under 2PL is throughput: more blocking, more waiting.
Multiversion concurrency control (MVCC) takes a different bet: instead of blocking readers, keep multiple versions of every row. When T2 reads a row that T1 is currently modifying, T2 sees the last committed version — the row as it was before T1 started. Reads never block writes; writes never block reads. This is how Postgres, MySQL InnoDB, Oracle, and CockroachDB achieve READ COMMITTED and REPEATABLE READ without locking readers out. The cost is storage overhead (old versions must be kept until no transaction needs them — Postgres calls this "dead tuples" and VACUUM is how they're cleaned up) and implementation complexity.
Deadlocks: when two locks wait on each other
Locks introduce a new failure mode: deadlock. T1 holds a lock on row A, wants row B. T2 holds a lock on row B, wants row A. Both wait forever — unless the database detects the cycle and kills one transaction. Postgres does this automatically; the victim transaction receives ERROR: deadlock detected and rolls back. Your application must be prepared to retry.
-- Connection 1 BEGIN; UPDATE runs SET loss = 0.31 WHERE id = 1; -- acquires lock on row 1 -- (paused here while connection 2 runs) UPDATE runs SET loss = 0.29 WHERE id = 2; -- wants lock on row 2 → blocks -- Connection 2 (concurrent) BEGIN; UPDATE runs SET loss = 0.44 WHERE id = 2; -- acquires lock on row 2 UPDATE runs SET loss = 0.40 WHERE id = 1; -- wants lock on row 1 → DEADLOCK -- ERROR: deadlock detected — one transaction is rolled back
The reliable fix for deadlocks is consistent lock ordering: always acquire locks in the same order. If every transaction that needs rows 1 and 2 always locks row 1 first, the circular wait cannot form. In practice this means ordering your UPDATE statements by primary key, or using SELECT ... FOR UPDATE ORDER BY id to sort what you lock. Deadlock detection is a safety net; consistent ordering is the seat belt.
SELECT FOR UPDATE — taking locks explicitly
MVCC gives you non-blocking reads, but sometimes you need to lock a row you're about to update. SELECT ... FOR UPDATE does this: it reads the row AND acquires an exclusive lock, so any concurrent transaction trying to write that row will block until you commit or roll back. This is the explicit "I intend to update this" signal that MVCC alone doesn't provide.
BEGIN; -- Lock the row before reading, so concurrent updaters queue up SELECT step FROM runs WHERE id = 42 FOR UPDATE; -- ... compute new value in application code ... UPDATE runs SET step = step + 1 WHERE id = 42; COMMIT;
The simpler pattern when you're just incrementing: push the computation into a single atomic SQL statement. UPDATE runs SET step = step + 1 WHERE id = 42 holds its own row lock implicitly during the update — no explicit transaction required for this one-operation case. The lost-update bug only bites when you read-compute-write across multiple round trips.
TRACELABTraceLab v2: reproducing the lost-update race
TraceLab is now a working client-server system (HTTP API + Postgres), and the CLI has been reborn as a client. With multiple robots writing telemetry concurrently, a real race condition emerges in the step counter. Let's reproduce it exactly, then fix it with the tools this lesson gave you.
The v2 schema has a runs table. The problem arises when two concurrent telemetry writers try to increment a run's step counter:
CREATE TABLE runs ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- preferred over SERIAL (see Lesson 3-4) experiment_id INTEGER REFERENCES experiments(id), started_at TIMESTAMPTZ DEFAULT NOW(), step INTEGER DEFAULT 0, -- the vulnerable counter loss NUMERIC(10,6), reward NUMERIC(10,6), status VARCHAR(20) DEFAULT 'running' );
The buggy write path that two robot processes run simultaneously:
# POST /runs/{run_id}/step — called by each robot's telemetry loop async def record_step(run_id: int, payload: StepPayload, db: AsyncSession): # Round-trip 1: read current step result = await db.execute( select(Run).where(Run.id == run_id) ) run = result.scalar_one() # compute in Python — this is the window where the race lives new_step = run.step + 1 # Round-trip 2: write new value await db.execute( update(Run) .where(Run.id == run_id) .values(step=new_step, loss=payload.loss, reward=payload.reward) ) await db.commit() return {"step": new_step}
Two robot processes call this concurrently. Both read step = 0. Both compute new_step = 1. Both write back step = 1. After two telemetry writes, step is 1 instead of 2. Run long enough and the step counter can be wildly wrong — and loss/reward values from one robot silently overwrite the other's.
Two fixes, and which one you reach for matters:
async def record_step(run_id: int, payload: StepPayload, db: AsyncSession): # Push the increment into a single SQL statement — no round-trip read result = await db.execute( update(Run) .where(Run.id == run_id) .values( step=Run.step + 1, # SQL expression, not Python value loss=payload.loss, reward=payload.reward ) .returning(Run.step) ) await db.commit() new_step = result.scalar_one() return {"step": new_step}
async def record_step(run_id: int, payload: StepPayload, db: AsyncSession): # Lock the row before reading — concurrent writers queue up result = await db.execute( select(Run) .where(Run.id == run_id) .with_for_update() # SELECT ... FOR UPDATE ) run = result.scalar_one() new_step = run.step + 1 await db.execute( update(Run).where(Run.id == run_id) .values(step=new_step, loss=payload.loss, reward=payload.reward) ) await db.commit() return {"step": new_step}
Prefer the atomic SQL expression (fix 1) whenever possible — it is faster (one round trip), simpler (no explicit lock management), and avoids any risk of forgetting to commit before the lock times out. Reserve FOR UPDATE for cases where you genuinely need to read the data to compute the update, or when the logic is too complex to express in SQL.
Transactions prevent lost updates, but what about retried requests? If a robot's HTTP call fails and it retries, you might record the same step twice. The complementary fix is idempotency: the client includes a step number in its payload, and the server uses INSERT ... ON CONFLICT DO NOTHING or a unique constraint on (run_id, step). Transactions handle concurrency; idempotency handles retries. You need both.
WEAKNESSWhere isolation bites at scale
Postgres defaults to READ COMMITTED. MySQL InnoDB defaults to REPEATABLE READ. Neither is wrong — they are tradeoffs chosen by the database vendor for broad compatibility. The problem is teams that never consciously chose. Non-repeatable reads at READ COMMITTED are expected behavior, not a bug — but they will corrupt your analytics if you're doing multi-step reads expecting consistency. Check your default with SHOW transaction_isolation; (Postgres) or SELECT @@tx_isolation; (MySQL). Set it explicitly in your connection string or per-transaction. Never inherit it passively.
MVCC keeps old row versions alive as long as any active transaction might need to see them. A transaction that runs for an hour forces Postgres to keep all rows modified in that hour's worth of table writes. This causes "table bloat" — the physical table grows because dead tuples can't be vacuumed. The pathological case is a forgotten BEGIN in a psql session (type \echo 'in transaction' to check), an idle connection holding a transaction open, or a batch job that opens one giant transaction. Monitor transaction ages with SELECT now() - xact_start FROM pg_stat_activity WHERE state = 'idle in transaction'; — if you see transactions open for hours, that's your vacuum problem's source.
SERIALIZABLE with SSI (Postgres 9.1+, CockroachDB) doesn't just slow transactions down — it can abort them with a serialization failure error. Your application must handle ERROR: could not serialize access due to concurrent update with a retry loop. Teams that set SERIALIZABLE and don't add retry logic end up with brittle code that fails under load. Serializable is not a magic correctness switch; it's a contract that requires your application to handle aborts. Measure the abort rate under your actual workload before committing to this level in production.
KEY INSIGHTHow this connects
- Databases I: Relational Thinking (3.4) — the
runstable whose rows we've been updating, the schema design choices that determine what constraints the database enforces (the C in ACID), and why primary keys matter for lock ordering. - Databases II: Queries, Indexes, and the Planner (3.5) — indexes affect locking behavior: a missing index on a WHERE clause means a sequential scan that takes shared locks on every row it visits, creating much wider contention than a targeted index scan.
- Concurrency I: Threads, Processes, and the GIL (3.7) — the next lesson. The lost-update race you reproduced here is the exact same hazard that thread-based code faces — but without the database's transaction machinery. The anomalies are identical; the tools are different (mutexes, semaphores) and the guarantees weaker. Because you've already seen interleaving anomalies here in their most forgiving environment, the thread lesson starts from a position of "you know this hazard — now see it without the safety net."
Transactions are the first time this course explicitly addresses "two things happening at once." The database wraps the hazard in a formal framework with documented behavior. Next lesson removes the wrapper.
GLOSSARYKey terms introduced
- Transaction
- A group of reads and writes that execute atomically — either all changes commit or none do. The unit of correctness in relational databases.
- Isolation level
- A configuration that controls which concurrent-transaction anomalies the database permits. The four SQL-standard levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
- Lost update
- Anomaly where two transactions both read a value, compute independently, and write back — one write overwrites the other, losing its increment. The canonical TraceLab concurrency bug.
- Write skew
- Anomaly where two transactions each read overlapping data, make a locally valid decision, and write to disjoint rows — but the combined result violates a constraint both checked. Only SERIALIZABLE prevents it.
- MVCC (Multiversion Concurrency Control)
- An isolation implementation strategy that keeps multiple row versions so readers never block writers and writers never block readers. Used by Postgres, MySQL InnoDB, Oracle, and CockroachDB.
- Deadlock
- A cycle in the lock-wait graph: T1 holds lock A and wants B; T2 holds B and wants A. Both wait forever unless the database detects the cycle and aborts one transaction.
- SELECT FOR UPDATE
- An SQL clause that reads a row and acquires an exclusive lock on it, preventing concurrent writes until the transaction commits or rolls back.
- Write-ahead log (WAL)
- Postgres's durability mechanism: changes are written to a log before being applied to data files. On crash recovery, the log is replayed, guaranteeing no committed transaction is lost.
ACTIVE RECALLCheck your understanding
Answer in your own words first, then reveal.
1Two robot processes both call UPDATE runs SET step = step + 1 WHERE id = 42 concurrently. Is there a lost-update risk? Why or why not?›
No — a single atomic SQL statement acquires its lock, reads, computes, and writes in one operation at the database level. There's no round-trip where another transaction can sneak in between the read and the write. The lost-update risk only exists when you do a read in one round-trip and a write in another, with computation in between. The rule: push read-modify-write into a single SQL expression whenever possible.
2What is write skew, and why doesn't REPEATABLE READ prevent it?›
Write skew occurs when two transactions read an overlapping set of rows, each makes a decision that is locally valid given what they read, and each writes to different rows — but the combined result violates a constraint both transactions verified. REPEATABLE READ prevents non-repeatable reads on rows you've already read, but it doesn't track the constraint that spans both transactions' reads. Lock-based isolation would need to lock every row in the constraint's scope; MVCC snapshot isolation doesn't take those locks. Only SERIALIZABLE (with predicate locking or SSI) sees the full dependency graph and can detect the conflict.
3Postgres is running with READ COMMITTED (default). You open a transaction, read row 42, do some computation, then read row 42 again. Can you get different values on the two reads?›
Yes. READ COMMITTED re-reads the latest committed version on each statement — it does not hold a snapshot for the duration of the transaction. If another transaction committed a change to row 42 between your two reads, your second read will see the new value. This is the definition of a non-repeatable read, and it is permitted — by design — at READ COMMITTED. To get consistent reads across multiple statements within a transaction, you need REPEATABLE READ or SERIALIZABLE.
4You set your application to SERIALIZABLE hoping it will "just be correct." Under load, you start seeing database errors. What's happening, and what must your application code handle?›
Postgres's SSI (Serializable Snapshot Isolation) detects serialization conflicts at commit time and aborts one of the conflicting transactions with ERROR: could not serialize access due to concurrent update. This is not a bug — it is the mechanism. SERIALIZABLE doesn't slow conflicting transactions down; it aborts them. Your application must catch this error and retry the transaction from scratch. Without retry logic, SERIALIZABLE is worse than a weaker level: it adds aborts without adding correctness guarantees the application can rely on.