【实用技能】 SQL Server 中阻塞和死锁分别是什么？如何监视和解决阻塞和死锁问题？

在数据库性能优化方面，数据库管理员或开发人员必须了解 SQL Server 中阻塞和死锁之间的区别，因为这些概念通常需要澄清。阻塞和死锁有助于在处理并发事务时处理对共享资源的访问。但是，管理不当或对其行为缺乏了解可能会导致性能问题，例如处理事务失败和延迟。

在本文中，我们将探讨 SQL Server 中阻塞和死锁的基础知识以及这两个概念之间的区别。我们还将讨论防止阻塞问题和死锁的场景和方法。最后，我们将研究如何使用 dbForge Studio for SQL Server（试用下载）监视和解决阻塞和死锁问题。

阻塞和死锁简介

在任何数据库管理系统中，锁都可用于在并发访问数据库期间维护数据完整性。因此，控制并发事务至关重要。然而，锁的使用不当可能会带来诸如阻塞和死锁等问题，这会极大地影响数据库性能并导致数据库操作失败。

阻塞和死锁是帮助协调对共享资源的访问并确保事务一致性的锁定策略。

因此，对于数据库管理员和开发人员来说，深入了解这些机制非常重要。妥善处理阻塞和死锁可以优化数据库性能，同时保持数据的完整性和一致性。

SQL Server 中的阻塞是什么？

在 SQL Server 中，阻塞是指一个进程占用另一个进程所需的资源。在这种情况下，下一个进程必须等待资源可用。SQL Server 一次只允许一个进程使用资源，以保持数据的准确性和一致性。虽然阻塞是数据库中的预期行为，但其较长的等待时间会降低性能并导致延迟。

例如，两个事务（和）试图访问表中的同一行。想要更新一行但尚未提交，因此它对该行持有锁定。同时，尝试读取此行但必须等到释放其锁定。因此，在提交或回滚后，锁定将被解除，并且可以继续并返回结果。

1Transaction 2AccountsTransaction 1Transaction 2Transaction 1Transaction 1Transaction 2

如果在数据库级别启用了 READ COMMITTED SNAPSHOT 选项，则意味着基于快照的隔离已打开，这可以避免阻塞读取操作。因此，在这种情况下，事务 2 不会被事务 1 阻塞。

堵塞的常见原因

以下是一些可能导致 SQL Server 阻塞的情况：

资源争用：多个事务同时以相互冲突的方式访问同一资源。例如，事务 1更新一条记录并持有锁，而事务 2尝试读取或更新同一条记录，并且必须等到事务 1完成。
长时间运行的事务：当某个事务占用资源的时间过长，从而导致其他事务等待时。例如，您使用复杂的查询或错误地将事务保持打开状态（例如未使用COMMIT或）ROLLBACK。
缺乏适当的索引：当缺少适当的索引时，查询可能需要全表扫描或页面级锁。
过度使用锁：当事务影响大量数据时，它可能会从锁定个别行或页面转变为锁定整个表。
显式锁定提示：开发人员可能会使用显式锁定提示，例如HOLDLOCK或TABLOCK，这会导致一些锁。
应用程序逻辑设计不良：由于事务管理不善或客户端处理延迟，应用程序可能会不必要地打开事务，或者在事务保持活动状态时使事务保持打开状态的时间比需要的时间长。
事务隔离级别：使用高隔离级别（例如SERIALIZABLE）可以增加锁定和阻塞，因为它强制执行更严格的访问规则，例如防止幻读。

检测 SQL Server 中的阻塞

在 SQL Server 中，有多种方法可以识别和排除涉及阻塞的系统进程 ID (spid)。它们可能包括：

系统存储过程

您可以使用内置sp_who2系统存储过程来查看阻塞信息。要查看阻塞的实际操作，请运行检查活动事务的查询：

EXEC sp_who2;

该查询返回服务器上所有活动的事务。状态 RUNNABLE 或 SUSPENDED 表示它们持有锁。BlkBy列显示阻塞会话。在我们的示例中，BlkBy列中的值53 指的是阻塞进程的会话 ID（SPID 列）。

如您所见，它执行起来简单快捷，无需额外设置。

动态管理视图 (DMV)

DMV 可用于监控工作负载性能并检测被阻止或长时间运行的查询。

例如，具有指定 WHERE 条件的 DMV 仅返回被阻止的进程。sys.dm_exec_requests

SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

DMV可让您查看当前正在等待资源的进程。请注意，运行此 DMV 需要用户拥有管理员权限或实例上的 VIEW SERVER STATE 权限。sys.dm_os_waiting_tasks

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0
GO

活动监视器

如果您更喜欢使用 SQL Server Management Studio (SSMS) 而不是执行系统对象或存储过程的查询，则可以使用 SSMS 监视工具 -活动监视器，它允许您实时查看阻塞会话。

要查看服务器活动：

1. 在对象资源管理器中，右键单击服务器实例并选择活动监视器。

2. 在仪表板上，展开“进程”窗格以查看所有活动会话。

3. 在暂停的会话旁边，查看“阻止者”列中的值（它显示导致阻止的会话 ID）。

屏幕截图显示了被阻止的（#58）会话和被阻止的（#62）会话。

报告

SSMS 还允许使用报告功能监控阻塞事务。它可以生成显示服务器实例上所有阻塞事务的报告。

要打开报告，请右键单击要检查阻止事务的实例名称，然后选择报告>标准报告>活动-所有阻止事务。

这将在新的 SQL 文档中打开生成的报告。

生成的包含所有阻塞交易的报告

我们已经讨论了阻塞的原因以及可能导致这些事件的情况。现在是时候探讨如何最大限度地减少或避免 SQL Server 数据库中的阻塞。

减少阻塞的策略

为了减少阻塞、提高并发性并增强整体性能，建议优化查询、使用适当的索引并尽量减少长时间运行的事务。以下是实现此目标的一些实际步骤：

仅检索所需的列，而不是。SELECT *
在子句中使用带有索引列的特定过滤器WHERE。
除非必要，否则请避免连接很多桌子。
将大型查询（例如更新和删除）分解为较小的批次。
在经常查询的列或仅包含查询需要的列上创建索引。
定期重建或重新组织索引以保持其高效。
保持事务简短以便快速释放锁。
在交易打开时避免用户输入。
使用READ COMMITTED或SNAPSHOT隔离级别来最小化锁定。
SERIALIZABLE除非必要，否则请避免使用高隔离级别，例如。
尽可能使用 ROWLOCK 或 PAGLOCK 来减少锁的范围。
除非批处理操作需要，否则请避免使用 TABLOCK。
使用监控工具或存储过程查询（例如）。sys.dm_exec_requests
在必要时识别并终止长期运行或卡住的交易。

SQL Server 中的死锁是什么？

与阻塞相反，死锁是指并发事务因每个事务都持有其他事务所需的资源的锁并等待其他事务解锁该资源而陷入停滞。因此，这会创建一个依赖循环，并且该过程可能需要无限长的时间。在这种情况下，所有事务都无法继续，直到 SQL Server 因错误而中止一个事务，让其他事务完成。

例如，交易试图同时在两个账户之间转移资金。

交易 A正在将资金从帐户 1转移到帐户 2。
交易 B正在将资金从Account2转移到Account1。

如果两笔交易都尝试以不同的顺序锁定账户，则可能会发生死锁。

死锁的常见原因

如上所述，当两个或多个事务在依赖循环中互相等待，导致任何事务都无法继续执行时，就会发生死锁。死锁最常见的原因是资源顺序冲突和高争用。

资源顺序冲突

死锁通常是因为对多个表或资源的查询没有遵循一致的锁定顺序而发生的。例如，事务 A锁定资源 X，然后尝试锁定资源 Y。与此同时，事务 B锁定资源 Y，然后尝试锁定资源 X。因此，每个事务都会等待对方释放其锁定，从而导致死锁。

共享资源竞争激烈

另一个原因是，由于频繁更新行、长时间运行的查询或长时间持有锁的事务可能会出现死锁。此外，如果大量行级锁转换为单个表级锁，也可能会出现死锁。

僵局该如何解决？

SQL Server有一个内置机制——锁监视线程——可以自动识别和解决死锁以维持系统稳定性。

SQL Server 数据库引擎会定期在后台搜索可能存在死锁的事务。检测到死锁后，SQL Server 会根据事务成本或死锁优先级确定哪个事务是“受害者”，并可终止该事务。例如，成本最低的事务将被选为受害者，因为放弃该事务对系统性能的影响最小。

至于死锁优先级，默认情况下所有事务都具有相同的优先级。但是，开发人员可以使用语句明确为事务分配死锁优先级，例如低、正常（默认状态）或高SET DEADLOCK_PRIORITY。或者，开发人员可以将死锁优先级设置为范围（-10 到 10）内的任意整数值。

SET DEADLOCK_PRIORITY HIGH;

如果死锁循环中的会话具有相同的死锁优先级和相同的成本，则 SQL Server 将随机选择一个牺牲者。如果没有设置明确的优先级，它将选择成本最低的事务来终止。

死锁“受害者”终止后，其事务将回滚。然后，SQL Server 释放终止事务所持有的所有锁，其他事务可以继续进行。

防止死锁的方法

到目前为止，我们已经介绍了 SQL Server 中死锁发生的原因和方式，以及如何解决死锁。虽然无法完全防止死锁，但我们至少可以尽量减少 SQL Server 中的死锁。以下是一份简短的清单，可能有助于在使用 SQL 数据库时减少死锁：

以一致的顺序访问资源。
将大型交易分解为较小的单位。
避免事务内的用户交互。
使用适当的索引和所需的最低隔离级别。
避免使用过于严格的级别，例如 SERIALIZABLE，除非它们是必需的。
监控并优化查询性能。

阻塞和死锁之间的主要区别

总而言之，关键的区别在于死锁是一种恶性循环，其中两个或多个进程通过持有其他进程所需的资源而相互阻塞，从而阻止所有进程继续运行。相反，阻塞是指一个进程持有另一个进程所需的资源，导致被阻塞的进程等待，直到阻塞进程完成其操作。

概念差异

该表显示了 SQL Server 中阻塞和死锁之间的概念差异。

交易影响

虽然阻塞是 SQL Server 并发控制的标准操作，但当阻塞持续时间过长或频繁发生时，它会极大地影响性能。等待资源的事务将保留在队列中，这可能会延迟其完成并降低整体系统性能。此外，如果阻塞事务涉及长时间运行的查询或打开的事务，则可能会导致级联延迟，从而影响多个事务，并进一步降低数据库性能。

另一方面，死锁对性能的影响更为严重，因为它们会导致一个或多个事务失败。发生死锁时，SQL Server 会检测循环依赖并终止其中一个事务（将其视为死锁牺牲品），以允许其他事务继续进行。这种回滚浪费了处理时间和资源，因为必须重试失败的事务。因此，死锁会延迟事务并影响系统可靠性和用户体验。

检测和解决技术

下表总结了SQL Server中阻塞和死锁之间的检测和解决技术。

尽管如此，如果数据库管理员使用正确的工具和策略进行检测和解决，他们可以减少阻塞和死锁的影响并提高性能。

使用 dbForge Studio for SQL Server 分析锁定问题

dbForge Studio for SQL Server 是一款用于数据库开发、管理和维护的终极SQL Server IDE 。这个功能丰富的工具集让用户可以从单个界面执行不同的数据库相关操作。除了数据库设计器、SQL 编辑器、查询生成器、模式/数据比较工具外，dbForge Studio 还提供用于跟踪和检测阻塞问题的高级监视器、用于实时跟踪事件和查询的事件分析器以及用于优化数据库性能的查询分析器。

Monitor是一款终极监控工具，专注于 SQL Server 数据库的实时监控和性能分析。它可以帮助数据库管理员和开发人员识别和解决问题，例如查询速度慢、会话阻塞和资源使用效率低下等。

让我们展示使用 SELECT 查询和监视器检测死锁的示例。

打开 Studio。在SQL工具栏上，选择New SQL打开一个新的 SQL 文档。然后执行以下脚本创建一个测试表，在其中插入数据，开始事务并锁定表行。

-- Create a test table
CREATE TABLE DeadlockTest (
    ID INT PRIMARY KEY,
    Value NVARCHAR(50)
); 

-- Populate the table with data
INSERT INTO DeadlockTest (ID, Value)
VALUES (1, 'A'), (2, 'B');

-- Begin transaction
BEGIN TRANSACTION;

-- Lock the #1 row
UPDATE DeadlockTest SET Value = 'X' WHERE ID = 1;

-- Enable delay
WAITFOR DELAY '00:00:05';

-- Lock the #2 row
UPDATE DeadlockTest SET Value = 'Y' WHERE ID = 2;

要继续，请打开另一个 SQL 文档并执行以下脚本。它将运行显式事务，这意味着所有后续操作都是单个事务的一部分。请注意，在执行COMMIT或之前，所做的更改不会提交或对其他会话可见。ROLLBACK

BEGIN TRANSACTION;

-- Lock the #2 row
UPDATE DeadlockTest
SET Value = 'Z'
WHERE ID = 2;

-- Enable delay
WAITFOR DELAY '00:00:05';

-- Lock the #1 row 
UPDATE DeadlockTest
SET Value = 'W'
WHERE ID = 1;

该事务包括以下操作：

DeadlockTestUPDATE 语句更新表中的记录。ID = 2
WAITFOR DELAY '00:00:05';在交易中引入 5 秒的暂停，模拟延迟，在此期间记录 ( ) 上的锁保持活动状态。ID = 2
第二条 UPDATE 语句尝试更新记录。ID = 1

现在，打开一个新的 SQL 文档并执行以下 SELECT 查询，通过识别被其他会话阻止的活动请求来帮助检测 SQL Server 中的阻止会话：

SELECT 
    r.session_id AS BlockingSessionID,
    r.blocking_session_id AS BlockedSessionID,
    t.text AS QueryText
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

在哪里：

sys.dm_exec_requests是一个动态管理视图，显示有关在 SQL Server 中执行的所有活动请求的详细信息。每个请求代表 SQL Server 正在处理的一个特定操作，例如查询或存储过程。
blocking_session_id > 0表示所代表的会话被另一个会话阻止。r.session_id

该查询返回持有资源的阻塞会话的 ID 和被阻塞会话的 ID。该查询还检索被阻塞会话执行的查询的 SQL 文本。它有助于识别哪个查询是阻塞查询。

执行 SELECT 查询来识别阻塞和被阻止的会话

现在，通过选择功能区上的数据库，然后选择任务 > 显示监视器来打开监视器。在打开的监视器文档中，导航到会话选项卡。

请注意，我们通过SampleDB数据库过滤了结果以提高可读性。

监视显示阻塞和被阻止的会话

如您所见，spid #66 的会话处于暂停状态。这意味着会话已暂停，正在等待当前由另一个会话锁定的资源。阻塞会话具有 spid #63，它显示在结果网格的阻塞列中。

要解决锁，请使用KILL LOCK或KILL LOCK SESSION查询：

对于阻塞事务，执行ROLLBACK命令
或者打开一个新的 SQL 文档并执行KILL id_number查询，其中id_number是阻塞会话的 ID。

如果要查看与死锁相关的事件，请使用dbForge Studio 的 SQL Server 分析功能。它旨在实时监控和分析 SQL Server 事件和查询。该工具有助于排除性能问题、优化查询并了解 SQL Server 引擎如何处理请求。

您可以在“配置文件服务器事件”向导中设置服务器事件的配置文件。要打开它，请在数据库资源管理器中右键单击连接并选择任务>配置文件服务器事件。

在向导的“要捕获的事件”页面上，选择要捕获的死锁事件，然后选择“执行”。

为了方便搜索，您可以在向导右上角的搜索栏中输入deadlock 。

在向导中选择要捕获的事件

Event Profiler会根据配置的选项实时显示信息。

事件分析器仪表板

如果你需要优化查询性能，那么最好的工具就是查询分析器。它提供了查询执行计划的详细可视化表示，包括锁定行为和资源使用情况。

例如，查询分析器的“等待统计”选项卡可帮助您了解哪些事件导致了延迟以及查询执行期间资源被占用了多长时间。

对于长时间运行的查询，Query Profiler 可让用户直观地查看耗时操作，并识别导致性能不佳的 SQL 代码的特定部分。此外，用户可以比较查询分析结果，以跟踪一段时间内的改进或退步。

查询分析器执行计划

结论

在本文中，我们探讨了了解和管理阻塞和死锁对于保持最佳数据库性能和正确的事务处理的重要性。如果管理不当，阻塞可能会导致很大的延迟。然而，死锁可能会产生严重的影响，并可能完全暂停事务，需要 SQL Server 来解决它们。我们还提供了检测和解决技术，以使用查询和 dbForge Studio for SQL Server 大限度地减少阻塞和死锁对数据库的影响。除了其他高级功能和工具外，后者是优化查询、监控和分析锁的不错选择。