x

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

more ▼

asked Mar 03, 2010 at 11:03 PM in Default

avatar image

JohnFx
84 3 2 6

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Mar 04, 2010 at 09:32 PM

avatar image

Nathan Skerl
432 2 3 4

(comments are locked)
10|1200 characters needed characters left

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

IF @@TRANCOUNT > 0            
    ROLLBACK TRANSACTION            
more ▼

answered Mar 04, 2010 at 10:31 AM

avatar image

graz ♦
525 2 6 10

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 10, 2010 at 11:26 AM

avatar image

gernblandston
22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x72
x25

asked: Mar 03, 2010 at 11:03 PM

Seen: 8982 times

Last Updated: Mar 03, 2010 at 11:03 PM

Copyright 2016 Redgate Software. Privacy Policy