Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

  • Alkesh Khedle

    Right there with Babe

    Points: 780

    Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction even using with(nolock) option in select query

    HI got the above error in production site during high load .When SQL Server2005 used one select query with some joins in it and all table with nolock hint.

    I am looking for some best solutions to avoid those errors .

  • twalston

    Mr or Mrs. 500

    Points: 505

    We had similar problems at a job I worked. Basically we had a third party software that the dev manager was impatient with the software producer. So he wrote his own applications against the db. Eventually the db hit a critical mass of usage, vs changes, vs triggers to contain the business logic.

    Turns out the software in question always wrote data to tables in a consistent table order, found using profiler. Efforts were taken in development to make sure that all triggers and code followed the same order of inserts and updates to minimize the possibility of deadlocks. If you use the Profiler tool during these periods of deadlock you should be able to narrow down the processes in question that are causing the problem. Work out the data flow for these processes and you should be able to find your major culprit.

  • gourav.j

    SSC Veteran

    Points: 275

    The tips to resolve this type of errors can be found in this link.

    http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

    Can you please tell me that how can I identify during the production that which process caused the deadlock?

    Regards,

    Gourav

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Enable traceflag 1222 (DBCC TRACEON (1222.-1))

    With that on, a deadlock graph is written to the error log when a deadlock occurs. You can read through the graph to see what processes were involved in the deadlock, what they were doing and what resources they were deadlocked over.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gourav.j

    SSC Veteran

    Points: 275

    Hi,

    Thanks for the reply but my concern is that the dead lock occured yesterday and I need to answer my client that which process caused the deadlock then how can I find it?

    Thanks in advance

    Gourav

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Unless you had one of the traceflags on or a profiler trace running with the deadlock events, there's no way to tell now what happened.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • kalyan.appdev

    SSC Enthusiast

    Points: 100

    "transaction(Process ID 59) was deadlocked on lock resources with another process and has been choosen as the deadlock victim.Return the transaction"

    I got the above error in production site during high load Iam using sqlserver 2005 , i dont have sql profiler, what would be the problem , When i restart the Sql service ,application works smothly please Help me out of this Situation ….

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Please post your question in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • cuk3m

    SSC Enthusiast

    Points: 145

    SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH }

    I resolve same problem with this command.

  • Mavi

    SSC Veteran

    Points: 210

    @GrassHopper

    After running the syntax

    "SET DEADLOCK_PRIORITY NORMAL;

    GO'

    Did the error go away permanently?

  • pratap.julu

    SSC Enthusiast

    Points: 167

    Hi i had faced the same problem as query was taking lots of time to give the results and ending up in deadlock. I have used "SET DEADLOCK_PRIORITY NORMAL;" before the select query and deadlock error is gone now. The Query still take lots of time to execute but it is giving me the result

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply