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

Question

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

Answer 1

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

Answer 2

0

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

IF @@TRANCOUNT > 0            
    ROLLBACK TRANSACTION

answered Mar 04 '10 at 10:31 AM

graz ♦
525 ● 2 ● 4 ● 5

I thought about that, but won't this break if I call this proc from another proc that has the call wrapped in another transaction?

Mar 04 '10 at 02:10 PM JohnFx

(comments are locked)

Answer 3

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

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

Follow this question

Related Questions