Two buyers press “reserve” for the last seat. The application cannot treat two individually successful reads as proof that both reservations fit. It needs one unit of work whose committed outcome preserves available_seats >= 0, and it must say what a crash, competing transaction, or retry may observe.
ACID separates obligations that are often conflated
| Claim | Concrete question | What it does not promise |
|---|---|---|
| Atomicity | Can a crash expose only the debit, not the credit? | that the completed transfer was authorized |
| Consistency | Do commits preserve declared and encoded business invariants? | that an unexpressed rule is magically checked |
| Isolation | Which concurrent writes/read effects can this transaction see? | an absence of aborts or retries |
| Durability | After acknowledgement, which failures retain the commit? | survival of every regional or adversarial event |
For a reservation, a row-local invariant can often be enforced by one conditional statement:
UPDATE seat_inventory
SET available = available - :requested
WHERE event_id = :event_id
AND available >= :requested;
An affected-row count of zero means the predicate was false at the protected update boundary; it is not a successful reservation. This is stronger than SELECT available, computing in the application, then issuing an unconditional update.
Prediction — retry scope
A transaction inserts an order, reserves inventory, commits, then calls a payment provider. The network times out after the call. Should the client blindly repeat the whole sequence?
HintExpandCollapse
Separate a database rollback-safe action from an externally visible action that may already have occurred.
Worked solutionExpandCollapse
No. Retrying the database transaction before knowing the previous commit can duplicate inventory or orders; retrying the provider call can double-charge. Commit an outbox or an idempotency key with the database work, then make the provider operation retryable against that stable key. Isolation protects database histories; it does not make an external API atomic with them.
Histories can be tested with a precedence graph
Build a graph with one node per transaction. Add when an operation of precedes and conflicts with an operation of on the same item; a conflict has the same item and at least one write. The history is conflict-serializable exactly when this graph is acyclic.
Build a graph with one node per transaction. Add when an operation of precedes and conflicts with an operation of on the same item; a conflict has the same item and at least one write. The history is conflict-serializable exactly when this graph is acyclic.
Reads of the same item do not conflict. A write followed by a read, a read followed by a write, or two ordered writes does. An acyclic graph yields a topological order that preserves every conflict order. A cycle is stronger evidence than a suspicious final balance: it proves no serial ordering can retain all observed conflicts.
Worked trace — a lost update
Start with available = 20. T1 reads 20 and plans to subtract 7. T2 reads 20 and plans to subtract 7. T1 writes 13; T2 then writes 13. The intended serial result is 6, but the final result is 13: one subtraction disappeared. The writes create a cycle when each transaction’s read/write dependencies are represented, so a serializable protocol must block, abort, or otherwise reject one path.
Expose a lost update in the schedule
Advance the interleaving. Both transactions read the same version, so the second write overwrites rather than composes with the first.
- 1T₁ reads x=0
- 2T₂ reads x=0
- 3T₁ computes x+1
- 4T₂ computes x+1
- 5T₁ writes x=1
- 6T₂ writes x=1
The final value alone cannot explain the anomaly. The read-from relation and write order show why both commits are individually valid but the pair is not equivalent to serial execution.
- 1T₁ reads x=0
- 2T₂ reads x=0
- 3T₁ computes x+1
- 4T₂ computes x+1
- 5T₁ writes x=1
- 6T₂ writes x=1
A schedule is the evidence: two successful transactions can still violate the intended increment invariant.
Use the schedule explorer to commit to the observed value before revealing its explanation. It models a read-modify-write lost update; it is not evidence that every database’s named isolation levels behave identically.
Snapshot visibility has a known boundary
Counterexample. “At least one doctor remains on call” can fail even when each transaction reads a consistent snapshot. T1 reads doctors A and B and turns A off; T2 reads the same snapshot and turns B off. The writes are disjoint, both commit, and no doctor remains.
Snapshot isolation often prevents dirty reads and many read anomalies while allowing readers and writers to proceed concurrently. It does not automatically serialize predicates over several rows. A serializable level may detect a dangerous dependency and abort one transaction; another repair is to lock/update a summary row that represents the invariant, or to encode a viable declarative constraint where possible.
| Requirement | Reliable repair shape | Why a weaker shortcut fails |
|---|---|---|
| no duplicate email | unique constraint | SELECT then INSERT races |
| nonnegative stock in one row | conditional UPDATE ... WHERE stock >= n | a prior read becomes stale |
| at least one on-call doctor | serializable validation or protected summary/predicate | row-local updates are disjoint |
| price + order snapshot agree | same transaction and foreign-key/version rules | application-side sequencing can expose a prefix |
Application — pick the conflict boundary
A tenant has a monthly credit limit across many invoices. Two requests each read remaining credit and add a different invoice. Name a repair and state the price it pays.
Worked solutionExpandCollapse
Maintain a tenant-month summary row and update it conditionally in the same transaction, or use serializable isolation with a retry on serialization failure. The summary row makes the aggregate invariant a shared write boundary but can become hot; serializable validation preserves flexibility but can abort more often under contention. A normal row lock on each new invoice does not protect the aggregate predicate.
Isolation labels require an implementation contract
PostgreSQL, for example, documents Read Committed, Repeatable Read, and Serializable behavior, treats Read Uncommitted as Read Committed, and may report a serialization failure that the application must retry. That is an implementation example, not a license to generalize its exact snapshot boundary to another engine.
Transfer — design a test rather than trust the label
You are told a service uses “repeatable read.” Give the smallest experiment that reveals whether it protects the on-call invariant.
HintExpandCollapse
Run the two disjoint writes concurrently and retain outcome evidence, not just the final rows.
Worked solutionExpandCollapse
Create two on-call rows, begin two transactions at the claimed level, have both read the predicate, have each disable a different doctor, then commit. Record blocking, errors, commits, and the final predicate. If both commit with no doctor on call, the level does not protect this invariant; add a serializable/summary/predicate-based repair and rerun the same schedule.
Deep Practice
Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.
Exercise 4.1.1: Conflict-Serializability Precedence Graph Construction
Consider history Using Theorem 4.1.1
HintExpandCollapse
Identify conflicting operation pairs on the same data item and draw directed edges.
Worked solutionExpandCollapse
The conflicting pairs are:
- precedes on item inducing directed edge
- precedes on item inducing directed edge
The serialization graph contains the cycle By Theorem 4.1.1, because the graph contains a directed cycle, the history is not conflict-serializable.Theorem 4.1.1Conflict-serializability testBuild a graph with one node per transaction. Add when an operation of precedes and conflicts with an operation of on the same item; a conflict has the same item and at least one write. The history is conflict-serializable exactly when this graph is acyclic.
Exercise 4.1.2: Write Skew Anomaly under Snapshot Isolation
Explain why snapshot isolation permits write skew in the classic hospital on-call problem (where at least one doctor must remain active) while preventing dirty reads, non-repeatable reads, and lost updates.
HintExpandCollapse
Each transaction reads the shared active count but modifies disjoint doctor rows.
Worked solutionExpandCollapse
Under snapshot isolation, transactions read from a consistent snapshot taken at start and check for write conflicts only on the specific rows they update (first-committer-wins). In write skew, transaction updates doctor while updates doctor Because their write sets and are disjoint, no write-write conflict is detected, and both commit. However, their commit decisions depended on each other’s state, violating the multi-row invariant.
Exercise 4.1.3: Multi-Invoice Aggregate Credit Limit Protection
A business requires that the total of all unpaid invoices for a tenant cannot exceed credit limit Two concurrent transactions attempt to insert new invoices for tenant 12. Specify two alternative architectural mechanisms to prevent exceeding under concurrent execution.
HintExpandCollapse
Consider explicit lock serialization on a parent row versus serializable snapshot isolation.
Worked solutionExpandCollapse
- Explicit parent row lock: Each transaction begins by executing
SELECT * FROM Tenant WHERE tenant_id = 12 FOR UPDATE;. This serializes all invoice additions for tenant 12, forcing each transaction to read the committed sum of prior transactions before inserting. - Serializable isolation: Run both transactions at
SERIALIZABLEisolation. The database tracks predicate read dependencies and aborts one transaction with a serialization error (40001), requiring the application to retry.
Exercise 4.1.4: Empirical Isolation Level Validation Harness
Design an automated test harness to empirically verify whether a database’s declared REPEATABLE READ isolation level is vulnerable to write skew. Define the initial database fixture, the interleaved operations of two concurrent sessions, and the assertion that detects invariant violation.
HintExpandCollapse
Seed two doctors on call, have two concurrent threads each take one doctor off call, then assert final count.
Worked solutionExpandCollapse
- Fixture: Insert
Doctor(id, on_call)with rows(1, true)and(2, true). Invariant:COUNT(*) FILTER (WHERE on_call) >= 1. - Schedule:
- Session 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;reads active count (2). - Session 2:
BEGIN ISOLATION LEVEL REPEATABLE READ;reads active count (2). - Session 1:
UPDATE Doctor SET on_call = false WHERE id = 1; COMMIT; - Session 2:
UPDATE Doctor SET on_call = false WHERE id = 2; COMMIT;
- Session 1:
- Assertion: Query
SELECT COUNT(*) FROM Doctor WHERE on_call = true;. If count is 0, both committed and write skew occurred, proving the level is snapshot isolation rather than true serializability.