Hi,

We had deadlock on 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

kart (k***@discussions.microsoft.com) writes:
With knowledge about the table in question and its indexes, it is very
difficult to tell. It's not always that apparent even when you have this
information...

Do you get this deadlock frequently, or was it just occasional?

HI Erland,

It happened very rarely and we found the problem was in logic. The logic
fails when we have very less data in a table with higher transactions on the
same (trying to modify the same page data). The deadlock is due to conversion
problem.

Rgs
Kart



Show quoteHide quote

SQL Server 2005 Activity Monitor doesn't show connections from Windows 98 clients
differnt execution plans for same query
Import Data succeeds but doesn't copy data?
SQL Server 2005 Management Studio vs. 2000 Enterprise Manager/Query Analyzer
SIMPLE command to convert string to number? Not CAST or CONVERT.