同一张表上的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锁）

我的问题是为什么会发生这种情况。两个会话同时持有整个表的一个锁。我以为通常死锁是当两个或更多会话持有不同资源的锁并互相等待。但这里的锁是在相同的资源上。它不是索引上的锁，而是表上的锁。我们的应用程序中经常出现这种错误，某个锁必须是第一个被请求的，为什么如果已经有一个锁在整个表上，第二个锁会被接受？

有人能够给出提示有什么问题或者有没有经历过类似的死锁情况吗？

- John

---

这个表是一个带有一些非聚集索引的堆吗？等待资源不同。11:290100074:5与11:290100074:0。最终数字应该代表sys.indexes中的indexId根据此处。另外，为什么你的第二个事务被命名为INSERT EXEC，当它显然只执行了一个SELECT？在同一事务中之前是否有其他代码运行？ - Martin Smith

该表有一个聚集索引在一个列（id列）上。我不想呈现所有代码的错误日志，因此我重新命名了一切并删除了执行堆栈中除最后一个语句之外的一些内容。除了语句本身创建的事务外，没有任何语句在事务中运行。第二个事务是动态执行的字符串。它所做的是将一些记录插入到临时表中，如：“@insert into #temp exec sp_execute_sql @sql”，其中@sql是我在问题中提出的选择语句。 - John

我现在查了sys.index表。indexId 5指的是一个非聚集索引，但indexId 0不存在。这是否意味着11:290100074:0指代的是表本身？这可能解释了问题的一部分。插入需要锁定一个已经被某个select占用的索引。还有一个重要问题，为什么它锁定整个索引和整张表？插入只添加了一行数据，而且select子句不应该影响太多行。如果你没有代码访问权限，这可能是一个难以回答的问题，但任何可能的提示都将非常感激。 - John

不知道为什么单行插入会在整个索引上请求“X”锁。该索引的行锁和页锁是否已被禁用？ - Martin Smith

我将表格脚本编写为“创建”到一个新窗口，并且同时打开了ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS。 - John

Answer 1

经过更多的搜索和测试，我非常自信地能够回答自己的问题。我要感谢Martin Smith指出等待资源不同，让我找到了正确的方向。

正如Martin在评论中所写，等待资源是：11:290100074:0和11:290100074:5。 搜索后发现，如果在拥有16个或更多CPU的机器上运行Sql Server R2，则Sql Server可以使用称为lock partitioning的功能。

这篇文章说：

仅对单个分区获取NL、SCH-S、IS、IU和IX锁模式。

在我的情况下，spid 155在一行或一页上放置共享锁，因此在对象上放置一个旨在共享的锁，并且使用锁分区功能，这恰好发生在分区ID 5上。

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

必须在从分区ID 0开始并按照分区ID顺序的所有分区上获取共享（S），排他（X）和NL，SCH-S，IS，IU和IX以外模式的其他锁。

当它到达分区ID 5时，它被告知spid 155持有IS锁，并且需要等待该锁被释放。

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

我不能保证这是完全正确的答案，但我相信我是，如果不是100％正确，至少接近答案。

解决方案是什么？嗯。锁定分区功能无法关闭，但另一方面，您可以通过不同的事务级别和alter table语句中的不同选项来控制锁升级。

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

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

- John

Answer 2

“通常死锁是指两个或多个会话持有不同资源的锁并相互等待”，这并不总是正确的——还存在转换死锁。即使两个进程只竞争一个资源，它们仍然可能陷入转换死锁中 （我在这里描述了这种情况）。

此外，尽管最常见的死锁场景涉及两个连接以不同的顺序修改两个表，但还存在其他只涉及一个表的死锁场景。此外，在某些场景中，每个连接只需要发出一条语句就足以导致死锁。在某些场景中，只有一个连接需要修改或获取独占锁，另一个连接可能仅读取数据并获取共享锁，并仍然会陷入死锁。 另外一件事：回答这个“没有查询运行在事务中”的评论 - 每个DML语句总是在一个事务中运行，而DML也包括选择。死锁中涉及的所有命令都在事务上下文中运行。跟随第二个链接，并运行可重现脚本 - 你会亲眼看到的。

无论如何，我只会在快照隔离下运行选择 - 这将防止发生这种特定的死锁（当一个连接只读取时）。

- A-K

---

您的示例是否可以使用读取提交隔离级别进行复现？您可能会遇到这个问题，是因为示例中的选择语句由于 SERIALIZABLE 级别持有锁直到事务结束。顺便提一下，只有我的语句之一是修改语句（插入），而且没有任何查询运行在事务中。 - John

Answer 3

这是由于您的锁定策略过于简单，遇到这种情况会对您造成影响。

例如：如果您选择了足够糟糕的锁级别，则会获得读取锁并且不能将其更新为写入锁，而另一个读取锁在使用中，如果两个应用程序都这样做...您可能会出现完全相同的行为（进程1获取读取锁，2获取读取锁，1想要更新为写入锁（等待），2想要更新为写入锁-死锁）。

在您的特定情况中，似乎读取将设置读取锁，而插入操作确实会使人们决定进行更新，但在需要插入的锁上爆炸（是的，您可以有一个阻止插入的锁）。

我们的应用程序经常出现此错误，必须首先请求某些锁定，为什么整个表上已经存在锁定时还接受第二个锁定？

这是初学者设计问题。该问题发生是因为某些锁是共享的（主要是读取锁），允许其他读取锁被建立。如果您允许这样做，我建议确保不会发生死锁。要么不让读取锁离开锁（使用NOLOCK），要么更早地获取适当的写入锁。

- TomTom

---

好的。但有一件事我不明白。在执行插入操作时（只是一个简单的插入语句），查询是否会立即请求独占锁呢？或者至少会请求更新锁并将其转换为独占锁吗？在我的情况下，插入已经请求了独占锁并被授予了。如果同时存在已授予的共享锁，它就不应该被授予。无论您的锁设计是否有问题，SQL Server都不应该在完全相同的资源上授予两个冲突的锁。为什么要提前请求写锁，我直到插入时才需要它..？ - John

不，这完全取决于您的隔离级别。这是非常微调的，您那边的某个人确实进行了调整（默认为串行化事务）。显然，调整者与开发人员不同步，并且做得很糟糕。这也可能是锁传播失败的原因。Upasert 不是插入操作 - 它是需要选择以进行插入或更新的插入或更新操作，这可能会使读取锁保持不变。 - TomTom

我不确定你在这里是正确的。默认的隔离级别是读提交，这是我到处都看到的。此外，该语句是一个插入语句。它在逻辑流程中早期就决定是否更新或插入，并且它不会从任何表中读取以做出决定。这取决于过程的参数。不要关注过程的名称。因此，我仍然不理解。此外，为什么它要锁定整个表格来插入一行？这对我来说也很奇怪。 - John