July 8, 2010 at 3:27 pm
another subtle gotcha! with TRY CATCH
CATCH blocks are resumptive unless there is another TRY block in the call stack
this means that the LAST CATCH (at the top of the call stack) will always be resumptive
but the rest will be non-resumptive
this shows the issue:
go
create proc dbo.spTestCatchThrowA
as
--
begin try
raiserror('A: Error Exec', 16, 1)
raiserror('A: after Error Exec', 0, 1)
end try
begin catch
raiserror('A: Error Catch', 16, 1)
raiserror('A: after Error Catch', 0, 1)
end catch
--(end)
go
create proc dbo.spTestCatchThrowB
as
--
begin try
exec dbo.spTestCatchThrowA
raiserror('B: after exec', 0, 1)
end try
begin catch
raiserror('B: catch', 0, 1)
end catch
--(end)
go
exec dbo.spTestCatchThrowA
--Msg 50000, Level 16, State 1, Procedure spTestCatchThrowA, Line 9
--A: Error Catch
--A: after Error Catch
exec dbo.spTestCatchThrowB
--B: catch
the only way to ensure consistent behaviour is to always use TRY blocks inside CATCH blocks
(until you have an empty or single statement CATCH block)
July 8, 2010 at 3:39 pm
July 8, 2010 at 5:39 pm
for example, in this code fragment from the Using TRY ... CATCH page in BOL
BEGIN CATCH
-- Check error number.
-- If deadlock victim error,
-- then reduce retry count
-- for next update retry.
-- If some other error
-- occurred, then exit
-- retry WHILE loop.
IF (ERROR_NUMBER() = 1205)
SET @retry = @retry - 1;
ELSE
SET @retry = -1;
-- Print error information.
EXECUTE usp_MyErrorLog;
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
if EXECUTE usp_MyErrorLog; throws an error
the transaction will be rolled back:
- if this procedure was at the top of the callstack
- if this procedure was called without a TRY block in the callstack
but NOT if:
there is a TRY block in the callstack
so in this example:
begin try
-- call the procedure with the catch block
end try
begin catch
end catch
in this scenario the transaction will remain UNCOMMITTABLE but NOT rolled back
July 9, 2010 at 2:25 am
There is also an inconsistency when handling certain errors
BOL states:
A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction.
however this error:
Msg 266, Level 16, State 2, Procedure spForgetToRollBack, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
does NOT result in the transaction being rolled back in the absence of a TRY
but DOES result in an UNCOMMITTABLE transaction when a TRY block is in effect
as this fragment demonstrates:
go
create proc dbo.spForgetToRollBack
as
--
begin tran
--(end)
go
create proc dbo.spForgetToRollBackX
as
--
begin try
exec dbo.spForgetToRollBack
end try
begin catch
raiserror('ooooh', 0, 1)
end catch
--(end)
go
exec dbo.spForgetToRollBack
select xact_state()
rollback tran
go
exec dbo.spForgetToRollBackX
select xact_state()
rollback tran
go
again this means that every procedure needs to be written to work under two circumstances
called with (1) or without (2) an active TRY block in the callstack
which is possible - but complicates everything a little bit
July 9, 2010 at 6:35 am
here is my attempt at a deadlock loop with try catch
there are two << which are critical to stop problems related to unwanted resumptive error handling
sorry if I am stating the obvious - they seem like subtle gotchas to me
go
create proc dbo.spDeadLockTryCatch
as
--
declare @loop int
--
set @loop = 10 -- max no of attempts
--
while (1 = 1) begin
begin try
--
begin tran
--
-- execute sql statements here
--
commit tran
--
return 1 -- success
--
end try
begin catch
begin try -- << second try block to stop possible resumptive error handling in catch block
if (xact_state() <> 0) begin
rollback tran
end--if
if (@loop > 0 and error_number() = 1205) begin
-- looping on deadlock
set @loop = @loop - 1
continue
end--if
-- either non-deadlock error
-- or too many deadlock errors
exec dbo.spThrowError
return 0 -- << not necessary but safer
end try
begin catch
exec dbo.spThrowError -- catch block failed
return 0 -- << for safety incase no TRY block in call stack // otherwise infinite loop
end catch
end catch
end--while
--(end)
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply