Transactions and Isolation

Transactions and Isolation

A transaction groups changes under a database contract. Atomicity prevents partial commit of those changes; isolation controls which concurrent histories may commit. Neither property alone makes an external payment call part of a database transaction. Start from a business invariant, identify every row that can affect it, then choose a mechanism and retry policy.

The baseline invariant

This original example has two on-call operators for one team. At least one must remain on call. Initially Ana and Ben are both on. Either may leave if the other is available. This is a predicate over several rows, so independent row updates can violate it even when neither update loses another update.

Execute the unsafe history

Read the diagram

A and B both read on-call count 2 → A turns Ana off; B turns Ben off → Distinct row writes both commit → Count becomes zero: invariant fails → Guard or serializable retry repairs it

View full sizeOpen original

Transactions and Isolation

Reset viewClose

Distinct rows avoid a write/write collision while still breaking the shared invariant.

Under PostgreSQL Repeatable Read, this example can exhibit write skew. A stable snapshot does not automatically serialize predicates across rows. Other database isolation names must be checked against their own documented implementation.

Try it with two SQL sessions

Use a disposable database with PostgreSQL semantics. These commands are an exercise; never run the reset against production data.

CREATE TABLE coverage_exercise (
  team_id integer NOT NULL,
  operator_name text NOT NULL,
  on_call boolean NOT NULL,
  PRIMARY KEY (team_id, operator_name)
);
INSERT INTO coverage_exercise VALUES
  (1, 'Ana', true), (1, 'Ben', true);

In both sessions start BEGIN ISOLATION LEVEL REPEATABLE READ; and run the following read before either session updates anything:

SELECT count(*) FROM coverage_exercise
WHERE team_id = 1 AND on_call;

Each sees 2. In A, set Ana off; in B, set Ben off; then commit both. The read was a decision in application logic; the updates below alone do not enforce the invariant.

-- Session A
UPDATE coverage_exercise SET on_call = false
WHERE team_id = 1 AND operator_name = 'Ana';
-- Session B uses operator_name = 'Ben' instead.

After committing, query the count again. Expect zero for the deliberately unsafe interleaving. Reset both rows to true before trying the repaired alternatives. The repository's model fixtures demonstrate this history without a live database; they do not claim to have executed these SQL sessions.

Repair A: serialize one team decision

Create a stable guard row per team. Every operation that can reduce coverage must follow the guard protocol, including administrative and bulk actions. With PostgreSQL Read Committed, obtain the guard first, then perform a new query after acquiring it:

CREATE TABLE coverage_guard (team_id integer PRIMARY KEY);
INSERT INTO coverage_guard VALUES (1);

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT team_id FROM coverage_guard WHERE team_id = 1 FOR UPDATE;
SELECT count(*) FROM coverage_exercise
WHERE team_id = 1 AND on_call;
-- Application: update the requested currently-on operator only if count > 1.
-- Otherwise return a business conflict; do not acknowledge successful departure.
COMMIT;

A holds the guard, reads 2 and turns Ana off. B waits, obtains the guard after A commits and performs its new count query. B sees 1 and refuses Ben's departure. Guarding only one endpoint fails if another writer bypasses it. A missing guard row must be an error or provisioned safely; locking a nonexistent row does not lock the abstract team.

Do not mechanically transplant this read-after-wait recipe to a fixed snapshot. Choose visibility and locking together. Keep transactions short and external calls outside their lock lifetime.

Repair B: serializable with whole-transaction retry

Run the read and conditional business decision inside a Serializable transaction. For the conflicting history, a transaction may fail with a serialization error. Discard the failed attempt's decisions, roll back, retry the whole transaction against a fresh view and recheck the business condition. It may now return a normal conflict because only one operator remains.

A bounded retry loop distinguishes transient concurrency failures from invalid requests. Use a retry deadline and jitter under pressure. Retrying just the failed final UPDATE preserves the stale premise and is incorrect. Do not send an irreversible notification inside an attempt that may later abort; record an outbox intent atomically if one is required.

Distinguish a last-seat update from write skew

For one stock row, the following statement checks and changes one authoritative value atomically. Require exactly one affected row; zero is a conflict, not a success.

UPDATE stock SET available = available - 1
WHERE sku = 'S7' AND available > 0;

For two SKUs in one order, use one supported transaction, acquire rows in a stable order and commit all lines together. If they live on independent shards without a shared atomic boundary, do not claim this SQL solves the cross-shard reservation. That becomes a workflow with explicit intermediate states.

For deadlocks, imagine A locks SKU 1 then wants 2 while B locks 2 then wants 1. A consistent acquisition order reduces this cycle. Still handle deadlock victims by retrying the complete safe transaction, and bound lock time and connection occupancy.

Operate the actual bottleneck

Assume a team guard is held for 5 ms per successful decision. Its ideal serialized ceiling is about 200 decisions/s before overhead. Adding 20 API replicas cannot make that one guard serve 4,000/s. Reduce work under the lock, separate unrelated teams and measure wait time, transaction duration, abort rates and oldest transaction age. More connections can turn contention into an outage.

Check your understanding

Question: Both updates touched different rows and both used transactions. Why is the zero-operator outcome still possible?

Question: B waited for a guard. Can it reuse a count fetched before the wait?

Question: A serialization failure occurs after a count of 2. What must be retried?

Transfer exercise and scoring

For Cart and Checkout, write the invariant, list all conflicting rows, choose a local atomic boundary, explain retry behavior and move payment outside that transaction. Score one point for each justified item. For Ticketmaster, change the requirement from one seat to adjacent seats and explain what the transaction must lock together.

Continue with PostgreSQL, Data Modeling and Dealing with Contention.

Primary reference and scope

PostgreSQL transaction isolation documents the database-specific isolation and retry behavior used here. The team, SQL exercise, interleaving, capacity estimate and scoring are original teaching material. A passing state-model fixture is not a live PostgreSQL integration test.