SQL Server 2005 Enhancement - SNAPSHOT ISOLATION LEVEL

This is probably going to turn out to be my favorite new feature in terms of bang for the buck.  With it, we can almost eliminate the dreaded nolock keyword from our queries, and even any SET TRANSACTION ISOLATION LEVEL READ COMMITTED.  Using it will take care of a large number of our problems we have had in the past writing concurrent systems, at least from a database locking perspective.  For the first blog in this series, let's just cover what it is from a mechanical standpoint.  Part 1a will cover a new related setting, Part 2 will cover a technique for using this for optimistic locking (and I am not just trying to get paid for two articles, I am doing this for the fun of it and since we need multiple connections this might get messy :)

Quick review of isolation levels in 2000:

• READ UNCOMMITTED - May see data that may never be persisted.  Leave no locks, honor no locks. 
• READ COMMITTED - Sees only data that has been committed, doesn't hold locks so data can change after it is read, on a row by row basis, Hence, running the same query twice in the same transaction may return more or less rows.  Honors locks, leaves locks very short amount of time.
• REPEATABLE READ - Adds to READ COMMITTED the caveat that locks are held so data you view cannot be deleted while your transaction is active. You will always get back the same set of rows, but new rows (called phantoms) could be added by another process.
• SERIALIZABLE - Adds to REPEATABLE READ that it locks data that may not even exist, so that other users could not create new rows that would affect the results.

Note that all behave the same way when you write data, but not when you read it (even when it comes to validating a foreign key or a trigger constraint. We will talk about this more later!)  If you don't understand isolation levels, you need to learn more about them as they are central to building concurrent RDBMS based applications. If you want to know more, check books online, or any of many SQL Server 2000 books <a shame = off> My 2000 Design Book</a>

SQL Server 2005 adds SNAPSHOT (if you hadn't guessed that by now click here, it's probably more your speed ) .  What this will give us the best of both worlds.  READ UNCOMMITTED was bad because it could give us "wrong" answers.  Absolutely wrong, because of this scenario:

• User A put in a million dollar sale, only to delete it because they found out that Mr I. C. Weiner didn't actually live at 200 GetALIFE Way after the address checker did it's thing.
• User B, being User A's manager, runs the sales report, discovering that sales are way up this month because they saw the invalid data.  He proceeds to purchase a 40 foot yacht which he will regret later.  Sure the report could be run twice, but this is still a bad solution. 

READ COMMITTED was okay for concurrency, but since locks aren't held, you can execute the same statement multiple times in a transaction and get different answers.  It could cause concurrency issues in that it had to wait for other users to complete, and if the task was too big, it might just escalate to a table lock and cause everyone else to wait.  Boo.  The higher isolation levels made it even worse because they hold locks and range so everyone waits.  Now generally speaking you should only hold locks as short of a time as you need to ensure consistency, but the definition of short varies greatly.  If you have two users on a system, short could mean a minute.  If you have 10000, short could be 100 ms.  

SNAPSHOT ISOLATION LEVEL lets us look at how the data was when the transaction started.  So, changes don't affect you, so no need to lock there, and the results you get are consistent and completely right as of when you start the command.  They may not be still true when your query is finished, but at least they were true.  This is a distinct improvement, and, as we will discuss in an upcoming blog, perfect for optmistic locking.  So let's finish this off with a little example.  I have this table:

First, you have to allow snaphot transactions in your database:

ALTER DATABASE <databaseName>
  SET ALLOW_SNAPSHOT_ISOLATION ON

Then we create a table:

create schema test
go
create table test.testIsolationLevel
(  
    testIsolationLevelId    int
        CONSTRAINT PKtest_testIsolationLevel PRIMARY KEY,
    value                   varchar(20)
)
go
insert into test.testIsolationLevel(testIsolationLevelId, value)
values (1,'one')
go

Then run the following:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT * from test.testIsolationLevel

Now, without closing that transaction, run this command in a different connection:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
insert into test.testIsolationLevel(testIsolationLevelId, value)
values (2,'two')

Finally, go back to your first connection, and reexecute the SELECT STATMENT. 

You don't see the new row, but there was no blocking, commit the transaction and you will see the new row.  This is going to be awesome to break contention between readers and writers, but as I will discuss in later installments, there are will be major concerns when using SNAPSHOT ISOLATION LEVEL and writing data (having to do with the amount of time the transaction tabkes, and how this might affect consistency.)  There is absolutely no concerns (that I have right now) with using this for people running reports, as they will have data accurate as of the time they started.