Skip to main content

Module 4: Transactions & Consistency: Worked Examples

Example 1: Expose Write Skew​

Problem. Two doctors are on call. Each transaction reads that both are on call, then independently sets its own row to off call. Under snapshot isolation both can commit, leaving nobody on call.

Wrong first attempt. Say “each row update is atomic.” The invariant spans two rows, and the transactions write different rows, so write-write conflict detection does not protect it.

Correct reasoning. Make the predicate conflict visible: use serializable isolation with retry, lock the relevant set explicitly where supported, or redesign so the invariant is represented by a single contended record. Demonstrate the anomaly with two sessions and record the observed outcome.

Transfer question. Why would SELECT ... FOR UPDATE on only each doctor’s own row still fail?

Example 2: WAL Ordering and Recovery​

A transaction changes page P and emits log record L with LSN 420.

Required ordering. Before dirty page P may reach durable storage, the log through LSN 420 must be durable. Before reporting commit success, the commit record must be durable. After a crash, recovery redoes committed effects not reflected in pages and handles incomplete work according to the engine’s recovery design.

Wrong first attempt. Flush the data page first and “write the log later.” A crash can leave an unlogged change with no reliable recovery history.

Transfer question. Explain how checkpoints reduce recovery work without making the WAL unnecessary.

Example 3: Replace Distributed 2PC With a Saga—Carefully​

Problem. Order placement reserves inventory, charges payment, and schedules shipment across three services.

Wrong first attempt. Call three APIs and issue reverse calls on failure. A timeout does not reveal whether a remote action happened, and compensation can itself fail.

Correct reasoning. Persist each transition, use idempotency keys, publish through a transactional outbox, define compensations with business semantics, and make retries observable. A refund is not time travel: it is a new durable action.

Transfer question. Which step is irreversible, and how should that change ordering?

Completion Standard​

  • Reproduce write skew and show a verified remedy.
  • Draw the WAL-before-data and commit-durability ordering.
  • Specify saga states, idempotency behavior, compensation failures, and operator visibility.