473,687 Members | 3,289 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

why does this deadlock?

Using SQL Server 2000 SP3a, I run the following in 2 query analizer
windows on the Northwind database, the second one always gets the
deadlock Msg 1205:

Window 1:

declare @cnt int
select @cnt = 5
while @cnt > 0
begin
begin transaction
select * from orders (updlock) where employeeid = 1
update orders set employeeid = 1 where employeeid = 1
waitfor delay '00:00:03'
commit
select @cnt = @cnt -1
end

Window 2:

declare @cnt int
select @cnt = 5
while @cnt > 0
begin
begin transaction
select * from orders (updlock) where employeeid = 1 and customerid =
'ERNSH'
waitfor delay '00:00:02'
commit
select @cnt = @cnt -1
end
The query in the first window gets 123 rows and places update locks on
them, then updates them and commits. The query in the second window
gets a subset (about 5) of the results that window 1 gets also trying
to place update locks on the same rows. Shouldn't the query in window 2
just wait for the transaction in window 1 to finish? why would it
deadlock?

you can also get rid of the delay in the second window and it will
deadlock faster.

thanks in advance.
Eugene

Jul 23 '05 #1
11 2515
hendershot (ey****@yahoo.c om) writes:
Using SQL Server 2000 SP3a, I run the following in 2 query analizer
windows on the Northwind database, the second one always gets the
deadlock Msg 1205:


I was not able to reproduce this. Then again, in this late hour I could
not find a machine running 8.00.760 (SP3a). I ran your repro on 8.00.2026
(SP4 beta) and 8.00.818 (a security hotfix), and I did not get a deadlock
on any of them. It could be a bug that has been corrected since the
release of SP3a. (Hm, wonder if the number of processors could matter?
I think both machines I tried have more than one CPU.)

See here for info on the SP4 beta: http://support.microsoft.com/kb/290211
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
just tried on a dual processor machine and it still deadlocked. adding
an index=1 hint to the window 2 select fixes the deadlock problem.
looking at the estimated execution plan for both select statements,
they are identical when the window 2 select uses the index hint
(clustered index scan) and different w/o the hint. I am guessing it's
the order it gets the locks in which causes the problem, which I hope
is fixed in SP4.

Jul 23 '05 #3
hendershot (ey****@yahoo.c om) writes:
just tried on a dual processor machine and it still deadlocked. adding
an index=1 hint to the window 2 select fixes the deadlock problem.
looking at the estimated execution plan for both select statements,
they are identical when the window 2 select uses the index hint
(clustered index scan) and different w/o the hint. I am guessing it's
the order it gets the locks in which causes the problem, which I hope
is fixed in SP4.


Thanks for pointing out the issue about the query plan. No, this is not
fixed in SP4, but this is simply a classical deadlock situation of
processes locking resources in different order. Your solution of adding
an index hint is also the classical solution to that problem: make sure
that processes are accessing the resources in the same order.

Here is another way to illustrate the scenario. First create this function
in tempdb:

CREATE FUNCTION mydelay () RETURNS int AS
BEGIN
EXEC master.dbo.xp_c mdshell 'osql -E -Q"waitfor delay ''00:00:00.100' '"'
RETURN 1
END

Then in one window run:

select tempdb.dbo.myde lay(), * from Orders (updlock)
where EmployeeID = 1 order by OrderID desc

And in an other:

select tempdb.dbo.myde lay(), * from Orders (updlock)
where EmployeeID = 1 order by OrderID asc

For some extra experience, set output to text, and also, go into
the Control Panel and open the Sound applet and add a sound for Open
Program. (Select a short sound!). As you run the queries, you will see
the results come as well as hear the Open Program sound constantly.
Then the sound disappears, when the two queries meet in the middle of
the table. It's quiet for a couple of seconds, until the deadlock victim
passes out, and the other window completes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
thanks, I'll try that.

This seems like a major bug on SQL Servers part. in our app we don't
see this problem in Oracle and I've also seen some posts on the sybase
group about this being fixed in sybase.

Jul 23 '05 #5
hendershot (ey****@yahoo.c om) writes:
This seems like a major bug on SQL Servers part. in our app we don't
see this problem in Oracle and I've also seen some posts on the sybase
group about this being fixed in sybase.


Bug? I don't agree. Just because you don't get a deadlock in Oracle, does
not that it is a bug in SQL Server. The two engines are two very different
architectures, so what might work well in one engine, may work poorly in
the other.

I don't know Oracle, but that much I can tell: if two processes lock
resources in reverse order, they are going to deadlock in any multi-process
system, be that a DB engine or something else.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
if my app does things in reverse order and causes deadlocks it's poorly
designed and it's a bug, and it's something I would/should fix. why
can't that be applied to a DB engine (SQL Server) ?, where you can
have a single select and a single update deadlock each other
(http://www.windowsitpro.com/SQLServe...12/pg/1/1.html)

Jul 23 '05 #7
hendershot (ey****@yahoo.c om) writes:
if my app does things in reverse order and causes deadlocks it's poorly
designed and it's a bug, and it's something I would/should fix. why
can't that be applied to a DB engine (SQL Server) ?, where you can
have a single select and a single update deadlock each other

(http://www.windowsitpro.com/SQLServe...12/pg/1/1.html)

Believe me, that example is possible to recreate on about any DB engine,
at least as long as you use a higher isolation level than Read Committed,
and there is no snapshot being used. (In the original example you posted,
you used UPDLOCK, and thus snapshots are out.)

There are of course ways to avoid it - for always use table locks. In
that way you can never get a deadlock on a single table. But no sane
DBMS implementor would do that.

The fact that deadlocks can occur in a DB engine is a feature - high
concurrency. But how well that feature works, depends on how well you
design your application.

Apparently your real problem is not about Northwind Traders. Rather
than moaning and bitching, present the actual problem and see what
suggestions you can get from the crowd here. If the application does
something similar as your Northwind example, I'm curious why use all
those UPDLOCK.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8
the northwind example is a simple case of 2 different transactions
possibly wanting to update the same rows, the updlock is there to
prevent both from doing so at the same time and it is also prevents
deadlocks if an isolation level higher then read committed is used for
the transaction. in the example you would expect one of the
transactions to wait if the other has selected the same row for an
update, instead it deadlocks. I disagree that this is a common problem
that can be reproduced on any db engine.

Jul 23 '05 #9
hendershot (ey****@yahoo.c om) writes:
the northwind example is a simple case of 2 different transactions
possibly wanting to update the same rows, the updlock is there to
prevent both from doing so at the same time and it is also prevents
deadlocks if an isolation level higher then read committed is used for
the transaction. in the example you would expect one of the
transactions to wait if the other has selected the same row for an
update, instead it deadlocks.
Wrong. Not "instead". *Both* process waits for rows the other processes
has locked. And thus they deadlock.

And the responsibility to avoid this situation lies as you as the
programmer/DBA. What is happening that both proecesses that start to
reserve rows, but since they start in different end they deadlock.
SQL Server can't correlate what is going on - the queries are run
independently. So you need to design the queries so that they cannot
conflict. You probably need to use index hints - possibly sacrificing
the best plan for one of the query to avoid the deadlock.

Another alternative is to use application locks. Each access to the
table first gets an application lock with the table name as the
resource. (Or table name + partial-key value that is included in all
updates, if you know that updates for different partial keys cannot
clash). This means that the updates get perfectly serilized. But
since the best execution plans can be used, the updates are faster,
and there is less risk that you block readers.
I disagree that this is a common problem that can be reproduced on any db
engine.


Trust me. Two processes locking resources in different order causing a
deadlock is something you can achieve any an DB engine with multi-
process support. Ever wondered why DB engines have deadlock detection
at all?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

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

Similar topics

1
6069
by: Robert Brown | last post by:
I have a deadlock that's happening on one oracle instance but cannot be reproduced on any other. It is always caused by the same SQL statement colliding with itself and only happens under very high load. The statement is DELETE from userlogins WHERE numlogins <= 0 the schema for the userlogins table is
1
3996
by: dawatson833 | last post by:
I want to set an alert for a specific table whenever an event has caused a deadlock to occur on the table. I understand how to set up an alert. But I don't know which error number to use for the New Alert error number property for a deadlock. Or how to specify a deadlock on a specific table. Thanks, DW
3
7623
by: Nigel Robbins | last post by:
Hi There, I'm getting a deadlock when I have two clients running the following statement. DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid There is a compound index on file_uid / obj_uid. The isolation level is UR and I have set DB2_RR_TO_RS=YES. Any thoughts why I'm getting the deadlock ?
1
4238
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
6
3770
by: Todd McNeill | last post by:
Hi- We ran into some very strange deadlocks this AM, and I was hoping to get some insight. We were running a REORGCHK on a database, and started getting deadlocks. What is curious is that according to the deadlock event monitor, the deadlock was between the REORGCHK process and another client attempting a SQL execution, and the Prepare was requesting an Exclusive lock. Is that normal? Maybe I'm reading the monitor output...
15
9994
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.
1
3676
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
2
8960
by: Sumanth | last post by:
Hi , I am trying to acquire a lock on a table A in exclusive mode, and this statement gives an error indicating a deadlock or timeout has been detected. The lock timeout value is set to 0 which I understand is to wait for however long it takes to acquire a lock. Also there are other processes that have acquired row level exclusive locks on the table A when this error happened.Is DB2 throwing this error as a pre-emptive measure.
13
2540
by: Jonathan Amsterdam | last post by:
I think there's a slight design flaw in the Queue class that makes it hard to avoid nested monitor deadlock. The problem is that the mutex used by the Queue is not easy to change. You can then easily get yourself into the following situation (nested monitor deadlock): Say we have a class that contains a Queue and some other things. The class's internals are protected by a mutex M. Initially the Queue is empty. The only access to the...
0
8590
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, 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...
0
9070
Oralloy
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...
0
8947
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8781
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,...
0
8784
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 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...
0
7621
agi2029
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...
0
4321
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...
0
4543
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2214
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.