Data committed even though System.Transactions.TransactionScope.Commit() not called

Question

Under what circumstances can code wrapped in a System.Transactions.TransactionScope still commit, even though an exception was thrown and the outermost scope never had commit called?

There is a top-level method wrapped in using (var tx = new TransactionScope()), and that calls methods that also use TransactionScope in the same way.

I'm using typed datasets with associated tableadapters. Could it be that the commands in the adapter aren't enlisting for some reason? Do any of you know how one might check whether they are enlisting in the ambient TransactionScope or not?

Answer 1

The answer turned out to be because I was creating the TransactionScope object after the SqlConnection object.

I moved from this:

using (new ConnectionScope())
using (var transaction = new TransactionScope())
{
    // Do something that modifies data

    transaction.Complete();
}

to this:

using (var transaction = new TransactionScope())
using (new ConnectionScope())
{
    // Do something that modifies data

    transaction.Complete();
}

and now it works!

So the moral of the story is to create your TransactionScope first.

Answer 2

The obvious scenario would be where a new (RequiresNew) / null (Suppress) transaction is explicitly specified - but there is also a timeout/unbinding glitch that can cause connections to miss the transaction. See this earlier post (the fix is just a connection-string change), or full details.

Answer 3

Be aware how TransactionScope works:
It sets property System.Transactions.Transaction.Current at the begining of using scope and then set it back to previous value at end of using scope.

Previous value depends on where given scope is declared. It can be inside another scope.

You can modify code like this:

using (var sqlConnection = new ConnectionScope())
using (var transaction = new TransactionScope())
{
    sqlConnection.EnlistTransaction(System.Transactions.Transaction.Current);
    // Do something that modifies data
    transaction.Complete();
}

I show this possibility for those who have their code more complicated and cannot simply change code to open DB connection first.

Answer 4

This example (C#, .NetFramework 4.7.1) shows how we can persist to the database even if the code is wrapped in a TransactionScope. The first insert will be rolled back, and the second insert will be inserted without transaction.

See my related post, where I ask for help in how to detect this.

using (var transactionScope = new TransactionScope())
{
    using (var connection = new SqlConnection("Server=localhost;Database=TestDB;Trusted_Connection=True"))
    {
        connection.Open();

        new SqlCommand($"INSERT INTO TestTable VALUES('This will be rolled back later')", connection).ExecuteNonQuery();

        var someNestedTransaction = connection.BeginTransaction();
        someNestedTransaction.Rollback();

        new SqlCommand($"INSERT INTO TestTable VALUES('This is not in a transaction, and will be committed')", connection).ExecuteNonQuery();
    }

    throw new Exception("Exiting.");

    transactionScope.Complete();
}

Answer 5

using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        try
        {
            using (IDbConnection sqlConnection = GetDbconnection("SQL_CONNECTION_STRING"))
            {
                using (IDbConnection db2Connection = GetDbconnection("DB2_CONNECTION_STRING"))
                {
                    sqlConnection.Open();
                    db2Connection.Open();

                    string db2Query1 = "";  // INSERT QUERY
                    result = await db2Connection.QueryFirstOrDefaultAsync<bool>(db2Query1, new DynamicParameters(), commandType: CommandType.Text);
                    
                    string db2Query2 = "";  // INSERT QUERY
                    result = await db2Connection.QueryFirstOrDefaultAsync<bool>(db2Query2, new DynamicParameters(), commandType: CommandType.Text);

                    string procName = "SP_NAME";
                    DynamicParameters dynamicParams = new DynamicParameters();
                    dynamicParams.Add("Parameter1", "VALUE1", DbType.String);
                    GridReader sqlResult = await sqlConnection.QueryMultipleAsync(procName, dynamicParams, commandType: CommandType.StoredProcedure, commandTimeout: 30);
                    resultSet = await sqlResult.ReadAsync<T>();
                    int sqlExecResult = await sqlConnection.ExecuteAsync(procName, dynamicParams, commandTimeout: 30, commandType: CommandType.StoredProcedure);

                    scope.Complete();  // COMPLETE THE TRANSACTION
                    IsSuccess = true;
                }
            }
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
            throw;
        }
    }

It is not working only for DB2 and I could see that once inserted it gets committed immediately.

But SQL Server rollback works fine. Until the call is complete() SQL server waits to commit but DB2 commits immediately.

Please let me know what is the issue in my code