New 2005 feature: READ_COMMITTED_SNAPSHOT

 There wasn't as much out there but I still think Kim Tripp's article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp) covered it and I just missed it.

There is a setting on the database (READ_COMMITED_SNAPSHOT) that changes the behavior of the READ COMMITTED isolation level.  Instead of placing shared locks and honoring other connections locks to prevent dirty reads, it uses the SNAPSHOT buffers stored in tempdb (much like Oracle does, I am told several times a day!)  .  For example, say you have the following code:

--Snapshot isolation needn't be enabled for the database.  This setting will take care of
--enabling the engine to maintain snapshots for statements

ALTER DATABASE <databasename>
    SET READ_COMMITTED_SNAPSHOT ON

BEGIN TRANSACTION
SELECT column FROM table
--MIDPOINT
SELECT column FROM table
COMMIT TRANSACTION

When you start the first SELECT statement, no matter what any other connection does to the table, you will get the table as it appeared when you started fetching rows.  It is incredibly hard changing a table while results are being spooled to the client in a demo, since it is hard to down results returning in Management Studio (so I won't try.)  Just like ordinary READ COMMITTED mode, the second SELECT would reflect any changes that had been made up until the moment the statement begins. 

 In full SNAPSHOT isolation level, the second results from the SELECT would be exactly the same as the first. It is a really cool setting, but it can suffer from being just a little bit less restrictive than typical READ COMMITTED.  The problem comes when an update to the child table requires validation of the parent.  Once the table is being scanned for the validation, the parent can be modified.  In regular READ COMMITTED, you are guaranteed that at the instant you do the validation the condition is met. And, if you put validations on the parent and the child, any parent mod will put shared locks on the child, and vice versa.  After the validation is complete, the shared locks are removed, so while the concern is reduced, it does still exist.

 In either case, use REPEATABLE READ or SERIALIZABLE when you need the enhanced data protection.  And if you set READ_COMMITTED_SNAPSHOT to ON, these two advanced settings when used for modification procedures should have a far reduced effect on concurrency than they would in READ COMMITTED.

Using this form of SNAPSHOT isolation level eliminates the following type of data modification anomoly that could very easily occur if you do data modifications in SNAPSHOT.  This is far more worrisome than the quirky way (in my opinion) it handles optimistic locking (see this post) you may be opening up a data integrity hole and a half, especially if you use it in a long running transaction (and you might be tempted if the transaction was causing/being affected by  blocking).  For example:

ALTER DATABASE <databasename>
    SET ALLOW_SNAPSHOT_ISOLATION ON

Create a schema and a table:

create schema test
go
create table test.testIsolationLevel
(  
    testIsolationLevelId    int
        CONSTRAINT PKtest_testIsolationLevel PRIMARY KEY,
    value                   varchar(20)
)
go

--Then a simple trigger that looks for duplicate values in the table. This is a simple
--way to show what the trigger can see that is in the table.  Obviously a
--unique index would be more appropriate :)
create trigger test.testIsolationLevel$afterInsertUpdate
on test.testIsolationLevel AFTER INSERT
AS
  begin
       --check for duplicate rows
       IF EXISTS(select count(*)
                 from   testIsolationLevel
                 where  exists (select *
                                        from   inserted
                                        where  testIsolationLevel.value = inserted.value)
                 group by value
                 having count(*) > 1)
          begin
                raiserror ('duplicate', 16,1)
                rollback transaction
          end
   end
go

Add a couple of rows:

insert into test.testIsolationLevel(testIsolationLevelId, value)
values (1,'one')
insert into test.testIsolationLevel(testIsolationLevelId, value)
values (2,'two')
select * from test.testIsolationLevel

This returns

testIsolationLevelId value
-------------------- --------------------
1                    one
2                    two

Then try to insert a duplicate value:

insert into test.testIsolationLevel(testIsolationLevelId, value)
values (3,'two')

Msg 50000, Level 16, State 1, Procedure testIsolationLevel$afterInsertUpdate, Line 14
duplicate

Now, here is the concern.  In one connection, start a transaction and get some rows:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

select * from test.testIsolationLevel

You will see the data in the table.  Then open another connection.  Run the following:

insert into test.testIsolationLevel(testIsolationLevelId, value)
values (10,'ten')

Now, you can execute this on connection with the open transaction (inserting a 10 will cause a PK error):
insert into test.testIsolationLevel(testIsolationLevelId, value)
values (11,'ten')

commit transaction

Now look at the data:

select * from test.testIsolationLevel

testIsolationLevelId value
-------------------- --------------------
1                    one
2                    two
10                   ten
11                   ten

It violates the trigger conditions.  When working in READ COMMITTED (either version), data integrity issues can still happen because of phantoms and non-repeatable reads (people deleting rows we used to validate some data is especially troublesome,) but the period of time where there was concern was much lower due to the fact that everytime you read data you got the current data, even if it changed.   

My initial feelings on how to use SNAPSHOT ISOLATION level is to add it to all of my read stored procedures that don't do any modifications (including transactions), and leave all of the procs that modify data at READ COMMITTED or higher.  This has the benefit that the results you get back will always be in sync with themselves (in optimistic locking we expect that noone made a change, though they could have done. The requirement is that each fetch fetches data that was correct. Heck, the goal quite often is to cache rows off of the data layer, and once you cache it it can be invalid.  So what problem is it if the actual cached data is being invalidated while you fetch it? 

Setting your default isolation level to snapshot based READ COMMITTED is something that is going to require testing (as mentioned, tempdb is used to store the versioning information, so you will probably have to beef up your architecture if you are not using optimal hardware (and who is :)) but I intend to start adding it to new systems I create.