urgent deadlock problem on Sql Server 2000

[gjspaho]

it occours every about 5-6 minutes
this is the info i fo8und on sql server log

2007-03-26 12:55:33.64 spid4 ResType:LockOwn er Stype:'OR' Mode: S SPID:135 ECID:0 Ec:(0x4DB77570) Value:0x4a
2007-03-26 12:55:33.64 spid4 Victim Resource Owner:
2007-03-26 12:55:33.64 spid4 ResType:LockOwn er Stype:'OR' Mode: X SPID:137 ECID:0 Ec:(0x4DE61570) Value:0x4a
2007-03-26 12:55:33.64 spid4 Requested By:
2007-03-26 12:55:33.64 spid4 Input Buf: RPC Event: sp_LIST_ITEM_FR EE;1
2007-03-26 12:55:33.64 spid4 SPID: 135 ECID: 0 Statement Type: SELECT Line #: 17
2007-03-26 12:55:33.64 spid4 Owner:0x4c9cbda 0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:135 ECID:0
2007-03-26 12:55:33.64 spid4 Grant List 2::
2007-03-26 12:55:33.64 spid4 KEY: 8:670625432:27 (ed01093823e3) CleanCnt:2 Mode: S Flags: 0x0
2007-03-26 12:55:33.64 spid4 Node:2
2007-03-26 12:55:33.64 spid4
2007-03-26 12:55:33.64 spid4 ResType:LockOwn er Stype:'OR' Mode: S SPID:135 ECID:0 Ec:(0x4DB77570) Value:0x4a
2007-03-26 12:55:33.64 spid4 Requested By:
2007-03-26 12:55:33.64 spid4 Input Buf: RPC Event: sp_executesql;1
2007-03-26 12:55:33.64 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE Line #: 1
2007-03-26 12:55:33.64 spid4 Owner:0x4c244b0 0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:137 ECID:0
2007-03-26 12:55:33.64 spid4 Grant List 0::
2007-03-26 12:55:33.64 spid4 PAG: 8:1:14584 CleanCnt:2 Mode: IX Flags: 0x2
2007-03-26 12:55:33.64 spid4 Node:1
2007-03-26 12:55:33.64 spid4
2007-03-26 12:55:33.64 spid4 Wait-for graph
2007-03-26 12:55:33.64 spid4
2007-03-26 12:55:33.64 spid4 ...

the deadlock seem to happen between a select SP and Update statement executed with sp_executesql

can anyone help me avoid this problem
if you need anything else such as SP sql or table definition please ask me

thanks in advance

[iburyak]

Yes, without code it is almost impossible to resolve this issue.
But on my experience it happens when update is not within a transaction. I mean

[PHP]
BEGIN TRAN

COMMIT TRAN [/PHP]

Or it could be a trigger on this table that does something on the same table at the time of update.

[gjspaho]

i modified the select sp in the from clause(deadlock object table) with "with (nolock)"
and no deadlock happend since
hope this works
thank you for your response

[iburyak]

You should be really careful when giving query hints.
Usually server does best job for you and you shouldn't recommend doing something that can possibly have unpredictable consequences.


Good Luck.