Efcore: EF Core does not check transaction state before executing each command in a batch of changes

[deadalusai]

Occasionally SqlClient fails to raise a SqlException when an ExecuteScalar command is rolled back as a deadlock victim. The transaction is correctly marked as Aborted, however subsequent commands issued using that aborted/completed transaction end up being silently executed outside of a transaction.

EF Core is sensitive to this issue as it also does not (or is not able to?) check the state of the transaction in between commands - it's expecting an exception if anything goes wrong. So:

1. EF Core has a bunch of changes queued up: TableA, TableB
2. SaveChanges called
3. Execute command "insert into TableB values ...; select Sequence from TableB;"
4. Deadlock
   
   
   
   • Chosen as victim and rolled back
   
   
   • Transaction marked as "Aborted"
   
   
   • No deadlock exception raised...
   
   
5. Execute command "insert into TableA values ...;"
6. Mark all pending changes as committed
7. SaveChanges ends

In the normal case step number 4 will fail with an SqlException indicating that the command was chosen as a deadlock victim, allowing the user code the opportunity to abandon the transaction and retry the SaveChanges operation.

Instead, when we finally call transaction.Complete() to commit the changes an exception is raised as the transaction is already in a completed state (Aborted). Unfortunately it is impossible to roll back the changes which did manage to be committed.

I have also raised an issue for the underlying cause on SqlClient:
https://github.com/dotnet/SqlClient/issues/458

Steps to reproduce

1. Clone the repository https://github.com/deadalusai/SqlClient-Aborted-Transaction-Repro
2. Create a new database using the ZombieDb.sql script

3. Run the test program:

   For .NET Core 3.1 with Microsoft.Data.SqlClient:

   > dotnet run -p .\ZombieTester.NET31.csproj
   

   For .NET Core 2.1 with System.Data.SqlClient:

   > dotnet run -p .\ZombieTester.NET21.csproj
   

This test program just uses raw SqlClient calls, however it is straightforward to reproduce the error using an Entity Framework context. If desired I can produce an EF-based reduced test case.

Further technical details

EF Core version: 2.2
Microsoft.Data.SqlClient version: 1.1.1
System.Data.SqlClient version: 4.8.1
.NET target: .NET Core 3.1, .NET Core 2.1
SQL Server version: SQL Server 2017, SQL Server 2016
Operating system: Windows 10

[ajcvickers]

@cheenamalhotra Let us know what you find/decide here at the SqlClient level. Feel free to pull us in as needed.

[cheenamalhotra]

Hi @ajcvickers

I'm investigating the issue opened in our repo (dotnet/SqlClient#458) will share some updates soon.

[deadalusai]

I've done some investigation on mitigating this issue as part of EF Core. I had some success modifying the RelationalCommand class to check the status of the transaction before each command is executed.

E.g.

if (connection.CurrentTransaction != null)
{
    // TryGetInternalTransactionState does reflection to extract the internal state field
    var internalState = TryGetInternalTransactionState(connection.CurrentTransaction.GetDbTransaction());
    if (internalState != "Active")
    {
        throw new InvalidOperationException($"Internal transaction status was {internalState ?? "Unknown"}");
    }
}

This works, most of the time. I'm not sure how the mechanism which updates the internal transaction state operates, but occasionally the status field has not been updated at the time this code executes...

Another option we've considered is injecting a snippet of SQL into each command which is part of a transaction, which has the advantage of moving the check to the server:

if (@@TRANCOUNT = 0) throw 50000, N'Command not executing in transaction', 1

Is there an option built-in to EF Core for modifying command SQL before it is sent to SQL Server to be executed?

[ajcvickers]

@deadalusai Yes, you can use database interceptors--see https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/#interception-of-database-operations

[ajcvickers]

This is being tracked by https://github.com/dotnet/SqlClient/issues/458, so closing here for now. We can re-open if there turns out to be something EF-specific going in.