ADO.Net IsolationLevel.Snapshot with SET READ_COMMITTED_SNAPSHOT ON

Question

I am curious as to the effects of using IsolationLevel.Snapshot with SET READ_COMMITTED_SNAPSHOT ON on a database. The documentation for the IsolationLevel enumeration notes the behavior from Snapshot isolation, which is not what we are looking for in our situation.

Having enabled READ_COMMITTED_SNAPSHOT, should we specify IsolationLevel.Unspecified, or not provide this value at all? Or, if we DO specify IsolationLevel.Snapshot, will we realize the behavior expected with READ_COMMITTED_SNAPSHOT enabled?

Thanks!

Answer 1

If you enabled read_committed_snapshot at the DB level then all queries will have that default isolation level unless modified.

If you change the isolation level of the Query itself then the query will use the isolation level you modified it with.

Answer 2

I've done the following test with SQL 2008 R2 and Entity Framework 4. (The database has the READ_COMMITTED_SNAPSHOT option ON)

I've Created the following Stored Procedure to return the contextual Isolation Level (original from here):

Create Procedure TempTestIsolation
AS 
Begin
  DECLARE   @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
  INSERT    @UserOptions
  EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

  SELECT    Value
  FROM      @UserOptions
  WHERE     SetOption = 'isolation level'
End

And then I coded the following test:

static void Main(string[] args)
{
    var entities = new MyEntities();
    // Execute the SP to get the isolation level
    string level = entities.TempTestIsolation().First().Value;
    Console.WriteLine("Without a transaction: " + level);

    var to = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.Snapshot };
    using (var ts = new TransactionScope(TransactionScopeOption.Required, to))
    {
        // Execute the SP to get the isolation level
        level = entities.TempTestIsolation().First().Value;
        Console.WriteLine("With IsolationLevel.Snapshot: " + level);
    }

    to = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
    using (var ts = new TransactionScope(TransactionScopeOption.Required, to))
    {
        // Execute the SP to get the isolation level
        level = entities.TempTestIsolation().First().Value;
        Console.WriteLine("With IsolationLevel.ReadCommitted: " + level);
    }
    Console.ReadKey();
}

Whose output was:

As you can see, when you set the IsolationLevel to Snapshot in your TransactionOptions, the stored procedure executes under the "Snapshot" isolation level, and NOT under "Read Committed Snapshot".

Instead, if you set the IsolationLevel to ReadCommitted it is executed under "Read Committed Snapshot".

Hope it helps.