最近,我们的一个ASP.NET应用程序显示了一个数据库死锁错误,要求我检查并修复该错误。我设法找到导致死锁的原因是存储过程正在严格更新游标中的表。

这是我第一次看到此错误并且不知道如何跟踪并有效地修复它。我尝试了所有可能的方法,最后发现正在更新的表没有主键!幸运的是,这是一个身份专栏。

我后来发现开发人员编写了用于部署脚本的数据库。我添加了主键,问题就解决了。

我感到很高兴,回到了我的项目,并进行了一些研究以找出造成僵局的原因...

显然,这是一个循环等待条件,导致死锁。没有主键的更新显然要比使用主键的更新花费更长的时间。

我知道这不是一个明确定义的结论,这就是为什么我要在此处发布...


缺少主键是问题吗?
是否还有其他导致死锁的条件(互斥,保留和等待,没有抢占和循环等待)?
如何防止和跟踪死锁?


评论

我见过的IME多数(全部?)死锁是由于循环等待(主要是由于过度使用触发器)引起的。

循环性是僵局的必要条件之一。如果所有会话都以相同顺序获取锁,则可以避免任何死锁。

#1 楼

跟踪死锁是两者中的一个比较容易:



默认情况下,死锁不会写入错误日志中。您可以使SQL使用跟踪标志1204和3605将死锁写入错误日志。

将死锁信息写入SQL Server错误日志:
DBCC TRACEON(-1,1204,3605)

将其关闭:
DBCC TRACEOFF(-1,1204,3605)

有关跟踪标志1204和输出的讨论,请参见“疑难解答”。
https://msdn.microsoft.com/zh-cn/library/ms178104.aspx


预防实际上比较困难,必须注意以下事项:

代码块1依次锁定资源A,然后锁定资源B。

代码块2锁定资源B,然后锁定资源A,

这是可能发生死锁的经典条件,如果两个资源的锁定都不是原子的,则代码块1可以锁定A并被抢占,然后代码块在A获得处理时间之前2个锁B。现在您已陷入僵局。

要防止这种情况,您可以执行以下操作

代码块A(伪代码)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...


代码块B(伪代码)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...


完成A和B操作后,别忘了对其进行解锁

从数据库的角度来看,这将防止代码块A和代码块B之间的死锁。

我不确定如何防止这种情况,因为锁是由数据库本身处理的,即更新数据时,行/表锁定。我看到最多的问题是在光标内看到问题的位置。众所周知,游标的效率很低,请尽可能避免使用它们。

评论


您是要在代码块B中将资源A锁定在资源B之前吗?如所写,这将导致死锁..正如您自己在之前的评论中提到的。即使在开始时需要虚拟查询来确保锁定顺序,也要始终以相同的顺序锁定资源。

– Gerard ONeill
19 Mar 5 '19 at 3:38

#2 楼

我最喜欢阅读和了解死锁的文章包括:
简单谈话-追踪死锁

SQL Server Central-使用探查器解决死锁。
它们将为您提供示例和关于如何处理糟糕情况的建议。

总而言之,为了解决当前的问题,我会缩短所涉及的交易,从中删除不需要的部分,并按顺序处理使用对象,查看实际需要的隔离级别,而不是读取不需要的数据...

但是更好地阅读文章,它们将在建议中变得更好。

#3 楼

有时可以通过添加索引来解决死锁,因为它使数据库可以锁定单个记录而不是整个表,因此可以减少争用和事物阻塞的可能性。

例如InnoDB:


如果您没有适合您的语句的索引,并且MySQL必须扫描整个表以处理该语句,则表的每一行都将被锁定,从而依次阻止所有插入其他用户到表。创建良好的索引很重要,这样您的查询才不会不必要地扫描很多行。


另一个常见的解决方案是在不需要事务时关闭事务一致性,否则将更改隔离级别,例如,一项长期工作来计算统计信息...一个简单的答案通常就足够了,您不需要精确的数字,因为它们在您的下方不断变化。而且如果需要30分钟才能完成,则您不希望它停止这些表上的所有其他事务。


跟踪它们取决于您正在使用的数据库软件。

评论


降低投票率时通常会提供评论...这是一个有效的答案,将select语句升级为表锁并且永久使用肯定会导致死锁。

– BlackICE
2011年1月4日19:42

如果索引不是群集的,则MS SQLServer还可以提供意外的锁定行为。它会默默地忽略您使用行级锁定的方向,并将执行页级锁定。然后,您可以在页面上等待死锁。

–杰伊
13年7月9日在20:43

#4 楼

只是为了开发游标而已。
确实确实很糟糕。它会锁定整个表,然后一个一行地处理行。

最好使用while循环以游标的形式遍历行

在while循环中,将对循环中的每一行执行一次选择,此时锁定将仅发生在一行上。该表中的其余数据可自由查询,从而减少了发生死锁的可能性。

此外它还更快。让您怀疑为什么仍然存在游标。

下面是这种结构的示例:您可能要提取一个单独的ID列表并进行迭代:

DECLARE @LastID INT = (SELECT MAX(ID) FROM Tbl)
DECLARE @ID     INT = (SELECT MIN(ID) FROM Tbl)
WHILE @ID <= @LastID
    BEGIN
    IF EXISTS (SELECT * FROM Tbl WHERE ID = @ID)
        BEGIN
        -- Do something to this row of the table
        END

    SET @ID += 1  -- Don't forget this part!
    END


#5 楼

缺少主键不是问题。至少是自己。首先,您不需要主数据库即可拥有索引。其次,即使您正在执行表扫描(如果您的特定查询未使用索引也必须进行表扫描,表锁本身也不会导致死锁。写入过程将等待读取,读取过程将等待读取

再加上其他答案,事务隔离级别很重要,因为可重复的读取和序列化是导致“读取'锁将一直保持到事务结束。锁定资源不会导致死锁;保持资源锁定不会导致死锁。写入操作始终将其资源锁定到事务结束。

我最喜欢的锁定预防策略是使用“快照”功能,“读取已提交的快照”功能意味着读取不使用锁!如果您需要的控制比“读取已提交”更多,则可以使用“快照隔离级别”功能。允许进行序列化(此处使用MS术语)的事务,而不会阻止其他玩家。

最后,可以使用更新锁来防止一类死锁。如果您读取并保持读取(HOLD,或使用重复读取),并且另一个进程执行相同的操作,那么两者都尝试更新相同的记录,则将出现死锁。但是,如果两者都请求更新锁,则第二个进程将等待第一个进程,同时允许其他进程使用共享锁读取数据,直到实际写入数据为止。如果其中一个进程仍请求共享的HOLD锁,那么这当然将不起作用。

#6 楼

尽管在SQL Server中游标速度很慢,但是可以通过将游标的源数据拉入Temp表并在其上运行游标来避免游标死锁。这可以使光标避免锁定实际数据表,并且您获得的唯一锁定仅是针对在光标内部执行的更新或插入的,这些锁定仅在插入/更新期间保持,而不在光标持续期间保持。 >