Transactions and Isolation

The booking that exists twice

Two customers try to reserve the same room for the same hour. Each request runs this transaction:

begin
  query confirmed bookings that overlap 14:00-15:00
  if none exist, insert a confirmed booking
commit

Both transactions can begin when the interval is empty. Both queries return no rows. Both inserts can then commit. Every statement succeeded, every transaction was atomic, and the result is still wrong.

That contradiction is the reason to study isolation. A transaction is a boundary around a decision, not a protective wrapper around whatever statements happen to be inside it. It protects a business rule only when the reads that justify the decision and the writes that could violate it are in one commit boundary, and when concurrent decisions are prevented from producing an invalid result together.

For this system the rule is precise:

No two confirmed bookings for the same room may overlap in time.

The rest of the design follows from that sentence.

ACID does not supply the missing rule

The four ACID properties describe different parts of the database’s promise. Atomicity means the changes in one transaction commit together or not at all. Isolation constrains what concurrent transactions may observe and which combined outcomes may commit. Durability says what an acknowledged commit survives under the configured storage and replication guarantees.

Consistency is the easily abused term. A database can preserve rules it knows: primary and foreign keys, uniqueness, checks, exclusion constraints, and the effects of transactional statements. It cannot infer the booking rule from an application query. If that rule is neither encoded as a constraint nor protected by the concurrency mechanism, saying “the database is ACID” does not repair it.

Durability is also a concrete promise rather than a ceremonial word. A commit acknowledged after a local log flush differs from one acknowledged only after synchronous replication to another failure domain. Chapter 65 followed that write path. Here the important point is that durability preserves the outcome the transaction reached; it does not make that outcome correct.

Watch the interleaving

Put the two booking requests on a timeline:

T1: begin
T1: query overlap -> none
                         T2: begin
                         T2: query overlap -> none
T1: insert booking A
T1: commit
                         T2: insert booking B
                         T2: commit

The dangerous object is not an existing row. It is the predicate “confirmed bookings for room 7 that overlap this interval.” Locking the rows returned by the query does nothing when the query returns none.

Anomaly names are compressed descriptions of histories like this one. A dirty read observes another transaction’s uncommitted work. A non-repeatable read sees one row change between two reads. A phantom appears when repeating a predicate query produces a different set of rows. A lost update lets one writer overwrite another writer’s change. Write skew occurs when transactions read overlapping facts, update different rows, and jointly break a rule—as when two doctors each see the other on call and both sign off.

The names help only after the invariant and history are visible. “Prevent phantoms” is weaker reasoning than “two empty overlap queries must not both authorize a confirmed booking.”

A stable snapshot can preserve the wrong decision

Multi-version concurrency control, or MVCC, lets a reader see an appropriate version of a row while a writer creates a newer version. Readers therefore do not have to block writers in many common paths. The database retains old versions while a transaction may still need them and later reclaims those versions; long transactions can delay cleanup and create storage and maintenance pressure.

MVCC improves concurrency, but it does not decide which concurrent outcomes are valid. Our two booking transactions may each read a perfectly stable snapshot in which the interval is empty. Stability within each snapshot does not make the pair of commits safe.

This is why the isolation level’s name comes after the history. Broadly:

• read uncommitted may expose uncommitted state, although some engines do not implement it as a meaningfully weaker level;
• read committed usually gives each statement a view of committed data, so later statements in the same transaction may see newer commits;
• repeatable-read or snapshot-style isolation gives a stable transactional view but may still permit write skew or predicate races;
• serializable isolation requires committed transactions to have an outcome equivalent to some serial order, often by blocking a conflict or aborting a transaction that must be retried.

These are families of behavior, not portable specifications. Products assign different guarantees to the same labels. The design must verify the engine’s documented semantics and reproduce the dangerous interleaving in a test.

Make competing bookings conflict

The broken design allows both transactions to succeed because they change different rows and nothing represents the shared claim. Several mechanisms can create the missing conflict.

The strongest answer is often a database constraint. Some relational databases can express non-overlapping ranges directly. The constraint then protects every writer—web request, background job, migration, or admin tool— and turns the race into a commit conflict. If the product sells fixed slots, a unique key on (room_id, slot_start) may express the same rule more simply. Neither solution should be generalized past the schema it actually protects.

Serializable isolation is another answer. Both transactions may do their natural predicate read and insert, while the database detects that accepting both would produce no serial explanation. The application must treat a serialization failure as an expected outcome, retry the whole decision from a fresh transaction, and bound that retry under contention.

A guard row can materialize the contested logical object. Lock one row for room 7—or for room 7’s calendar partition—before querying and inserting. Bookings for that guard serialize while unrelated rooms proceed concurrently. The price is reduced concurrency for a busy room and a convention that every writer must follow.

A single command owner for each room can provide the same ordering above the database. That can be a useful architectural boundary, but it introduces queueing, ownership transfer, and recovery questions. It is not automatically simpler than a local constraint.

Choosing among these mechanisms is a choice about the shape of the invariant, contention, and operational ownership. A table lock would also prevent the race, but would serialize unrelated rooms. A row lock on existing bookings is too narrow. The protection earns its cost only when it covers exactly the facts that authorize the write.

Pessimistic and optimistic control answer different questions

A pessimistic lock claims a resource before changing it. A second transaction waits, fails quickly, or becomes part of a deadlock that the database resolves by aborting one participant. Keep locked transactions short, acquire resources in a consistent order when possible, and define bounded behavior for lock waits and deadlock retries. A deadlock is a conflict outcome to handle, not evidence that transactions cannot be used safely.

Optimistic concurrency permits the work to proceed and checks at write time that the state has not changed:

update booking_notes
set note = :note, version = version + 1
where booking_id = :id
  and version = :expected_version;

Updating zero rows means another writer won. The caller can reload, merge, or return a conflict. This works well for an infrequently contested record and for human edits whose intent should not be silently overwritten.

The version check covers only the record whose version it tests. It does not protect an empty time range or the rule across all bookings for a room. The same limitation applies to a conditional decrement: an atomic stock = stock - 1 where stock >= 1 is excellent for a one-row inventory rule, but it says nothing about a cross-row schedule. Concurrency controls are not ranked from weak to strong in the abstract; each protects a particular shape of truth.

The conflict policy is part of the transaction

After choosing the database mechanism, decide what the caller experiences. A serialization failure might be retried automatically when the transaction is cheap and has no external side effects. A stale human edit may deserve 409 Conflict with the current version. A busy room can queue briefly behind its guard, while a heavily contended flash sale may need admission control before requests reach the database.

Retries must rerun the entire decision, not only the failed final statement. The old reads were evidence from an invalid execution. Retrying forever is also unsafe: it can turn contention or database distress into an internal request storm. Put a limit on attempts, add jitter where many clients collide, and expose conflict, wait, retry, and exhaustion metrics separately from ordinary errors.

A lost response creates a second race

Suppose the booking commits, but the response is lost. The customer retries. The server cannot infer from the timeout whether the first request committed. If it simply runs the create path again, network uncertainty becomes a duplicate booking attempt.

Give the command an idempotency key and record that key with its durable result:

begin
  claim idempotency key, or load its existing result
  create the confirmed booking under the overlap protection
  store booking ID and response against the command
commit

A unique command key arbitrates concurrent duplicates. Once the first command finishes, later attempts return the stored outcome rather than repeating the mutation. The overlap constraint and the command key do different jobs: one protects the room’s schedule; the other protects the caller’s retry contract. The system still needs a policy for a duplicate that arrives while the first attempt is in progress, for keys that are reused with different request data, and for abandoned command records.

Idempotency does not mean “run it twice and hope the state looks similar.” It means repeated presentation of one command has one durable identity and an intentional observable result.

The network ends the local atomic boundary

Now the booking requires payment from an external provider. Holding the database transaction open during the network call is usually the wrong boundary. The provider is not participating in the database commit; the call can be slow; its response can be lost; and database locks remain held while the system knows least about the outcome.

Use durable local transitions instead:

transaction 1:
  create booking pending_payment with command identity

network:
  authorize payment with the same stable idempotency key

transaction 2:
  record the provider outcome if this attempt is still current
  confirm the booking or begin release
  write an outgoing event beside the state change

The first transaction creates a fact that recovery can find. When the provider enforces the key, a repeated authorization request is safe from the caller’s point of view. The second transaction advances an explicit state machine. Writing an outbox record beside that state change prevents the local database from committing a confirmation with no durable intention to notify downstream systems. The publisher may deliver the event more than once, so consumers still need deduplication or idempotent handling.

A distributed transaction protocol such as two-phase commit can coordinate one atomic outcome across participating resource managers. It is appropriate when that atomic outcome is required and all participants can accept the latency, availability, resource-holding, recovery, and operational costs. It does not make an arbitrary external HTTP API transactional.

A saga chooses a different guarantee: a sequence of local commits, durable progress, retry-safe steps, and compensating business actions when later work fails. Releasing a room or voiding a payment authorization is not rollback in the database sense. The intermediate state may have been visible, the compensation may fail and require repair, and some effects—an email already read or a parcel already shipped—cannot be undone. Calling a workflow a saga is useful only when its states, retries, compensations, and stuck-work ownership are explicit.

Eventual consistency belongs outside the invariant

After confirmation, the search index, analytics counter, recommendation model, email feed, and cache may update later. That delay can be acceptable because none of those copies is allowed to confirm a conflicting booking. The authoritative reservation path still protects the room synchronously.

Eventual consistency is a product decision when the temporary state is named: which view may lag, what a user sees meanwhile, how retries and duplicates are handled, how divergence is detected, and what repair path exists. “It will eventually converge” is not a behavior specification.

Money available to spend, authorization, legal entitlement, exclusive reservation, and irreversible commands usually require a synchronous guard at their decision boundary. Search freshness, counts, notifications, and rebuildable read models often tolerate lag. A single workflow may therefore use strong local protection for one invariant and asynchronous propagation for everything derived from it.

This is more useful than choosing “strong” or “eventual” consistency for an entire system. Different facts carry different consequences.

Prove the race, not the happy path

A normal integration test creates one booking and finds it afterward. It says nothing about isolation. A useful test opens two independent transactions, uses a barrier so both complete the overlap read before either proceeds, then allows both to attempt the insert. The assertion is about the final invariant: at most one conflicting booking commits. If serializable isolation is the mechanism, the test should also verify that the rejected transaction is retried from the beginning under the intended budget.

Run corresponding histories for the rules that matter: two decrements at stock one, two users leaving an on-call set, two creates with one command key, and a commit whose response is deliberately dropped. Use the production engine and configuration. An in-memory substitute rarely reproduces lock, snapshot, predicate, or deadlock behavior faithfully.

Production evidence completes the argument. Observe lock waits, deadlocks, serialization failures, optimistic conflicts, retry attempts, idempotency-key collisions, pending-state age, outbox lag, and reconciliation work. Rising conflict may mean the invariant is correctly defended while the ownership or admission design has stopped scaling.

The durable reasoning sequence is short: state the invariant; identify the reads that justify the write; draw the interleaving that can break it; choose a mechanism that makes those decisions conflict; and define what the caller sees after conflict, timeout, and retry. Once the source-of-truth decision is sound, the next chapter can ask how cached copies remain fast without pretending they are always current.