October 24, 2005 at 12:02 pm
Occassionally I get a production batch fail due to it being selected as deadlock victim. When this happens all I want to do is redo the statement that was killed by the server. I've tried the following script:
create procedure dbo.DeadLockSafe
@sql as nvarchar(4000),
@tries as int = 2
as
print @sql
declare @error int
set @error=1205
while @tries>0 and @error=1205
begin
execute(@sql)
select
@error=@@error,
@tries=@tries-1
if @error=1205 and @tries>0
print 'Retrying... ('+convert(varchar,@tries)+')'
end
as a wrapper to work around this, but it doesn't work (the whole sp is terminated when the deadlock victim is chosen, rather than the "sub-script" in @sql, which is the actual cause of the deadlock). Does anybody have any ideas about how to automatically restart deadlock victim statements?
October 24, 2005 at 12:10 pm
Does this have to be run as an SP or can the code be fired from another application?
October 24, 2005 at 12:25 pm
I'm trying to get a sp solution because all my production jobs run as sp's and I'm looking to replace:
exec my_sp
with:
exec DeadLockSafe 'my_sp'
which is a minimally intrusive wrapper that can be used wherever 'my_sp' is called without any special programming. In the Agent Job Scheduler I use the "retry" option, but that only works in that environment.
October 24, 2005 at 12:36 pm
I see. I've never faced that problem and I can't think of a solution at the moment... maybe someone else will be in a better position to help you.
October 24, 2005 at 1:19 pm
Well, it appears that this is a fundamental problem with SQL Server 2000 because the deadlock monitor thread cannot distinguish between the caller (my "DeadLockFree" sp) and the called sp itself as it kills the entire spid associated with the deadlock, not just the deadlocking code itself.
However, "2005" provides the try/catch syntax which can be used to trap error 1205 and therefore resolves this problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply