ADO.NET zombie transaction bug? How to ensure that commands will not be executed on implicit transaction?

Question

e.g. When deadlock occurs, following SQL commands are successfully executed, even if they have assigned SQL transaction that is after rollback. It seems, it is caused by a new implicit transaction that is created on SQL Server.

Someone could expect that ADO.NET would throw an exception that the commands are being executed on a zombie transaction. However, such exception is not thrown. (I think this is a bug in ASP.NET.) Moreover, because of zombie transaction the final Dispose() silently ignores the rollback.

Any ideas, how can I ensure that nobody can execute commands on implicit transaction? Or, how to check that transaction is zombie? I found that Commit() and Rollback() check for zombie transaction, however I can call them for a test:)

I also found that also reading IsolationLevel will do the check, but I am not sure whether simple calling transaction.IsolationLevel.ToString(); will not be removed by a future optimizer. Or do you know any other safe way invoke a getter (without using reflection or IL emitting)?

EDIT: Remus Rusanu pointed out that this situation would not normally occur. Yes, that's true. It usually occurs when there is a bug in a code. In our case, there was some logging routine in a finally statement that was trying to store the failure into the database. Now, I am trying to find a solution how to detect such bugs in a future. Since these bugs are difficult to test. If ADO.NET would check that the provided transaction is zombie, this bug will be found much more easily. I found two possibilities:

1. Turn off creation of implicit transactions - I am not sure whether this is possible.
2. Ensure that before executing any commands, check for zombie transaction will run.

Answer 1

What you describe doesn't exists. A transaction rollback will throw a very visible exception in your application. for some reason, I much rather believe your code captures the exception and silently ignores it, continuing to execute statements after the transaction had rolled back.

Answer 2

Probably not directly related to your problem, as it was caused by a bug, but still might be of interest. Not all errors cause a transaction to rollback, so sometimes a transaction might be "partially successful" - some statements errored while others compeleted fine.
There is an option SET XACT_ABORT ON that makes the server abort transaction on any error.

Considering your question, you cannot turn off implicit transactions (if you execute an SQL statement, an implicit transaction will be created unless another transaction is already active). So you just have to handle the errors correcty to make sure that a transaction is there when you need it.
Have a look at TransactionScope class, you can use it to avoid managing these transactions in your code.

Answer 3

//Based upon your description I'm guessing that your code effectively does this

        SqlConnection conn = new SqlConnection("ConnectionString");
        SqlCommand cmd = new SqlCommand("insert into ....");

        cmd.Connection = conn;

        conn.Open();

        SqlTransaction tran = conn.BeginTransaction();


        cmd.Transaction = tran;

        tran.Rollback(); //or tran.Dispose();

        cmd.ExecuteNonQuery();

This causes the cmd to be executed outside the scope of a transaction.

Removing the line cmd.Connection = conn; will achieve the behavior that I think you're looking for (e.g. the command will fail because the transaction is no longer valid.)

SqlConnection conn = new SqlConnection("ConnectionString");
    SqlCommand cmd = new SqlCommand("insert into ....");

    //cmd.Connection = conn;

    conn.Open();

    SqlTransaction tran = conn.BeginTransaction();
    cmd.Connection = tran.Connection;

    cmd.Transaction = tran;

    tran.Rollback(); //or tran.Dispose();

    cmd.ExecuteNonQuery();