473,836 Members | 2,079 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
11 2527
>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?


yes, 2 processes locking resources in different order causing deadlock
can be achieved on any db engine, but in this case it's not the user
locking things in different order it's the db engine, what I am saying
is that other db engines don't seem to do this, if the user does things
in the same order there are no worries.

Jul 23 '05 #11
hendershot (ey****@yahoo.c om) writes:
yes, 2 processes locking resources in different order causing deadlock
can be achieved on any db engine, but in this case it's not the user
locking things in different order it's the db engine, what I am saying
is that other db engines don't seem to do this, if the user does things
in the same order there are no worries.


No, you don't get it. You submit two queries that potentially can conflict.
And if you look at the indexes for the table, and what plans that would
be best for the individual queries, you will find that there is a risk for
a deadlock.

Could SQL Server detect this? No. SQL Server runs queries independently.
And typically it runs the queries from a cached query plan (like any
other normal DB engine would do). To avoid the risk for deadlock, SQL
Server would have to choose a different plan. But how would SQL Server
know that in this precise moment is there a risk for deadlock? By
analysing queries? Far too expensive. Could SQL Server always generate
plans that are deadlock-safe? Sure, big fat table locks at least sorts
out intra-table deadlocks. But the throughput in a DB engine that did
this would be unacceptable.

DB engines is a lot about concurrency. The more concurrency you can
have, the better throughtput you can get. But the more concurrency you
permit, the more windows for deadlocks you get.

If you have a real problem that needs a solution, please post the details,
so we can look into a good solution.

--
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 #12

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

Similar topics

1
6080
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
4004
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
7632
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
4246
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
3774
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
10019
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
3691
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
8971
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
2555
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
9818
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
10844
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...
1
10590
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
10254
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
9374
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...
1
7791
isladogs
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...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5649
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
5825
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.