THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Snapshot isolation and SQL Server 2000 compatibility level

What is the simplest test to see the snapshot isolation level at work?

 

There was a question in one of the forums on whether you can use the snapshot isolation level in a SQL Server 2005 database that is in the SQL2000 compatibility level. I don’t know what may be the official answer from the Books Online. It may be documented somewhere, but I wasn’t able to find it (perhaps I did not search hard enough).

 

In any event, it is simple enough to try it out. But it’s not enough to just run ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON. The fact that you can change the database isolation setting to allow_snapshot_isolation may not be a sufficient proof that the feature actually works in a SQL2000 compatible database. To be safe, it’s better to try it out with an example.

 

This led to the question of this post: what may be the simplest test to see the snapshot isolation level at work?

 

Note that it’s not enough to show that a writer does not block a reader. We must show that it gives us the transaction level read consistency when the read committed isolation level gives us ‘inconsistent’ data (i.e. non-repeatable reads).

 

Below is a simple test. Whether it is the simplest, I’ll leave you to judge. For the test, we need two connections and a single column table with two rows.

 

First, verify that the default isolation level (read committed) gives ‘inconsistent’ result.

 

On connection 1, run the following script:

 

drop table t

go

create table t(i int)

create clustered index cix_t on t(i)

go

insert t values(1)

insert t values(2)

go

begin tran

update t

   set i = 2

 where i = 2

 

waitfor delay '00:00:10'

 

update t

   set i = 3

 where i = 1

commit tran

 

On connection 2, run the following script (you should run this within 10 seconds of running the first script or bump up the waitfor delay value):

 

select * from t

 

The above SELECT statement will produce three rows when table t only has two rows, and the SELECT statement will be blocked by the first connection until the first script finishes executing. This is expected when the isolation level is read committed.

 

Now, try the same scripts with the snapshot isolation level as follows.

 

On connection 1, run the following script:

 

alter database testDB set allow_snapshot_isolation on;

go

use testDB

go

drop table t

go

create table t(i int)

create clustered index cix_t on t(i)

go

insert t values(1)

insert t values(2)

go

begin tran

update t

   set i = 2

 where i = 2

 

waitfor delay '00:00:10'

 

update t

   set i = 3

 where i = 1

commit tran

 

On connection 2, run the following script (again, make sure you run this within 10 seconds of starting the first script):

 

Use testDB

go

set transaction isolation level snapshot;

select * from t

 

You’ll get two rows, and the SELECT statement won’t be blocked by the script running on the first connection.

 

Changing the database compatibility level to 80, and repeat the test to verify that the snapshot isolation level works even when the database is in the SQL2000 compatibility level.

 

Note that in this test I only check for the prevention of non-repeatable reads. You can change the scripts to show the prevention of phantom reads.

 

If you have an even simpler test, I’d love to see it.

 

Published Friday, September 25, 2009 9:54 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

Linchi,

Thank you for exposing this. This is very bad. Snapshot isolation may be a breaking change. As such, it should never be available under 80 level, because it can compromise data integrity.

September 25, 2009 9:41 AM
 

Uri Dimant said:

I love Jim Gray's example

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

September 29, 2009 2:41 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement