SQL Server 2008 deal, return required?

Question

I have a stored procedure that has a BEGIN TRANSACTION and COMMIT TRANSACTION statement. Inside the transaction, the WITH(XLOCK, ROWLOCK) query is selected WITH(XLOCK, ROWLOCK) .

A transaction may potentially fail due to some calculations that cause an arithmetic overflow error if values ​​are supplied outside the bounds. This error would occur before any insert / update operations.

My question is: should I transfer the transaction to TRY / CATCH and rollbacks or is it really not required and all locks will be released automatically if the transaction fails? My only concern is that SQL will not release all transaction locks in the event of a transaction failure.

Thanks,

Tom

Answer 1

Short answer: Yes.

Whenever I use BEGIN TRANSACTION, I always turn on usage error handling and ROLLBACK. The consequences of getting unexpected (and / or unexpected - you cannot know how your code might need to be changed in the future), the situation that leaves an open transaction on the Production server is too serious not to do this.

In SQL Server 2000 and earlier, you must use @@ error logic. In SQL 2005 and above, you can use the syntax (far superior) to TRY ... CATCH ...

Answer 2

Much simpler:

 set xact_abort on 

This will roll back the transaction automatically when an error occurs.

Code example:

 set xact_abort on begin transaction select 1/0 go print @@trancount -- Prints 0 set xact_abort off begin transaction select 1/0 go print @@trancount -- Prints 1 

If you execute the second segment several times, you will see an increase in the number of transactions to 2,3,4, etc. One run of the first segment discards all transactions.

Answer 3

I like Brad's approach, but it needs to be cleaned up a bit so you can see the error causing the problem.

 begin try begin transaction; ... commit transaction; end try begin catch declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int; select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); rollback transaction; raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState); end catch 

Answer 4

TRY/CATCH not required to release locks. However, I think the following template would be good for most transactions.

 BEGIN TRY BEGIN TRAN ... IF (@@error <> 0) ROLLBACK TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH --BEGIN FINALLY (doesnt exist, which is why I commented it out) IF (@@trancount > 0) COMMIT TRAN --END FINALLY 

Answer 5

 begin transaction; -- you don't want to hit catch block if begin transaction will fail begin try ... updates/inserts/selects ... commit transaction; -- the last statement in try code block is always commit end try begin catch rollback transaction; -- first step before other error handling code is rollback transaction declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int; select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState); end catch