Try..Catch.. and @@Trancount>0 location

Question

Try..Catch.. and @@Trancount>0 location

Barkingdog

SSCoach

Points: 18670
More actions
April 18, 2010 at 4:07 pm

#231863

The following extract is from http://msdn.microsoft.com/en-us/library/ms175976.aspx
>>>
USE AdventureWorks;
GO
BEGIN TRANSACTION;
BEGIN TRY
...
END TRY
BEGIN CATCH
...
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
>>>
Shouldn't the
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
test occur after BEGIN TRANSACTION but just before the "BEGIN CATCH?
TIA,
Barkingdog
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Barkingdog (4/18/2010)
The following extract is from http://msdn.microsoft.com/en-us/library/ms175976.aspx
...
Shouldn't the
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
test occur after BEGIN TRANSACTION but just before the "BEGIN CATCH?

No, the idea here is that you start a transaction, do some work and if an error occurs (the catch section) , you roll back the transaction.

Then, after the try...catch you check to see if the transaction has been rolled back or not - if it has not, you commit.

(the only reason there would not be an active transaction at this point would be if the "catch" had been triggered and the rollback had happened.)

Does that help?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Barkingdog SSCoach Points: 18670 More actions · Answer 2

That was helpful.

Would this interpretation of the original code be correct?

>>>

USE AdventureWorks;

GO

BEGIN TRANSACTION;

BEGIN TRY

...

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

...

END CATCH

TIA,

barkingdog

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Yes, that should be functionally the same.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 4

Barkingdog (4/18/2010)
That was helpful.
Would this interpretation of the original code be correct?

No, the ROLLBACK TRANSACTION would fail if the error had already caused the transaction to abort.

Use XACT_ABORT() rather than @@TRANCOUNT to check the status of any open transaction.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Paul White NZ (4/18/2010)
Barkingdog (4/18/2010)
That was helpful.
Would this interpretation of the original code be correct?
No, the ROLLBACK TRANSACTION would fail if the error had already caused the transaction to abort.
Use XACT_ABORT() rather than @@TRANCOUNT to check the status of any open transaction.

It's a good point - I was assuming the tests for an active transaction had been removed for brevity, not on purpose...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 6

mister.magoo (4/19/2010)
It's a good point - I was assuming the tests for an active transaction had been removed for brevity, not on purpose...

And you were probably right! Just making sure (and mentioning the XACT_STATE function).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi