Unit 4.6 · about 4 h
262 / 324Transactions and isolation anomalies
Assume Programming's transactions-isolation unit and its REPEATABLE READ update-conflict retry. Here disjoint writes violate a shared invariant, and the engine must track read/write dependencies rather than merely conflicting row updates.
- explain
You can now
- Separate atomicity, application invariants, isolation and stable-storage durability.
- Classify dirty reads, nonrepeatable reads, phantoms and write skew by transaction histories.
- Explain why PostgreSQL
SERIALIZABLEprevents a write-skew commit history and why aborted work must not escape.
Covers
- Transactions, atomicity, consistency, isolation and durability
- Dirty reads, nonrepeatable reads, phantoms and write skew
Study, in this order
2 steps
- DDIA, second edition: ACID and write skewDesigning Data-Intensive Applications, 2nd edition - storage and transactions
Chapter 8, The Meaning of ACID, printed pp. 279-283; Write Skew and Phantoms, pp. 303-308
- PostgreSQL 18: Transaction IsolationPostgreSQL - EXPLAIN and transaction isolation lab
Table 13.1 and sections 13.2.1-13.2.3, particularly the Serializable example, read/write dependencies and
SQLSTATE 40001Depth · Use the level definitions and SSI mechanism. Do not repeat Programming's concurrent-update
REPEATABLE READlab.
Focus on
- Consistency in ACID means preserving specified invariants. It is not the same term as replica consistency, and the engine cannot invent the application's business rule.
- Write skew needs no common written row: decisions can depend on rows another transaction changes. A stable snapshot alone does not rule it out.
- PostgreSQL maps
READ UNCOMMITTEDtoREAD COMMITTEDand prevents phantoms atREPEATABLE READ, but onlySERIALIZABLErules out serialization anomalies. - SSI tracks read/write dependencies using nonblocking predicate locks. It may abort at a statement or commit; do not publish effects before a successful commit.
Reproduce closed-book
Use two interactive psql sessions in the section PostgreSQL container, adding
-v VERBOSITY=verbose to the psql command. Create duty(id int PRIMARY KEY,on_call boolean NOT NULL); insert (1,true),(2,true). The rule is at least one doctor remains on call; an application may leave only after reading a count greater than 1. In A and B: BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM duty WHERE on_call; both must read 2 before either writes. A executes UPDATE duty SET on_call=false WHERE id=1; B executes the same for id=2. Commit A, then B. In the tested PostgreSQL 18.6 schedule, A committed and B's COMMIT reported 40001, leaving count 1. Record the actual failing operation; do not assume the same victim for a different schedule. Restart the failed transaction from BEGIN, re-read the count and decline the leave request at 1. On paper, allowing both original writes to commit gives count 0; neither serial order could permit both requests.Traps
- Protecting each doctor row separately and assuming the cross-row invariant is protected.
- Calling a
40001an engine failure. Aborting an unsafe history is the promised isolation behavior. - Retrying only COMMIT or sending an external success message before commit. Neither preserves the transaction's decision boundary.
Practice
The section mini-project covers this: a barrier-driven SERIALIZABLE write-skew test, full-transaction restart with invariant recheck, and evidence that aborted effects do not escape.
Self-test
-
Why is this not a lost update?
The transactions update different rows. Neither overwrites the other's update; their decisions conflict through the shared count. -
What would the second request see in either serial order?
One doctor remains, so it must refuse to leave. This is why two successful leave requests cannot be serialized. -
Would a row
CHECKconstraint enforce at least one doctor on call?No. That rule spans rows; ordinary PostgreSQLCHECKconstraints are not a cross-row concurrency protocol. -
Does SSI predicate locking block the other reader or writer?
Its SIRead locks track dependencies rather than blocking. Ordinary write locks can still block independently.
Completion criterion
Stop when
Your recorded schedule has only one successful leave, includes the 40001 and failing operation, and explains why a fresh recheck rejects the other request.
Leads to
- 4.7 Two-phase locking, timestamp ordering and MVCC Explain the locks, snapshots and dependency graph underneath this history.
Resources for this unit
- PostgreSQL - EXPLAIN and transaction isolation lab Create one normalized schema, joins, an aggregation and a window query. Compare EXPLAIN ANALYZE before and after one justified index. In two sessions, reproduce one documented isolation anomaly and prevent it with a stated database or application invariant.
- Designing Data-Intensive Applications, 2nd edition - storage and transactions Read only the sections on data models and query languages, storage and retrieval, encoding and evolution, and transactions that answer a live design question from the CMU route. Write one comparison table covering layout, index, isolation and recovery trade-offs.
Also in this section
- CMU 15-445/645 - database and storage core Use Fall 2025 Lectures 1-9, 11-18 and 20-22 with their notes: relational model, SQL, storage, buffer pools, layouts, indexes, execution, planning, concurrency, MVCC, logging and recovery. Complete the matching SQL, Storage, Indexes and Filters, Transactions and Recovery homeworks before considering a BusTub project.
- Operating Systems: Three Easy Pieces - persistence and integrity Use Chapters 36, 39-45 to trace devices, filesystem structures, journaling, log-structured storage, flash and integrity. Apply the crash sequence to your own append-only store rather than repeating the SYS-1 file experiment.
- CMU 15-445 - one reduced BusTub project slice If a C++ database implementation is the chosen depth lane, complete Project 1 Buffer Pool Manager or Project 2 Database Index, not both during the first pass. Follow the course publication rules and keep code private. The blank-file append-only store remains the core checkpoint.
- Point-in-time joins Read Point-in-time joins and Retrieving features as of the event time. Separate event-time eligibility from actual feature availability; implement the predicates in SQL, not a Feast installation.
- CMU Lecture 19: Timestamp Ordering Concurrency Control Section 1 only, p. 1: timestamp order as a chosen serialization order. The section pool omits Lecture 19; this fills the timestamp-ordering vocabulary gap without assigning all of OCC.
- SQLite Write-Ahead Logging Sections 1 and 3 only: rollback journaling remains the default and WAL is an opt-in mode. Correct the dated SQLite statement in CMU Lecture 21; inspect the bundled engine without running concurrent WAL writers.