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:LockOwn er 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:LockOwn er 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:0x24ae148 0 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:LockOwn er 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_ GetOrderForStac ker;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:0x6e56ce6 0 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_dtsInde x" field, which is
updatable very often, and used by other resources (fill factor 90%).
Procedure: proc_importData
Procedure: proc_generateNe xtOrder
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_importDat a), 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
5  2648 
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_dtsInde x" field, which is
updatable very often, and used by other resources (fill factor 90%).
Procedure: proc_importData
Procedure: proc_generateNe xtOrder
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****@sommarsk og.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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
|
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 situation is taking place.
What is the most detailed way to detect such the cause of such a
deadlock to the innermost level of detail, like what statements, stored
procedures and locks are causing the deadlock to occur.
|
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. From the log it looks
like the problem happens when 2 threads insert 1 record each in the
same table and then try to aquire a NS (Next Key Share)
lock on the record inserterd by the other thread.
Thanks
Rohit
|
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) 3-4 times and give up if after that
it's still in deadlock. I'm very sure that many experienced people out
there already deal with this issue somehow. Is there an alternative to it?
Thanks for your comments and suggestions.
|
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. Each task is processed in a separate thread. Each of the executer threads is an STA thread, and it goes ahead and executes the task.
No problems are encountered when tasks are executed one at a time, but when multiple tasks are executed...
| | |
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: don't forget to increase your free space map settings
|
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 "access_log", the referential integrity
triggers generate these queries:
SELECT 1 FROM ONLY "public"."application_type" x
|
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
java.sql.SQLException: Deadlock found when trying to get lock; Try
restarting transaction message from server: "Lock wait timeout
exceeded; try restarting transaction";
We get such errors generally on inserts or updates while applying a
|
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 custom Store Procedure.
I have Article table and ArticleLanguage table.
One record from Article (Id, Position, StatusId) table is the same for all languages and in ArticleLanguage (Id, LanguageId, ArticleId, Name) table I have only article names...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
