Snapshot Isolation: A 'Photo' of Your Database

WHY IT EXISTS Strictly serializing database transactions is safe but slow, as transactions must often wait for each other. Lower isolation levels are faster but can lead to inconsistent reads. Snapshot Isolation was created to find a middle ground: providing high concurrency by letting reads and writes not block each other, while still offering strong consistency guarantees.

THE MENTAL MODEL Imagine you're an auditor given a photocopy of a company's entire ledger from 9 AM. You perform your analysis on this static copy, never seeing the live changes happening throughout the day. When you're ready to submit your findings, you check if anyone else has modified the specific accounts you also changed. If they have, your work is rejected, and you must start over with a fresh photocopy. If not, your changes are accepted.

HOW IT WORKS When a transaction begins, it gets a consistent view of the database as it existed at that moment. This is typically implemented using Multi-Version Concurrency Control (MVCC), where the database keeps old versions of data rows. All reads within the transaction access the data versions that were current at its start time. When the transaction attempts to commit its writes, the database checks if any other transaction has concurrently modified the same data. If a direct write-write conflict exists, the commit fails, and the transaction aborts. If not, it succeeds.

WHEN TO USE IT Snapshot Isolation is ideal for read-heavy workloads or mixed workloads where you want to prevent long-running analytical queries from blocking short-lived updates. It allows readers and writers to operate in parallel without locking each other, dramatically improving concurrency. It's the default or a common option in databases like PostgreSQL (as 'Repeatable Read'), Oracle, and Microsoft SQL Server.

WHEN NOT TO USE IT Do not rely on it when you need absolute, logical consistency that only true serializability provides. Its primary weakness is an anomaly called 'write skew'. This occurs when two transactions read an overlapping set of data, make decisions based on it, and then write to two different items. Since they don't write to the same item, the database doesn't detect a conflict, but the business logic is violated. For example, two transactions check that a meeting room is free, then book it, but write to different calendar entries.

ONE CANONICAL EXAMPLE Two transactions, T1 and T2, start. Both read that there are two doctors on call. T1 updates Doctor A's schedule to be 'on vacation'. T2 updates Doctor B's schedule to be 'on vacation'. Since T1 and T2 modified different rows, there is no write-write conflict, and both transactions commit. The database now incorrectly shows zero doctors on call, violating a business rule that at least one must be available. This is a classic write skew anomaly.