Course contentsShow
← Software Engineering
Lesson 909 of 1,93328. Transactions and ConsistencyFree lesson

Snapshot Isolation Explained

How snapshot isolation provides consistent views without full serializability; MVCC implementation and write skew anomalies.

Snapshot Isolation Explained

You'll learn how snapshot isolation gives each transaction a consistent view of the database without the full cost of serializability.

The Core Idea

Snapshot isolation is like giving every transaction its own photograph of the database at the moment it begins. While the transaction runs, it reads from this frozen snapshot—seeing no changes made by other concurrent transactions. When it comes time to commit, the database checks whether any data the transaction modified was also changed by another transaction since the snapshot was taken. If so, one transaction must abort.

This provides excellent consistency for reads (you never see partial or inconsistent data) while allowing high concurrency (multiple transactions can read and work simultaneously without blocking each other).

MVCC: The Implementation Behind the Scenes

Multi-Version Concurrency Control (MVCC) makes snapshot isolation possible by keeping multiple versions of each row. When you read data, you see the version that was current when your transaction started. When you write, you create a new version. This eliminates read locks entirely—readers don't block writers, and writers don't block readers.

The Write Skew Anomaly

Despite its strengths, snapshot isolation isn't truly serializable. The classic problem is write skew: two transactions read overlapping data, make decisions based on what they read, then write to different rows. Both pass the commit check (they didn't modify the same data), but the combined result violates a business constraint.

Example: two doctors on call. Transaction A checks "at least one doctor on call" (sees both), then removes doctor 1. Transaction B does the same check, removes doctor 2. Both commit successfully—now zero doctors are on call, violating the constraint.

Key Takeaway: Snapshot isolation provides consistent reads and high concurrency through MVCC, but doesn't prevent write skew anomalies—you need application-level checks or true serializability for that.