主页 / dba / 问题 / 126
Accepted
CoderHawk
Asked: 2011-01-04 23:14:08 +0800 CST

死锁的主要原因是什么，可以预防吗？

  • 772

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

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

后来我发现为部署编写数据库脚本的开发人员搞砸了。我添加了一个主键，问题就解决了。

我感到很高兴并回到我的项目中，并进行了一些研究以找出导致僵局的原因......

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

我知道这不是一个明确的结论，这就是我在这里发帖的原因......

  • 缺少主键是问题吗？
  • 除了（互斥、保持等待、无抢占和循环等待）之外，是否还有其他导致死锁的条件？
  • 如何防止和跟踪死锁？
sql-server sql-server-2008

6 个回答

  1. Best Answer

    跟踪死锁是两者中比较容易的：

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

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

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

    有关跟踪标志 1204 的讨论以及打开它时您将获得的输出，请参阅“故障排除死锁”。 https://msdn.microsoft.com/en-us/library/ms178104.aspx

    预防比较困难，基本上你必须注意以下几点：

    代码块 1 按顺序锁定资源 A，然后锁定资源 B。

    代码块 2 依次锁定资源 B，然后是资源 A。

    这是可能发生死锁的典型情况，如果两个资源的锁定都不是原子的，则代码块 1 可以锁定 A 并被抢占，然后代码块 2 在 A 获得处理时间之前锁定 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 之间的死锁

    从数据库的角度来看，我不确定如何防止这种情况，因为锁是由数据库本身处理的，即更新数据时的行/表锁。我看到的问题发生最多的地方是您在光标内看到的问题。游标是出了名的低效，尽可能避免使用它们。

    • 40

  2. 我最喜欢阅读和了解死锁的文章是： Simple Talk - 追踪死锁 和 SQL Server Central - 使用 Profiler 解决死锁。他们将为您提供有关如何处理糟糕情况的样本和建议。

    简而言之，为了解决当前的问题，我会缩短涉及的事务，从中取出不需要的部分，注意对象的使用顺序，看看实际需要什么隔离级别，而不是读取不需要的数据...

    但是最好阅读这些文章，它们的建议会更好。

    • 25

  3. 有时可以通过添加索引来解决死锁，因为它允许数据库锁定单个记录而不是整个表，因此您可以减少争用和事情被卡住的可能性。

    例如，在InnoDB中：

    如果您没有适合您的语句的索引并且 MySQL 必须扫描整个表以处理该语句，则表的每一行都会被锁定，这反过来又会阻止其他用户对表的所有插入。创建良好的索引很重要，这样您的查询就不会不必要地扫描许多行。

    另一个常见的解决方案是在不需要时关闭事务一致性，或者以其他方式更改隔离级别，例如，计算统计数据的长时间运行的作业......通常一个接近的答案就足够了，你不需要精确的数字，因为他们正在从你下面改变。如果需要 30 分钟才能完成，您不希望它停止这些表上的所有其他事务。

    ...

    至于跟踪它们，这取决于您使用的数据库软件。

    • 16

  4. 只是为了开发光标的东西。这确实很糟糕。它锁定整个表，然后一一处理行。

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

    在 while 循环中，将对循环中的每一行执行一次选择，并且一次锁定只会发生在一行上。表中的其余数据可供查询，从而减少发生死锁的机会。

    另外它更快。让你想知道为什么会有游标。

    这是这种结构的一个例子：

    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

    如果您的 ID 字段稀疏，您可能需要提取一个单独的 ID 列表并遍历它：

    DECLARE @IDs TABLE
    (
    Seq INT NOT NULL IDENTITY PRIMARY KEY,
    ID  INT NOT NULL
    )
INSERT INTO @IDs (ID)
    SELECT ID
    FROM Tbl
    WHERE 1=1  -- Criteria here

DECLARE @Rec     INT = 1
DECLARE @NumRecs INT = (SELECT MAX(Seq) FROM @IDs)
DECLARE @ID      INT
WHILE @Rec <= @NumRecs
    BEGIN
    SET @ID = (SELECT ID FROM @IDs WHERE Seq = @Seq)

    -- Do something to this row of the table

    SET @Seq += 1  -- Don't forget this part!
    END
    • 7

  5. 缺少主键不是问题。至少就其本身而言。首先，您不需要主数据库来拥有索引。其次，即使您正在执行表扫描（如果您的特定查询不使用索引，则必须发生这种情况，表锁本身不会导致死锁。写入进程将等待读取，而读取进程将等待写入，当然读取根本不必相互等待。

    添加到其他答案，事务隔离级别很重要，因为可重复读取和序列化是导致“读取”锁被持有直到事务结束的原因。锁定资源不会导致死锁。保持锁定确实如此。写操作始终保持其资源锁定，直到事务结束。

    我最喜欢的锁定预防策略是使用“快照”功能。Read Committed Snapshot 特性意味着读取不使用锁！如果您需要比“已提交读取”更多的控制，则可以使用“快照隔离级别”功能。这允许在不阻塞其他玩家的情况下发生序列化（此处使用 MS 术语）事务。

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

    • 6

  6. 虽然 SQL Server 中的游标速度很慢，但您可以通过将游标的源数据拉入 Temp 表并在其上运行游标来避免游标中的死锁。这可以防止游标锁定实际数据表，并且您获得的唯一锁定是用于在游标内执行的更新或插入，这些锁定仅在插入/更新期间而不是在游标期间保持。

    • -2

相关问题