LINQ + TransactionScope will not change isolation level in SQL Server Profiler

Question

I'm using the following format for commiting changes to my db using linq.

Begin Transaction (Scope Serialized, Required)
    Check Business Rule 1...N
    MyDataContext.SubmitChanges()
    Save Changes Done In Previous Query To Log File
End Transaction Scope

But in the SQL Server profiler I see the following line in the Connection:Start.

set transaction isolation level read committed

I went through this (http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/93a45026-0425-4d49-a4ac-1b882e90e6d5) and thought I had an answer;

Until I saw this (https://connect.microsoft.com/VisualStudio/feedback/details/565441/transactionscope-linq-to-sql?wa=wsignin1.0) on Microsoft Connect.

Can someone please tell me whether my code is actually executed under Serialized Isolation Level or whether it is infact just running under read committed?

Answer 1

It depends on how you created the transaction.

If you executed inline SQL to begin it (EG. BEGIN TRAN), L2S will not be aware of the transaction and will spin up a new nested one in READ COMMITTED.

However, if you used System.Transaction, or have a transaction set on your DataContext, SubmitChanges will participate in that transaction.

You can see these transaction starting and stopping in Profiler if you choose the TM: Begin Tran and TM: Commit Tran event classes.

Note: ADO.Net does not issue BEGIN TRAN nor does it issue SET TRANSACTION ISOLATION in batches, this is done at a lower level.

If you really want to confirm the behavior, create a trigger on a table that inserts the current isolation level into a logging table and check on it.

You can pick up your current isolation level by running:

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'Read Uncommitted' 
WHEN 2 THEN 'Read Committed' 
WHEN 3 THEN 'Repeatable Read' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID