这是我第一次看到此错误并且不知道如何跟踪并有效地修复它。我尝试了所有可能的方法,最后发现正在更新的表没有主键!幸运的是,这是一个身份专栏。
我后来发现开发人员编写了用于部署脚本的数据库。我添加了主键,问题就解决了。
我感到很高兴,回到了我的项目,并进行了一些研究以找出造成僵局的原因...
显然,这是一个循环等待条件,导致死锁。没有主键的更新显然要比使用主键的更新花费更长的时间。
我知道这不是一个明确定义的结论,这就是为什么我要在此处发布...
缺少主键是问题吗?
是否还有其他导致死锁的条件(互斥,保留和等待,没有抢占和循环等待)?
如何防止和跟踪死锁?
#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锁,那么这当然将不起作用。
评论
我见过的IME多数(全部?)死锁是由于循环等待(主要是由于过度使用触发器)引起的。循环性是僵局的必要条件之一。如果所有会话都以相同顺序获取锁,则可以避免任何死锁。