why does this deadlock?

[hendershot]

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

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[hendershot]

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.

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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_cmdshell 'osql -E -Q"waitfor delay ''00:00:00.100''"'
RETURN 1
END

Then in one window run:

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

And in an other:

select tempdb.dbo.mydelay(), * 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[hendershot]

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.

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[hendershot]

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)

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[hendershot]

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.

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[hendershot]

>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.

[Erland Sommarskog]

hendershot (ey****@yahoo.com) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp