CODEWITHSUNDEEP
sql-servert-sqltransactionsrollback
TJF
TJF

Reputation: 2258

SQL Server 2008 Transaction, rollback required?

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

The transaction can potentially fail due to some calculations that cause an arithmetic overflow error if out of bounds values are supplied. This error would happen before any insert/update statements.

My question is, should I wrap the transaction in a TRY/CATCH and rollback or is this not really required and all locks would be released automatically if the transaction fails? My only concern here is that SQL would not release all locks of the transaction in case the transaction fails.

Thanks,

Tom

Upvotes: 7

Views: 9494

Answers (5)

user3493986
user3493986

Reputation: 11

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

Upvotes: 1

Ben Gripka
Ben Gripka

Reputation: 16548

I like Brad's approach but it needed a little clean up so you can see the error that caused 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

Upvotes: 5

Brad
Brad

Reputation: 15567

TRY/CATCH isn't required to release locks. However, I think that 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

Upvotes: 3

Andomar
Andomar

Reputation: 238048

A much easier way is:

set xact_abort on

This will cause the transaction to be rolled back automatically when an error occurs.

Example code:

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 multiple times, you'll see the transaction count increase to 2,3,4 etc. A single run of the first segment resets all transactions.

Upvotes: 8

Philip Kelley
Philip Kelley

Reputation: 40289

Short answer: Yes.

Whenever I use BEGIN TRANSACTION, I always include use error handling and ROLLBACK. The consequences of hitting an unanticipated (and/or unanticipatable -- you can't know how your code may need to be modified in the future) situation that leaves an open transaction on a Production server are too severe to not do it.

In SQL Server 2000 and earlier, you have to use @@Error logic. In SQL 2005 and up, you get to use the (far superior) TRY...CATCH... syntax.

Upvotes: 6

Related Questions