We have a DB that is heavly hit. Recently, we started to notice deadlocks on this machine. Now its up to one deadlock every 5 minutes.
For the most part, deadlock occurs between a select query (either inside a Stored Proc or just straight select query) and an update statement.
Some DB info:
Most of the data is stored in just one table with 120 columns. [don't ask me why...] All necessary columns are indexed (about 16 columns).
One example of deadlock is between:
select query returns exactly 1000 records (select top 1000)
Update statement updates exactly one row (update.... where ID = 1)
Any ideas are greatly appreciated.
Following is the Deadlock Log:
thanks,
For the most part, deadlock occurs between a select query (either inside a Stored Proc or just straight select query) and an update statement.
Some DB info:
Most of the data is stored in just one table with 120 columns. [don't ask me why...] All necessary columns are indexed (about 16 columns).
One example of deadlock is between:
select query returns exactly 1000 records (select top 1000)
Update statement updates exactly one row (update.... where ID = 1)
Any ideas are greatly appreciated.
Following is the Deadlock Log:
Code:
Deadlock encountered .... Printing deadlock information
2007-10-03 09:08:53.08 spid3
2007-10-03 09:08:53.08 spid3 Wait-for graph
2007-10-03 09:08:53.08 spid3
2007-10-03 09:08:53.08 spid3 Node:1
2007-10-03 09:08:53.08 spid3 PAG: 6:6:84082 CleanCnt:2 Mode: S Flags: 0x2
2007-10-03 09:08:53.08 spid3 Grant List 1::
2007-10-03 09:08:53.08 spid3 Owner:0x3ecab6a0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:67 ECID:2
2007-10-03 09:08:53.08 spid3 SPID: 67 ECID: 2 Statement Type: INSERT Line #: 60
2007-10-03 09:08:53.08 spid3 Input Buf: RPC Event: pr_PendingReportListing;1
2007-10-03 09:08:53.08 spid3 Requested By:
2007-10-03 09:08:53.08 spid3 ResType:LockOwner Stype:'OR' Mode: IX SPID:323 ECID:0 Ec:(0x55BEF550) Value:0x578c46c0 Cost:(0/268)
2007-10-03 09:08:53.08 spid3
2007-10-03 09:08:53.08 spid3 Node:2
2007-10-03 09:08:53.08 spid3 PAG: 6:6:302 CleanCnt:2 Mode: IX Flags: 0x2
2007-10-03 09:08:53.08 spid3 Grant List 0::
2007-10-03 09:08:53.08 spid3 Owner:0x2b343540 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:323 ECID:0
2007-10-03 09:08:53.08 spid3 SPID: 323 ECID: 0 Statement Type: UPDATE Line #: 19
2007-10-03 09:08:53.08 spid3 Input Buf: RPC Event: sp_executesql;1
2007-10-03 09:08:53.08 spid3 Requested By:
2007-10-03 09:08:53.08 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:67 ECID:2 Ec:(0x2FFD40C0) Value:0x72be5020 Cost:(0/0)
2007-10-03 09:08:53.08 spid3 Victim Resource Owner:
2007-10-03 09:08:53.08 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:67 ECID:2 Ec:(0x2FFD40C0) Value:0x72be5020 Cost:(0/0)
2007-10-03 09:08:53.08 spid67 Error: 1205, Severity: 13, State: 61
2007-10-03 09:08:53.08 spid67 Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
2007-10-03 09:09:40.63 spid3
Deadlock encountered .... Printing deadlock information
2007-10-03 08:42:37.22 spid3
2007-10-03 08:42:37.22 spid3 Wait-for graph
2007-10-03 08:42:37.22 spid3
2007-10-03 08:42:37.22 spid3 Node:1
2007-10-03 08:42:37.22 spid3 PAG: 6:6:84817 CleanCnt:2 Mode: S Flags: 0x2
2007-10-03 08:42:37.22 spid3 Grant List 1::
2007-10-03 08:42:37.22 spid3 Owner:0x7bda92a0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:113 ECID:4
2007-10-03 08:42:37.22 spid3 SPID: 113 ECID: 4 Statement Type: SELECT Line #: 1
2007-10-03 08:42:37.22 spid3 Input Buf: RPC Event: dbo.pr_SearchResults;1
2007-10-03 08:42:37.22 spid3 Requested By:
2007-10-03 08:42:37.22 spid3 ResType:LockOwner Stype:'OR' Mode: IX SPID:100 ECID:0 Ec:(0x35FF7598) Value:0x69484220 Cost:(0/33C)
2007-10-03 08:42:37.22 spid3
2007-10-03 08:42:37.22 spid3 Node:2
2007-10-03 08:42:37.22 spid3 PAG: 6:5:84948 CleanCnt:2 Mode: IX Flags: 0x2
2007-10-03 08:42:37.22 spid3 Grant List 0::
2007-10-03 08:42:37.22 spid3 Owner:0x69484420 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:100 ECID:0
2007-10-03 08:42:37.22 spid3 SPID: 100 ECID: 0 Statement Type: UPDATE Line #: 109
2007-10-03 08:42:37.22 spid3 Input Buf: RPC Event: dbo.pr_RoundRobinAssignment;1
2007-10-03 08:42:37.22 spid3 Requested By:
2007-10-03 08:42:37.22 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:113 ECID:4 Ec:(0x3106C0C0) Value:0x2ab4d160 Cost:(0/0)
2007-10-03 08:42:37.22 spid3 Victim Resource Owner:
2007-10-03 08:42:37.22 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:113 ECID:4 Ec:(0x3106C0C0) Value:0x2ab4d160 Cost:(0/0)
2007-10-03 08:42:37.22 spid113 Error: 1205, Severity: 13, State: 61
2007-10-03 08:42:37.22 spid113 Transaction (Process ID 113) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
2007-10-03 08:50:28.47 spid3
thanks,