How to detect that rollback has occurred?

Question

I'm hunting down a bug in a large business application, where business processes are failing but partially persisted to the database. To make things harder to figure out, the process fails only once every few weeks, with hundreds of thousands successfully processed between every failure. The error frequency seems to go up when concurrency/number of worker processes goes up.

So far, we've been able to recreate what we're seeing with this program. (.NET 4.7.1 framework)

using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    using (var sqlConnection = new SqlConnection("Server=localhost;Database=Database1;Trusted_Connection=True"))
    {
        sqlConnection.Open();

        // Doing some unwanted nested manual transaction and rolling it back
        var transaction = sqlConnection.BeginTransaction();
        new SqlCommand($"INSERT INTO TestTable VALUES('This will be rolled back}')", sqlConnection).ExecuteNonQuery();

        transaction.Rollback();

        // Now doing some work with the DB.
        // You might expect it to become a part of transactionScope, but that is NOT the case!
        // So this command will actually succeed, with data written to the DB.
        new SqlCommand($"INSERT INTO TestTable VALUES('This will be inserted')", sqlConnection).ExecuteNonQuery();

        // Doing something which promotes the local transaction to a distributed transaction.
        using (var sqlConnection2 = new SqlConnection("Server=localhost;Database=Database2;Trusted_Connection=True"))
        {
            // The program will fail here, with message "The transaction has aborted, Failure while attempting to promote transaction."
            sqlConnection2.Open();

            new SqlCommand($"INSERT INTO TestTable2 VALUES('We will never come this far')", sqlConnection2).ExecuteNonQuery();

        }
    }
    transactionScope.Complete();
}

Our production code does not explicitly make calls to transaction.Rollback(), it is simply in my example as the means to reproduce the error message and behavior. But if any of our third party libraries makes this call, I would like to throw exception and exit as soon as possible. Preferably in the application layer.

How can I detect that the call to Rollback() has been made? I really do not want to make crud operations without being sure that the transactionScope is able to do it's job.

Update 1

My unwanted "rollback" was caused by a bug somewhere in the connection sharing mechanism of .Net. The bug is reproduced on all .Net Framework version between 4.5.1 and 4.8, and also on the new System.Data.SqlClient package.

An issue has been added to the System.Data.SqlClient repository.

Answer 1

The different transaction APIs don't all work together. So you're in dangerous territory here.

How can I detect that the call to Rollback() has been made?

select @@trancount should always tell you. The rollback will revert @@trancount to 0.