Deadlock problem

Can somebody help me.
I spend 2 days on this problem, and i stuck. i`ve got this error:
Transaction (Process ID ***) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction...
There is a trace:
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 1204, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 3605, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON -1, server process ID
(SPID) 57.
2006-06-01 23:37:43.24 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Wait-for graph
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:1
2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 0::
2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:2
2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 3::
2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x93212e40 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:142 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 142 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Wait-for graph
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:1
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 0::
2006-06-01 23:38:00.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:2
2006-06-01 23:38:00.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 3::
2006-06-01 23:38:00.74 spid4 Owner:0x9339d7a0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 130 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:3
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Wait List:
2006-06-01 23:38:00.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)
2006-06-01 23:38:00.74 spid4 Victim Resource Owner:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)(gaploid@.yandex.ru) writes:
> Can somebody help me.
> I spend 2 days on this problem, and i stuck. i`ve got this error:
> Transaction (Process ID ***) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction...
>...
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Wait-for graph
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:1
> 2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 0::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
> UPDATE Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:2
> 2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
> CleanCnt:2 Mode: S Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 3::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
> SELECT Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
> round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
> (sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
> complex, so.New_name FROM New_ServiceOrderProduct sop INNER
> JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
> 2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:55.74 spid4
Without knowing tables, and not seeing the text of the UDPATE statement
is a bit difficult to say for sure.
But there is an UPDATE statement, and the locks involve the clustered
index of two different tables. That would indicate that the UPDATE
includes a cascading foreign key - or a trigger. Or that it's part
of a longer transaction.
As a starting point, post the full text of the SELECT statement,
the full text of the UPDATE statement and the table definitions,
including constraints and indexes for the tables. Also, include
the output of
SELCECT object_name(894678285), object_name(862678171)
I assume that you know which database is database 8.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I solve this problem. in my select statement i have a inner join to
another table, so i simply redisign my select query and make it without
inner join.
thx for your answer.

> Without knowing tables, and not seeing the text of the UDPATE statement
> is a bit difficult to say for sure.
> But there is an UPDATE statement, and the locks involve the clustered
> index of two different tables. That would indicate that the UPDATE
> includes a cascading foreign key - or a trigger. Or that it's part
> of a longer transaction.
> As a starting point, post the full text of the SELECT statement,
> the full text of the UPDATE statement and the table definitions,
> including constraints and indexes for the tables. Also, include
> the output of
> SELCECT object_name(894678285), object_name(862678171)
> I assume that you know which database is database 8.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx