none
DeadLock (Page+KEY) Mix - How to solve?

    Question

  • Hi,

    We had deadlock at page level. How do we solve this issue. I identified the
    SP's that cause this based on SPID and ObjectID from SQL Log along with
    Profiler data.
    Here are the details of SQL Log

    ResType:LockOwner Stype:'OR'Xdes:0x0E78F8A8 Mode: S SPID:74 BatchID:0 ECID:0
    TaskProxy:(0x2F6AC374) Value:0x3e49d40 Cost:(0/3780)
    Victim Resource Owner:
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x0E78F8A8 Mode: S SPID:74 BatchID:0 ECID:0
    TaskProxy:(0x2F6AC374) Value:0x3e49d40 Cost:(0/3780)
    Requested By:
    Owner:0x03E41CA0 Mode: S        Flg:0x2 Ref:1 Life:00000000 SPID:75 ECID:0
    XactLockInfo: 0x11871BE4
    Wait List:
    KEY: 9:72057594058637312 (060091fd2fe9) CleanCnt:4 Mode:X Flags: 0x0
    Node:6
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x11871398 Mode: S SPID:72 BatchID:0 ECID:0
    TaskProxy:(0x0C3E8374) Value:0x3e43900 Cost:(0/3780)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 74 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E3FB60 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:74 ECID:0
    XactLockInfo: 0x0E78F8CC
    Grant List 3:
    KEY: 9:72057594058637312 (0700af96ed06) CleanCnt:2 Mode:X Flags: 0x0
    Node:5
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x11871BC0 Mode: S SPID:75 BatchID:0 ECID:0
    TaskProxy:(0x08D2E374) Value:0x3e41ca0 Cost:(0/5772)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 72 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E486A0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:0
    XactLockInfo: 0x118713BC
    Grant List 0:
    KEY: 9:72057594058637312 (060091fd2fe9) CleanCnt:4 Mode:X Flags: 0x0
    Node:4
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x45E426D8 Mode: S SPID:68 BatchID:0 ECID:0
    TaskProxy:(0x47954374) Value:0x3e3ee60 Cost:(0/0)
    Requested By:
    Grant List 3:
    Grant List 2:
    Grant List 1:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 75 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E43CA0 Mode: IX       Flg:0x0 Ref:0 Life:02000000 SPID:75 ECID:0
    XactLockInfo: 0x11871BE4
    Grant List 0:
    PAGE: 9:3:40339                CleanCnt:2 Mode:IX Flags: 0x2
    Node:3
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x47EA9408 Mode: IX SPID:66 BatchID:0
    ECID:0 TaskProxy:(0x30FD0374) Value:0x3e3f9c0 Cost:(0/0)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1602820772]
    SPID: 68 ECID: 0 Statement Type: SELECT Line #: 98
    Owner:0x03E47F80 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:68 ECID:0
    XactLockInfo: 0x45E426FC
    Grant List 1:
    PAGE: 9:3:40338                CleanCnt:3 Mode:S Flags: 0x2
    Node:2
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x4141D578 Mode: S SPID:76 BatchID:0 ECID:0
    TaskProxy:(0x08A48374) Value:0x3e443a0 Cost:(0/0)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1903345845]
    SPID: 66 ECID: 0 Statement Type: UPDATE Line #: 82
    Owner:0x03E3F9C0 Mode: IX       Flg:0x2 Ref:1 Life:02000000 SPID:66 ECID:0
    XactLockInfo: 0x47EA942C
    Wait List:
    PAGE: 9:3:40338                CleanCnt:3 Mode:S Flags: 0x2
    Node:1
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    Wait-for graph
    Deadlock encountered .... Printing deadlock information
    ResType:LockOwner Stype:'OR'Xdes:0x0D15E448 Mode: S SPID:63 BatchID:0 ECID:0
    TaskProxy:(0x45B7C374) Value:0x3e46300 Cost:(0/3780)
    Victim Resource Owner:
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x0D15E448 Mode: S SPID:63 BatchID:0 ECID:0
    TaskProxy:(0x45B7C374) Value:0x3e46300 Cost:(0/3780)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 72 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E486A0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:0
    XactLockInfo: 0x118713BC
    Grant List 0:
    KEY: 9:72057594058637312 (060091fd2fe9) CleanCnt:3 Mode:X Flags: 0x0
    Node:3
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x0E78F8A8 Mode: S SPID:74 BatchID:0 ECID:0
    TaskProxy:(0x2F6AC374) Value:0x3e49d40 Cost:(0/3780)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 63 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E46300 Mode: S        Flg:0x2 Ref:1 Life:00000000 SPID:63 ECID:0
    XactLockInfo: 0x0D15E46C
    Wait List:
    KEY: 9:72057594058637312 (060091fd2fe9) CleanCnt:3 Mode:X Flags: 0x0
    Node:2
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    ResType:LockOwner Stype:'OR'Xdes:0x11871398 Mode: S SPID:72 BatchID:0 ECID:0
    TaskProxy:(0x0C3E8374) Value:0x3e43900 Cost:(0/3780)
    Requested By:
    Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 1647344933]
    SPID: 74 ECID: 0 Statement Type: INSERT Line #: 62
    Owner:0x03E3FB60 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:74 ECID:0
    XactLockInfo: 0x0E78F8CC
    Grant List 3:
    KEY: 9:72057594058637312 (0700af96ed06) CleanCnt:3 Mode:X Flags: 0x0
    Node:1
    Log Viewer could not read information for this log entry. Cause: Data is
    Null. This method or property cannot be called on Null values.. Content:
    Wait-for graph
    Deadlock encountered .... Printing deadlock information

    Regards
    Kart

    Wednesday, August 30, 2006 3:14 PM

All replies

  • I met the same problem. Any suggestions?
    Wednesday, December 03, 2008 4:41 PM
  • sqlserver_Patrick,

     

    Can you turn on trace flag 1222 if you are on SQL Server 2005, or 1204 if you are on SQL Server 2000, and then post the output of the deadlock graph from the SQL Server Error Log onto this post?  From that, I can provide you a more pointed answer to resolving your problem.  If you already have the deadlock graph from either of those trace flags, then please post it here so we can see the details.

    Wednesday, December 03, 2008 5:47 PM
    Moderator