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!

score 1 · Answer 1 · answered Jan 16 '13 at 22:23

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.

answered Jan 16 '13 at 22:23

gh9

10,169
10
63
96

Understood. I suppose my question is truly one for LLBLGen. The tool requires an IsolationLevel be provided when constructing a Transaction instance. Need to understand what value will cause the library to use the default on the database. – Steve Wagner Jan 16 '13 at 23:09

score 1 · Answer 2 · edited May 23 '17 at 12:01

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: Test output

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.

ADO.Net IsolationLevel.Snapshot with SET READ_COMMITTED_SNAPSHOT ON

2 Answers2