TransactionScope connection dies but EF SaveChanges still commits

[Abrissirba]

Ask a question

We have a problem where we are a bit stuck and need all the help we can get to pinpoint why it is happening. There is quite a lot of components in play so it is a bit difficult to set up and explain but I will try my best.

We are using TransactionScope to make sure that all changes made to the database will be made in one transaction. There will be multiple connections open, to the same db, at the same time which will elevate the transaction to DTC. Since we use Azure SQL this will be an Elastic Transaction (from what I understand)

This works as expected in most scenarios. If an error occur, the transaction scope is disposed and not changes will be commited to the Database.

The problem occur if the connection that created the transaction dies for some reason. For us this happend because of a timeout in azure that killed the connection after 30 minutes. We are aware that you should not keep transactions going on for so long and have made improvements that will make this scenario less likely. However, connections might die for other reasons making us continue to investigate this issue.

The thing that happens when the first connection dies, subsequantly queries made by EF will save the changes to the database even though the transaction died.

If we do the same but with ADO.NET we get an exception about the transaction no longer work.

Here is an example.

• First we create a TransactionScope
• Then open a new connection and update. This will create a transaction in the db.
• We then open a new connection, by initiating a DbContext (named ModelContainer). This will elevate the transaction to DTC. Looking at the db, with the provided select query, it looks like the new connection is enlisted into the transaction created by the first connection
• If we kill the first connection, by running the provided sql query and Kill, right before SaveChanges, the update will be commited to the DB. This is not what we expected.
• If we try the same but with ADO.NET we get an exception when we run ExecuteNonQueryAsync saying that the transaction no longer is active.
• If we first run the EF example and then ADO.NET, the ADO.NET example work aswell.

When looking at the transactions with the sql query it is like the transaction is killed by EF somehow. It just disappears after SaveChanges.

So what we are currently trying to figure out is why ADO.NET throws an exception, and not commiting, while EF doesn't throw any Exception and commits.

using System.Data.SqlClient;
using System.Transactions;
// This should be executed in ssms or something similiar to get information about the connections/transactions
// The select query will return the transactions
// the transaction with is_local = 1 should then be killed to simulate a connection error.
//
//SELECT
//    st.session_id,
//    st.transaction_id,
//  st.is_local,
//  st.is_enlisted,
//  s.client_interface_name
//FROM sys.dm_tran_session_transactions st
//JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
//WHERE st.transaction_id IN 
//(select Transaction_id from sys.dm_tran_active_transactions where name = 'user_transaction')

//kill <id of the session that has is_local set to 1>

var options = new TransactionOptions
{
    IsolationLevel = System.Transactions.IsolationLevel.Serializable,
    Timeout = TimeSpan.Zero // TimeSpan.Zero is default of `TransactionOptions.Timeout`
};
var scope = new TransactionScope(TransactionScopeOption.RequiresNew, options, TransactionScopeAsyncFlowOption.Enabled);

var connectionString = "";
var efConnectionString = $"metadata=res://<metadata>;provider=System.Data.SqlClient;provider connection string=\u0022{connectionString}\u0022";

// First connection, this will have the transaction as local
{
    var conn1 = new SqlConnection(connectionString);
    conn1.Open();
    var cmd = conn1.CreateCommand();
    cmd.CommandText = $"Update Prize set Name = 'Test2' where PrizeId = '8892372A-8E0D-4FDA-AE6B-00496C212545'";
    _ = await cmd.ExecuteNonQueryAsync();
}

// run only one of the examples below at a time
// comment out the other one
{

    // ADO.NET EXAMPLE
    // This will not commit to database, throwing an exception
    {
        var conn2 = new SqlConnection(connectionString);
        conn2.Open();
        // KILL THE SESSION THAT HOLDS THE is_local TRANSACTION HERE
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = $"Update Prize set Name = 'Test2' where PrizeId = '8892372A-8E0D-4FDA-AE6B-00496C212545'";
        _ = await cmd2.ExecuteNonQueryAsync();
    }

    // EF EXAMPLE
    // SaveChanges will commit to database even though the dtc died
    //using (var modelContainer = new ModelContainer(efConnectionString))
    //{
    //    var prizeId1 = Guid.Parse("8892372A-8E0D-4FDA-AE6B-00496C212545");
    //    var prize1 = modelContainer.Prizes.FirstOrDefault(x => x.PrizeId == prizeId1);

    //    prize1!.Name = "New Name 87";

    //    // KILL THE SESSION THAT HOLDS THE is_local TRANSACTION HERE
    //    _ = modelContainer.SaveChanges();

    //    // changes is commited and the transaction is gone.
    //}

}

scope.Complete();
scope.Dispose();

[Abrissirba]

Came one step forward here, I guess this might not be a EF6 issue but I am thankfull if you are able to point me in any direction going forward.

It seems like closing and opening an existing connection makes the transaction "unenlist" from the transaction.

The ADO.NET example above that threw an exception no longer throw if the connection is closed, the connection holding the transaction killed, and then opened again.

Digging in EF6 it looks like this is what EF does, that will say opening and closing the connection.

{
    var conn2 = new SqlConnection(connectionString);
    conn2.Open();
    conn2.Close();
    // KILL THE SESSION THAT HOLDS THE is_local TRANSACTION HERE
    conn2.Open();
    var cmd2 = conn2.CreateCommand();
    cmd2.CommandText = $"Update Prize set Name = 'Test' where PrizeId = '8892372A-8E0D-4FDA-AE6B-00496C212545'";
    _ = await cmd2.ExecuteNonQueryAsync();
}

[Abrissirba]

Created an issue in SqlClient instead, so this issue can probably be closed.

https://github.com/dotnet/SqlClient/issues/2970