Handling of Transaction Commit Failures

As part of 6.1 we are introducing a new connection resiliency feature for EF: the ability to detect and recover automatically when transient connection failures affect the acknowledgement of transaction commits. The full details of the scenario are best described in Issue #1114 and in the blog post SQL Database Connectivity and the Idempotency Issue.  In summary, the scenario is that when an exception is raised during a transaction commit there are two possible causes:

  1. The transaction commit failed on the server
  2. The transaction commit succeeded on the server but a connectivity issue prevented the success notification from reaching the client

When the first situation happens the application or the user can retry the operation, but when the second situation occurs retries should be avoided and the application could recover automatically. The challenge is that without the ability to detect what was the actual reason an exception was reported during commit, the application cannot choose the right course of action. The new feature allows EF to double-check with the database if the transaction succeeded and take the right course of action transparently.

Using the feature

In order to enable the feature you need include a call to SetTransactionHandler in the constructor of your DbConfiguration. The new feature can be used in combination with the automatic retries we introduced in EF6, which help in the situation in which the transaction actually failed to commit on the server due to a transient failure:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;
public class MyConfiguration : DbConfiguration
{
    public MyConfiguration()
    {
        SetTransactionHandler(SqlProviderServices.ProviderInvariantName, () => new CommitFailureHandler());
        SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () => new SqlAzureExecutionStrategy());
    }
}

How transactions are tracked

When the feature is enabled, EF will automatically add a new table to the database called “__Transactions”. A new row is inserted in this table every time a transaction is created by EF and that row is checked for existence if a transaction failure occurs during commit. If the row exists the exception will be ignored and the application will continue normally. If it does not then the exception is rethrown, triggering  the retry logic for the failed operation.

Although EF will do a best effort to prune rows from the table when they aren’t needed anymore, the table can grow if the application exits prematurely and for that reason you may need to purge the table manually in some cases.

There is a helper method that can be used to clear the table. Note that you should only use it when no transactions are active on the server (e.g. on app start).

var commitFailureHandler = CommitFailureHandler.FromContext(context);
if (commitFailureHandler != null)
{
    commitFailureHandler.ClearTransactionHistory();
}

Transaction table customization

The transaction information is stored using a TransactionContext which can be derived from and configured like any other DbContext:

using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;

public class MyConfiguration : DbConfiguration
{
    public MyConfiguration()
    {
        SetTransactionHandler(SqlProviderServices.ProviderInvariantName, () => new CommitFailureHandler(c => new MyTransactionContext(c)));
    }

}

public class MyTransactionContext : TransactionContext
{
    public MyTransactionContext(DbConnection existingConnection)
        : base(existingConnection)
    {
    }
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       modelBuilder.Entity<TransactionRow>()
           .ToTable("MyTransactions");
   }
}

There is some overhead resulting from reading and writing the transaction history using TransactionContext. If your scenario demands it a different implementation can be used by creating a class derived from TransactionHandler.

When there is no TransactionHandler set failures on transaction commit will be wrapped in the following exception:

CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information.

If your application can handle these being thrown on a relatively infrequent basis (e.g. the user can just verify retry the last operation themselves if needed) you can avoid the overhead of CommitFailureHandler by adding an implementation of TransactionHandler that just throws a helpful exception.

Last edited Mar 14 at 9:03 PM by AndriySvyryd, version 13

Comments

nayato Mar 20 at 7:24 AM 
@martincostello: I suppose neither is appropriate and DB should act as a source of timestamp instead.

martincostello Feb 13 at 9:39 PM 
Just having a look at the code for this feature, and I notice that DateTime.Now is used by CommitFailureHandler for the database row timestamp. Would DateTime.UtcNow be more appropriate?

divega Jan 16 at 8:20 AM 
Hey Unai,

Good catch! That one came up in API review and we are fixing it (i.e. making it consistent with other APIs in DbConfiguration). In fact this change should be by now in the nightly builds.

Diego

UnaiZorrilla Dec 20, 2013 at 7:03 PM 
Just curious!

Why the provider, "System.Data.SqlClient" is not the first parameter in SetTransactionHandler, like in SetExecutionStrategy?

:-)

Unai