Serializable vs. Snapshot Isolation Level
Based on an article by Craig Freedman: Serializable vs. Snapshot Isolation Level
The Serializable and Snapshot transaction isolation levels provide consistent reads from the database. At any of these isolation levels, a transaction can only read committed data. Moreover, a transaction can read the same data multiple times without worrying about any concurrent transactions making changes to the same data. The undesirable effects that have been demonstrated in previous articles with Read Committed and Repeatable Read are simply not possible with Serializable and Snapshot isolation levels.
Note that I used the phrase “not caring about any… making changes”. This choice of words is deliberate. At the Serializable isolation level, the SQL Server engine places a key range lock and holds it until the end of the transaction. A key range lock ensures that once a transaction has read the data, no other transaction can modify that data (even for phantom row inserts) until the transaction holding the lock completes. At the Snapshot isolation level, the SQL Server engine does not acquire any locks. Thus, a concurrent transaction can modify data that has already been read by a second transaction. The second transaction simply does not notice these changes and continues to use the old version of the data.
The Serializable isolation level is based on pessimistic concurrency control. It guarantees consistency by assuming that two transactions can attempt to update the same data, and uses locks to ensure they don’t, but (at the cost of reduced concurrency) one transaction must wait for the other to complete, and two transactions can block . The Snapshot isolation level is based on optimistic concurrency control. This allows transactions to run block-free and with maximum concurrency, but can fail and then rollback the transaction if two transactions attempt to modify the same data at the same time.
As you can see, there are differences between Serializable and Snapshot isolation levels in the level of concurrency (which can be achieved) and in the set of possible problems (deadlocks and update conflicts).
Let’s take a look at how Serializable and Snapshot work differently in terms of the transaction isolation they provide. With Serializable, everything is pretty simple. For the result of two transactions to be considered serializable, they must be executed in some order, one transaction at a time.
Snapshot does not guarantee this level of isolation. A few years ago Jim Gray offered an excellent example demonstrating the differences between these levels. Imagine that we have a bag containing a mixture of white and black balls. Suppose we want to start two transactions. One transaction recolors each white ball into a black ball. The second transaction recolors each black ball into a white ball. If we start these transactions with Serializable isolation, they will be executed one by one. After the first transaction, there will be a bag with balls of only one color. After that, the second transaction will change all these balls to a different color. There are only two possible outcomes: a bag of only white balls or a bag of only black balls.
If we run these transactions with Snapshot isolation, there is a third result that is not possible with Serializable isolation. Each transaction can simultaneously take a snapshot of the bag of balls as it was before the change. Now one transaction finds white balls and recolors them into black balls. At the same time, other transactions find the black balls (but only those balls that were black when we took the picture, not the balls that the first transaction changed to black) and recolor them white. As a result, the bag will contain a mixture of white and black balls. In fact, with this isolation level, we correctly changed the color of each ball.
The following figure illustrates these differences:
We can demonstrate this behavior using SQL Server. Note that Snapshot isolation is only available from SQL Server 2005 and must be explicitly enabled for the database being used:
alter database database_name set allow_snapshot_isolation onLet’s start by creating a simple table with two rows representing two balls of different colors:
create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')Then, in the first session, start a transaction with Snapshot isolation level:
set transaction isolation level snapshot
begin tran
update marbles set color="White" where color="Black"Now, before you commit your changes, run the following in your second session:
set transaction isolation level snapshot
begin tran
update marbles set color="Black" where color="White"
commit tranFinally, commit the transaction in the first session and check the data in the table:
commit tran
select * from marblesHere is the result:
id color
-- -----
1 White
2 BlackAs you can see, ball 1, which was originally black, has now become white, and ball 2, which was originally white, has become black. If you try the same experiment with the Serializable isolation level, one transaction will wait for the other to complete, and depending on the order, both balls will be white or black.
