Module 4: Transactions & Consistency: Guided Labs

Lab 1: Isolation Anomaly Harness

Create two-session scripts for dirty read (if supported), non-repeatable read, lost update, write skew, and phantom behavior. Run them under the database’s available isolation levels. Record observed behavior rather than assuming the SQL-standard label fully describes the implementation.

Evidence: interleaving table, runnable scripts, outcomes by isolation level, and one verified remedy.

Lab 2: WAL and Crash Recovery Model

Implement a small WAL-backed state machine or simulator with LSNs, page LSNs, commit records, and checkpoints. Crash after every persistence step. Recovery must never expose an acknowledged transaction as absent and must not retain uncommitted state under your declared model.

Evidence: ordering diagram, crash matrix, and automated recovery tests.

Lab 3: Idempotent Consumer and Transactional Outbox

Persist a domain update and outbox record atomically. Relay events with deliberate duplicates and crashes before/after publish acknowledgement. Build a consumer inbox or idempotency table so repeated delivery does not repeat the business effect.

Evidence: schema, relay/consumer code, duplicate-delivery tests, and cleanup policy.

Lab 4: Saga Failure Review

Model order, payment, inventory, and shipment as a state machine. Inject timeout, duplicate, permanent rejection, and failed compensation. Define operator-visible stuck states and safe retry rules.

Exit check: the report separates atomicity, isolation, durability, delivery semantics, and business compensation.

Required outbox extension

Complete Transactional outbox and duplicate-effect contracts, prediction practice and all TX1–TX6 gates. Budget 24–36 hours for guided Lab 3’s executable core, reusing evidence. Original two-session isolation, WAL/checkpoint and saga work remains required.

Required engine-boundary clinic

Complete Process exit and overlapping transactions, including DB1–DB3 at level 2. This adds 12–18 hours, bringing the module to 102–148. Retain the original PostgreSQL, WAL, outbox and saga requirements.