New Isolation Level options in SQL Server 2005

Microsoft introduced two new isolation levels in SQL Server 2005 and it is important to understand the differences between them in order to be able to make a judicious decision on which one to use. Both of these options support row versioning. The two new isolation levels are:

a) A new implementation of the READ COMMITTED Isolation: This is the READ_COMMITTED_SNAPSHOT database option. When this option is set, this provides statement level read consistency and we will see this using some examples in the post. Using this option, the reads do not take any page or row locks (only
SCH-s: Schema Stability locks) and read the version of the data using row versioning by reading the data from tempdb. This option is set at the database level using the ALTER DATABASE command.

b) A new Isolation Level called the SNAPSHOT ISOLATION level. This option requires to be set at the database level as well as the connections need to specify the SET TRANSACTION ISOLATION LEVEL SNAPSHOT in order to use this isolation level. When operating under this isolation level, again, no page or row locks are taken by the read operations (only SCH-S locks are taken), however, the key difference is that this gives a snapshot of the data as it was at the start of the transaction (the READ_COMMITTED_SNAPSHOT option does this at the statement level). So, essentially the statements within the transaction have the snapshot of the data as it existed prior to the start of the transaction.

Let’s pick up an example and go through it to see what the changes are and how the error handling is done in the case of conflicts in the case of the SNAPSHOT ISOLATION LEVEL. We will begin by creating a database and then setting it’s options:

/************************************************************************
Now, let’s make sure that we have the right settings:
*************************************************************************/
select is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc
from sys.databases
where name = ‘TEST’

Msg 3951, Level 16, State 1, Line 3
Transaction failed in database ‘TEST’ because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.

As the error states, the transaction needed to be started under the SNAPSHOT isolation mode when the transaction starts rather than after it. So, let’s do that and then let’s run this in the different sessions:

However, now if we go back to the previous session and commit it, the results will still be the same in SESSION 2 since it took the snapshot at the start of the transaction unlike READ_COMMITTED_SNAPSHOT which gives the state of the data just before the statement. Example:

And now if you commit the data in the first session and just run the SELECT statement in the second session, you will get:
COL1 COL2 COL3
———– ———– ———-
1 1 abc
2 1 xyz
3 30 axz
4 40 vbq

Another key difference between these two is that for the update conflicts, the application needs to handle the error in the case of the SNAPSHOT ISOLATION LEVEL. Since the SNAPSHOT isolation takes the snapshot at the beginning of the transaction itself, if an error check mechanism is not in place, then problems pertaining to lost updates (one session overwriting the results of another session’s transaction) can easily happen. There is an error check mechanism in place which helps to prevent this. Let’s follow that statement with an example:

This statement will get blocked as it should. Now, go back to the first session and commit the transaction. Once you do that, look at the second session and you will see this error message:
Msg 3960, Level 16, State 2, Line 3
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.TEST’ directly or indirectly in database ‘TEST’ 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 error is self explanatory. Hopefully this post helped explain the differences between the two isolation levels. In a majority of the situations, the READ_COMMITTED_SNAPSHOT is a good solution over the Snapshot Isolation level. In one of the upcoming posts, we will go over the different scenarios in which these two isolation levels should be used.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on April 18, 2008 at 9:26 am and is filed under SQL Server.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.