Serializable vs. Snapshot Isolation Level


Both the serializable and snapshot isolation levels provide a read consistent view of the database to all transactions.  In either of these isolation levels, a transaction can only read data that has been committed.  Moreover, a transaction can read the same data multiple times without ever observing any concurrent transactions making changes to this data.  The unexpected read committed and repeatable read results that I demonstrated in my prior few posts are not possible in serializable or snapshot isolation level.


Notice that I used the phrase “without ever observing any … changes.”  This choice of words is deliberate.  In serializable isolation level, SQL Server acquires key range locks and holds them until the end of the transaction.  A key range lock ensures that, once a transaction reads data, no other transaction can alter that data – not even to insert phantom rows – until the transaction holding the lock completes.  In snapshot isolation level, SQL Server does not acquire any locks.  Thus, it is possible for a concurrent transaction to modify data that a second transaction has already read.  The second transaction simply does not observe the changes and continues to read an old copy of the data.


Serializable isolation level relies on pessimistic concurrency control.  It guarantees consistency by assuming that two transactions might try to update the same data and uses locks to ensure that they do not but at a cost of reduced concurrency – one transaction must wait for the other to complete and two transactions can deadlock.  Snapshot isolation level relies on optimistic concurrency control.  It allows transactions to proceed without locks and with maximum concurrency, but may need to fail and rollback a transaction if two transactions attempt to modify the same data at the same time.


It is clear there are differences in the level of concurrency that can be achieved and in the failures (deadlocks vs. update conflicts) that are possible with the serializable and snapshot isolation levels.


How about transaction isolation?  How do serializable and snapshot differ in terms of the transaction isolation that they confer?  It is simple to understand serializable.  For the outcome of two transactions to be considered serializable, it must be possible to achieve this outcome by running one transaction at a time in some order.


Snapshot does not guarantee this level of isolation.  A few years ago, Jim Gray shared with me the following excellent example of the difference.  Imagine that we have a bag containing a mixture of white and black marbles.  Suppose that we want to run two transactions.  One transaction turns each of the white marbles into black marbles.  The second transaction turns each of the black marbles into white marbles.  If we run these transactions under serializable isolation, we must run them one at a time.  The first transaction will leave a bag with marbles of only one color.  After that, the second transaction will change all of these marbles to the other color.  There are only two possible outcomes:  a bag with only white marbles or a bag with only black marbles.


If we run these transactions under snapshot isolation, there is a third outcome that is not possible under serializable isolation.  Each transaction can simultaneously take a snapshot of the bag of marbles as it exists before we make any changes.  Now one transaction finds the white marbles and turns them into black marbles.  At the same time, the other transactions finds the black marbles – but only those marbles that where black when we took the snapshot – not those marbles that the first transaction changed to black – and turns them into white marbles.  In the end, we still have a mixed bag of marbles with some white and some black.  In fact, we have precisely switched each marble.


The following graphic illustrates the difference:



We can demonstrate this outcome using SQL Server.  Note that snapshot isolation is only available in SQL Server 2005 and must be explicitly enabled on your database:



alter database database_name set allow_snapshot_isolation on


Begin by creating a simple table with two rows representing two marbles:



create table marbles (id int primary key, color char(5))
insert marbles values(1, ‘Black’)
insert marbles values(2, ‘White’)


Next, in session 1 begin a snaphot transaction:



set transaction isolation level snapshot
begin tran
update marbles set color = ‘White’ where color = ‘Black’


Now, before committing the changes, run the following in session 2:



set transaction isolation level snapshot
begin tran
update marbles set color = ‘Black’ where color = ‘White’
commit tran


Finally, commit the transaction in session 1 and check the data in the table:



commit tran
select * from marbles


Here are the results:

id          color
———– —–
1 White
2 Black

As you can see marble 1 which started out black is now white and marble 2 which started out white is now black.  If you try this same experiment with serializable isolation, one transaction will wait for the other to complete and, depending on the order, both marbles will end up either white or black.

Comments (22)

  1. arun.philip says:

    Interesting. I found the example very illustrative!

  2. Yan Liberman says:

    В этом посте я собрал, перевел и адаптировал под Microsoft SQL Server 2005 (где

  3. AndrewF@Oz says:

    Can you tell me if the application of these isolation levels is affected by compatibility levels ?

    I have a DB in SQL 2005 running at level 80. Will it be able to support snapshot isolation ?

  4. Snapshot isolation is available at compatibility level 80, but you do need to enable it for the database using the ALTER DATABASE database_name SET ALLOW_SNAPSHOT_ISOLATION ON statement.

  5. Tom McCann says:

    You lost me in the second sentance: "In either of these isolation levels, a transaction can only read data that has been committed.". I assume you mean that another transaction (i.e. not my transaction which is operating on the Serializable level) cannot read data that my transaction has not committed.

    Why do I find that EVERY article I read on this subject fails to make this distinction in perspective? You might think that the meaning is obvious, but in my experience newcomers are confused about who is applying the locks and how isolation levels actually work.

  6. Hi Tom,

    What I meant is that a transaction X running at either serializable or snapshot isolation level cannot read data written by another transaction Y that has not yet committed.  Whether transaction Y can read data written by transaction X depends solely on the isolation level of transaction Y.  If transaction Y is running at the read uncommitted isolation level it can read uncommitted data; if it is running at any other isolation level, it cannot.

    I'm sorry that you found my post confusing.  Thanks for the feedback.

    Craig

  7. nita says:

    so… can txn X read data wrtten by itself before commit ?

  8. Yes, a snapshot transaction can always read its own changes before commit.

  9. Michail says:

    Absolutly perfect article!

    Thank you!

  10. Shanky_621 says:

    Hello Craig,

    Either i did not understand or you did not explained how values changed in result.If you can do that ,it would make your article excellent one

  11. Shanky_621 says:

    >>Moreover, a transaction can read the same data multiple times without ever observing any concurrent transactions making changes to this data.

    Above line of your is true for Snapshot IL or serializable IL please comment

  12. Re: how values changed in the result.  The marbles table started with two rows (id, color) = { (1, 'Black'), (2, 'White') } and (in the snapshot example) ended with { (1, 'White'), (2, 'Black') }.  This result is possible with snapshot isolation but is NOT possible with serializable.

    Re: "a transaction can read the same data multiple times without ever observing any concurrent transactions making changes to this data."  This statement is true for both snapshot and serializable.

    HTH,

    Craig

  13. drg says:

    Hi Craig, great article. I wanted more explanation on the snapshot sequence of events — let me know if I have this right.

    (1) The first transaction that is not committed until the end (tran 1) updates black marbles to white marbles.

    (2) I think it is an important step here to mention that a snapshot isolation read *after* this transaction in another session will read these marbles as black still since we are under snapshot isolation and the row version will be black.

    (3) Another session (tran 2) updates (in snapshot isolation) the white marbles to black marbles. Again, I think it's helpful here to state that the update first finds all white marbles and according to the row version from tran 1, the black marbles are still black. So, tran 2 updates white marbles to black marbles and commits.

    (4) At this point, I should note that another snapshot isolation read, say in session 3 would read all black marbles. It would find the black marbles from the row version in tran 1 and it would find the black marbles from the committed tran 2 update where white turned to black. So reads at this point have all marbles as black.

    (5) When the tran 1 is finally committed, it means the row version of black goes away and those marbles would now read as white. So alas, we now have 2 white marbles and 2 black marbles.

  14. drg says:

    In the last step above (5), I meant 1 white marble and 1 black marble. Looking at the picture I saw 2 but in the example only 1 of each were inserted.

  15. drg says:

    Just confirmed the steps 1-5 in testing

  16. How is serializable implemented? says:

    Thank you for that nice example. It got me thinking about how the serializable isolation level is actually implemented and I'm a bit puzzled. I wrote a question on stack overflow about it here:

    stackoverflow.com/…/how-are-serializable-transactions-implemented

    I used your example and I also linked to this blog post there.

  17. AMS says:

    there is a question regarding the illustration. Say the bag had 2 white , 2 black and 2 orage marbels.

    Transaction 1 converts white to black and orange to green marbles.

    transaction 2 converts black to white and orange to red marbles.

    What would be the outcome in case these two transactions were run under the snapshot isolation level and cimmitted at precisely the same time?

  18. When two snapshot transactions both attempt to update the same row, one of the transaction will be aborted due to an update or write-write conflict.  This is true both for snapshot isolation on disk based tables and for snapshot isolation on memory optimized tables (new to SQL Server 2014).

    For example, extending the original example from this post:

    alter database db set allow_snapshot_isolation on

    create table marbles (id int primary key, color char(8))

    insert marbles values(1, 'Black')

    insert marbles values(2, 'White')

    insert marbles values(3, 'Orange')

    go

    — transaction 1 in session 1

    set transaction isolation level snapshot

    go

    begin tran

    update marbles set color = 'Black' where color = 'White'

    update marbles set color = 'Green' where color = 'Orange'

    go

    — pause and run transaction 2 in session 2

    set transaction isolation level snapshot

    go

    begin tran

    update marbles set color = 'White' where color = 'Black'

    update marbles set color = 'Red' where color = 'Orange'

    — hangs here waiting for transaction 1 to commit or abort

    — return to transaction 1 in session 1

    commit tran

    go

    — transaction 2 in session 2 now fails with the following error:

    Msg 3960, Level 16, State 5, Line 5

    Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.marbles' directly or indirectly in database 'db' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

    The same example using in-memory OLTP and memory optimized tables:

    — enable database for in-memory OLTP

    ALTER DATABASE db ADD FILEGROUP [mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA

    ALTER DATABASE db ADD FILE (name = [mod_dir], filename= 'c:mod_dir') TO FILEGROUP mod_fg;

    create table marbles (id int primary key nonclustered, color char(8)) with (memory_optimized = on)

    insert marbles values(1, 'Black')

    insert marbles values(2, 'White')

    insert marbles values(3, 'Orange')

    go

    — transaction 1 in session 1

    begin tran

    update marbles with (snapshot) set color = 'Black' where color = 'White'

    update marbles with (snapshot) set color = 'Green' where color = 'Orange'

    go

    — pause and run transaction 2 in session 2

    begin tran

    update marbles with (snapshot) set color = 'White' where color = 'Black'

    update marbles with (snapshot) set color = 'Red' where color = 'Orange'

    — transaction 2 in session 2 immediately fails with the following error:

    Msg 41302, Level 16, State 110, Line 5

    The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

    Msg 3998, Level 16, State 1, Line 5

    Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

    The statement has been terminated.

    HTH

    Craig

  19. Sanket Patle says:

    Very nice article. Cheers.

  20. 92fs says:

    Hello Craig,

    I ran the code example that you posted but I'm not getting the same results as you. I'm using MS SQL 2012 if that makes a difference. This is the code that I ran.

    USE [MS-70-461_TEST];

    GO

    — SNAPSHOT ISOLATION must be explicitly enabled on your database:

    ALTER DATABASE [MS-70-461_TEST] SET ALLOW_SNAPSHOT_ISOLATION ON

    — Begin by creating a simple table with two rows representing two marbles:

    IF OBJECT_ID('Marbles', 'U') IS NOT NULL

    DROP TABLE Marbles;

    GO

    CREATE TABLE Marbles

    (

    id int primary key

    ,color char(5)

    );

    GO

    INSERT INTO Marbles VALUES

    (1, 'BLACK')

    ,(2, 'WHITE');

    GO

    — Next, in session 1 begin a snapshot transaction:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRAN

    UPDATE Marbles SET color = 'WHITE' WHERE color = 'BLACK';

    GO

    — Now, before committing the changes, run the following in session 2:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRAN

    UPDATE Marbles SET color = 'BLACK' WHERE color = 'WHITE'

    COMMIT TRAN;

    GO

    — Finally, commit the transaction in session 1 and check the data in the table:

    COMMIT TRAN

    SELECT * FROM Marbles

  21. What result do you get?  Did you remember to run the two update transactions in two different sessions?  For example, using Management Studio, open two connections in separate tabs and run one update transaction in each tab.

    HTH

    Craig

  22. Med says:

    Simple, clear, concise and very instructive.

    This article should be elected "Best SQL Server Article" for ever

    Thank you Graig