TransactionScope not rolling back transaction

Question

Here is the current architecture of my transaction scope source code. The third insert throws an .NET exception (Not a SQL Exception) and it is not rolling back the two previous insert statements. What I am doing wrong?

EDIT: I removed the try/catch from insert2 and insert3. I also removed the exception handling utility from the insert1 try/catch and put "throw ex". It still does not rollback the transaction.

EDIT 2: I added the try/catch back on the Insert3 method and just put a "throw" in the catch statement. It still does not rollback the transaction.

UPDATE:Based on the feedback I received, the "SqlHelper" class is using the SqlConnection object to establish a connection to the database, then creates a SqlCommand object, set the CommandType property to "StoredProcedure" and calls the ExecuteNonQuery method of the SqlCommand.

I also did not add Transaction Binding=Explicit Unbind to the current connection string. I will add that during my next test.

public void InsertStuff()
{
    try
    {
        using(TransactionScope ts = new TransactionScope())
        {
            //perform insert 1
            using(SqlHelper sh = new SqlHelper())
            {
                SqlParameter[] sp = { /* create parameters for first insert */ };

                sh.Insert("MyInsert1", sp);
            }

            //perform insert 2
            this.Insert2();

            //perform insert 3 - breaks here!!!!!
            this.Insert3();

            ts.Complete();            
        }
    }
    catch(Exception ex)
    {
        throw ex;
    }
}

public void Insert2()
{
    //perform insert 2
    using(SqlHelper sh = new SqlHelper())
    {
        SqlParameter[] sp = { /* create parameters for second insert */ };

        sh.Insert("MyInsert2", sp);
    }
}

public void Insert3()
{
    //perform insert 3
    using(SqlHelper sh = new SqlHelper())
    {
        SqlParameter[] sp = { /*create parameters for third insert */ };

        sh.Insert("MyInsert3", sp);
    }
}

Answer 1

I have also run into a similar issue. My problem occurred because the SqlConnection I used in my SqlCommands was already open before the TransactionScope was created, so it never got enlisted in the TransactionScope as a transaction.

Is it possible that the SqlHelper class is reusing an instance of SqlConnection that is open before you enter your TransactionScope block?

Answer 2

It looks like you are catching the exception in Insert3() so your code continues after the call. If you want it to rollback you'll need to let the exception bubble up to the try/catch block in the main routine so that the ts.Complete() statement never gets called.

Answer 3

An implicit rollback will only occur if the using is exited without calling ts.complete. Because you are handling the exception in Insert3() the exception never causes an the using statement to exit.

Either rethrow the exception or notify the caller that a rollback is needed (make change the signature of Insert3() to bool Insert3()?)

Answer 4

(based on the edited version that doesn't swallow exceptions)

How long do the operations take? If any of them are very long running, it is possible that the Transaction Binding bug feature has bitten you - i.e. the connection has become detached. Try adding Transaction Binding=Explicit Unbind to the connection string.

Answer 5

I dont see your helper class, but transaction scope rollsback if you don't call complete statement even if you get error from .NET code. I copied one example for you. You may be doing something wrong in debugging. This example has error in .net code and similar catch block as yours.

  private static readonly string _connectionString = ConnectionString.GetDbConnection();

    private const string inserttStr = @"INSERT INTO dbo.testTable (col1) VALUES(@test);";

        /// <summary>
        /// Execute command on DBMS.
        /// </summary>
        /// <param name="command">Command to execute.</param>
        private void ExecuteNonQuery(IDbCommand command)
        {
            if (command == null)
                throw new ArgumentNullException("Parameter 'command' can't be null!");

            using (IDbConnection connection = new SqlConnection(_connectionString))
            {
                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void FirstMethod()
        {
            IDbCommand command = new SqlCommand(inserttStr);
            command.Parameters.Add(new SqlParameter("@test", "Hello1"));


                ExecuteNonQuery(command);

        }

        public void SecondMethod()
        {
            IDbCommand command = new SqlCommand(inserttStr);
            command.Parameters.Add(new SqlParameter("@test", "Hello2"));


                ExecuteNonQuery(command);

        }

        public void ThirdMethodCauseNetException()
        {
            IDbCommand command = new SqlCommand(inserttStr);
            command.Parameters.Add(new SqlParameter("@test", "Hello3"));


                ExecuteNonQuery(command);
            int a = 0;
            int b = 1/a;

        }

    public void MainWrap()
    {


        TransactionOptions tso = new TransactionOptions();
        tso.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
        //TransactionScopeOption.Required, tso
        try
        {
            using (TransactionScope sc = new TransactionScope())
            {
                FirstMethod();
                SecondMethod();
                ThirdMethodCauseNetException();
                sc.Complete();
            }
        }
        catch (Exception ex)
        {
            logger.ErrorException("eee ",ex);

        }
    }

If you want to debug your transactions, you can use this script to see locks and waiting status etc.

SELECT 
request_session_id AS spid,
CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncomitted' 
WHEN 2 THEN 'Readcomitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL ,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) as DBNAME,
resource_description AS res,
resource_associated_entity_id AS resid,
CASE 
when resource_type = 'OBJECT' then OBJECT_NAME( resource_associated_entity_id) 
ELSE 'N/A'
END as ObjectName,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks l
left join sys.dm_exec_sessions s on l.request_session_id = s.session_id
where resource_database_id = 24
order by spid, restype, dbname;

You will see one SPID for two method calls before calling exception method.

Default isolation level is serializable.You can read more about locks and transactions here

Answer 6

I ran into a similar issue when I had a call to a WCF service operation in TransactionScope. I noticed transaction flow was not allowed due to the 'TransactionFlow' attribute in the service interface. Therefore, the WCF service operation was not using the transaction used by the outer transaction scope. Changing it to allow transaction flow as shown below fixed my problem.

[TransactionFlow(TransactionFlowOption.NotAllowed)]

to

[TransactionFlow(TransactionFlowOption.Allowed)]