March 5, 2007 at 8:38 am
How can I write code to trap the deadlock error and re-submit the transaction in SQL?
Does anyone have any sample code?
Thanks,
Ninel
March 5, 2007 at 8:56 am
Ninel
You need to trap error message 1205. See Detecting and Ending Deadlocks in Books Online for more details.
John
March 5, 2007 at 10:36 am
Though that was not your question, Changing the snapshot isolation level too would alleviate blocking and there will be no need to write code to trap deadlocks
March 5, 2007 at 11:03 am
mngong - What exactly is the snapshot isolation level and where would I change that?
March 5, 2007 at 11:28 am
Isn't the snapshot isolation level used in SQL Server 2005? I am working with 2000.
March 6, 2007 at 9:19 am
There's no way I know of in SQL to detect the deadlock, mostly because sql server terminates the deadlock process chosen as victim. You'll have to have the application that submitted the deadlock victim monitor the return codes of the call and rerun it when the deadlock victim notification comes back.
What application are you using to submit the query thats getting deadlocked?
March 6, 2007 at 9:22 am
VB.net
March 6, 2007 at 9:29 am
You should set up a loop that looks something like (C#ish)
TryAgain = true;
while (TryAgain)
{
TryAgain = False;
try
{
Submit the query
}
catch (Exception e)
{
if (e.Message.InStr("Deadlock") > -1)
TryAgain = true;
else
{
other error handling
}
}
}
I'm not sure which exception you are going to get, but this pattern should work for you. also, i usually set up a counter and complain if i get the deadlock 10 times in a row. which almost never happens (i got it once so far - your mileage may vary).
March 6, 2007 at 9:38 am
Thank you so much for your help. I'll try it.
March 7, 2007 at 10:55 am
Enable Trace Flag 1204:
DBCC TRACEON (3605,1204,-1)
This will enable all deadlocking info to be written to the SQL Server Error Log which should provide you with the info you need to properly troubleshoot your problem.
Tim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply