为什么这个僵局？ [英] why does this deadlock?
问题描述
使用SQL Server 2000 SP3a，我在Northwind数据库中运行以下2个查询分析器
windows，第二个总是得到
死锁消息1205：
窗口1：
声明@cnt int
选择@cnt = 5
而@cnt> 0
开始
开始交易
从订单中选择*（updlock）其中employeeid = 1
更新订单设置employeeid = 1其中employeeid = 1
waitfor delay''00：00：03''
commit
select @cnt = @cnt - 1
结束
窗口2：
声明@cnt int
选择@cnt = 5
而@cnt> 0
开始
开始交易
从订单中选择*（updlock）其中employeeid = 1且customerid =
''ERNSH''
waitfor delay''00：00：02''
commit
select @cnt = @cnt -1
结束
第一个窗口中的查询得到123行并将更新锁定放在
它们上，然后更新它们并提交。第二个窗口中的查询
得到一个子集（大约5）的结果，窗口1也会尝试
将更新锁定放在相同的行上。不应该在窗口2中查询
只是等待窗口1中的事务完成？为什么会这样？
僵局？
你也可以摆脱第二个窗口的延迟而且它会更快地死锁b $ b 。
提前感谢。
Eugene
hendershot（ey****@yahoo.com）写道：使用SQL Server 2000 SP3a，我在Northwind数据库的2个查询分析器窗口中运行以下命令，第二个总是得到
死锁消息1205：
我无法重现这个。然后，在这么晚的时候我可以找到一台运行8.00.760（SP3a）的机器。我在8.00.2026
（SP4测试版）和8.00.818（一个安全修补程序）上运行你的repro，我没有得到任何僵局
。这可能是自SP3a的
版本以来已经纠正的错误。 （嗯，想知道处理器的数量是否重要？
我认为我试过的两台机器都有不止一个CPU。）
请看这里获取信息SP4测试版： http://support.microsoft.com/kb/290211
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
刚尝试使用双处理器机器，它仍处于死锁状态。添加
索引= 1提示窗口2选择修复死锁问题。
查看两个选择语句的估计执行计划，
当窗口2选择使用索引提示
（聚簇索引扫描）和不同提示时，它们是相同的。我猜它是'
它获得锁定导致问题的顺序，我希望
在SP4中得到修复。
hendershot（ey****@yahoo.com）写道：刚刚在双处理器机器上试过，它仍然死锁。添加
一个index = 1提示到窗口2选择修复死锁问题。
查看两个select语句的估计执行计划，
当window 2 select使用索引时它们是相同的提示
（聚集索引扫描）和不同的提示。我猜它是锁定导致问题的顺序，我希望在SP4中修复它。
感谢您指出有关查询计划的问题。不，这不是固定在SP4中的
，但这只是一个典型的死锁情况
按不同顺序处理锁定资源。您添加
索引提示的解决方案也是该问题的经典解决方案：确保进程以相同的顺序访问资源
。
这是另一种说明方案的方法。首先在tempdb中创建这个函数
：
CREATE FUNCTION mydelay（）RETURNS int AS
BEGIN
EXEC master.dbo.xp_cmdshell''osql -E -Q" waitfor delay''''00：00：00.100''''''''
返回1
结束
然后在一个窗口中运行：
从Orders（updlock）中选择tempdb.dbo.mydelay（），* br />
其中EmployeeID = 1订单由OrderID desc
在另一个：
select tempdb.dbo.mydelay （），*来自订单（updlock）
其中EmployeeID = 1订单由OrderID asc
为了获得额外的体验，请将输出设置为文本，并且，进入
控制面板并打开声音小程序并为Open
程序添加声音。 （选择一个短音！）。当您运行查询时，您将看到
结果以及不断听到Open Program声音。
然后声音消失，当两个查询在中间的
表。它安静了几秒钟，直到死锁受害者
消失，另一个窗口完成。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
适用于SQL Server SP3的联机丛书
http ：//www.microsoft.com/sql/techinf...2000/books.asp
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
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
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.
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
这篇关于为什么这个僵局？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
