sql-server - SQL Server 在同一个表上发生死锁

Question

我们的应用程序中存在死锁问题。在过去的几天里，我阅读了很多有关阻塞、锁定和死锁的内容，试图了解问题并解决它。

现在，当我阅读有关死锁的错误日志信息时，我无法理解这种情况是如何存在的。看看这个(我已经重命名了表名，但重要的是日志消息中名为 OurTable 的表名):

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 153 正在等待 OurTable 上的共享表锁 (spid 124 持有冲突的 X 锁)

spid 124 正在等待 OurTable 上的独占表锁 (spid 155 持有冲突的 IS 锁)

我的问题是这是如何发生的。两个 session 同时持有整个表的一把锁。我认为通常的死锁是两个或多个 session 持有不同资源上的锁并互相等待。但这里的锁是在同一个资源上。它不是索引上的锁，而是表上的锁。这个错误在我们的应用程序中很常见，某些锁必须是第一个被请求的锁，如果整个表上已经有锁，为什么还要接受第二个锁？

任何人都可以提示可能出现的问题或任何人经历过类似的僵局吗？

Answer 1

经过更多的搜索和测试，我非常有信心能够为自己的问题给出正确的答案。

我必须感谢 Martin Smith，他指出等待资源是不同的，为我指明了正确的方向。

正如 Martin 在评论中所写，等待资源为:11:290100074:0 和 11:290100074:5。搜索后发现，如果您在具有 16 个或更多 CPU 的计算机上运行 Sql Server R2，则 Sql Server 能够使用名为 lock partitioning 的功能。 .

本文除其他外还提到:

Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition.

在我的例子中，spid 155 在行或页上放置了一个共享锁，因此在对象上放置了一个预期的共享锁，并且使用锁分区功能，这恰好位于分区 id 5 上。

同时spid 124需要使用排他锁锁定完整对象，因此需要在所有分区上放置X锁。

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 锁，并且需要等待该锁被释放。

现在当 spid 124 正在等待 IS 锁被释放时 lock escalation发生在 spid 155 上，它请求表上的共享锁。这意味着它需要在 id 0 开始的所有分区上放置 S 锁。但是在 id 0 上它立即就碰壁了，因为 spid 124 已经在该分区上持有排他锁。这就是僵局的原因。

我不能保证 100% 这是准确的答案，但我很确定我是，即使不是 100% 正确，至少也接近答案。

解决方案？出色地。锁定分区功能无法关闭，但另一方面您可以控制lock escalation具有不同的事务级别以及 alter table 语句中的不同选项。

我将继续调查为什么查询会强制锁定升级，因为我相信我的特定情况下的解决方案是以某种方式调整查询以使其不升级。至少在使用上述工具之前我会尝试一下。

希望这个答案可以帮助其他遇到类似问题的人。