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.