Why might a SqlTransaction fail to commit when the commit function is called?

Question

I'm running into an issue where changes made are being rolledback even when none of the queries throw an exception. It's strange since the code works in one environment but isn't committing changes in another.

Here is the function that handles the transaction. When I put a break point on the commit I hit the commit and I can see the changes in the database but when the transaction is disposed the changes are rolled back.

UPDATE:Additional tests show that it isn't a problem with the transaction. If the transaction is completely removed from the code below the app behaves in the same way. The changes are undone when the connection closes.

public bool Transaction(List<string> sqlStatements)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlTransaction tran = conn.BeginTransaction())
        {
            try
            {
                foreach (string query in sqlStatements)
                {
                    SqlCommand cmd = new SqlCommand(query, conn, tran);
                    cmd.CommandTimeout = 300;
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();

                return true;
            }
            catch (SqlException sqlError)
            {
                tran.Rollback();
                //Log Exception
                return false;
            }
        }
    }
}

Answer 1

Though I was sure, I tried your code at my end and it worked as expected. Again I am repeating that the method is good enough for the transaction handling. And once the transaction is committed, it can't be roll back.

In the above method, transaction disposal has got nothing to do with any rollback. I think, you have been debugging in wrong direction. Though, you may paste the original method here as you might be doing some other database operations.

Just out of blue, what kind of queries you have been firing? Do note that the DDL commands are auto-commit and transaction won't be effective.

Answer 2

When you say you "can see the changes in the database", how are you determining this? I would expect them to be "in the database" if the following query returns the data that was "committed" (run this tsql after stepping over the commit call in Sql Server Management Studio for example):

-- Force the isolation level to "read committed" so we 
-- guarantee we are getting data that has definitely been committed.
-- If the data changes back, it must have been from a separate operation.
set transaction isolation level read committed
begin tran

select * from MyTableWithExpectedChanges;

-- You aren't changing anything so this can be rollback or commit
rollback tran

If the data did indeed commit, I would run a SQL Server Profiler session and see what is causing the data to revert back. It sounds like something separate is triggering to restore the data in that scenario.

If the data didn't commit, you have some sort of transaction count mismatch as per other comments.

Answer 3

This issue was eventually tracked back to a trigger that had recently been updated to include a transaction.

We solved the issue by removing the transaction from the trigger.