Is there a way to test a potential zombie transaction to see if it can be rolled back?

Question

If a sql call fails, say to timeout due to deadlock, the transaction can turn into a zombie transaction-- I guess either my code or framework code does the rollback. The SqlTransaction isn't null, but it is a zombie can throws an error if you try to do a Rollback(). I can't find the .IsZombie property.

// Make sure the transaction is not null
if (transaction != null)
{
    //TODO: Is there a way to test a transaction to see if it can be rolled back?
    transaction.Rollback();  
}

Answer 1

Hi,

You many want to consider placing your transaction handling code inside of the database where you can then test for @@ERROR = 1205 to determine if your query was the victim of a deadlock in which case you can retry or ROLLBACK. Allowing client applications to create and manage transactions is a bit risky and better to avoid if possible.

Hope this helps,

Bill

Answer 2

You could try using the TransactionScope class from .NET 2.0's System.Transactions namespace. This class allows you to specify a timeout after which the transaction will automatically be cancelled and rolled back. ADO.NET in .NET 2.0+ is TransactionScope aware, and will automatically enroll a DbTransaction in the scope if one is present at the time the database is called:

public void DoSomething()
{
    using (TransactionScope scope = new TransactionScope(TransactionScopeOptions.Required, TimeSpan.FromSeconds(60)))
    {
        MyDac();

        scope.Complete(); // If timeout occurrs, this line is never hit, scope is disposed, which causes rollback if Complete() was not called
    }
}

public class MyDac()
{

    using (SqlConnection ...)
    {
        using (SqlCommand ...)
        {
            // Do something with ADO.NET here...it will autoenroll if a transaction scope is present
        }
    }
}

TransactionScope creates a System.Transactions.Transaction internally, which by default allows light-weight transactions to SQL Server if only a single server is involved. If there are multiple servers or distributed resource managers involved in the transaction, the Transaction wrapped by TransactionScope will be promoted to a distributed transaction, which will require MSDTC to coordinate, which can complicate the use of TransactionScope. If all of your transactions are lightweight, then TransactionScope can offer a lot of benefits over managing your db transactions manually.

Answer 3

I beg your pardon but I cannot avoid to disagree. Client transactions is what makes it possible to have a business-process atomic operation. If you want to move all the transactioned operations into DB you are invariably moving business logic into it. It is an approach, but highly un-recommended provided you will use some mildly complex logic on your program. Having whiles/for/foreachs, string checks and other trivial operations are really heavy to move into DB (and sometimes, even impossible). The deadlock hint, however, seems to be quite useful and provides further control to the client application (which is the best way to go, in my opinion).

Cheers