ReadUnCommitted isolationlevel not working with EF Core

Question

For isolation level "ReadUncommitted", we should create a transaction:

using var transaction = await db.Database.BeginTransactionAsync(isolationLevel: System.Data.IsolationLevel.ReadUncommitted);

try
{
    // Run the query
    await transaction.CommitAsync();
}
catch (Exception)
{ 
    await transaction.RollbackAsync();
}

But xEvent Profiler show this profile login:

This login profile is using "Read Committed"!

According to this question, we have to create two transactions:

using var tran = await db.Database.BeginTransactionAsync(isolationLevel: System.Data.IsolationLevel.ReadUncommitted);

try
{
    // without any query
    await tran .CommitAsync();
}
catch (Exception)
{ 
    await tran .RollbackAsync();
}

using var transaction = await db.Database.BeginTransactionAsync(isolationLevel: System.Data.IsolationLevel.ReadUncommitted);

try
{
    // Run query
    await transaction.CommitAsync();
}
catch (Exception)
{
    await transaction.RollbackAsync();
}

It does not seem like a good idea to open and close the transaction twice.

How to set the isolation level to "Read Uncommitted" in EF Core?

I think it may set it as part of the `sp_reset_connection` TDS packet. You can verify by using `SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID` result should be `1`. @Hacker That is entirely irrelevant — Charlieface, Apr 02 '21 at 05:02
It's entirely expected and normal for a login to use `READ COMMITTED` as the default isolation level, as that is the default for all connections. When you actually begin a transaction under another isolation level, it should issue a `SET TRANSACTION ISOLATION LEVEL` command. Your profiler trace appears to be missing all signs of a transaction actually being issued; perhaps you've excluded those events? — Jeroen Mostert, Apr 02 '21 at 10:48
Also don't use READ UNCOMMITTED. You can get wrong results and failures. If you can't switch your database to READ COMMITTED SNAPSHOT mode, use SNAPSHOT isolation when you want to run queries without read locks. — David Browne - Microsoft, Apr 02 '21 at 12:32

ReadUnCommitted isolationlevel not working with EF Core

0 Answers0