Systems Engineering and RDBMS

New Isolation Level options in SQL Server 2005

Posted by decipherinfosys on April 18, 2008

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:

USE MASTER
GO
CREATE DATABASE TEST
GO
USE TEST
GO
/************************************************************************
Set the Read committed snapshot option ON
*************************************************************************/
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON;
GO
/************************************************************************
Set the Allow Snapshot Isolation option ON
*************************************************************************/
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

/************************************************************************
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’

is_read_committed_snapshot_on snapshot_isolation_state snapshot_isolation_state_desc

----------------------------- ------------------------ ------------------------------

1                             1                        ON

This shows that we have the settings in place now.

Let’s create a table and populate it with data and then we will start different sessions with the transactions and see how the isolation options effect the results:

CREATE TABLE TEST (COL1 INT NOT NULL IDENTITY PRIMARY KEY, COL2 INT, COL3 NVARCHAR(10));
INSERT INTO TEST (COL2, COL3) VALUES (10, ‘abc’);
INSERT INTO TEST (COL2, COL3) VALUES (20, ‘xyz’);
INSERT INTO TEST (COL2, COL3) VALUES (30, ‘axz’);
INSERT INTO TEST (COL2, COL3) VALUES (40, ‘vbq’);

Now, let’s start 2 sessions:

SESSION 1:
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE TEST SET COL2 = 100 WHERE COL1 < 3

Do not commit or rollback in that session…and in the second session, execute:

SESSION 2:
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM TEST

You will get the older values from the row versions that are created in tempdb database:

COL1 COL2 COL3
———– ———– ———-
1 10 abc
2 20 xyz
3 30 axz
4 40 vbq

If this were just a READ COMMITTED transaction isolation level WITHOUT the READ_COMMITTED_SNAPSHOT option being turned on at the DB level, this select statement would have been blocked.

Now, let’s see a couple of things pertaining to the SNAPSHOT transaction isolation level:

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
UPDATE TEST SET COL2 = 100 WHERE COL1 < 3

This will give an error:

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:

SESSION 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE TEST SET COL2 = 100 WHERE COL1 < 3

And now, in session 2:

SESSION 2:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT * FROM TEST

And you will get the same results as before:
COL1 COL2 COL3
———– ———– ———-
1 10 abc
2 20 xyz
3 30 axz
4 40 vbq

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:

SESSION 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE TEST SET COL2 = 1 WHERE COL1 < 3

And then in SESSION 2:
SESSION 2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
SELECT * FROM TEST

You will get:

COL1 COL2 COL3
———– ———– ———-
1 10 abc
2 20 xyz
3 30 axz
4 40 vbq

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:

SESSION 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE TEST SET COL3 = ‘123’ WHERE COL1 = 1

And now, in the second session, execute this:

SESSION 2:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE TEST SET COL3 = ‘999’ WHERE COL1 = 1

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.

One Response to “New Isolation Level options in SQL Server 2005”

  1. […] Understanding the new isolation levels in SQL Server 2005 – our blog post here. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: