March 21, 2003 at 1:06 pm
i, I have a deadlock problem on our server. Basically,
our environment: we have web connections, someone can
place an order while someone else is also placing an
order. There is a trigger on the awards table which
basically is there to control inventory. Users are
concurrently executing the same stored procedure which is
then firing a trigger on an awards table. The profiler
shows the deadlock occuring between a spid that is running
the stored procedure and a different spid that has ran the
same stored procedure. They are in two different places.
One is updating the clustered index, the other is updating
a non-clustered index. They are both requesting update
locks on the opposite index which creates the deadlock
situation.
I guess I am not clear on how to troubleshoot this since
they are excueting the same stored proc.
Would it help or hurt the deadlock situtation if we try to
use Rowlock on the update statement?
Here is the deadlock message
Deadlock encountered .... Printing deadlock information
2003-03-21 10:22:58.22 spid2
2003-03-21 10:22:58.22 spid2 Wait-for graph
2003-03-21 10:22:58.22 spid2
2003-03-21 10:22:58.22 spid2 Node:1
2003-03-21 10:22:58.22 spid2 KEY: 7:1682157088:2
(3d009f00fadc) CleanCnt:1 Mode: U Flags: 0x0
2003-03-21 10:22:58.22 spid2 Grant List 0::
2003-03-21 10:22:58.22 spid2 Owner:0xb87df8a0 Mode:
U Flg:0x0 Ref:1 Life:00000000 SPID:69 ECID:0
2003-03-21 10:22:58.22 spid2 SPID: 69 ECID: 0
Statement Type: UPDATE Line #: 232
2003-03-21 10:22:58.22 spid2 Input Buf: RPC Event:
DBO.SPIVRINSERTORDER;1
2003-03-21 10:22:58.22 spid2 Requested By:
2003-03-21 10:22:58.22 spid2 ResType:LockOwner
Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x890F79D8)
Value:0xb96bd9e0 Cost:(0/E24)
2003-03-21 10:22:58.22 spid2
2003-03-21 10:22:58.22 spid2 Node:2
2003-03-21 10:22:58.22 spid2 KEY: 7:1682157088:1
(110010ceffed) CleanCnt:1 Mode: X Flags: 0x0
2003-03-21 10:22:58.22 spid2 Grant List 0::
2003-03-21 10:22:58.22 spid2 Owner:0xb94db640 Mode:
X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2003-03-21 10:22:58.22 spid2 SPID: 62 ECID: 0
Statement Type: UPDATE Line #: 20
2003-03-21 10:22:58.22 spid2 Input Buf: RPC Event:
DBO.SPIVRINSERTORDER;1
2003-03-21 10:22:58.22 spid2 Requested By:
2003-03-21 10:22:58.22 spid2 ResType:LockOwner
Stype:'OR' Mode: U SPID:69 ECID:0 Ec:(0x6A65B9D8)
Value:0xb718f420 Cost:(0/2E10)
2003-03-21 10:22:58.22 spid2 Victim Resource Owner:
2003-03-21 10:22:58.22 spid2 ResType:LockOwner
Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x890F79D8)
Value:0xb96bd9e0 Cost:(0/E24)
--SPID 69
UPDATE Awards SET OnHandQuantity = (ISNULL(OnHandQuantity,
0) - @Quantity) WHERE AwardID = @AwardID
--SPID 69
Execution Tree
--------------
Clustered Index Update(OBJECT:([discovercbb].[dbo].
[Awards].[Ref_4108_FK]), SET:([Awards].[OnHandQuantity]=
[Expr1004]))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=isnull
([Awards].[OnHandQuantity], 0)-[@Quantity]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([discovercbb].[dbo].[Awards]))
|--Index Seek(OBJECT:([discovercbb].[dbo].
[Awards].[PK_AWARDS]), SEEK:([Awards].[AwardID]=
[@AwardID]) ORDERED FORWARD)
SPId 62
Update Statement:
UPDATE Awards SET OnHandStatus = 'AVAILABLE' WHERE AwardID
= @i_AwardID
--SPID 62
Execution Tree
--------------
Assert(WHERE:(If (NOT([Pass1008]) AND ([Expr1007] IS
NULL)) then 0 else NULL))
|--Nested Loops(Left Semi Join, WHERE:('AVAILABLE' IS
NULL)OUTER REFERENCES:('AVAILABLE'), DEFINE:([Expr1007] =
[PROBE VALUE]))
|--Clustered Index Update(OBJECT:([discovercbb].
[dbo].[Awards].[Ref_4108_FK]), SET:([Awards].[OnHandStatus]
='AVAILABLE'))
| |--Top(1)
| |--Index Seek(OBJECT:([discovercbb].[dbo].
[Awards].[PK_AWARDS]), SEEK:([Awards].[AwardID]=
[@i_AwardID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([discovercbb].[dbo].
[L_OnHandStatus].[PK_L_OnHandStatus]), SEEK:
([L_OnHandStatus].[OnHandStatus]='AVAILABLE') ORDERED
FORWARD)
March 24, 2003 at 8:00 am
This was removed by the editor as SPAM
March 24, 2003 at 8:48 am
I have seen the ROWLOCK hint resolve strange Deadlock issues. However, I have never seen a deadlock to occur like this when the entire transaction occurs only on a single table - even under very high volume. So it seems to be related to a larger picture.
Do you have the stored procedure wrapped in an explicit BEGIN TRANSACTION statement? It may cause a little slowdown, but prevents deadlocks (mostly).
Is it necessary to have a clustered index? Since normal indexes point to a page on the clustered index at their leaf level, their updates must wait if the clustered index is being updated.
Just some thoughts that might help.
Guarddata-
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply