Isolation Levels: What Other Transactions Can See

Atomicity says a transaction's writes happen together or not at all. Isolation answers a different question: while a transaction is running, what do other transactions see of it, and what does it see of them? Run two sessions against one store and the answer decides whether a program can trust its own reads.

Never read uncommitted data

The first rule of every level worth using: a transaction's writes are private until it commits. In this lesson each transaction keeps its writes in a private write set (a key maps to a new value, or to "deleted"). Nothing reaches the shared committed store until COMMIT; ROLLBACK just throws the write set away, so there is nothing to undo. Reading another transaction's uncommitted writes is called a dirty read, and neither level here allows it.

A transaction does see its own writes: look in the write set first, then fall back to the shared data. A key it deleted reads as missing even if the committed store still has it.

Two levels, two answers to "which committed data?"

Read committed reads whatever is committed at the moment of each read. Two reads of the same key inside one transaction can return different values if another session commits between them (a non-repeatable read). It is cheap, and it is what many databases use by default.

Repeatable read gives the transaction a snapshot: a frozen view of the committed store taken at BEGIN. Every read of a key returns the same answer no matter what others commit afterwards. Here the snapshot is literally a copy of the committed map; real engines avoid the copy with versioned rows (a later lesson), but the visible behavior is the same.

A: SET bal 100                       committed: bal=100
R: BEGIN (repeatable read)           snapshot: bal=100
A: SET bal 150                       committed: bal=150   (A has no transaction: it commits at once)
R: GET bal  -> 100                   from the snapshot
A: GET bal  -> 150

The level is chosen with LEVEL read-committed|repeatable-read; it applies to transactions that begin afterwards, and a running transaction keeps the level it started with.

Lost updates and first-committer-wins

Snapshots create a new danger. Two transactions both read n=1, both compute a new value and write it. If the second to commit simply overwrites the first, the first update is lost, and the writer never learns that its snapshot was stale.

Repeatable read prevents this with first-committer-wins. Each committed key remembers the number of the commit that last changed it. A transaction remembers how many commits had happened when it started. At COMMIT, if any key it wrote was changed by a commit after its start, it must fail: nothing is installed and the answer is ERR could not serialize access: <key> (the smallest such key, when several conflict). The application is expected to retry the transaction from the top. Read committed has no such check: the last writer wins.

Only keys the transaction wrote are checked. A transaction that merely read a key someone later changed commits fine. (Catching that too is the job of serializable isolation, which this lesson leaves out.)

Sessions and commands

Every line starts with a session name, then a command, and the output line is prefixed with that session and a colon and space, for example A: OK. A session with no open transaction commits each statement immediately. LEVEL ... and DUMP (the committed store, sorted, or (empty)) are global and have no prefix.

Common mistakes

• Writing through to the committed store before commit. Other sessions then see dirty data and ROLLBACK has nothing to undo.
• Taking the snapshot lazily, at the first read. The view must freeze at BEGIN.
• Reading the snapshot before the transaction's own writes. A transaction must see what it just wrote.
• Comparing against the wrong number. The conflict test is "changed after I started", not "changed at all".

Your exercise

The starter parses commands. You implement begin, read_key and commit. The mistake to avoid: forgetting that a delete in the write set must hide the key from later reads.