We develop software used by banks and financial planners throughout
Australia.

On one particular site (out of thousands) we are getting an error:

This SqlTransaction has completed; it is no longer usable.

Server stack trace:
   at System.Data.SqlClient.SqlTransaction.ZombieCheck()
   at System.Data.SqlClient.SqlTransaction.Rollback()
   at NAB.WM.SHAPE.HI.DataAccess.Dax.InsertAllDocumentData(Int32
documentTypeID, Boolean documentIsEncrypted, Int32 adaptorID, String
userName, String data, NameValueCollection keys, NameValueCollection
metaData)

The code of InsertAllDocumentData is very simple - it rollbacks on
error (Catch block) and commits otherwise (within Try). None of the
code that InsertAllDocumentData does or calls closes or disposes the
connection or the transaction.

In investigation I decompiled .NET 2.0 code and found that
SQLTransaction's ZombieCheck method calls
_internalTransaction.IsCompleted rather than
_internalTransaction.IsZombied (which seems a little odd):

Friend ReadOnly Property IsZombied As Boolean
    Get
        If (Not Me._internalTransaction Is Nothing) Then
            Return Me._internalTransaction.IsCompleted
        End If
        Return True
    End Get
End Property

Is that a bug in the framework? It seems odd to have a method called
IsZombied on the SqlInternalTransaction class but not to use it...

mjwil***@optusnet.com.au wrote:

Show quoteHide quote     creating a SqlTransaction is simply doing this:
1) at creation time, a BEGIN TRANS <name> is sent to the db server,
which means a DB-side transaction is started over the connection the
transaction is created on
2) at commit, a COMMIT TRANS is sent to the db server
or
2a) at rollback, a ROLLBACK TRANS is sent to the db server.

    This means that the system in control over the transaction is the db
server, not the client code. It also means that there's no connection
between the DB transaction and the Sqltransaction object. Now, with
certain errors inside sqlserver, e.g. an FK violation on a DELETE
statement, sqlserver will raise an error, and every error has a
severity level. If I recall correctly, if the severity level is above
16, any db transaction controlling the statement causing the error is
rolled back.

    So what does this mean? well, the db transaction started with the
creation of the SqlTransaction is already rolled back, so when you call
RollBack on the SqlTransaction object, it first checks if the db
connection is still there. Apparently not, so it declares itself a
zombie (i.e. not related to its counterpart in the DB, as that's
already rolled back by the server due to the error).

    So, if you're wrapping transactions with a try/catch to roll back
transactions on a db error, it's perhaps wise to either check the error
level or to wrap the rollback in a try/catch as well.

        FB

Generics?
Elementary question on connection string
Creating a new Access database file (.mdb) in .NET/C#
How much of the functionality of SQL Server is mirrored in ADO.Net
Inconsistent dat paremeter in query