设计无SQL死锁的编码模式-AchievingSQLDeadlock-FreeDesign

在我运行于MS SQL Server 2005的.NET 2.0 Web应用中，偶尔会遇到令人头疼的SQL死锁问题。过去，我们主要通过调整查询来解决这些问题，但这既耗时又不可靠。我希望找到一种确定性的查询模式，确保从设计上彻底避免SQL死锁。

例如，在C#多线程编程中，一个简单的设计规则是按字典顺序获取锁，这可以确保不会发生死锁。那么，SQL中是否有类似的编码模式可以保证无死锁呢？

解决方案

#1

编写防死锁代码非常困难。即使以相同的顺序访问表，仍然可能会遇到死锁。我在博客中详细讨论了一些方法，可以帮助避免和解决死锁情况。如果您希望确保两个语句/事务不会死锁，可以使用sp_lock系统存储过程观察每个语句使用的锁，但必须非常迅速或使用带有HOLDLOCK提示的开放事务。

#2

在一般情况下，零死锁是一个非常昂贵的问题，因为您必须知道每个运行事务将读取和修改的所有表/对象（包括SELECT语句）。常见的解决方法是有序严格的两阶段锁（2PL），但即使是2PL也不能保证无死锁。很少有DBMS实现严格的2PL，因为这会导致大量性能损失。您可以考虑调整SQL Server中的隔离级别，例如使用快照隔离或读未提交隔离级别。

#3

没有通用的解决方案可以完全避免死锁。您可以将并发控制移到应用程序层面，但这可能非常复杂，尤其是需要与其他程序协调时。一些减少死锁机会的一般方法包括：基本查询优化、热点回避设计、尽量缩短事务时间、设置合理的查询超时等。

#4

在某些限制条件下，以下读/写模式可以防止死锁：

• 约束：
  • 一个表
  • 使用索引或主键进行读/写，避免表锁
  • 可以使用单个SQL WHERE子句读取一批记录
  • 使用SQL Server术语
• 写周期：
  • 所有写操作在一个“读提交”事务中
  • 事务中的第一个更新是针对每个更新组中特定的、始终存在的记录
  • 随后可以以任何顺序写入多个记录
• 读周期：
  • 默认读提交事务级别
  • 无事务
  • 将记录作为一个选择语句读取
• 好处：
  • 二级写周期在第一个记录的写入过程中被阻塞，直到第一个写事务完全完成
  • 在写提交之间，读取被阻塞/排队/自动执行
  • 无需使用“可序列化”即可实现事务级别的一致性

#5

始终以相同的顺序访问表是避免死锁的好方法。此外，尽可能缩短事务时间。另一个技巧是尽可能将两个SQL语句组合成一个。单个语句总是事务性的。例如，使用“UPDATE ... SELECT”或“INSERT ... SELECT”，使用“@@ERROR”和“@@ROWCOUNT”代替“SELECT COUNT”或“IF (EXISTS ...)”。最后，确保调用代码可以通过重新发布查询的可配置次数来处理死锁。

#6

除了一致的锁获取顺序，还可以显式使用锁和隔离提示，以减少在读取过程中无意地获取锁（如共享意图）所浪费的时间/资源。

#7

对于SQL Server，许多锁定问题可以通过正确的覆盖索引来消除。这是因为它可以大大减少对表的聚集索引的书签查找，从而减少争用和锁定。

#8

如果您对应用程序有足够的设计控制，可以将更新/插入限制在特定的存储过程中，并从应用程序使用的数据库角色中删除更新/插入权限（仅显式允许通过这些存储过程进行更新）。将数据库连接隔离到应用程序中的特定类（每个连接必须来自这个类），并指定“仅查询”连接将隔离级别设置为“脏读”。

#9

没有绝对保证的技术可以避免死锁。如果您预先锁定流程中可能需要的所有资源，即使最终不需要这些资源，也可能导致第二个进程等待获取所需的第一个锁，从而影响可用性。解决SQL死锁问题的最佳方法是查看分析器中的工作负载并理解其行为。

#10

虽然不是直接回答您的问题，但“用餐哲学家问题”是一个经典的死锁问题思想实验。阅读相关内容可能会帮助您找到解决特定问题的方法。