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

JohnFx gravatar image

JohnFx
84 2 2 4

(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

Nathan Skerl gravatar image

Nathan Skerl
432 1 1 3

(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

graz gravatar image

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, 2010 at 02:10 PM JohnFx
(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

gernblandston gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x60
x21

asked: Mar 03, 2010 at 11:03 PM

Seen: 5657 times

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