January 8, 2011 at 6:07 am
What is the difference between these two approaches:
BEGIN TRANSACTION;
BEGIN TRY
-- Some code
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
and
BEGIN TRANSACTION;
BEGIN TRY
-- Some code
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;
Are these two approaches same? If not, where is the difference and which one should we use?
January 9, 2011 at 6:28 am
They are pretty equivalent, but you can't use the second one very well if you have code after your try catch statement that logically belongs in the same transaction.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 9, 2011 at 8:36 am
I'm not seeing anything radically different either.
The only exception is, I'd move the BEGIN TRAN inside the BEGIN TRY statement so that if there are errors on the initiation of the transaction, they're captured as well.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 9, 2011 at 11:47 am
BEGIN TRY
-- Some code
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
I will use first statement, because I hope that is suitable for all the requirement.
If any error occurs in the Try block, control go to Catch statement.
In catch first, it schecking,
IF @@TRANCOUNT > 0,
If any row updated in the transaction, RollBack performing and reverting back all the transaction.
If no error in the Try block, control will directly comes to statements after the catch statement. Here also if any any transaction is happend, committing the transactions bt commit transaction.
Thanks.
Reji PR,
Bangalore
😀
January 9, 2011 at 12:48 pm
Grant Fritchey: what kind of errors could occur in the initiation of the transaction?
Reji: and where is the difference?
January 9, 2011 at 2:49 pm
Who knows. Could be anything. Maybe it's a nested transaction and starting another one causes an error. I'd just place it inside the TRY statement, just in case. One example, you start a transaction on a linked server but DTC is not running or not configured correctly. You'll get an error.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 10, 2011 at 12:29 pm
It might be trivial, but I don't see why you would have the COMMIT after the CATCH. Putting the BEGIN/COMMIT TRAN inside the TRY block seems for reasonable:BEGIN TRY
BEGIN TRANSACTION;
-- Some code
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
January 11, 2011 at 3:11 pm
Lamprey13 stole me the word of mouth (or code from the keyboard) 😉
That code is the best IMHO. Think of this situation: If the code inside the try-block calls a procedure that do a rollback and raise some error, what will happen? This catch block will still work ok. Begin tran increases @@trancount by 1. Commit tran decreases @@trancount by 1. @@trancount measures how deep your transaction is nested in other transactions. Rollback sets @@trancount to 0 (gets you out of all nested transactions) no matter how deep you are in nested transactions. And if you try a rollback or commit when @@trancount is 0 (you are not in a transaction), you get an error. So, Lamprey13's code is the best and will work in any situation, IMHO.
January 11, 2011 at 3:21 pm
Sounds logical, thanks 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply