Isolation and anomalies
Explain how indexes change work and how transactions protect application invariants under concurrency.
THE IDEA
Isolation and anomalies
Isolation decides which interleavings of concurrent transactions are allowed. Two transactions that each read a counter (10), add one and write back can both write 11: one increment is lost, where running them one after the other gives 12. Stronger isolation (serializable, or explicit locking of the read) prevents this, sometimes at the cost of retries; weaker levels permit anomalies that the application must tolerate or guard against.
Isolation decides which concurrent histories are allowed. Putting statements in a transaction is not by itself enough: depending on the isolation level and how you write the update, a lost update can still happen. Stronger isolation can mean retries; weaker levels can leave anomalies the application must handle.
Predict first. Is the word transaction alone enough to rule out every lost-update pattern?
Chapter context
Databases execute concurrent requests against shared state. Fast lookup and correct updates are separate concerns: an index can improve a query while the application still loses updates or violates a cross-row rule.
TRY IT YOURSELF
Part 1. Do it hands-on
Run the interleavings by hand, then simulate them and add a lock.
Isolation decides which concurrent histories are allowed. Putting statements in a transaction is not by itself enough: depending on the isolation level and how you write the update, a lost update can still happen. Stronger isolation can mean retries; weaker levels can leave anomalies the application must handle.
- To do: A counter is 10. T1 and T2 each do read, +1, write, interleaved as: T1 reads, T2 reads, T1 writes, T2 writes. Final value?
- To do: Run one after the other (T1 completes, then T2)? Final value?
- To do: How many increments were lost in the interleaved run?
- To do: Is the word "transaction" alone enough to rule out every lost-update pattern? Type yes or no
Part 2. Do it in code
- run_schedule(initial, schedule): schedule is a list of (txn, step) with step "read" or "write". Each transaction reads the shared value into a private variable, and its write stores that private value + 1. Run the steps in the given order, no locking. Return the final value.
- lost_updates(initial, schedule): the number of increments that were lost: (initial + number of transactions) − final.
- serial_results(initial, n): the final value of n transactions run one after another.
- run_locked(initial, schedule): like run_schedule, but a read takes an exclusive lock that is held until that transaction's write; a read by another transaction while the lock is held WAITS (it runs right after the holder's write, in order). A transaction's steps stay in order. Return the final value.
- is_serializable_result(initial, schedule, n): True if the unlocked result equals the result of the serial order.
Stuck, or something not working? You can come back to it any time.
CHECK YOUR UNDERSTANDING
Is the word transaction alone enough to rule out every lost-update pattern?
Chapter reference: math, worked example & code
This section belongs to the whole chapter, so it is the same on every lesson in it and may cover a different lesson's topic. The code for the experiment you just ran is inside the experiment itself.
Math and notation
For an idealized search tree with effective fanout f and N distinguishable leaves, log base f of N gives a rough depth scale. Actual pages, occupancy and row-pointer costs differ. For an optimistic update, change value and version only when the stored version equals the version read by the caller.
Worked example
Two callers read count=10 at version 1 and each intends to increment. Unconditional writes of 11 lose one increment. With an atomic expected-version check, the first writes 11/version 2; the second stale attempt is rejected. After rereading, it can write 12/version 3. The model below demonstrates the condition, not database transaction implementation.
Reference code
The following example uses the Python standard library and does not call a remote service.
state={'count':10,'version':1}
def update(expected):
if state['version'] != expected: return False
state['count']+=1
state['version']+=1
return True
assert update(1)
assert not update(1)
assert update(2)
assert state == {'count':12,'version':3}
Python examples are provided for study and local execution; this page does not execute Python.
Common mistakes
A transaction's isolation level matters. A unique application-side check is race-prone without a database-enforced invariant. Index depth estimates are not actual query-latency measurements.