Help needed by deadlock problem

[Matik]

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

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

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

Without seeing the queries that deadlocks it is a tad difficult to say
for sure, but 339 is not much for a table. Unless the table has excessive
row size, I would not expect that the index be that much of a performance
booster. Right now it appears to be a deadlocker. The update process comes
from the clustered index, and needs to update the index node, where as
the SELECT process has arrived from an index seek and is now going to the
data page.

It's possible that more shrewd indexing is possible, but without knowledge
of the application, I cannot tell.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Matik]

Thanks Erland,

Well, I can say, that the update is done only in one statement, and
it's over the where id = any_number condition .
I can't tell you about the selects' because there is many of them.
Excluding views, where I'm using nonlock option, there are many
procedures and points, where this table is reffered.
Usualy, over join with other tables, over fk_dtsIndex field.
Do You think, that removing the index (because this acctualy not
increases perfomrance, by such a small table) which is put on
fk_dtsIndex field could generaly solve the problem?
In this case, sqlsrv will not need to lock the index, which need to be
locked by update.

I can not avoid calling this booth sp separately (in the same time),
but I also can not remove joins by select, over this field ... so I'm
confused.

Mateusz

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

Well, I can say, that the update is done only in one statement, and
it's over the where id = any_number condition .
That may be enough.
Do You think, that removing the index (because this acctualy not
increases perfomrance, by such a small table) which is put on
fk_dtsIndex field could generaly solve the problem?

Yes, that is my theory.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[gjvdkamp]

Are you sure you're referencing the tables in the same order in your
SP's? If you select from tables in the same order in both SP's they
should not get in eachothers way.

You want to avoid reading from table A and then B in SP1, and first
table B and then A in SP2. WHen they execute at the same moment, they
lock eachothers second tables, and we know what happens then...

If that doent help maybe you can set the isolation level to read
uncommitted if the business logic allows this.

Good luck,

Gert-Jan

[Matik]

Gert-Jan!

Thanks. This might be that. I'll review the coding keeping that in my
mind. This could be an issue, because, as far I can remember, joins
are done in different order.
Am I right that You mean referencing them in the same order by JOINS?
Not generaly in the sp code?

I'll check booth possibilities tommorow (also removing index).

Thanks,

Mateusz