Repeatable Read and snapshot isolation

Why does Repeatable Read mean three different things, and what does snapshot isolation forbid and permit?

Repeatable Read: at least three different promises

The name Repeatable Read (RR) means at least three inequivalent things:

• ANSI RR. It forbids dirty reads and non-repeatable reads and permits phantoms. This describes long read locks on each row, without range locks.
• PostgreSQL RR. It is snapshot isolation, and PostgreSQL's documentation says so.
• InnoDB (MySQL) RR. It is a hybrid. Plain (non-locking) reads see one snapshot for the whole transaction. But UPDATE, DELETE and SELECT ... FOR UPDATE read the latest committed version and take next-key locks (locks on a row plus the gap before it). So one InnoDB transaction can observe two different values of the same row: a plain SELECT returns 5, and an UPDATE in the same transaction applies to 7. Read-then-write in application code is not protected there, even though a blind UPDATE ... SET x = x + 1 is. (Exact gap-locking behaviour depends on version and configuration. Verify it against your own engine version rather than trusting this shape.)

The practical rule is blunt: a level name tells you which vendor wrote it, not what it guarantees.

Snapshot Isolation: the level the standard cannot express

Snapshot isolation (SI) works like this: each transaction reads from a consistent snapshot taken at its start. At commit time the engine checks only the write sets: if another transaction already committed a write to the same row since the snapshot, this transaction loses. The rule "the first one to commit wins" is called first-committer-wins. PostgreSQL implements the variant first-updater-wins: the second writer is blocked or aborted as soon as it tries to touch the row. The effect is the same, but the conflict is noticed earlier.

Because validation looks only at writes, it catches every cycle that contains at most one rw edge. It is structurally blind to cycles that contain two. From this:

• SI forbids: dirty read, non-repeatable read, read skew, lost update, and phantoms inside the transaction's own view.
• SI permits: write skew, and predicate-based constraint violations (two transactions each insert a row that the other's check would have seen).
• SI costs nothing in read tracking. Read-only transactions neither block nor abort. This is the whole reason for its popularity.

Here is a clear demonstration that levels are not rungs on a ladder. SQL Server's READ_COMMITTED_SNAPSHOT (RCSI) and SNAPSHOT are the same mechanism. They differ only in whether the snapshot is taken per statement or per transaction. Read Committed and Snapshot Isolation are one scope decision apart.

Two beliefs about SI are widespread and wrong.

Read-only transactions are not automatically safe. Fekete, O'Neil and O'Neil showed that a read-only transaction under SI can observe a state that no serial order produces. Worse, its mere presence can make an execution of two read-write transactions non-serializable when they would have been fine without it. This is why SSI must reason about read-only transactions too. It is also why PostgreSQL offers READ ONLY DEFERRABLE: a read-only transaction that waits until it gets a provably safe snapshot.

Oracle's SERIALIZABLE is snapshot isolation. Oracle has never offered a true serializable level. This is established in the literature and is not a quibble. It is the most consequential level-name collision in the field.