SQL Server在同一张表上的死锁 [英] SQL Server deadlock on the same table
问题描述
我们在应用程序中遇到死锁情况。最近几天，我读了很多有关阻塞，锁定和死锁的文章，试图了解该问题以便解决。
We have problems with deadlock situations in our application. I have read a lot about blocking, locking and deadlocks the last few days to try to get an understanding about the problem in order to solve it.
现在，当我阅读有关死锁的错误日志信息，我无法理解这种情况如何存在。看一下（我已经重命名了表名，但重要的是在日志消息中称为OurTable的表名）：
Now when I read the error log information about the deadlocks I can't understand how this situation can exist. Look at this (I have renamed the table names but the important one is the one called OurTable in the log message):
deadlock-list
deadlock victim=process1e2ac02c8
process-list
process id=process1e2ac02c8 taskpriority=0 logused=0 waitresource=OBJECT: 11:290100074:0 waittime=704 ownerId=3144354890 transactionname=SELECT lasttranstarted=2011-12-01T14:43:20.577 XDES=0x80017920 lockMode=S schedulerid=6 kpid=7508 status=suspended spid=155 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-12-01T14:43:20.577 lastbatchcompleted=2011-12-01T14:43:20.577 clientapp=.Net SqlClient Data Provider hostname=DE-1809 hostpid=5856 loginname=2Ezy isolationlevel=read committed (2) xactid=3144354890 currentdb=11 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=.dbo.RetrieveSomething line=23 stmtstart=1398 stmtend=3724 sqlhandle=0x03000b0030d42d645a63e6006a9f00000100000000000000
select
Col1
,Col2
,(
SELECT TOP(1)
Col1
FROM
OurTable2 AS C
JOIN OurTable AS ETC ON C.Id = ETC.FKId
AND E.Id = C.FKId
ORDER BY ETC.Col2
) AS Col3
from OurTable3 AS E
process id=process2df4894c8 taskpriority=0 logused=0 waitresource=OBJECT: 11:290100074:0 waittime=9713 ownerId=3144330250 transactionname=INSERT EXEC lasttranstarted=2011-12-01T14:43:11.573 XDES=0x370764930 lockMode=S schedulerid=13 kpid=4408 status=suspended spid=153 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2011-12-01T14:43:11.573 lastbatchcompleted=2011-12-01T14:43:11.573 clientapp=.Net SqlClient Data Provider hostname=DE-1809 hostpid=5856 loginname=2Ezy isolationlevel=read committed (2) xactid=3144330250 currentdb=11 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000ba6cb42612240bdb19f7303e279a714276c04344
select
Col1
, Col2
, Col3
, ISNULL(
(select top(1)
E_SUB.Col1 + ' ' + E_SUB.Col2
from OurTable3 as E_SUB
inner join OurTable2 as C on E_SUB.Id = C.FKId
inner join OurTable as ETC on C.Id = ETC.FKId
as Col3
from OurTable4
inner join dbo.OurTable as ETC on Id = ETC.FKId
process id=process8674c8 taskpriority=0 logused=0 waitresource=OBJECT: 11:290100074:5 waittime=338 ownerId=3143936820 transactionname=INSERT lasttranstarted=2011-12-01T14:38:24.423 XDES=0x1ecd229f0 lockMode=X schedulerid=7 kpid=12092 status=suspended spid=124 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-12-01T14:38:23.027 lastbatchcompleted=2011-12-01T14:38:23.013 clientapp=.Net SqlClient Data Provider hostname=DE-1809 hostpid=5856 loginname=2Ezy isolationlevel=read committed (2) xactid=3143936820 currentdb=11 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=.dbo.UpsertSomething line=332 stmtstart=27712 stmtend=31692 sqlhandle=0x03000b00bbf2a93c0f63a700759f00000100000000000000
insert into dbo.OurTable
(
Col1
,Col2
,Col3
)
values
(
@Col1
,@Col2
,@Col3
)
resource-list
objectlock lockPartition=0 objid=290100074 subresource=FULL dbid=11 objectname=dbo.OurTable id=lock16a1fde80 mode=X associatedObjectId=290100074
owner-list
waiter-list
waiter id=process1e2ac02c8 mode=S requestType=wait
objectlock lockPartition=0 objid=290100074 subresource=FULL dbid=11 objectname=dbo.OurTable id=lock16a1fde80 mode=X associatedObjectId=290100074
owner-list
owner id=process8674c8 mode=X
waiter-list
waiter id=process2df4894c8 mode=S requestType=wait
objectlock lockPartition=5 objid=290100074 subresource=FULL dbid=11 objectname=dbo.OurTable id=lock212f0f300 mode=IS associatedObjectId=290100074
owner-list
owner id=process1e2ac02c8 mode=IS
waiter-list
waiter id=process8674c8 mode=X requestType=wait
我的阅读方式是：
spid 155正在等待OurTable
上的共享表锁（spid 124拥有冲突的X锁）
spid 155 is waiting for a Shared table lock on OurTable (spid 124 holds a conflicting X lock)
spid 153正在等待OurTable
（spid上的共享表锁） 124拥有冲突的X锁）
spid 153 is waiting for a Shared table lock on OurTable (spid 124 holds a conflicting X lock)
spid 124正在等待OurTable
上的互斥表锁（spid 155拥有冲突的IS锁）
spid 124 is waiting for an Exclusive table lock on OurTable (spid 155 holds a conflicting IS lock)
我的问题是这怎么发生。两个会话同时在整个表上拥有一个锁。我以为通常的死锁是两个或两个以上的会话在不同的资源上持有锁并互相等待。但是这里的锁在同一资源上。它不是对索引的锁定，而是对表的锁定。此错误在我们的应用程序中很常见，某些锁必须是要请求的第一个锁，如果整个表上已经有一个锁，为什么还要接受第二个锁？
My question is how this can happen. Two sessions holds one lock on the whole table at the same time. I thought that a usual deadlock is when two ore more sessions hold locks on different resources and wait for each other. But here the lock is on the same resource. It is not a lock on an index but on the table. This error is frequent in our application and some lock has to be the first one to be requested and why is the second lock accepted if there already is a lock on the entire table?
有人能暗示什么是错的，或者有人遇到过类似的僵局？
Anyone who can give a hint of what can be wrong or anyone experienced a similar deadlock?
推荐答案
搜索和测试我非常有信心我可以为自己的问题给出正确的答案。
After a little bit more searching and testing I am pretty confident I can give the correct answer to my own question.
我要感谢Martin Smith，他指出了正确的方向
I have to thank Martin Smith who put me in the right direction by pointing out that the wait resources were different.
正如马丁在他的评论中所写，等待资源是：11：290100074：0和11：290100074：5。
搜索后发现，如果在具有16个或更多CPU的计算机上运行Sql Server R2，则Sql Server可以使用称为锁定分区。
As Martin wrote in his comment the wait resources are: 11:290100074:0 and 11:290100074:5. After searching this it turns out that if you run Sql Server R2 on a machine with 16 CPUs or more Sql Server is able to use a feature called lock partitioning.
本文除其他外说：
在单个
分区上仅获取NL，SCH-S，IS，IU和IX锁定模式。
Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition.
在我的情况下，发生的情况是spid 155在行或页上放置了共享锁，因此在对象上放置了预期的共享锁，并具有锁分区功能碰巧是在分区ID 5上。
What happens in my case is that spid 155 puts a shared lock on a row or page and therefor puts an intended shared lock on the object and with the lock partition feature this happens to be on partition id 5.
同时，spid 124需要用排他锁来锁定整个对象，因此需要在所有分区上加X锁。
At the same time spid 124 needs to lock the full object with an exclusive lock and therefor needs to put X lock on all partitions.
在NL，
SCH-S，IS以外的模式下共享（S），排他（X）和其他锁，IU和IX必须在所有从w开始的分区上获取就是
分区ID 0，然后按照分区ID顺序。
Shared (S), exclusive (X), and other locks in modes other than NL, SCH-S, IS, IU, and IX must be acquired on all partitions starting with partition ID 0 and following in partition ID order.
到达分区ID 5时，将告诉spid 155拥有一个IS锁，它需要等待直到该锁被释放。
When it arrives at partition id 5 it is told that spid 155 holds an IS lock and it needs to wait until that lock is released.
现在，当spid 124正在等待释放IS锁时，锁升级，它要求在表上共享锁。这意味着它需要将S锁放在从id 0开始的所有分区上。但是在id 0上它立即撞墙，因为spid 124已经在该分区上拥有排他锁。那就是造成僵局的原因。
Now when spid 124 is waiting on the IS lock to be released lock escalation occurs on spid 155 and it requests a shared lock on the table. This means it needs to put S lock on all partitions starting at id 0. But immediately on id 0 it hits the wall because spid 124 already holds an exclusive lock on that partition. And there you have the cause of the deadlock.
我不能保证100％这是确切的答案，但是我很确定我是正确的，即使不是100％正确，至少接近答案。
I can not guarantee 100% this is the exact answer but I am pretty sure I am, if not 100% right, at least close to the answer.
解决方案？好。锁分区功能无法关闭，但另一方面，您可以控制锁升级具有不同的事务级别，并且在alter table语句中具有不同的选项。
The solution? Well. The lock partition feature can not be turned off but on the other hand you can control lock escalation with different transaction levels and also different options in the alter table statement.
我将继续调查为什么查询强制锁定升级的原因，因为我相信解决方案我的特殊情况是调整查询以某种方式不升级。至少我会在使用上述工具之前尝试一下。
I will continue to investigate why the query forces lock escalation because I believe the solution in my particular case is to tune the query somehow to not escalate. At least I will try this before using the tools mentioned above.
希望这个答案可以帮助其他遇到类似问题的人。
Hope this answer helps other with similar problems.
这篇关于SQL Server在同一张表上的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
