Transactions, Isolation, and Concurrency Anomalies

The Bug Hides Between Two Correct Requests

Concurrency failures are frustrating because each request can look correct when inspected alone. One checkout reads one remaining seat and reserves it. Another does the same. Two admins each confirm that another approver is active before deactivating themselves. Nothing in either request handler looks reckless. The impossible state appears only when the requests are placed on the same timeline.

Transactions help only when the transaction boundary, isolation behavior, and application retry rules match the invariant being protected. “The database is ACID” is not a design review. It does not say whether the system prevents a lost update, whether a cross-row invariant can break under snapshot reads, whether a uniqueness rule is enforced by a constraint or by hopeful application code, or whether an email, payment capture, queue message, and row update share one atomic boundary.

The useful question is concrete: which concurrent actions can violate this fact, and what mechanism proves they cannot?

What a Transaction Actually Buys

A transaction is a bounded unit of work. Atomicity says the included database changes commit together or roll back together. Durability says committed changes survive the failure cases the system promises to survive. Isolation says concurrent work does not interact in some forbidden ways. Consistency, in the ACID phrase, is often the slippery word: the database preserves the rules it knows about, but the business invariants must still be represented by constraints, transaction logic, locks, conditional writes, queues, reconciliation, or other mechanisms.

A database can reject two rows with the same unique key if the unique constraint exists. It cannot infer that an account must always have at least one active billing owner unless the design expresses that rule. Nor can it make a payment gateway call part of a database transaction merely because the row update and the external request happen in the same handler.

The transaction boundary should therefore be drawn around facts that must change together. Keep it small enough to reduce lock time, version retention, deadlock risk, and retry cost. Keep it large enough that the protected invariant cannot be broken between two separate commits. When the business process is longer than one database transaction, name the temporary states instead of pretending the whole process is atomic.

Anomalies as Broken Invariants

Isolation terminology becomes useful when tied to a fact that can break. A dirty read exposes a write that may still roll back. A non-repeatable read lets one row change between two reads in the same transaction. A phantom changes the set returned by a repeated predicate. Each can invalidate a decision made from the earlier view.

Other anomalies are easier to miss because the reads can each look internally plausible. Read skew combines related facts from different points in time. A report might show a transfer leaving one account before it appears in the other. A lost update lets two actors compute from the same old value and overwrite one another. Counters, quotas, edits, and reservations are common casualties.

Write skew reaches across rows. Two transactions read overlapping facts, then update different rows and jointly violate a rule. No single row necessarily receives competing writes, which is why row-conflict intuition is not enough.

Lost update is the anomaly most teams can picture. Suppose a seat row says remaining = 1. Session A reads 1. Session B reads 1. A writes 0 and commits. B writes 0 and commits. Both users may believe they reserved the last seat, or one reservation may be overwritten by the other depending on the schema. The issue is not arithmetic; it is that the write did not prove the precondition was still true.

Write skew is the anomaly that exposes shallow isolation thinking. Two rows each say an approver is active. The invariant is “at least one approver must remain active.” Session A reads both rows, sees B active, and deactivates A. Session B reads the same snapshot, sees A active, and deactivates B. Each transaction updates a different row, so ordinary row-write conflicts may not fire. The invariant was cross-row, but the mechanism protected only row updates.

Isolation Level Names Are Not Portable Guarantees

The common names are vocabulary, not a substitute for product-specific verification.

Read committed commonly means each statement sees committed data, but a transaction may not see a stable view across statements. Repeatable read or snapshot-style isolation commonly means a transaction reads from a stable snapshot while concurrent writers proceed. Serializable aims to make committed transactions equivalent to some serial order. Those descriptions are intentionally cautious. Real systems differ in whether they prevent lost updates automatically, how they treat predicate reads, when they abort conflicting transactions, how they implement locks or snapshots, and what the application must retry.

Snapshot isolation is especially easy to overtrust. It can give a clean, stable view for reads and still allow write skew when two transactions update disjoint rows after reading the same predicate. Serializable behavior may close that gap, but often by aborting one transaction. An abort is a correctness feature only if the application catches it, retries safely, and avoids repeating external effects.

For production design, write the required behavior before naming the isolation level. The capacity check must not admit two reservations for one remaining seat. Concurrent deactivation must not commit if it would leave zero active approvers. The order row, inventory reservation, and outbox event must commit or roll back together. An edit based on version 12 must fail if version 13 has already committed.

Those sentences can be tested. “Use repeatable read,” “use serializable,” and “use optimistic locking” cannot be evaluated until the product, schema, statements, driver, and retry behavior give the names concrete meaning. Choose the setting and mechanism only after the behavior is explicit.

MVCC: Snapshots, Versions, and Cleanup Debt

Many databases use multi-version concurrency control. Instead of making every reader block every writer, the engine keeps versions. A transaction reads a snapshot. Writers create newer versions. Old versions stay around until the system knows no active transaction, recovery process, replica, or snapshot still needs them.

This model explains both the appeal and the operational cost. Readers can often run without blocking writers. Long analytical reads can see a consistent view. Writers can proceed while old readers finish. At the same time, old versions become cleanup debt. A long transaction can prevent garbage collection. A high-churn table can grow even when the visible row count is flat. Snapshot reads can hide concurrent changes from application logic. Serializable modes may detect dangerous structures and abort a transaction that would otherwise commit.

MVCC is not a correctness guarantee by itself. It is a way to manage versions and conflicts. The isolation level and conflict rules decide which anomalies remain possible.

Choosing the Mechanism, Not the Slogan

Most production systems use a mix of mechanisms. The right choice depends on conflict frequency, invariant shape, latency tolerance, retry cost, and the blast radius of being wrong.

Start as close to the write boundary as the rule allows. A declared constraint can enforce uniqueness, referential integrity, or another local rule. A conditional update can protect a counter, quota, inventory value, or simple state transition: UPDATE seats SET remaining = remaining - 1 WHERE id = ? AND remaining > 0 proves the precondition when the write occurs. Both approaches still need careful migration and a defined response when enforcement rejects a write.

When the rule depends on what the actor previously saw, version columns and compare-and-swap can make stale edits fail. They suit relatively rare conflicts and workflows that can retry or ask a user to merge. Under frequent conflict, pessimistic locking may be more honest: actors wait their turn. The cost moves into lock queues, deadlocks, long-tail latency, and the need to keep transactions short.

Cross-row predicates require a mechanism that protects the relationship. An explicit lock can serialize work through a small policy row representing an approver group or booking slot. A serializable transaction can protect broader read/write relationships when the database implements the needed behavior and the application safely retries aborts. A single-writer queue or partition can provide per-key ordering outside the database, at the price of hot keys, lag, replay semantics, and another operational component.

Long-running work that crosses services does not fit inside one local database transaction. A saga makes intermediate states and compensation explicit; it does not prevent temporary inconsistency. The design still needs detection, idempotency, repair ownership, and an answer for compensation that fails.

Avoid using a broad transaction as a way to postpone modeling. A transaction that wraps unrelated work increases contention without clarifying the invariant. A saga that lacks detection and repair steps is just partial failure with better naming.

Draw the Boundary Around Side Effects

Database transactions do not automatically include the world outside the database. This is where otherwise correct transaction designs leak.

A checkout can insert an order, reserve inventory, write an outbox event, call a payment provider, publish a message, update search, and send an email. Some of those effects are database facts. Some are external effects. If the database commits and the message publish fails, downstream systems miss a fact. If the message publishes and the database rolls back, downstream systems observe a fact that did not happen. If the payment call times out, the payment may have succeeded even though the local transaction does not know yet.

The usual production answer is to make the database commit the source-of-truth change and an outbox record together, then let a publisher deliver the message with retries. Consumers need idempotency because delivery can repeat. External commands need stable request identities so retry does not create a second effect. Reconciliation jobs are not a substitute for correct boundaries, but they are necessary where external systems can disagree.

A transaction boundary is credible when the design says what is inside it, what is outside it, how outside effects are made idempotent, and how disagreement is detected.

Testing the Interleaving

Concurrency claims should be executable. A small two-session test is often more valuable than a long paragraph in an architecture document.

For a lost-update test:

1. Create a row with remaining = 1.
2. Session A begins a transaction and reads the row.
3. Session B begins a transaction and reads the same row.
4. Session A attempts the reservation and commits.
5. Session B attempts the reservation and commits or fails.
6. Assert that only one reservation exists and the invariant still holds.

Then run the same test against the chosen fix: conditional update, row lock, atomic increment/decrement, unique constraint, version check, or serializable transaction. The expected result should be precise. “One transaction fails with a retryable conflict” is a valid result if the application handles that conflict. “Both transactions commit and a repair job notices later” is valid only for invariants the business allows to be temporarily broken.

For write skew, the test must read a predicate or set of rows, then write different rows from two sessions. If the invariant is “at least one active approver remains,” both sessions should read the active set before either commits. The test passes only if one deactivation fails, retries against a new view, or is compensated according to a documented policy.

Run these tests against the real database class, isolation setting, schema, driver behavior, connection pool, retry policy, and migration state that production uses. Mocks do not model locks, snapshots, deadlocks, serialization failures, or cleanup pressure. Property-based and fault-injection tests can expand coverage, but the first artifact should be a readable reproduction of the dangerous interleaving.

Invariant-to-Isolation Matrix

Use this matrix before approving transaction behavior for a critical path:

The matrix is not paperwork. It is the bridge between business correctness, database semantics, application code, and production tests.

Operational Failure Modes

The first failure mode is validation without enforcement. A handler reads a state, checks a rule, waits on application logic, then writes as if nothing changed. Unless the write proves the precondition, locks the relevant fact, or runs under isolation that rejects the dangerous interleaving, the check is advisory.

The second is a transaction that is too large. Long transactions retain old versions, hold locks, expand conflict windows, increase deadlock probability, and make deploys and maintenance harder. A transaction should protect named facts, not wrap a whole request because it feels safer.

The third is unsafe retry. Deadlocks, timeouts, and serialization failures are normal outcomes in concurrent systems. Retrying can be correct, but only if the operation is idempotent or the external side effects are delayed until after the transaction commits through an outbox or equivalent pattern.

The fourth is invisible contention. A correctness fix can become a latency incident when a hot account, tenant, slot, or counter forces many actors through one lock or serializable conflict. Monitor lock waits, deadlocks, serialization failures, retry counts, version cleanup lag, transaction age, and queue depth. Correctness mechanisms are production load-bearing components.

Review the Proof, Not the Label

A transaction design is ready for review when someone can state the forbidden final state, draw the concurrent sequence that could produce it, and point to the mechanism that interrupts that sequence. The boundary must name its rows and predicates as well as messages, external calls, and derived views that remain outside it. The proof belongs to the actual database, schema, statements, driver, and retry policy—not to an isolation-level label copied into an architecture document.

Then ask whether the proof survives operations. Serialization failures, deadlocks, duplicate requests, and timeouts must have safe outcomes. Lock waits, conflict rates, long transactions, cleanup debt, and repair queues must be visible. A mechanism that preserves the invariant by turning one hot key into an unbounded queue is correct but unfinished.

Transactions are a contract about which facts change together and what concurrent actors are allowed to make true. Once authority for those facts crosses replicas, shards, or regions, the proof acquires a network and a new failure model.

Exercise

Choose one counter, quota, booking slot, approval policy, or external-command path from a system you know. Write the dangerous interleaving as two sessions: reads, writes, waits, commits, and expected final state. Then implement or specify two fixes, such as a conditional write and a serializable transaction, and explain which one has the better operational profile for the workload.

Finally, add the invariant to an invariant-to-isolation matrix. Include the fallback repair even if the intended answer is prevention. A design that claims an impossible state can never happen should still say how operators would detect evidence that it did.