This project is read-only.

Design Meeting Notes - July 5, 2013

Connection failures on transaction commit

The ADO.NET SQL provider (SQLClient) currently has an issue with connection failures that happen after transactions have been committed—see http://blogs.msdn.com/b/adonet/archive/2013/03/11/sql-database-connectivity-and-the-idempotency-issue.aspx#10413539. The general problem is that the failure can happen either before or after the transaction is committed on the database. There is no way for the client code to know from the exception whether or not the transaction was committed. So always retrying will sometimes cause the same data to be committed twice, and never retrying will sometimes cause the data to not be committed at all.

Workaround

This is an issue below the level at which EF can handle it automatically. The discussion here is around what can be done in EF to allow an application implement a workaround. The general idea is:

  • After starting a transaction write the transaction ID to a separate table
  • After connection failure during Commit() check whether the corresponding ID is present in the table and retry if necessary
  • On successful commit delete the transaction ID (to prevent table from filling up)

Support for this pattern should be integrated with the general connection resiliency work—i.e. it should be used with an execution strategy.

EF Changes

To implement this workaround the following EF changes are required:

  • The ability to intercept transaction creation (to write the ID) and commit
  • The wrapping of exception from commit such that they will not be automatically retried

Possible API surface

interface IDbConnectionInterceptor : IDbInterceptor
{
    void BeginTransactionExecuting(
        DbConnection connection,
        DbTransactionInterceptionContext interceptionContext);

    bool BeginTransactionExecuted(
        DbConnection connection,
        DbTransactionInterceptionContext interceptionContext);
}

interface IDbTransactionInterceptor : IDbInterceptor
    bool CommitExecuting(
         DbTransaction transaction,
         DbInterceptionContext interceptionContext);

    bool CommitExecuted(
        DbTransaction transaction,
        DbInterceptionContext interceptionContext);
}

class DbTransactionInterceptionContext : DbInterceptionContextWithResult<DbTransaction>
{
    public IsolationLevel IsolationLevel { get; }
    public DbTransactionInterceptionContext WithIsolationLevel(
        IsolationLevel isolationLevel);
}

Consider:

  • Following the pattern of the existing interceptors by providing the result using the interception context.
  • When defining these interfaces we should look for other things on the target objects that might need to be intercepted. This helps guard against interface breaking changes in the future.
    • An alternative is to make this a very specific interceptor just for handling transaction retries

Possible related changes:

  • Change all xxxExecuted methods to return bool indicating whether the original exception should be re-thrown
  • Re-visit the interception context hierarchy and consider:
    • Merge DbCommandInterceptionContext with DbInterceptionContext
    • Separate DbCommandInterceptionContext{TResult} to DbInterceptionContextWithResult{TResult}

Scheduling

We don’t believe that this work can be done in time to be in the box with EF6. Tentative plan is to instead target a post EF6 point release.

Last edited Jul 8, 2013 at 5:22 PM by ajcvickers, version 2