Dapper + Oracle + TransactionScope = Transaction has aborted

Question

I've looked around but couldn't find an appropriate (or one that is satisfactory to me) on how to address an issue we are having.

I use Dapper and ODP.NET 12 Managed drivers. The problem is not encountered when TransactionScope is not used.

When performing commands under a transaction scope, I get an error "Transaction has aborted" via the TransactionAbortedException thrown.

Observed behaviors:

1) TransactionAbortedException is thrown if and only if the transaction is completed and the TransactionScope is disposed. The point at when the exception is thrown is during dispose.

2) Despite the exception, the transaction concept actually works! After Complete() is invoked, the changes are committed into the database.

Below is the code snippet.

// Conn string: "Data Source=OraDB;Persist Security Info=True;User ID=userxxx;Password=passwordxxx;"   providerName="Oracle.ManagedDataAccess.Client
// Note: GetDbFactory().Create() returns a DbConnection object
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
using (var dbConn = GetDbFactory().Create())
{
    foreach (MyDTO dto in dtoList)
    {
        var tableDAO= new TableDAO(dbConn);
        MyEntity entity = new MyEntity()
        {
            Field1 = dto.Field1,
            Field2 = dto.Field2
        };
        tableDAO.AddOrUpdate(entity);
     }
     // Commit changes
     scope.Complete();
}

// This method is under the DAO class
public void AddOrUpdate(MyEntity entity)
{
    // Verify arguments
    entity.AsArgumentThrowExceptionIfNull("entity");
    // build param

    OracleDynamicParameters parameters = new OracleDynamicParameters();

    parameters.Add("P_FIELD1", entity.Field1);
    parameters.Add("P_FIELD2", entity.Field2);

    // execute SP
    dbConnection.Execute("PRC_MY_ENTITY_ADDORUPDATE", parameters, commandType: CommandType.StoredProcedure);
}//-- end AddOrUpdate()

==================================================================
UPDATE (09-Apr-15)

I have changed my approach and use the following pattern for now for Oracle. Our code deals with connections in both Oracle and SQL Server so I'd much prefer that the coding pattern is consistent, but until a solution is found with using Oracle+TransactionScope, we'll use the pattern below for Oracle command execution:

using (var dbConnection = dbConnFactory.Create())
{
    // Open db connection
    dbConnection.Open();
    using (var trans = dbConnection.BeginTransaction())
    {
        bool isSuccess = false;
        try
        {
             // Perform DB operations here
             trans.Commit();
             isSuccess = true;
        }
        finally
        {
             if(!isSuccess) trans.Rollback();
        }
   }
}

Jeff Siver Jeff Siver · Accepted Answer · 2015-04-08T04:18:18

First, the exception is documented as possible:

A call to the Dispose method marks the end of the transaction scope. Exceptions that occur after calling this method may not affect the transaction.

This is in the documentation on the TransactionScope class (https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28v=vs.110%29.aspx).

One other thing I noticed is that the complete on the transaction is being before the connection is closed. I'd change this to close the connection and then complete the transaction.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
{
  using (var dbConn = GetDbFactory().Create())
  {
    foreach (MyDTO dto in dtoList)
    {
        var tableDAO= new TableDAO(dbConn);
        MyEntity entity = new MyEntity()
        {
            Field1 = dto.Field1,
            Field2 = dto.Field2
        };
        tableDAO.AddOrUpdate(entity);
     }
  }
     // Commit changes
  scope.Complete();
}

Dapper + Oracle + TransactionScope = Transaction has aborted

3 Answers