deadlock on a single table but multiple processes

Hi! We have a third party application that calls same stored procedure
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec0x72B99520) Value:0xb61fa660 Cost0/7280)
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)Hi,
May I know why Index hint is used in update statement
(index(IND_CASH_ACT_SPD1))?
Manu
"James" wrote:

> Hi! We have a third party application that calls same stored procedure
> simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
> Deadlock trace shows both spids are running exactly same statement within
> the procedure. Depending upon input parameter the statement does either
> insert or update. But the deadlock trace shows that deadlock happens when
> both are running update statements. Multiple thread supposed to update sam
e
> table but different rows (at most couple of rows).
> The object (key) they are deadlocking on is a non clustered index used to
> search data for update. Update statement doesn't modify any column that
> belongs to this non clustered index. Database is running on default
> (read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
> the stored procedrue, so I assume that the statement is not a part of
> explicit transaction.
> Questions:
> 1. Why sql server is using update lock (And not the shared lock) on the no
n
> clustered index which used to search the data. The update statement doesn'
t
> modify this non clustered index. In below statement Index id 5 is on
> position_id, security_alias and long_short_indicator.
> 2. Why deadlock and not just blocking? What is a fix for this?
> Below is the update_statement that both SPID are running:
> UPDATE CA
> SET CANCEL_STATUS = 'Y',
> UPDATE_SOURCE = @.in_update_source,
> UPDATE_DATE = GETDATE()
> from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
> WHERE POSITION_ID = @.nTargetPositionId
> AND SECURITY_ALIAS = @.in_security_alias
> AND LONG_SHORT_INDICATOR = 'L'
> AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
> AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
> AND STAR_TAG25 = @.in_event_id
> AND CASH_BAL_INST = @.in_event_sequence
> AND CANCEL_FLAG = 'N'
> AND REFLEXIVE_FLOW = 'Y'
> Below is output of deadlock trace:
> Deadlock encountered ... Printing deadlock information
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Wait-for graph
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:1
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
> CleanCnt:2 Mode: X Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 3::
> 2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x
0
> Ref:0 Life:02000000 SPID:589 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:470 ECID:0 Ec0x72B99520) Value:0xb61fa660 Cost0/7280)
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:2
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
> CleanCnt:2 Mode: U Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 2::
> 2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x
0
> Ref:0 Life:00000001 SPID:470 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
> 2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
>
>sql