TransactionScope error in ambient transaction does not rollback the transaction

Question

I use an ambient transaction like this :

using(TransactionScope tran = new TransactionScope()) {
    CallAMethod1();//INSERT
    CallAMethod2();//INSERT
    tran.Complete();
}

The method CallAMethod2(); returns affected rows =-264 So it fails to insert however the first Insert has been committed !

I want to know how to work with ambient transaction and what if the second method has more than one action which needs internal transaction , should i put these actions in internal transaction ? like this :

     DAL_Helper.Begin_Transaction();

              //------Fill newKeysDictioanry

                affectedRow = DBUtilities.InsertEntityWithTrans("table2", newKeysDictioanry, DAL_Helper);

                if (affectedRow == 1)
                {
                    if (!string.IsNullOrEmpty(sp_confirm))
                    {
                        result_dt = UserTransactionDAL.Run_PostConfirm_SP(sp_PostConfirm, OBJ.ValuesKey, DAL_Helper);
                        if (result_dt.Rows.Count > 0 && result_dt.Rows[0][0].ToString() == "0")
                        {
                            DAL_Helper.current_trans.Commit();

                            if (DAL_Helper.connectionState == ConnectionState.Open)
                            {
                                DAL_Helper.Close_Connection();
                            }
                            return 1;// affectedRow;
                        }
                        else
                        {
                            DAL_Helper.current_trans.Rollback();
                            if (DAL_Helper.connectionState == ConnectionState.Open)
                            {
                                DAL_Helper.Close_Connection();
                            }
                            return -2; 
                        }
                    }
//etc

Do you know whether the database was created with transactions? Informix (Dynamic Server) supports unlogged databases with no transactions, and logged, buffered logged, and MODE ANSI with transactions. Error -264 is cannot write to temporary file; I don't know whether that indicates a problem on the machine running the database server. — Jonathan Leffler, Jan 28 '15 at 16:50
@JonathanLeffler:Don't know, why the transaction didn't rollback when the second insert operation doesn't succeed for any reason — Anyname Donotcare, Jan 29 '15 at 10:27
using (var ambientTxn = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)){...} — Amin Golmahalleh, Aug 14 '21 at 18:45

score 6 · Accepted Answer · edited May 23 '17 at 11:44

1) You need to check whether the tran.Complete(); is called. If the tran.Complete(); is called, the TransactionScope is considered completed successfully.

From MSDN

When your application completes all work it wants to perform in a transaction, you should call the Complete method only once to inform that transaction manager that it is acceptable to commit the transaction. Failing to call this method aborts the transaction.

The call to tran.Complete(); is to inform the Transaction Manager to complete the transaction. Actually, the Transaction Manager does not track your Db adapter and does not know if an operation in a connection was successful or failed. Your application has to let it know by calling Complete

How does TransactionScope roll back transactions?

To fail your transaction, just ensure that you don't call tran.Complete(); in your code:

If no exception occurs within the transaction scope (that is, between the initialization of the TransactionScope object and the calling of its Dispose method), then the transaction in which the scope participates is allowed to proceed. If an exception does occur within the transaction scope, the transaction in which it participates will be rolled back.

In your case, maybe you can throw an exception in your CallAMethod2(); if you think the operation has failed so the tran.Complete(); is not called and the transaction is rolled back.

2) The second thing you can check is whether your connection is enlisted in the transaction. TransactionScope does not rollback if the connection is not enlisted. The possible problems are:

If the connection exists before entering the transaction scope, it won't enlist: Does TransactionScope work with pre-existing connections?
Your underlying connection does not support auto enlistment.

In these cases, you can try enlisting your connection manually (extracted from the link above):

connection.EnlistTransaction(Transaction.Current)

Regarding your second question:

what if the second method has more than one action which need internal transaction , should i put these actions in internal transaction ?

I would say it really depends on whether you consider your CallAMethod2(); as an automic operation which means you can call it elsewhere directly without wrapping it inside a transaction. Most of the cases, it would make sense to create internal transactions as transactions can be nested. In your case, it's recommended to also use TransactionScope in your CallAMethod2();, we have some options when creating a new transaction scope:

The TransactionScope class provides several overloaded constructors that accept an enumeration of the type TransactionScopeOption, which defines the transactional behavior of the scope. A TransactionScope object has three options:

Join the ambient transaction, or create a new one if one does not exist.

Be a new root scope, that is, start a new transaction and have that transaction be the new ambient transaction inside its own scope.

Not take part in a transaction at all. There is no ambient transaction as a result.

Which one to choose really depends on your application. In your case, I guess you could go with the first option. Below is an example from MSDN

void RootMethod()
{
     using(TransactionScope scope = new TransactionScope())
     {
          /* Perform transactional work here */
          SomeMethod();
          scope.Complete();
     }
}

void SomeMethod()
{
     using(TransactionScope scope = new TransactionScope())
     {
          /* Perform transactional work here */
          scope.Complete();
     }
}

Do you mean that: I have not to handle the exceptions inside the transactions so that it rollback if there was an exception ? — Anyname Donotcare, Feb 01 '15 at 10:32
@just_name: I did not say that you don't handle exception inside your `CallAMethod2();`. Exception handling varies depending on the case. I just said that In your case, if the exception bubbles up, it will skip the `tran.Complete();`, but I'm not sure whether it makes sense in your case because I don't know your app logic. The point is you need to ensure `tran.Complete();` is not called if you want to rollback your transaction. — Khanh TO, Feb 01 '15 at 12:13
Then How to make sure that `tran.Complete();` isn't called if something wrong happened in the second method ? — Anyname Donotcare, Feb 01 '15 at 12:17
@just_name: there are many ways to do. For example: you can check the return value, you can throw an exception, .... That really depends on your application. — Khanh TO, Feb 01 '15 at 12:19

score 2 · Answer 2 · answered Feb 05 '15 at 11:26

You can use scope inner and outer for transaction:

string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
var option = new TransactionOptions
{
     IsolationLevel = IsolationLevel.ReadCommitted,
     Timeout = TimeSpan.FromSeconds(60)
};
using (var scopeOuter = new TransactionScope(TransactionScopeOption.Required, option))
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText="INSERT INTO Data(Code, FirstName)VALUES('A-100','Mr.A')";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    using (var scopeInner = new TransactionScope(TransactionScopeOption.Required, option))
    {
        using (var conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText="INSERT INTO Data(Code, FirstName) VALUES('B-100','Mr.B')";
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        scopeInner.Complete();
    }
    scopeOuter.Complete();
}

score 1 · Answer 3 · 2015-02-07T12:07:56.637

Read what Khanh TO says. If your connection is opened outside the outer transaction scope the connection won't be enlisted.

That is why the first call didn't rollback when the second failed. You will have to enlist your connection:

using (TransactionScope tran = new TransactionScope(TransactionScopeOption.Required))
{
   connection.EnlistTransaction(Transaction.Current);
   CallAMethod1();//INSERT
   CallAMethod2();//INSERT
   tran.Complete();
}

TransactionScope error in ambient transaction does not rollback the transaction

3 Answers3

Linked