Post reply

DeadLock issues

  • March 5, 2007 at 8:38 am

    #116617

    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

    #693141

    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

    #693175

    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

    #693177

    mngong - What exactly is the snapshot isolation level and where would I change that?

  • March 5, 2007 at 11:28 am

    #693183

    Isn't the snapshot isolation level used in SQL Server 2005? I am working with 2000.

  • March 6, 2007 at 9:19 am

    #693348

    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

    #693352

    VB.net

  • March 6, 2007 at 9:29 am

    #693355

    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

    #693359

    Thank you so much for your help. I'll try it.

  • March 7, 2007 at 10:55 am

    #693618

    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