My organization enabled READ COMMITTED SNAPSHOT ISOLATION (RCSI) on one of our major systems before we entered a peaking operating cycle. We had tested the impact to our system and felt that it was pretty safe. About half way through our operating cycle we were sick of fixing duplicate insert issues by escalating to REPEATABLE READ and then fighting deadlock issues in other creative ways.
RCSI was going to be disabled but we enjoyed snapshot isolation for 90% of our stored procedures, it was the minority that were a problem based on the way they were used in our applications. This lead us to a decision to enable SNAPSHOT and then handle the isolation on a per-stored-procedure basis. This essentially gave us back READ COMMITTED (RC) as an option because, with RCSI, your RC behavior is overridden with SNAPSHOT.
I wrote a short PowerShell script to checkout all of my stored procedures from Team Foundation Server and open them in NotePad++. I would then use notepad++’s Replace All in All Opened Documents and their Extended replace syntax to add SET TRANSACTION SNAPSHOT ISOLATION to over 1,000 files.
The script: check-out-to-notepad++
OK, OK, enough with the back story.
Once I checked in my changes to my test environment I quickly realized that I had overlooked an important limitation of SNAPSHOT isolation. We, at times, wrap our database calls in .NET transactions and those isolation levels are not always SNAPSHOT. With this sweeping change I was rudely reminded that SNAPSHOT can only be used if the transaction began as SNAPSHOT.
A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.
This is what the error looks like…
Msg 3951, Level 16, State 1, Procedure spGetOrder, Line 6
Transaction failed in database ‘foo’ 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.
Fortunate for us, the work around for this is simple and is easily implemented across the board. We can thank Alexander Kuznetsov for pointing out that a simple check for and existing transaction with @@TRANCOUNT solves our problem.