none
开启DBCC TRACEON(1222)检查死锁,发现如下信息，请帮忙看下。 RRS feed

 > 

  • 问题

  • Question
    登录进行投票
    0
    登录进行投票

    开启DBCC TRACEON(1222)检查死锁,发现如下信息，好像不能抓取出具体的sql。

    而且 Producer: Xid Slot: 1, Task = 0x00FE64D8, SPID: 67, ECID: 4, e_waitPortOpen
    这种信息是什么意思？

     

     

     

    2011-09-01 04:19:48.07 spid6s      Deadlock encountered .... Printing deadlock information
    2011-09-01 04:19:48.07 spid6s      Wait-for graph
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Node:1

    2011-09-01 04:19:48.07 spid6s      PAGE: 5:1:77314                CleanCnt:2 Mode:IX Flags: 0x2
    2011-09-01 04:19:48.07 spid6s       Grant List 1:
    2011-09-01 04:19:48.07 spid6s         Owner:0x592ECC40 Mode: IX       Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x057E4CBC
    2011-09-01 04:19:48.07 spid6s         SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 1
    2011-09-01 04:19:48.07 spid6s         Input Buf: Language Event: update T_OLTDetailTestData  set Last_Run='N' where F_TestId=19809
    2011-09-01 04:19:48.07 spid6s       Requested By:
    2011-09-01 04:19:48.07 spid6s         ResType:LockOwner Stype:'OR'Xdes:0x059B8778 Mode: U SPID:67 BatchID:0 ECID:6 TaskProxy:(0x1AD18580) Value:0x4af5f8a0 Cost:(0/0)
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Node:2

    2011-09-01 04:19:48.07 spid6s      PAGE: 5:1:538391               CleanCnt:3 Mode:U Flags: 0x2
    2011-09-01 04:19:48.07 spid6s       Grant List 3:
    2011-09-01 04:19:48.07 spid6s         Owner:0x4C74EF00 Mode: U        Flg:0x0 Ref:0 Life:00000001 SPID:67 ECID:4 XactLockInfo: 0x057F079C
    2011-09-01 04:19:48.07 spid6s         SPID: 67 ECID: 4 Statement Type: UPDATE Line #: 1
    2011-09-01 04:19:48.07 spid6s         Input Buf: No Event:
    2011-09-01 04:19:48.07 spid6s       Requested By:
    2011-09-01 04:19:48.07 spid6s         ResType:LockOwner Stype:'OR'Xdes:0x057E4C98 Mode: IU SPID:69 BatchID:0 ECID:0 TaskProxy:(0x3CDAE378) Value:0x4cabd820 Cost:(0/0)
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Node:3

    2011-09-01 04:19:48.07 spid6s      Port: 0x05E38100  Xid Slot: 1, Task: 0x00FE64D8, ECID: 4 (Producer), Exchange Wait Type :e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Coordinator: Task = 0x61E70C58, SPID: 67, ECID: 0, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Consumer List::
    2011-09-01 04:19:48.07 spid6s      Consumer: Xid Slot: 0, Task = 0x61E70C58, SPID: 67, ECID: 0, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer List::
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 1, Task = 0x00FE64D8, SPID: 67, ECID: 4, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 2, Task = 0x00A7E4D8, SPID: 67, ECID: 5, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 3, Task = 0x00DBA5C8, SPID: 67, ECID: 2, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 4, Task = 0x00FD05C8, SPID: 67, ECID: 3, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 5, Task = 0x00A785C8, SPID: 67, ECID: 1, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 6, Task = 0x00A625C8, SPID: 67, ECID: 7, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 7, Task = 0x008CC898, SPID: 67, ECID: 6, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 8, Task = 0x47F5E6B8, SPID: 67, ECID: 10, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 9, Task = 0x61BC47A8, SPID: 67, ECID: 9, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 10, Task = 0x61C5A4D8, SPID: 67, ECID: 8, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 11, Task = 0x61E864D8, SPID: 67, ECID: 14, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 12, Task = 0x61F9CD48, SPID: 67, ECID: 12, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 13, Task = 0x344CAA78, SPID: 67, ECID: 13, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 14, Task = 0x47F58988, SPID: 67, ECID: 11, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 15, Task = 0x61E707A8, SPID: 67, ECID: 15, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 16, Task = 0x00D245C8, SPID: 67, ECID: 16, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Node:4

    2011-09-01 04:19:48.07 spid6s      Port: 0x05E38100  Xid Slot: 2, Task: 0x00A7E4D8, ECID: 5 (Producer), Exchange Wait Type :e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s         SPID: 67 ECID: 5 Statement Type: UPDATE Line #: 1
    2011-09-01 04:19:48.07 spid6s         Input Buf: No Event:
    2011-09-01 04:19:48.07 spid6s      Coordinator: Task = 0x61E70C58, SPID: 67, ECID: 0, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Consumer List::
    2011-09-01 04:19:48.07 spid6s      Consumer: Xid Slot: 0, Task = 0x61E70C58, SPID: 67, ECID: 0, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer List::
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 1, Task = 0x00FE64D8, SPID: 67, ECID: 4, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 2, Task = 0x00A7E4D8, SPID: 67, ECID: 5, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 3, Task = 0x00DBA5C8, SPID: 67, ECID: 2, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 4, Task = 0x00FD05C8, SPID: 67, ECID: 3, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 5, Task = 0x00A785C8, SPID: 67, ECID: 1, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 6, Task = 0x00A625C8, SPID: 67, ECID: 7, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 7, Task = 0x008CC898, SPID: 67, ECID: 6, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 8, Task = 0x47F5E6B8, SPID: 67, ECID: 10, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 9, Task = 0x61BC47A8, SPID: 67, ECID: 9, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 10, Task = 0x61C5A4D8, SPID: 67, ECID: 8, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 11, Task = 0x61E864D8, SPID: 67, ECID: 14, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 12, Task = 0x61F9CD48, SPID: 67, ECID: 12, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 13, Task = 0x344CAA78, SPID: 67, ECID: 13, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 14, Task = 0x47F58988, SPID: 67, ECID: 11, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 15, Task = 0x61E707A8, SPID: 67, ECID: 15, e_waitNone
    2011-09-01 04:19:48.07 spid6s      Producer: Xid Slot: 16, Task = 0x00D245C8, SPID: 67, ECID: 16, e_waitPortOpen
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Node:5

    2011-09-01 04:19:48.07 spid6s      PAGE: 5:1:109365               CleanCnt:2 Mode:IX Flags: 0x2
    2011-09-01 04:19:48.07 spid6s       Grant List 1:
    2011-09-01 04:19:48.07 spid6s         Owner:0x4AD5E0E0 Mode: IX       Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x057E4CBC
    2011-09-01 04:19:48.07 spid6s       Requested By:
    2011-09-01 04:19:48.07 spid6s         ResType:LockOwner Stype:'OR'Xdes:0x057E96D8 Mode: U SPID:67 BatchID:0 ECID:2 TaskProxy:(0x3474FF88) Value:0x33cce660 Cost:(0/0)
    2011-09-01 04:19:48.07 spid6s     
    2011-09-01 04:19:48.07 spid6s      Victim Resource Owner:
    2011-09-01 04:19:48.07 spid6s       ResType:LockOwner Stype:'OR'Xdes:0x057E96D8 Mode: U SPID:67 BatchID:0 ECID:2 TaskProxy:(0x3474FF88) Value:0x33cce660 Cost:(0/0)

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月1日 6:59
    |

答案

全部回复

  • Question
    登录进行投票
    0
    登录进行投票

    update T_OLTDetailTestData set Last_Run='N' where F_TestId=19809 这个应该是问题的sql

    这个表有多少数据，testid上有索引么，先优化一下这个语句，在观察一下。

    family as water
    2011年9月1日 8:49
    |
  • Question
    登录进行投票
    0
    登录进行投票

    sql 2005/2008 的 Profile  可以直接 Trace Deadlock 事件，一般用 Deadlock Graph 那个事件就可以了，这个比较详细

    2011年9月2日 0:37
    |
  • Question
    登录进行投票
    0
    登录进行投票

    sql 2005/2008 的 Profile  可以直接 Trace Deadlock 事件，一般用 Deadlock Graph 那个事件就可以了，这个比较详细


    但是不能一直开着profiler的。。所以就用trace打开的。
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月2日 1:14
    |
  • Question
    登录进行投票
    0
    登录进行投票

    update T_OLTDetailTestData set Last_Run='N' where F_TestId=19809 这个应该是问题的sql

    这个表有多少数据，testid上有索引么，先优化一下这个语句，在观察一下。

    family as water

    这个表大概20W笔数据。然后在F_Testid上无索引，distinct的值有3W多个。
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月2日 1:17
    |
  • Question
    登录进行投票
    0
    登录进行投票
    Try update rows in smaller batches. And use snapshot isolation if possible, it'll prevent writing block reading at least.
    2011年9月2日 2:10
    |