Setting Transaction Isolation Level in .NET / Entity Framework for SQL Server

Question

I am attempting to set the Transaction Isolation Level in .NET/C# for a Transaction. I am using the following code to set up the transaction:

 using (var db = new DbContext("ConnectionString"))
 {
     using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, 
             new TransactionOptions() { IsolationLevel = IsolationLevel.Snapshot }))
     {
         ...code here

         transaction.Complete();
     }
 }

Using SQL Server Profiler, this produces the following:

set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Why is it setting the isolation level as 'read committed' when I specifically requested 'snapshot'?

Snapshot isolation has been turned on for the database in question.

This 'read committed' is being blocked by another long running transaction.

Running the following inside the SQL Server Management Studio works just fine during the long running transaction, but the code above is blocked because the isolation level is being changed from what I specified.

USE <database>;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION;
GO

<SELECT STATEMENT>

GO
COMMIT TRANSACTION;
GO

WHY?

usr · Accepted Answer · 2015-07-07 19:33:04Z

1

All that new TransactionScope does is to set Transaction.Current. That is 100% all that it does. Now anyone who want to support transactions can look at that property and enlist. The usual DbConnection classes enlist when they are opened. Apparently, your code does not open a connection while under this particular scope.

System.Transactions has no built-in support for changing the isolation level (which is a sad omission). You need to do that yourself.

answered Jul 7, 2015 at 19:33

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

log4code Over a year ago

I'm not sure I follow. It seems like you are saying that even though new TransactionScope() accepts parameters for TransactionOptions, that those options are not used? That doesn't make any sense. The outer scope (var db) is just a DbContext. Does this not open a connection? Is there a way that I can rewrite this to get the behavior I am expecting? How do I go about changing the isolation level of a transaction?

usr Over a year ago

"That doesn't make any sense" The parameters are going to be used when Transaction.Current is going to be used. But nobody is looking at that value. Your "outer scope" has nothing to do with transactions. Normally, EF opens a new connection for each DB operation. If you want more details post more complete code.

log4code Over a year ago

This is basically my complete code. That is my question. What am I doing wrong? I want my select statement (say this below) to use a snapshot transaction for the select. How do I do that?

using (var db = new DbContext("ConnectionString"))  {      using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew,               new TransactionOptions() { IsolationLevel = IsolationLevel.Snapshot }))      {          db.Person.ToList();           transaction.Complete();      }  }

usr Over a year ago

OK, instantiate the context inside the scope (an unlikely fix). Besides that this should work. Maybe we can't fully trust profiler here. The TDS protocol has a way to set the isolation level without SQL. Maybe it doesn't show up. Execute "select isolevel from sys.sessions where id = @@SPID" inside the scope to be sure. Or, set profiler to show "TM manager" events.

log4code Over a year ago

Setting up the profiler to show TM Manager events did not show anything, but I think I found my issue. I was inadvertently using TransactionScopeOption.Suppress instead of TransactionScopeOption.RequireNew. Also, I moved the transaction block to scope the new DbContext block.

|

Collectives™ on Stack Overflow

Setting Transaction Isolation Level in .NET / Entity Framework for SQL Server

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related