Is there a way to check whether a named transaction is open

Question

question

JohnFx asked Mar 3, '10

Is there a way to check whether a named transaction is open

Is there a simple way to detect if a transaction for a given name is open? For example in this code, I need to know whether to rollback the transaction in the error handler.

BEGIN TRY                    
  ... do stuff                    
  BEGIN TRANSACTION MyTransaction                    
   ...do stuff                    
  COMMIT TRANSACTION MyTransaction                    
  ...do more stuff                    
                    
END TRY                    
BEGIN CATCH                    
   IF (**Is MyTransaction still open?**) ROLLBACK MyTransaction                    
END CATCH

transaction error-handling

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-04T21:32:12Z

Nathan Skerl answered Mar 4, '10

A ROLLBACK will always roll back the outermost transaction. SQL Server doesnt yet support nested transactions (in the sense we are talking about here). If you are already in the context of a transaction, and you BEGIN TRANSACTION, you dont truly open a new transaction scope, you really just increment that @@trancount counter. Any subsequent COMMIT will decrement it, but only the final COMMIT actually commits it. ref link

Im not positive what you are trying to do here, but you may want to look into SAVE TRAN which will let you rollback to a predefined save point. BOL link

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-04T10:31:37Z

graz answered Mar 4, '10

You can query the @@TRANCOUNT variable to determine how nested transactions are currently open. I usually do something like:

IF @@TRANCOUNT > 0            
    ROLLBACK TRANSACTION

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-03-10T11:26:57Z

gernblandston answered Mar 10, '10

You should be able to see the open transaction in sys.dm_tran_active_transactions. You may need to narrow it down more using sys.dm_exec_sessions to get the specific session.

IF OBJECT_ID('t') IS NOT NULL DROP TABLE t            
CREATE TABLE t (id INT)            
            
BEGIN TRY            
BEGIN TRAN MyTransaction            
    INSERT INTO t(id)VALUES(1)            
    RAISERROR ('Error Before Commit', 16, 1 );            
    COMMIT TRAN MyTransaction            
    RAISERROR ('Error After the Commit.', 16, 1 );            
END TRY            
            
BEGIN CATCH            
IF EXISTS (SELECT [name] FROM sys.dm_tran_active_transactions WHERE name = 'MyTransaction')            
    ROLLBACK TRAN MyTransaction            
END CATCH            
            
SELECT * FROM t

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question