SSI (Serializable Snapshot Isolation) and MVCC (Multi-Version Concurrency Control)

📚 What is MVCC?

MVCC (Multi-Version Concurrency Control) is a concurrency control technique used by databases like PostgreSQL, Oracle, MySQL InnoDB, etc.

✅ What MVCC does:

Instead of locking rows, MVCC gives each transaction a consistent snapshot of the data at the time it starts.
When you SELECT, you see the version of rows visible to you (i.e., those committed before your transaction started).
Writes create new versions of rows rather than overwriting existing ones.

🔒 Isolation levels that use MVCC:

Read Committed
Repeatable Read
Snapshot Isolation (often = Repeatable Read in PostgreSQL)

🧠 What is SSI (Serializable Snapshot Isolation)?

Serializable Snapshot Isolation (SSI) is a way to achieve serializability — the strongest isolation level — without locking every read.

It's like Snapshot Isolation (MVCC-based) but with conflict detection.

If two transactions could violate serializability, one is aborted at commit.

Used in: PostgreSQL's SERIALIZABLE isolation level.

🧪 Let’s Revisit Your Race Conditions with MVCC & SSI

Race Condition	MVCC Handles?	SSI Handles?	Why
🧼 Dirty Read	✅ Yes	✅ Yes	MVCC never shows uncommitted data
🖍 Dirty Write	✅ Yes	✅ Yes	Writes are versioned; write-write conflicts abort one txn
🔀 Lost Update	❌ No	✅ Yes	MVCC allows read-modify-write races unless locked; SSI detects conflict and aborts
🤹 Read Skew (Non-Repeatable Read)	✅ Yes	✅ Yes	MVCC gives a consistent snapshot
🎲 Write Skew	❌ No	✅ Yes	MVCC sees consistent state, but both txns may write conflicting data (e.g., doctor off-call); SSI aborts
👻 Phantom	❌ No	✅ Yes	MVCC can’t lock range predicates; SSI tracks predicate reads and aborts conflicting inserts