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

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.