Transactions & ACID — Isolation Levels, MVCC

Runtime contract: The executable lab uses SQLite transactions in memory and an explicit snapshot/write-set teaching model. PostgreSQL 16 two-session schedules are documentation-reviewed, not run against a server. SQLite atomic rollback is real local execution; the write-skew model is not an implementation or test of PostgreSQL SSI. Nothing here measures crash durability or production throughput.

Why this session exists

Two chefs both read flour=500, each adds 100 mentally, and each writes 600. The final value loses one increment. A database transaction can group work, but grouping alone does not specify how concurrent decisions interact. Correctness starts with an invariant and a mechanism that protects it.

You will distinguish ACID's four properties; dirty/nonrepeatable/phantom reads, lost updates and write skew; PostgreSQL's actual isolation levels; MVCC snapshots; pessimistic, optimistic and serializable approaches; WAL and durability boundaries; and whole-transaction retries with external-effect idempotency.

Prerequisites and historical context

Read joins and indexes. The original prerequisite label calling S040 “Joins & Set Operations” was incorrect; S040 is CTEs.

The SQL isolation names predate today's engine implementations. PostgreSQL 9.1 (2011) introduced its Serializable Snapshot Isolation implementation; the original 2008 timeline was wrong. The label is not a probabilistic promise: successful serializable transactions obey the documented serial-equivalence contract. Modern distributed systems can also provide transactions; WAL and consensus solve different pieces, not successive names for the same mechanism.

ACID: four questions, four failure examples

• Atomicity: did all transactional changes happen or none? A failed transfer must not retain a debit without its credit. Database atomicity does not automatically include an HTTP payment or an email, and sequence allocation can have nontransactional exceptions.
• Consistency: do valid transactions preserve application invariants? Constraints enforce declared rules, but the engine does not invent “at least one doctor on call” or correct currency conversion. This C is not the same as distributed read-consistency terminology.
• Isolation: which observations/interleavings are allowed? Serializable promises an outcome equivalent to some serial ordering; weaker isolation does not promise “some lesser kind of serial order” for every history.
• Durability: which acknowledged changes survive which failures? Persisted logs/data, configuration, reliable storage, replication and tested backups determine the failure model. One server's commit does not guarantee survival if all of its storage is destroyed.

Atomicity also does not resolve a lost COMMIT response. The transaction may have committed while the client timed out. Retry with a stable operation identity and inspect/reconcile its recorded outcome, rather than assuming failure means rollback.

Name the anomaly before choosing the defence

Write skew does not require both transactions to update the same row. Locking only each doctor's own row is therefore insufficient. Likewise, a multirow invariant is not automatically protected just because each individual update has a CHECK constraint.

SQL levels versus PostgreSQL 16

The standard's increasingly restrictive levels are a starting map, not a universal implementation table. PostgreSQL behaves as follows:

The standard's minimum prohibitions differ from that PostgreSQL table: Read Uncommitted can permit dirty, nonrepeatable and phantom reads; Read Committed prohibits dirty reads; Repeatable Read additionally prohibits nonrepeatable reads but can permit phantoms; Serializable also excludes phantoms and serialization anomalies. Stronger engine guarantees are allowed, so PostgreSQL Repeatable Read preventing phantoms does not turn it into Serializable. Lost updates and write skew require their own operation/invariant analysis rather than being squeezed into a three-read-anomaly checklist.

Choose isolation for the whole business transaction, before its first data statement, not independently for each SELECT inside it. Two separately serializable transactions do not make an application workflow between them atomic. In the doctors schedule, both participants must use the stated protocol; labelling only one writer serializable is not a general defence against other writers that ignore the invariant.

MySQL InnoDB's default Repeatable Read and its consistent versus locking reads differ from PostgreSQL. Do not infer that every default database permits phantoms, that MySQL needs gap locks for every snapshot read, or that an isolation name alone describes all behaviour. Read the exact engine/version contract.

MVCC snapshots, locks and vacuum

MVCC retains versions so an ordinary reader can observe a version appropriate to its snapshot while another transaction updates the row. In PostgreSQL Read Committed the next SELECT may see a newer committed version. In Repeatable Read the same transaction retains its established snapshot:

The snapshot is not necessarily fixed at the textual BEGIN instant. Ordinary MVCC SELECTs avoid row-lock conflicts with ordinary updates, but “readers never block writers” is too broad: SELECT FOR UPDATE/SHARE, table locks and DDL can conflict. Writers can block writers; deadlocks can happen. Long-lived snapshots can prevent removal of old versions and increase vacuum/storage pressure, even without blocking an ordinary update immediately.

The serializable mechanism detects unsafe dependency patterns and can fail during a statement or at commit, not only at COMMIT. It is not a promise to fix an application invariant that the transaction itself fails to check. Serial execution of two individually incorrect transactions is still incorrect.

Match the mechanism to the invariant

Atomic relative update or conditional debit

For a like counter, UPDATE counters SET likes=likes+1 WHERE id=:id lets the engine serialize conflicting row updates; read-in-app then write an absolute value is a different, vulnerable operation.

For a positive debit amount in PostgreSQL 16:

UPDATE accounts
SET balance=balance-:amt
WHERE id=:id AND :amt>0 AND balance>=:amt
RETURNING balance;

The :name tokens describe application binding, not literal psql syntax. A driver must bind them using its own supported placeholder style. At Read Committed, a conflicting update waits and reevaluates the WHERE condition on the updated version. No returned row means no eligible account, which can be missing identity, invalid amount or insufficient funds—not always insufficient funds alone. Validate currency, bounds and amount precision as well.

A transfer also needs a credit, both account existence checks, and an atomic transaction. If the credit target is missing, roll back the debit; the local lab tests that negative case. For concurrent transfers, lock accounts in a consistent ID order if using explicit locks and retry recognised deadlock/serialization failures as appropriate.

Pessimistic, optimistic or serializable?

For on-call doctors at Read Committed, locking a shared schedule/guard row before counting serialises the invariant decision. At Repeatable Read a transaction may already hold a stale snapshot after waiting; simply adding a lock does not magically refresh it. Serializable with retries is often clearer for a wider transactional invariant. It is not the only possible defence, and row-level locking can protect a set when the shared guard protocol is correctly designed.

For a multi-statement analytics report, Repeatable Read can provide a consistent snapshot, at the cost of retaining old versions. For a simple relative increment, a single atomic update can suffice at Read Committed. Money movement does not universally require one isolation setting; the entire invariant and external-effect boundary determines the design.

WAL, atomic recovery and durability scope

PostgreSQL WAL requires log records describing changes to be durable before their corresponding dirty data pages are written. A commit can then wait for the necessary WAL flush rather than synchronously flushing every scattered data/index page. Recovery uses checkpoint state plus log records and transaction visibility/status. This is a mechanism for atomic recovery and durability, not proof that every ACID property comes from the log alone.

The log is not an infinite complete application event history; old WAL can be recycled once no longer needed under retention/recovery policies. Data pages can contain versions from transactions that never committed; rollback need not physically undo every byte before readers can proceed. Storage can suffer torn pages, so do not assume hardware grants page-level atomicity in every environment. Flush/group-commit behaviour, locks, CPU and replication waits affect commit latency; it is not universally bounded by exactly one fsync.

fsync=off and synchronous_commit=off are not equivalent PostgreSQL settings. Disabling fsync risks database corruption after an OS/hardware crash. Asynchronous commit can lose recent acknowledged transactions after a crash without the same inconsistency implication documented for fsync=off. Neither setting is changed here. A local durable commit also does not prove a failover target has replayed it; replication acknowledgments have their own configured stages and failure domains.

Deterministic PostgreSQL two-session schedules

These are reference steps for an owner-created disposable PostgreSQL 16 database, with no credentials, destructive cleanup or timing sleeps. They were not executed in this review. Create fresh batch2_accounts(id PRIMARY KEY,balance) with (1,1000),(2,1000) and batch2_on_call(doctor PRIMARY KEY,is_on_shift boolean NOT NULL) with Alice and Bob TRUE. Use two interactive sessions and advance only after the previous listed step completes.

Nonrepeatable read and phantom

1. A: BEGIN ISOLATION LEVEL READ COMMITTED; then SELECT balance for account 1 → 1000, and count accounts with balance at least 1000 → 2.
2. B: in a transaction update account 1 to 1500 and insert account 3 with balance 5000, then COMMIT.
3. A: repeat both reads → 1500 and 3, then COMMIT.
4. Reset the disposable fixture intentionally and repeat with A at REPEATABLE READ, establishing its first snapshot before B changes anything. A's repeated reads remain 1000 and 2.

These are documented expected results for the stated ordering. A sleep-based script does not prove that ordering, and silently ignoring background-process errors does not prove an anomaly.

Write skew and serializable retry

1. Start both A and B at REPEATABLE READ; both count TRUE doctors before either update and observe 2.
2. A updates Alice to FALSE; B updates Bob to FALSE. Their write rows are different.
3. Both COMMIT; expected result is zero on call, violating the application invariant.
4. Reset both to TRUE. Repeat the same interleaving with both transactions SERIALIZABLE. PostgreSQL cannot commit both outcomes under the documented contract; a serialization failure requires rollback and restart with fresh reads.
5. Retry the failed business transaction fully. It now sees only one doctor on call and must decline to switch that doctor off. Blindly retrying the old UPDATE would not preserve the rule.

For a dirty-read check, leave B's update uncommitted, then read from A even at requested READ UNCOMMITTED: PostgreSQL still does not expose B's uncommitted version. No dirty-read production claim is inferred from the SQLite lab.

Hands-on: real local transactions and a bounded anomaly model

Save as acid_lab.py. It asserts transfer sum preservation, rollback after an injected mid-transfer failure, invalid amount and missing-credit-target rejection, request-ID deduplication, and a stale compare-and-set rejection. The model explicitly permits disjoint snapshot writes and compares their joint outcome with both serial orders; it is not called SQLite or PostgreSQL snapshot isolation.

"""Local atomic transactions plus an explicitly separate snapshot model."""
import sqlite3
from itertools import permutations
 
conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys=ON')
conn.executescript('''
CREATE TABLE accounts(id INTEGER PRIMARY KEY,balance INTEGER NOT NULL CHECK(balance>=0));
CREATE TABLE requests(request_id TEXT PRIMARY KEY NOT NULL,
 src INTEGER NOT NULL,dst INTEGER NOT NULL,amount INTEGER NOT NULL CHECK(amount>0));
INSERT INTO accounts VALUES(1,1000),(2,1000);
CREATE TABLE versions(id INTEGER PRIMARY KEY,value INTEGER NOT NULL,version INTEGER NOT NULL);
INSERT INTO versions VALUES(1,500,0);
''')
 
def balances():
    return conn.execute('SELECT id,balance FROM accounts ORDER BY id').fetchall()
 
def transfer(request_id,src,dst,amount,fail_after_debit=False):
    if type(amount) is not int or amount<=0 or src==dst:
        raise ValueError('positive integer amount and distinct accounts required')
    # Python connection context commits on success, rolls back on exception.
    with conn:
        old = conn.execute('SELECT src,dst,amount FROM requests WHERE request_id=?',
                           (request_id,)).fetchone()
        if old is not None:
            if old != (src,dst,amount):
                raise ValueError('request ID reused with different payload')
            return 'duplicate'
        conn.execute('INSERT INTO requests VALUES(?,?,?,?)',(request_id,src,dst,amount))
        changed = conn.execute('UPDATE accounts SET balance=balance-? '
                               'WHERE id=? AND balance>=?',(amount,src,amount)).rowcount
        if changed!=1:
            raise ValueError('no eligible source account')
        if fail_after_debit:
            raise RuntimeError('injected failure after debit')
        changed = conn.execute('UPDATE accounts SET balance=balance+? WHERE id=?',
                               (amount,dst)).rowcount
        if changed!=1:
            raise ValueError('missing credit account')
    return 'committed'
 
assert transfer('t1',1,2,250)=='committed'
assert balances()==[(1,750),(2,1250)]
assert transfer('t1',1,2,250)=='duplicate'
assert balances()==[(1,750),(2,1250)]
for args,kwargs,error in [
    (('t2',1,2,100),{'fail_after_debit':True},RuntimeError),
    (('t3',1,2,900),{},ValueError),
    (('t4',1,99,100),{},ValueError),
    (('t5',1,2,-1),{},ValueError),
    (('t1',1,2,251),{},ValueError),
]:
    try:
        transfer(*args,**kwargs)
    except error:
        pass
    else:
        raise AssertionError('expected failure')
    assert balances()==[(1,750),(2,1250)]
assert conn.execute('SELECT request_id FROM requests').fetchall()==[('t1',)]
print('After success, duplicate and all failed transfers:',balances())
 
# Two clients read version 0; only the first compare-and-set succeeds.
with conn:
    first = conn.execute('UPDATE versions SET value=?,version=version+1 '
                         'WHERE id=1 AND version=?',(600,0)).rowcount
    stale = conn.execute('UPDATE versions SET value=?,version=version+1 '
                         'WHERE id=1 AND version=?',(600,0)).rowcount
assert (first,stale)==(1,0)
print('CAS rowcounts:',first,stale)
conn.close()
 
# MODEL ONLY: both decisions read the same snapshot, with disjoint writes.
# This is not a SQLite concurrent transaction or a PostgreSQL SSI emulator.
snapshot = {'alice':True,'bob':True}
def decision(view,doctor):
    return {doctor:False} if sum(view.values())>1 else {}
writes = [decision(snapshot.copy(),d) for d in ('alice','bob')]
assert set(writes[0]).isdisjoint(writes[1])
merged = snapshot.copy()
for change in writes:
    merged.update(change)
assert sum(merged.values())==0
serial = []
for order in permutations(snapshot):
    state = snapshot.copy()
    for doctor in order:
        state.update(decision(state.copy(),doctor))
    assert sum(state.values())==1
    serial.append((order,state))
assert all(merged!=state for _,state in serial)
print('Disjoint snapshot-write model:',merged)
print('Both serial-order oracles:',serial)
print('Local rollback and bounded-model checks passed; no crash/SSI benchmark')

Worked results and retry interpretation

A successful transfer of 250 changes (1000,1000) to (750,1250) and preserves 2000. Repeating the same request ID has no second effect. An exception after the debit, an insufficient debit and a missing credit account all preserve the last committed balances. Reusing the same request ID with a different payload is rejected, not silently accepted as a duplicate.

The snapshot model lets Alice and Bob both observe two TRUE values, then merges their disjoint writes to FALSE,FALSE. Its two serial-order oracles each leave exactly one TRUE because the second decision sees only one on call. This proves a counterexample in the bounded model, not a database implementation's behaviour under every interleaving.

SQLite's separate-connection isolation normally serialises writes; WAL mode offers snapshot isolation for readers with one writer, while rollback-journal mode has different blocking. Its special shared-cache/read_uncommitted configuration has separate rules. An in-memory single-connection rollback demo cannot establish either production concurrency throughput or power-loss durability.

Measure isolation costs correctly, without claiming a benchmark

The original pgbench --define="isolation_level=serializable" merely defines a script variable. Unless a script consumes it to set transaction isolation, it does not change the test's level. A valid experiment uses two custom transaction scripts with explicit BEGIN ISOLATION LEVEL READ COMMITTED versus ... SERIALIZABLE, checks SHOW transaction_isolation in the same context, resets the same data and measures successful business operations, failures, retries and latency under the same load.

PostgreSQL 16 pgbench documents -f for custom scripts and --max-tries for retrying serialization/deadlock failures. The invariant/check/update workload must actually exercise the conflict of interest; a standard unrelated workload cannot establish your doctor's or payment invariant. Initialization can create/drop benchmark tables, so it belongs only in a dedicated approved test database. No such test ran here, and no 10–25% overhead or “3× cheaper default” number is asserted. Serializable can be the appropriate production choice without a benchmark slogan against it.

Production boundaries: retries, failover and external effects

On SQLSTATE 40001, abort/rollback the failed transaction and retry the whole transaction including reads and business decisions, with bounded attempts/backoff and a stable business identity. Deadlock SQLSTATE 40P01 may also merit a whole-transaction retry. Connection loss around commit is a different uncertainty: inspect the operation record rather than assuming a known abort. Constraint violations require classification, not indiscriminate retries.

Do not send a payment/email inside a retriable transaction and assume rollback reverses it. Record a durable outbox intent with the business effect, then deliver idempotently using a supported provider key and reconcile ambiguous outcomes. The database and provider are separate atomicity boundaries. A saga is a workflow of compensating actions, not literal global rollback.

Distributed durability/isolation promises must be read with version, API, read/write concern and failure topology. Jepsen reports can supply useful concrete histories; the original broad claims that nearly every named product lied and the particular historical bank/incident narratives were not supported here. Retain the operational lesson: test the configured contract and read the exact postmortem rather than generalising from “ACID” or “multi-primary.” Replication/failover safety does not follow from a local WAL alone, and EXPLAIN is not a complete lock-trace tool.

Retry the decision, not only the write

Suppose both doctors read two on call and prepare different row updates. If Alice commits and Bob is aborted, a fresh retry reads one on call and declines Bob's update. Reissuing Bob's previously prepared UPDATE ... FALSE skips the predicate that justified the operation: even serial execution of that stale decision removes the last doctor. SQLSTATE 40001 tells the application to restart its reads and decision logic, not to treat an old write set as a valid new transaction. PostgreSQL 16's serialization-failure guidance explicitly includes the logic choosing SQL and values. Limit retries and report exhaustion; an ambiguous lost commit response follows the operation-identity reconciliation path instead.

Spanner and MongoDB stretch answers

Spanner's TrueTime exposes clock uncertainty and is used with its transaction/replication protocol to provide external consistency: serializable transactions ordered consistently with real-time completion. It is not merely a universal seven-millisecond wait or GPS devices in every rack; commit wait relates to the actual uncertainty bound and protocol. The official explanation and the primary OSDI 2012 Spanner paper, sections 3 and 4.1.2 support this scoped contract. This does not extend serializable external-consistency guarantees to an explicitly weaker isolation request.

MongoDB w:'majority' is a write-acknowledgment/durability concern, not a request to make an arbitrary sequence of multi-document writes atomic. MongoDB supports multi-document transactions on replica sets and sharded clusters; the old “only within a shard by default” claim was wrong. Transaction commit, write concern, read concern and causal-session guarantees must be considered separately. A majority read alone is not a universal latest-read guarantee. The documented transaction API and write concern define the relevant scope; this review did not run MongoDB or claim a particular session/failover trace.

Recall and explain-aloud answers

• I in ACID? The selected isolation contract; full serial equivalence specifically belongs to Serializable.
• Why RC default? It offers a practical documented statement-snapshot contract, but suitability depends on the workload. No universal correctness percentage or throughput multiplier exists.
• RR gap in PostgreSQL? Write skew can survive stable snapshots; a correctly ordered shared-guard protocol or Serializable plus retries can address the invariant.
• MVCC in one sentence? Visibility rules select row versions for a statement/transaction snapshot; ordinary readers avoid ordinary row-lock conflicts, not every lock.
• 40001? Roll back and re-read/recompute from the transaction start, not just reissue COMMIT or a stale update.
• Conditional debit? Put positive-amount and sufficient-balance checks in the UPDATE and inspect its result; a full transfer still needs atomic credit and existence checks.
• Durability config? fsync off risks corruption; asynchronous commit risks loss of recent acknowledgments under crash. Neither is a free performance knob.
• Write skew example? Two doctors, shared predicate, disjoint writes, zero remaining; each serial execution with the check would keep one.
• Like counter, report, transfer? Relative atomic update; consistent multi-statement snapshot; invariant-aware transactional debit/credit and idempotency respectively.

Sources and next steps

Continue to query planning, then replication/consistency and distributed workflows. Exactly-once processing, consensus and isolation are related responsibilities, not synonyms.

Primary sources inspected 7 October 2026: PostgreSQL 16 isolation, locking, WAL, WAL settings, pgbench, PostgreSQL 9.1 release, SQLite isolation, and the Spanner/MongoDB sources above. Runtime evidence explicitly separates local SQL, bounded-model oracle and unexecuted PostgreSQL/vendor behaviour.

Explain-out-loud answer guide

1. ACID in 30 seconds: atomic debit/credit avoids a partial transfer; declared constraints plus correct business checks preserve valid balances; a specified isolation protocol prevents the targeted concurrent anomaly; durable storage and recovery preserve acknowledged commits within their failure contract.
2. Write skew: Alice and Bob each observe two on call and update distinct rows. Snapshot write merging leaves zero; either checked serial order leaves one. A shared guard protocol or Serializable with full retries can preserve the set invariant.
3. Is Read Committed the right default? It is PostgreSQL's default, not a universal recommendation. Statement snapshots avoid the extra dependency-tracking/retry work of SSI, but throughput must be measured. An atomic conditional debit can be correct at RC while a multirow decision needs stronger coordination.

Optional archived resources

These links retain the original pre-read route; historical titles are labels, not verified endorsements. Video attribution, duration, playback and external reachability were not revalidated. Use the version-qualified primary references above for correctness; mutable /current or /latest pages may differ.

• ACID Properties Explained with Examples — archived video/discovery link.
• Transactions: myths, surprises and opportunities — archived video/discovery link.
• MVCC in Postgres — how it actually works — archived video/discovery link.
• PostgreSQL Docs — Transaction Isolation — archived reading link.
• Jepsen — Consistency Models — archived reading link.
• Hermitage — testing the ‘I’ in ACID — archived reading link.

Series route and study format

Hub: The 6-Month Learning Plan. Previous: S041; next: S043. These are navigation pointers, not certification of every linked body or permission to publish it.

The original 130-session series suggests a 90-minute study cycle: intuition → visual explanation → hands-on practice → production failure analysis → recall. Treat that as a pacing suggestion, not a tested completion time. Work the examples and explain the failure cases before moving on; reference-only service exercises need separate execution authority.