473,395 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Help needed by deadlock problem

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

Dec 1 '05 #1
5 2627
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
Dec 1 '05 #2
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

Dec 2 '05 #3
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
Dec 2 '05 #4
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

Dec 3 '05 #5
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

Dec 4 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into...
1
by: debian mojo | last post by:
Hello faculties, i'm encountering a strange a deadlock problem on my remote server which is used to give application demo to the client. It has happened that on one of the databases a deadlock...
1
by: Rohit Raghuwanshi | last post by:
Hello all, we are running a delphi application with DB2 V8.01 which is causing deadlocks when rows are being inserted into a table. Attaching the Event Monitor Log (DEADLOCKS WITH DETAILS) here....
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
4
by: Madhu Gopinathan | last post by:
Hi All, I am faced with a horrible hang problem. I have a COM exe server that executes some tasks. The task execution manager is a thread that manages the pool of threads, which is 4 per processor....
6
by: Shanmugasundaram Doraisamy | last post by:
Dear Group, Is there a way to increase the time of Dead Lock? The default is set to 1000 msec. Regards, Shan. ---------------------------(end of broadcast)--------------------------- TIP 7:...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
0
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to...
0
by: halex | last post by:
Hello, I am having deadlock problem when I have a lot of visitors on my website at the same time. I am using NetTiers templates to generate C# classes for accessing DB layer and problem is in my...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.