3

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?

log4code
  • 133
  • 1
  • 7

1 Answers1

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 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? – log4code Jul 07 '15 at 20:10
  • "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. – usr Jul 07 '15 at 20:12
  • 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(); } } ` – log4code Jul 07 '15 at 20:18
  • 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. – usr Jul 07 '15 at 20:29
  • 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. – log4code Jul 07 '15 at 21:08
  • I take that back.... Still able to reproduce deadlock even with TrasactionScopeOption.RequireNew (which I know I had tested before, thus the reason it was in my original code). – log4code Jul 07 '15 at 21:23
  • Perform the session isolation level query from within the scope to be sure. – usr Jul 07 '15 at 21:36
  • Within the scope it is 'Snapshot', which means I've got something else going on, but at least I can check this off the list now. Thanks for the info, I didn't know that stuff about the TDS protocol before this. – log4code Jul 08 '15 at 12:17