|
|
Posted by Matik on 10/14/83 11:33
Hello,
I've very often a deadlock problem.
The deadlock is generated always in the same way, by one application
calling in DB two sp's (application has two threads).
This is an error message from trace:
2005-11-29 08:20:29.97 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:1160 ECID:0 Ec:(0x296F9540) Value:0x3
2005-11-29 08:20:29.97 spid4 Victim Resource Owner:
2005-11-29 08:20:29.97 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:1160 ECID:0 Ec:(0x296F9540) Value:0x3
2005-11-29 08:20:29.97 spid4 Requested By:
2005-11-29 08:20:29.97 spid4 Input Buf: RPC Event: proc_importData;1
2005-11-29 08:20:29.97 spid4 SPID: 140 ECID: 0 Statement Type: UPDATE
Line #: 130
2005-11-29 08:20:29.97 spid4 Owner:0x24ae1480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:140 ECID:0
2005-11-29 08:20:29.97 spid4 Grant List 3::
2005-11-29 08:20:29.97 spid4 KEY: 7:1735833396:1 (4c0051adbb0e)
CleanCnt:1 Mode: X Flags: 0x0
2005-11-29 08:20:29.97 spid4 Node:2
2005-11-29 08:20:29.97 spid4
2005-11-29 08:20:29.97 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:140 ECID:0 Ec:(0x751B7540) Value:0x6d
2005-11-29 08:20:29.97 spid4 Requested By:
2005-11-29 08:20:29.97 spid4 Input Buf: RPC Event:
DS_SP_DVRS_PBS_GetOrderForStacker;1
2005-11-29 08:20:29.97 spid4 SPID: 1160 ECID: 0 Statement Type: SELECT
Line #: 238
2005-11-29 08:20:29.97 spid4 Owner:0x6e56ce60 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:1160 ECID:0
2005-11-29 08:20:29.97 spid4 Grant List 0::
2005-11-29 08:20:29.97 spid4 KEY: 7:1735833396:3 (5100200a3d21)
CleanCnt:1 Mode: S Flags: 0x0
2005-11-29 08:20:29.97 spid4 Node:1
2005-11-29 08:20:29.97 spid4
2005-11-29 08:20:29.97 spid4 Wait-for graph
2005-11-29 08:20:29.97 spid4
2005-11-29 08:20:29.97 spid4 ...
Table: 1735833396 = tab_locations
The lock is on one table.
Table contains only 339 rows, and it's a static table (no insert's
performed, just updates and selects)
Index: 1 = primary key, clustered, on "IDX" field (static) (fill factor
90%)
Index: 3 = non-clustered index, on "fk_dtsIndex" field, which is
updatable very often, and used by other resources (fill factor 90%).
Procedure: proc_importData
Procedure: proc_generateNextOrder
Now some additional explenations. This table, is used by other
processes very often, and it is major table,
for holding some informations necessary to display information in the
front-end user app.
This table is updated generaly ONLY over this one procedure
(proc_importData), but selects are perfomred in
lot of points.
Booth events, are comming from two different threads of same c++
application.
Note: fk_dtsIndex is unique in the collumn, means, tha can not be two
different ID's holding same fk_dtsIndex BUT there can
be possibility (and usualy it is like this) that for several IDX's the
value is NULL in fk_dtsIndex.
The index was created (on this fk_dtsIndex) to speed up joins and
search.
How can I resolve the deadlock? I can not rewrite application, so it
will use just ONE thread insetad of two.
I do not want change coding in sp's so much.
I was thinkig, maybe using just a different index, I could manage this
problem. But I can not figure out the best way.
Example table data:
IDX fk_dtsIndex
1 NULL
2 NULL
3 3462
4 3490
5 NULL
6 2290345
7 NULL
8 2290277
9 2290250
Any help appreciated. Thank You very much
Mateusz
[Back to original message]
|