Dapper + Oracle + TransactionScope = Transaction has aborted

c# dapper odp.net transactionscope

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();
        }
   }
}

Accepted Answer

I am resorting to using BeginTransaction() as the final approach (refer to my update in my original post). I have read more about why TransactionScope() was failing.

1) ODP.Net promotes to distributed transaction even when using a single DB connection when connecting to Oracle 10g and below (source). Lo and behold, the database I'm connecting to is indeed 10g.

2) You'll need Oracle MTS Service installed. This I didn't have setup on my dev machine.


Popular Answer

I don't see anything wrong with that code. However, if that loop runs long enough, the transaction is going to timeout. You'll then get the exception in question the next time you do an operation against the database. I would try increasing the timeout - Timeout is a property on the TransactionScopeOption class.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why