Entity Framework: how to find transaction isolation level in SQL Profiler?

Question

BeginTransaction used to manage transactions in Entity Framework 6. It allows you to set the isolation level for a transaction, as you can see in the code below (sample only):

 using (var context = new DataContext()) { using (var transaction = context.Database.BeginTransaction(IsolationLevel.Serializable)) { context.Entities.Add(new Entity()); context.SaveChanges(); transaction.Commit(); } } 

The problem is this: when I use SQL Server Profiler, I cannot find any isolation level information for a real SQL transaction.

Attempt # 1:

I tried to track ALL kinds of events and search for the keyword “isolation” in the trace results. Only two events I found:

 EventClass TextData ------------------------------------------------------------- ExistingConnection set transaction isolation level read committed AuditLogin set transaction isolation level read committed 

READ COMMITTED always in these events. So this is not my code, because IsolationLevel.Serializable installed above.

Attempt # 2:

In order for the transaction to be started and not yet completed, you can take the SPID and manually select the actual isolation level from the dm_exec_sessions :

 SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @Tran_SPID 

This is very undesirable.

Is it possible to record the isolation level for any transaction / session created by EF in the profiler directly? Maybe I'm just using the wrong tool?

PS Entity Framework 6.1.3 and MS SQL Server 2012 on board.

Answer 1

You cannot find the transaction isolation level in the profiler because it is never written. See How to track changes in transaction isolation level in SQL Profiler or any other tool for an explanation.