Transaction isolation

01Transaction isolation: definition and example

Transaction isolation defines how concurrent database transactions may observe and affect one another. A transaction groups operations into one unit: committing accepts its changes, while aborting discards them. Atomicity provides that all-or-nothing grouping; isolation determines which interfering executions are allowed. Atomicity alone does not keep an earlier decision valid while someone else changes the database.

A cross-row constraint exposes the difference between atomicity and isolation. Roster R7 contains rows D1 and D2, both on_duty = true, with the invariant count(on_duty) >= 1. Transaction T1 attempts to disable D1; T2 attempts to disable D2. Each procedure reads the count and proceeds only when it exceeds one. The following hypothetical interleavings test whether the isolation mechanism preserves the constraint.

One server does not solve this problem automatically. A database on one machine still runs concurrent transactions; two browser requests can read before either has written. “We use SQL” and “we put it in a transaction” are incomplete answers until we know the isolation level, statements, constraints, and retry behavior. Begin with the invariant—the condition that every committed state must preserve—then examine whether concurrent executions can break it.

02Read anomalies: dirty, nonrepeatable, and phantom reads

A read anomaly is a named observation that a stronger isolation level rules out. The names below distinguish seeing uncommitted data, seeing a previously read row change, and seeing the set of matching rows change. These cases let us compare what concurrent transactions may observe before considering the roster’s write rule.

A dirty read observes another transaction’s uncommitted work. T2 tentatively changes D2 to off-duty; T1 reads that value; T2 then aborts. T1 has used a state that never committed. Read Committed prevents that anomaly, but does not necessarily give every statement in a transaction the same snapshot.

A predicate is the condition selecting a set, such as roster_id = R7 AND on_duty = true. Locking only the rows currently returned does not generally protect every future row matching that condition. Database engines differ in their predicate or range protection. Recognize the exact set-level race before assuming that a row lock covers it.

The familiar four SQL isolation names are minimum contracts, not identical implementations:

PostgreSQL maps Read Uncommitted to Read Committed and its Repeatable Read also prevents phantoms. That stronger snapshot guarantee still permits the write-skew schedule below. Treat a database's tested behavior and documentation as the implementation contract.

03MVCC, statement snapshots, and transaction snapshots

A snapshot determines which row versions a query or transaction can see. It provides a defined view of the data while other transactions may be changing it. Multi-version concurrency control, abbreviated MVCC, retains row versions so readers can use such a view while other transactions make progress. Versions cost storage and cleanup work; long-running readers can delay reclamation.

In PostgreSQL’s Read Committed mode, an ordinary query uses a fresh statement snapshot. Two queries in T1 can therefore observe different committed roster states. PostgreSQL Repeatable Read uses a stable transaction snapshot and also prevents the phantom-read phenomenon shown above, although the SQL standard’s minimum Repeatable Read guarantees are weaker. Always name the implementation when discussing that detail.

04Write skew versus lost updates

Write skew occurs when transactions read overlapping state but update different items, allowing their combined effects to violate a constraint. In the roster example, T1 and T2 both evaluate the same initial count of two and update different rows.

This outcome has no valid serial explanation. If T1 completed first, T2 would read only D2 as on-duty and refuse to disable it. Reversing the order gives the symmetric result. Because the writes affect different rows, detecting only same-row write conflicts is insufficient.

Compare a lost update within this same roster service. Two requests read R7’s revision as 8 and both later assign 9; one logical increment disappears. An atomic revision = revision + 1 or a conditional version check addresses that counter race. Fixing it does not automatically fix the cross-row on-duty rule.

05Serializable isolation, conditional updates, and guard locks

Serializable isolation promises that committed transactions have the same effect as some serial execution. It need not literally execute them one at a time. Implementations may block conflicts, detect them and abort work, or combine techniques. This is a transaction-order promise; strict serializability additionally respects real-time order of non-overlapping transactions.

I choose a guard row for this small, frequently reviewed roster workflow. It makes the serialization point explicit: every transaction changing R7 must acquire the lock on the same row. I would revisit that choice if the operation grows into many independent rosters or complex predicates.

Isolation levels describe allowed outcomes; concurrency-control techniques determine how the database prevents disallowed ones. An optimistic approach performs work and validates that relevant state has not changed before accepting the update. A pessimistic approach acquires protection first. Compare-and-set is one atomic conditional-update mechanism that can support such validation.

Name the concurrency technique as well as its implementation:

For example, UPDATE item SET value = :new, version = version + 1 WHERE id = :id AND version = :seen succeeds only when the affected-row count is one. A zero-row result means conflict or absence, not permission to overwrite anyway. This protects that row’s change; it does not automatically protect a rule spanning other rows.

06Serialization retries and external side effects

A transaction may abort and rerun, so do not send “you are off duty” inside it. Save a pending notification in an outbox in the same transaction as the roster change. Send it afterward with duplicate protection. If the commit reply is lost, reuse an operation ID so a retry can find the saved result.

A concrete PostgreSQL Read Committed implementation uses separate statements inside one transaction:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT roster_id FROM roster_guard
WHERE roster_id = 'R7' FOR UPDATE;
-- Require exactly one existing guard row; otherwise abort.
SELECT count(*) FROM dispatcher
WHERE roster_id = 'R7' AND on_duty;
-- If count > 1 and D1 is currently on duty in R7:
UPDATE dispatcher SET on_duty = false
WHERE roster_id = 'R7' AND dispatcher_id = 'D1' AND on_duty;
-- Check the affected-row count; record outcome and outbox intent here.
COMMIT;

The application branches on the count; the comments are required control flow, not executable enforcement. Keep the guard held until commit. A missing guard row acquires no row lock, so create it as part of roster creation and reject missing guards. Do not combine lock acquisition and the protected count into one statement whose snapshot may predate a lock wait. This protocol also requires deletes and transfers to acquire the same guard before their decisions.

07Interview explanation: invariant, mechanism, and contention

A strong interview explanation begins: “The invariant is at least one on-duty dispatcher per roster. Two concurrent leave transactions can each read two and update separate rows, so atomicity and snapshot isolation alone are insufficient. I will have each transaction lock the R7 guard row before reading the current count, and require every membership change to obey that protocol.”

Then describe operational cost. A slow transaction holding the guard blocks other R7 changes, so no user interaction or remote API call belongs inside it. Transactions acquiring several roster guards should use a consistent order to reduce deadlocks; the system must still handle deadlock aborts. Measure lock wait time, transaction duration, abort rate, and retries per completed request.

Test the two requests together: run T1 and T2 concurrently and check that exactly one doctor goes off duty. Then drop the response after commit and retry with the same operation ID; the retry should find the saved result. Check waiting and retry limits too.

Recall the key ideas

Answer from memory before opening each card. Explain why the choice works and what it costs. Revisit missed cards tomorrow.