Snapshot Isolation: Consistent Views for Concurrent Transactions

We've explored snapshot isolation in depth—its elegant snapshot-based reads, its high concurrency, and its Achilles heel: write skew. We've also seen that SERIALIZABLE isolation can prevent write skew through mechanisms like SSI. But how do these isolation levels truly compare?

This page provides a comprehensive comparison between Snapshot Isolation (SI) and Serializable isolation, examining their theoretical foundations, anomaly prevention capabilities, performance characteristics, and practical trade-offs. Understanding this comparison is essential for making informed decisions about which isolation level to use in your applications.

To compare SI and Serializable, we must first understand their theoretical foundations and where they sit in the isolation hierarchy.

Serializability: The Gold Standard

Serializability is the strongest commonly-used isolation level. A schedule is serializable if its effects are equivalent to some serial execution of the same transactions. This means no anomalies are possible—every concurrent execution could have happened as a sequence of non-overlapping transactions.

Formal Definition:

A schedule $S$ is serializable if there exists a serial schedule $S'$ with the same transactions where: for all data items $x$, if transaction $T_i$ reads or writes $x$ in $S$, the final value and all intermediate values of $x$ are identical to those in $S'$.

Snapshot Isolation: A Different Approach

Snapshot Isolation provides a different guarantee: each transaction sees a consistent snapshot of the database as it existed at the transaction's start time. Writes are checked for conflicts using First-Committer-Wins. SI was developed as a practical alternative to serializability that provides high concurrency while preventing most common anomalies.

Where SI Fits in the Hierarchy:

Snapshot Isolation is not part of the original SQL standard's four isolation levels. It sits between REPEATABLE READ and SERIALIZABLE in terms of anomaly prevention:

• SI prevents all anomalies that REPEATABLE READ prevents
• SI also prevents phantom reads for read-only transactions
• SI does NOT guarantee serializability (allows write skew)
• SI uses a fundamentally different mechanism (snapshots vs locks)

Important Terminology Confusion:

Many databases conflate these terms:

• PostgreSQL calls SI "REPEATABLE READ" and offers true SERIALIZABLE via SSI
• Oracle calls SI "SERIALIZABLE" (misleadingly)
• MySQL InnoDB's "REPEATABLE READ" uses SI plus gap locking

Always verify what your specific database actually provides at each isolation level.

Let's systematically compare which anomalies are prevented by each isolation level.

Understanding Each Anomaly:

Dirty Read: Reading uncommitted data. SI prevents this because snapshots only include committed transactions.

Non-Repeatable Read: Reading the same row twice and getting different values. SI prevents this because the snapshot is immutable.

Phantom Read: A range query returns different rows on re-execution. For read-only transactions, SI's snapshot prevents this. For transactions that write based on range query results, phantoms can cause problems similar to write skew.

Lost Update: Two transactions read the same row, both modify it, one overwrites the other. SI's FCW ensures only the first committer's update survives; the second must retry.

Read Skew: Reading two related items that are inconsistent with each other. SI's snapshot ensures all reads see the same consistent state.

Write Skew: Reading overlapping data, writing different items, violating a spanning constraint. This is the one SI does NOT prevent.

SI and Serializable isolation use fundamentally different mechanisms, leading to different performance characteristics.

Snapshot Isolation Mechanism:

1. Snapshot on start: Transaction captures a point-in-time view
2. Read from snapshot: All reads see the frozen snapshot state
3. Write directly: Writes create new versions in current state
4. FCW on commit: Check for write-write conflicts on same rows

2PL Serializable Mechanism:

1. Acquire locks: Get shared locks for reads, exclusive for writes
2. Hold locks: Locks held until transaction completes (two-phase)
3. Block on conflict: Transactions wait for conflicting locks
4. Release at end: All locks released on commit/abort

SSI Serializable Mechanism:

1. Snapshot + tracking: Take snapshot AND track read/write sets
2. SIREAD locks: Virtual locks record reads without blocking
3. Detect cycles: Check for rw-antidependency cycles
4. Abort on danger: Abort if dangerous structure detected

SSI Combines Both Approaches:

SSI uses SI as its base (snapshot reads, FCW) but adds dependency tracking to detect serializability violations.

SIREAD Locks:

Unlike real locks, SIREAD locks don't block. They're record-keeping mechanisms that track:

• What transactions read which data
• What data was written after being read by another transaction

Conflict Detection:

At commit time, SSI checks if the committing transaction is part of a "dangerous structure":

• A transaction with rw-antidependency from a committed transaction
• AND rw-antidependency to another concurrent transaction

If detected, the transaction is aborted with a serialization failure. The application must retry.

Performance differences between SI and Serializable depend heavily on workload characteristics. Let's analyze different scenarios.

SI Performance Advantages:

1. Readers never block writers and vice versa: This is the fundamental advantage. Long-running reports don't hold up short OLTP transactions.

2. No deadlock between readers and writers: Deadlocks can still occur between writers, but the reader-writer deadlock class is eliminated.

3. Predictable read latency: Reads always succeed immediately with snapshot data. No lock waits.

4. High concurrency for multi-row reads: Reading multiple tables or rows for complex queries doesn't accumulate locks.

Serializable Performance Costs:

2PL Costs:

• Lock acquisition and release overhead
• Lock table memory
• Blocking wait time
• Deadlock detection and resolution
• Lock escalation overhead

SSI Costs:

• SIREAD lock tracking overhead
• Predicate lock management
• Conflict detection at commit
• Higher abort rate than SI
• SIREAD lock retention for active transactions

Understanding that isolation level names mean different things in different databases is crucial for portability and correctness.

The choice between SI and Serializable depends on your application's requirements, workload characteristics, and tolerance for complexity.

Decision Framework:

1. Identify all constraints in your application
   - Schema constraints (UNIQUE, FK, CHECK)
   - Application constraints (business rules)

2. Categorize application constraints
   - Single-row constraints → SI is sufficient
   - Multi-row constraints → Need Serializable OR explicit locking

3. Evaluate constraint enforcement options
   - Can constraint be a DB trigger? → SI + trigger
   - Can we use SELECT FOR UPDATE? → SI + locking
   - Too complex? → Use Serializable

4. Consider performance requirements
   - High read concurrency needed? → Prefer SI/SSI
   - Low contention? → SSI overhead is acceptable
   - High contention? → Consider 2PL or SI + explicit locking

5. Choose and test
   - Test with production-like workloads
   - Measure abort rates and latency
   - Verify constraint enforcement

Changing isolation levels in an existing application requires careful planning. Let's examine strategies for both directions.

Migrating SI → Serializable:

This is the 'safer' direction—you're adding protection, not removing it.

Steps:

1. Update connection configuration to use SERIALIZABLE
2. Implement retry logic for serialization failures:

   MAX_RETRIES = 3
   for attempt in range(MAX_RETRIES):
       try:
           with connection.begin():
               do_transaction_work()
           break  # Success
       except SerializationFailure:
           if attempt == MAX_RETRIES - 1:
               raise
           time.sleep(random.uniform(0.01, 0.1))
   

3. Monitor abort rates and adjust if too high
4. Test under production-like load

Potential Issues:

• Higher abort rates require retry logic everywhere
• Some transactions may retry repeatedly under high contention
• SSI's SIREAD tracking may increase memory pressure

Migrating Serializable → SI:

This is the 'dangerous' direction—you're removing protection.

Steps:

1. Audit all constraints: Identify every application constraint
2. Categorize by risk: Which constraints span multiple rows?
3. Implement alternatives for risky constraints:
   • Add database triggers
   • Use SELECT FOR UPDATE
   • Add explicit lock rows
   • Keep SERIALIZABLE for these specific transactions
4. Test exhaustively: Write tests that exercise concurrent scenarios
5. Monitor for violations: Log and alert on constraint violations

Potential Issues:

• Risk of introducing write skew bugs
• Need comprehensive test coverage
• May need to mix isolation levels per transaction
• Subtle bugs may only appear under high concurrency

The choice between Snapshot Isolation and Serializable is one of the most important decisions in database application design. Each level offers different trade-offs between correctness guarantees, performance characteristics, and implementation complexity.

What's Next:

The final page explores Practical Usage of Snapshot Isolation, covering real-world patterns, optimization strategies, monitoring techniques, and best practices for deploying SI-based systems in production.