Transactions group database changes into one atomic outcome, but isolation determines what concurrent transactions may observe. This anomaly lab runs two sessions against inventory and account tables, identifies dirty reads, nonrepeatable reads, phantoms, lost updates, write skew, and deadlocks, then chooses controls from the invariant rather than from an isolation-level name.
The core idea
A transaction makes its own changes atomic; isolation governs interaction with concurrent transactions. Correctness comes from identifying the invariant and the schedule that can violate it, then selecting constraints, locks, isolation, or retries whose actual engine behavior closes that schedule.
atomicity versus isolation
Commit makes one transaction durable as a unit; isolation constrains overlap with others. This distinction matters because a production contract is defined by observable behavior, not by the framework or storage engine used today. write each invariant separately from transaction mechanics Write the guarantee down in terms a caller and an operator can independently verify.
BEGIN;
UPDATE accounts SET balance=balance-100 WHERE id=1;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT;Atomicity prevents half a transfer; a balance invariant still depends on concurrent access rules.
The example is useful because it exposes identity, state, and the boundary of responsibility. It should be reviewed alongside crash and concurrency schedules around every statement. If the application reads, decides, and writes without protecting the decision, the design has hidden an important precondition. Track commits, rollbacks, invariant failures, and retryable SQL states so violations appear as evidence rather than customer anecdotes.
Comparison: isolation by anomaly
Standard names are useful, but engines implement snapshots and locks differently. A comparison is only useful when the criteria match the workload, so choose from forbidden outcomes and verify vendor semantics
| Level | Typical remaining concern |
|---|---|
| Read committed | Nonrepeatable reads, phantoms |
| Repeatable read | Engine-specific phantom/write behavior |
| Snapshot | Write skew may remain |
| Serializable | Abort/retry under conflict |
Treat each row as a hypothesis, not a universal verdict. Weight correctness guarantees before convenience when a mistake is expensive. Prototype dirty read, lost update, phantom, and write-skew schedules. Beware the conclusion when a label is assumed to guarantee behavior not documented by the engine; benchmark data without representative distribution and contention is especially misleading. Revisit the choice using serialization aborts and anomaly-test outcomes.
Failure reconstruction: two doctors create write skew
Imagine the alert has fired. Each transaction sees the other doctor on call and turns itself off; together they violate minimum coverage. The first response should be to capture both snapshots and writes, then enforce the cross-row invariant, preserving evidence before retries or manual repair change the state.
T1 reads B=on T2 reads A=on
T1 sets A=off T2 sets B=off
both commit -> nobody on callSerializable execution, an explicit lock on a shared coverage row, or a redesigned constraint can prevent this.
Walk the timeline twice: once from the caller's view and once from the durable system of record. The two stories often diverge at a timeout, commit, queue acknowledgement, or cache boundary. Recreate barriers that force both reads before either write. The likely trap is that tests use sequential requests and never create the dangerous overlap. Confirm recovery by watching serialization failures and invariant checks, not merely by seeing one successful request.
Debugging checkpoint: deadlock and retry
Opposite lock order can create a cycle; the database aborts one participant. Resist changing configuration immediately. First inspect the deadlock graph, normalize lock order, shorten work, and retry the whole transaction with jitter, then form a hypothesis that one additional observation can disprove.
T1 locks account 1 -> waits for 2
T2 locks account 2 -> waits for 1
database aborts one transactionRetry begins from BEGIN, never from the failed statement inside an aborted transaction.
Keep timestamps in one time base and preserve request, transaction, or query identifiers. Reproduce with opposite-order transfers under load. A false lead appears when external side effects occur before commit or retries are unbounded. The decisive signals are deadlocks, lock wait p95, abort rate, attempts, and transaction age; inspect their distributions and dimensions rather than a single average.
Operating reality: short boundaries and safe side effects
User think-time and network calls inside transactions retain locks and old snapshots. The mechanism is not production-ready until the team can validate outside where safe, transact only durable decisions, and publish with an outbox
flowchart LR
R[Request]-->V[Validate]
V-->T[Short DB transaction]
T-->D[(Domain + outbox)]
D-->P[Async publisher]Consumers deduplicate events because publication can repeat.
Alerts should indicate customer impact and a plausible action; lower-severity diagnostics can remain on dashboards. Exercise slow dependencies, process death after commit, and duplicate publication during a game day. Operations will stall if a transaction waits for a payment provider or user response. Measure transaction duration, oldest transaction, outbox age, and consumer duplicates with rates, percentiles, age, and saturation as appropriate, and link every alert to an owned recovery procedure.
Two-session concurrency laboratory
1. Rehearse atomicity versus isolation
Before sign-off, restate this article-specific claim in one sentence: Commit makes one transaction durable as a unit; isolation constrains overlap with others. Build a controlled exercise that includes crash and concurrency schedules around every statement. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where the application reads, decides, and writes without protecting the decision. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve commits, rollbacks, invariant failures, and retryable SQL states with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
2. Rehearse isolation by anomaly
Before sign-off, restate this article-specific claim in one sentence: Standard names are useful, but engines implement snapshots and locks differently. Build a controlled exercise that includes dirty read, lost update, phantom, and write-skew schedules. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a label is assumed to guarantee behavior not documented by the engine. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve serialization aborts and anomaly-test outcomes with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
3. Rehearse two doctors create write skew
Before sign-off, restate this article-specific claim in one sentence: Each transaction sees the other doctor on call and turns itself off; together they violate minimum coverage. Build a controlled exercise that includes barriers that force both reads before either write. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where tests use sequential requests and never create the dangerous overlap. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve serialization failures and invariant checks with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
4. Rehearse deadlock and retry
Before sign-off, restate this article-specific claim in one sentence: Opposite lock order can create a cycle; the database aborts one participant. Build a controlled exercise that includes opposite-order transfers under load. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where external side effects occur before commit or retries are unbounded. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve deadlocks, lock wait p95, abort rate, attempts, and transaction age with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
5. Rehearse short boundaries and safe side effects
Before sign-off, restate this article-specific claim in one sentence: User think-time and network calls inside transactions retain locks and old snapshots. Build a controlled exercise that includes slow dependencies, process death after commit, and duplicate publication. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a transaction waits for a payment provider or user response. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve transaction duration, oldest transaction, outbox age, and consumer duplicates with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
Conclusion: protect invariants explicitly
Atomicity is not serial execution. Isolation names have engine-specific behavior, and even strong snapshots may require retries or explicit locking. State the invariant, construct the dangerous schedule, inspect the engine documentation, and test the actual configuration. Keep transactions short, classify retryable failures, and observe lock waits and aborts before contention becomes an outage. The durable lesson is to state guarantees in language clients and operators can verify, then build the smallest mechanism that satisfies them under retries, concurrency, deployment, and partial failure. Revisit the decision when traffic, risk, or ownership changes.
Continue with Safe Database Schema Migrations With Expand and Contract to connect this topic to another production boundary. The Prime Axiom resource library collects practical implementation material.
Authoritative references
Isolation labels are not perfectly portable. Re-run the anomaly schedules on the configured engine after upgrades or topology changes.
