cannot get over this deadlock..

• thom smith-433882

  SSC Enthusiast

  Points: 142

  More actions

  April 26, 2007 at 6:41 am

  #117166

  PLs, can someone help me...

  The scenario:

  sp1:

  select from table1 (long running query with temporary table...)

  sp2:

  begin tran

  select from table2 with updlock

  if rowcount=0

    select from table1 with updlock

    if rowcount=0

      rollback

    else

      update table1  <-- deathlock occures here

  else

    update table2

  commit

  sp3:

  update table2

  if rowcount=0

    update table1 <-- deathlock occurs here

  how it happens (from tracelog)

  1. sp1 starts (pid = 51)

  2. sp2 starts (pid = 58)

  3. sp3 starts (pid = 56)

  4. deathlock found (sp2 is killed)

  5. sp3 end

  ..

  6. sp1 end

  info from traceflag 1204

  Node:1

  PAG: 12:1:180126               CleanCnt:2 Mode: U Flags: 0x2

   Convert List:

     Owner:0x70098920 Mode: IX       Flg:0x2 Ref:1 Life:02000000 SPID:58 ECID:0

     SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 263

     Input Buf: RPC Event: sp2;1

   Requested By:

     ResType:LockOwner Stype:'OR' Mode: IU SPID:56 ECID:0 Ec0x4ACD5570) Value:0x70e555a0 Cost0/0)

  Node:2

  PAG: 12:1:180126               CleanCnt:2 Mode: U Flags: 0x2

   Grant List 1::

   Grant List 2::

     Owner:0x5ae370e0 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:56 ECID:0

     SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 22

     Input Buf: RPC Event: sp3;1

   Requested By:

     ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec0x6261F570) Value:0x70098920 Cost0/0)

  Victim Resource Owner:

   ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec0x6261F570) Value:0x70098920 Cost0/0)

  Thanx much.

  Im investing it for 2 days, but only one idea I have that this is caused by the first long running procedure, because the 2 others procedures (from error log) cannot deadlock together (think)

  PAG: 12:1:180126 is PageLock on table1

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  April 26, 2007 at 10:25 am

  #702677

  In SP2, assuming the same filter condition for the SELECT and UPDATE of table1, is it possible that new rows can be added to table1 between the time of the SELECT and the time of the UPDATE with the same filter condition?

  If this is possible, you may need to alter the filter condition to exclude new rows or SERIALIZE the SELECT on table1.