Post reply

Deadlock Issue

  • November 2, 2012 at 2:09 am

    #284142

    I have been looking into deadlock issue. While doing so, was able to track the following spids from sys.sysprocesses at the time deadlock happened:

    spidkpidblockedwaittypewaittimelastwaittypewaitresource

    7280081310x000D1782LCK_M_RS_S KEY: 6:72057599836422144 (960788ab75f9)

    1159684720x000310781LCK_M_S PAG: 6:3:328156

    131108681150x000310776LCK_M_S PAG: 6:3:328156

    Now how should I proceed with investigation and nail down the issue ? Any suggestions ?

  • November 2, 2012 at 2:17 am

    #1555307

    try " with nolock " with select query on your tables

    like select * from temptable with (nolock)

  • November 2, 2012 at 2:20 am

    #1555308

    BriPan (11/2/2012)

    try " with nolock " with select query on your tables

    like select * from temptable with (nolock)

    What is your reasoning behind advising the use of nolock? Do you understand the implications of using this hint?

  • November 2, 2012 at 2:21 am

    #1555309

    sqlnaive (11/2/2012)

    I have been looking into deadlock issue. While doing so, was able to track the following spids from sys.sysprocesses at the time deadlock happened:

    spidkpidblockedwaittypewaittimelastwaittypewaitresource

    7280081310x000D1782LCK_M_RS_S KEY: 6:72057599836422144 (960788ab75f9)

    1159684720x000310781LCK_M_S PAG: 6:3:328156

    131108681150x000310776LCK_M_S PAG: 6:3:328156

    Now how should I proceed with investigation and nail down the issue ? Any suggestions ?

    Enable traceflag 1222 which will write the deadlock graph to the error log, from there it will tell you what is involved in the deadlock and you can then take your investigations from that.

  • November 2, 2012 at 2:23 am

    #1555314

    BriPan (11/2/2012)

    try " with nolock " with select query on your tables

    like select * from temptable with (nolock)

    Recommending nolock with no mention of the side effects on data?

    See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    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

  • November 2, 2012 at 4:18 am

    #1555367

    I have attached the log into after enabling the traceflag 1222. Let me know if you find anything suspicious

  • November 5, 2012 at 1:06 am

    #1555902

    A little R&D and I was able to crack the issue. There were two procs mentioned in the trace and the transactions in the two were showing a cyclic dependency. I changed one proc and kept one read statement out of transaction and introduced temp table and BOOM.... It broke the cyclic dependency chain. Thanks to both of you.

Viewing 7 posts - 1 through 6 (of 6 total)

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