CODEWITHSUNDEEP
c#.nett-sqltransactionsisolation-level
log4code
log4code

Reputation: 133

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

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?

Upvotes: 3

Views: 5704

Answers (1)

usr
usr

Reputation: 171178

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.

Upvotes: 1

Related Questions