在SQL Server里如何处理死锁

时间:2023-03-08 15:37:19
在SQL Server里如何处理死锁

在今天的文章里,我想谈下SQL Server里如何处理死锁。当2个查询彼此等待时会发生死锁,没有一个查询可以继续它们的操作。首先我想给你大致讲下SQL Server如何处理死锁。最后我会展示下SQL Sever里特定的死锁类型,还有你如何避免和解决它们。

死锁处理

死锁的好处是SQL Server自动检测并解决它们。为了解决死锁,SQL Server需要回滚2个事务中最便宜的那个。在SQL Server上下文中,最便宜的事务是写入事务日志更少字节的那个。

SQL Server在后台进程中实现死锁检测称为死锁监控(Deadlock Monitor)。这个后台进程每5秒钟运行一次,为死锁检查当前锁定情况。在最坏的情况中,因此一个死锁不应该超过5秒。这个查询会回滚并收到1205错误号。死锁的好事是你可以完整从错误情况下还原,不需要用户的任何干预。一个聪明的开发者必须按下列步骤来从死锁中恢复:

  • 当异常抛出时,检查1205错误号
  • 暂时停止程序,给其他查询时间来完成它的事务并释放它获得的锁
  • 重新提交查询,即被SQL Server回滚的。

重新提交查询后,这个查询应该继续执行,没有任何问题,因为其它查询已经完成它的事务。当然你应该保持再次发生死锁的跟踪,这样的话,你不用反复重试你的事务。

你可以用多种方法来故障排除死锁。SQL Server Profiler提供Deadlock Graph事件,一旦死锁检测到就会发生。如果你在SQL Server 2008或更高,你可以使用故障排除来故障排除死锁场景。扩展事件提供你system_health事件会话,它跟踪自SQL Server上次重启后发生过的死锁。还有启用1222跟踪标记,SQL Server会把死锁信息写入错误日志。

死锁类型

在SQL Server里会发生各种类型的死锁。在这一部分我想进一步谈下最常见的几个。

几乎每个SQL Server我看到最典型的的死锁是著名的书签查找死锁,当你有同时对聚集和非聚集索引读写活动时发生。它基本上是因为不好的索引设计造成的。在我作日常SQL Server的故障排除重,我可以说所有的死锁,至少有90%可以通过更好的索引设计来解决。书签查找死锁可以通过提供覆盖非聚集索引轻松解决。

另一个常见的死锁是所谓的循环死锁(Cycle Deadlock),这里你的每个查询用不同的顺序访问表。为了避免这个特定的死锁,你要确保每个查询用同样的顺序访问表。在SQL Server里会发生的最有意思的死锁是所谓的内部并行死锁(Intra-Parallelism Deadlock),这里平行的运算符在各自的线程内部死锁。下图展示了一个典型死锁图。

在SQL Server里如何处理死锁

图片本身就是一个艺术品,它因触发SQL Server里的BUG而发生。遗憾的是,这个BUG不会被微软修正,因为它引入回归的可能性。因此你要确保引起这个死锁的查询,要在SQL Server里单线程运行。你可以通过多个选项来实现单线程执行:

  • 在你的索引策略上下功夫,这样的话查询的花费会在当前并行阈值下(默认为5)
  • 使用MAXDOP 1查询提示,来提示SQL Server以单线程运行你的问题查询

另一个死锁的特效疗法是启用乐观并发,尤其是提交读快照隔离(Read Committed Snapshot Isolation (RCSI)),他对你的程序是完全透明的。使用乐观并发,共享锁消失,这意味着在SQL Server里你可以避免大量的典型锁。

小结

死锁是SQL Server通过回滚最便宜的事务自动处理。然而你要尽可能小的确保死锁,因为每个回滚的死锁都会给你的终端用户带来不好的影响。死锁可以通过好的索引策略来避免,另外使用乐观并发也是应付它们的特效药。

感谢关注!

原文链接

https://www.sqlpassion.at/archive/2017/02/20/how-to-handle-deadlocks-in-sql-server