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.