了解并解决 SQL Server 阻塞定问题

项目
06/20/2023

适用于：SQL Server（所有受支持的版本）、Azure SQL 托管实例

原始 KB 编号： 224453

目标

本文介绍 SQL Server 中的阻塞，并演示如何对阻塞问题进行疑难解答并解决阻塞问题。

在本文中，术语连接是指数据库的单个登录会话。在许多 DMV 中，每个连接都显示为会话 ID (SPID) 或 session_id。每个这种 SPID 通常都称为一个进程，尽管在通常意义上它不是单独的进程上下文。相反，每个 SPID 都包含服务来自给定客户端的单个连接请求所需的服务器资源和数据结构。单个客户端应用程序可能包含一个或多个连接。从 SQL Server 的角度来看，单客户端计算机上单客户端应用程序的多个连接与多客户端应用程序或多客户端计算机的多个连接之间没有区别；它们是最小单元。无论源客户端如何，一个连接都可以阻塞另一个连接。

注意

本文重点介绍 SQL Server 实例，包括 Azure SQL 托管实例。 有关对 Azure SQL 数据库中阻塞问题进行疑难解答的特定信息，请参阅了解并解决 Azure SQL 数据库阻塞问题。

什么是阻塞

阻塞是任何具有基于锁定并发的关系数据库管理系统 (RDBMS) 不可避免的设计特征。如前所述，在 SQL Server 中，当一个会话持有特定资源的锁，第二个 SPID 尝试在同一资源上获取冲突锁类型时，会发生阻塞。通常，第一个 SPID 锁定资源的时间范围较小。当所属会话释放锁时，第二个连接可以自由其在获取资源上的锁并继续处理。此处所述锁定是正常行为，一天可能会发生多次，不会对系统性能产生明显影响。

查询的持续时间和事务上下文决定其持有锁的时间，从而决定它们对其他查询的影响。如果查询未在事务中执行（并且未使用锁提示），则仅在实际读取 SELECT 语句时（而不是在查询期间）才会在资源上持有 SELECT 语句的锁。对于 INSERT、UPDATE 和 DELETE 语句，在查询期间回持有锁，既确保数据一致性，又允许在必要时回滚查询。

对于在事务中执行的查询，持有锁的持续时间由查询类型、事务隔离级别以及是否在查询中使用锁提示来确定。有关锁定、锁定提示和事务隔离级别的说明，请参阅以下文章：

以下原因会导致锁定和阻塞持续到对系统性能产生不利影响：

SPID 在释放一组资源之前，会在一段较长的时间内持有这些资源的锁。这种类型的阻塞会随着时间推移自行解析，但可能导致性能下降。
SPID 持有一组资源的锁，并且从不释放它们。这种类型的阻塞不会自行解析，并且会无限期地阻止访问受影响的资源。

在第一个场景中，情况可能非常不稳定，因为不同的 SPID 会随着时间推移阻塞不同资源，从而形成移动目标。这些情况很难通过 SQL Server Management Studio 将问题缩小到单个查询来进行故障排除。相比之下，第二种情况会出现一致状态，从而更轻松地进行诊断。

应用程序和阻塞

遇到阻塞问题时，可能会倾向于关注服务器端优化和平台问题。但是，只关注数据库可能无法解决问题，但能够更好地吸收时间和精力来检查客户端应用程序及其提交的查询。无论应用程序对数据库调用公开的可见性级别如何，阻塞问题仍然经常需要检查应用程序提交的确切 SQL 语句，以及应用程序在查询取消、连接管理、提取所有结果行等方面的确切行为。如果开发工具不允许对连接管理、查询取消、查询超时、结果提取等进行显式控制，则阻塞问题可能无法解决。在为 SQL Server 选择应用程序开发工具之前，应仔细研究这种可能性，尤其是对于性能敏感的 OLTP 环境。

在数据库和应用程序的设计和构造阶段，请注意数据库性能。特别是，应针对每个查询评估资源使用量、隔离级别和事务路径长度。每个查询和事务都应尽可能是轻量级查询和事务。必须执行良好的连接管理规则，如果没有它，应用程序在用户数较少的情况下其性能虽可接受，但随着用户数增加，性能可能会显著下降。

通过适当的应用程序和查询设计，SQL Server 能够在一台服务器上同时支持数千个用户，并且几乎没有阻塞。

对阻塞问题进行疑难解答

无论遇到哪种阻塞情况，对阻塞问题的疑难解答方法都是相同的。这些逻辑分离适用于本文的其余部分内容。理念是查找队头阻止程序并确定该查询正在执行的操作以及它阻止的原因。一旦识别出有问题的查询（即是什么长时间持有锁），下一步是分析并确定阻塞发生的原因。弄清楚原因后，就可以通过重新设计查询和事务做出更改。

疑难解答步骤：

识别主阻塞会话（队头阻止程序）
查找导致阻塞的查询和事务（是什么长时间持有锁）
分析/理解发生长时间阻塞的原因
通过重新设计查询和事务来解决阻塞问题

现在，让我们深入探讨如何通过相应的数据捕获来精确定位主阻塞会话。

收集阻塞信息

为了降低对阻塞问题进行疑难解答的难度，数据库管理员可以使用 SQL 脚本持续监视 SQL Server 上的锁定和阻塞状态。要收集此数据，有两种免费方法。

第一种是查询动态管理对象 (DMO) 并存储结果，以便日后进行比较。本文中引用的某些对象是动态管理视图 (DMV)，有些是动态管理函数 (DMF)。

第二种是使用扩展事件 (XEvents) 或 SQL 探查器跟踪来捕获正在执行的内容。由于 SQL 跟踪和 SQL Server 探查器已弃用，因此本疑难解答指南将重点介绍 XEvents。

收集来自 DMV 的信息

引用 DMV 来对阻塞问题进行疑难解答旨在识别阻塞链和 SQL 语句头部的 SPID（会话 ID）。查找被阻塞的受害者 SPID。如果任何 SPID 被另一个 SPID 阻塞，则调查拥有该资源的 SPID（阻塞 SPID）。该所有者 SPID 是否也被阻塞？可以浏览阻塞链以查找头部阻止程序，然后调查其保持锁定的原因。

为此，可以使用下列方法之一：

在 SQL Server Management Studio (SSMS) “对象资源管理器”中，右键单击顶级服务器对象，依次展开“报表”、“标准报表”，然后选择“活动 - 所有阻塞事务”。此报表显示阻塞链头的当前事务。如果展开事务，报表将显示被头部事务阻止的事务。此报表还将显示“阻塞中的 SQL 语句”和“已阻塞的 SQL 语句”。
在 SSMS 中打开“活动监视器”，并引用“发起阻止方”列。在此处查找有关活动监视器的详细信息。

通过 DMV 还可获取更详细的基于查询的方法：

sp_who 命令和 sp_who2 命令是显示所有当前会话的较旧命令。 DMV sys.dm_exec_sessions 在更易于查询和筛选的结果集中返回更多数据。将在其他查询的核心中找到 sys.dm_exec_sessions。
如果已标识特定会话，则可以使用 DBCC INPUTBUFFER(<session_id>) 来查找会话提交的最后一个语句。在更易于查询和筛选的结果集中，可以使用 sys.dm_exec_input_buffer 动态管理函数 (DMF) 返回类似的结果，从而提供 session_id 和 request_id。例如，要返回由 session_id 66 和 request_id 0 提交的最新查询：

SELECT * FROM sys.dm_exec_input_buffer (66,0);

请参阅 sys.dm_exec_requests 并引用 blocking_session_id 列。当 blocking_session_id = 0 时，会话不会被阻止。虽然 sys.dm_exec_requests 仅列出当前正在执行的请求，但任何连接（无论是否活动）都将列在 sys.dm_exec_sessions 中。在下一个查询的 sys.dm_exec_requests 和 sys.dm_exec_sessions 之间构建此通用联接。请记住，要通过 sys.dm_exec_requests 返回，查询必须使用 SQL Server 主动执行。
运行此示例查询，使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV 查找主动执行的查询及其当前 SQL 批文本或输入缓冲区文本。如果 sys.dm_exec_sql_text 的 text 列返回的数据为 NULL，则当前未执行查询。在这种情况下，sys.dm_exec_input_buffer 的 event_info 列将包含传递给 SQL 引擎的最后一个命令字符串。此查询还可用于标识阻止其他会话的会话，包括每个 session_id 阻止的 session_ids 列表。

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

运行 Microsoft 支持部门提供的更详细的示例查询，以识别多个会话阻塞链的头部，包括阻塞链中涉及的会话的查询文本。

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

若要捕获长时间运行或未提交的事务，请使用另一组 DMV 来查看当前打开的事务，包括 sys.dm_tran_database_transactions、 sys.dm_tran_session_transactions、 sys.dm_exec_connections 和 sys.dm_exec_sql_text 有几个 DMV 与跟踪事务相关联，要了解更多，请参阅此处的事务 DMV。

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

引用 sys.dm_os_waiting_tasks 位于 SQL Server 线程/任务层。这会返回有关请求当前遇到的 SQL wait_type 的信息。例如sys.dm_exec_requests，只有活动请求才由其返回sys.dm_os_waiting_tasks。

注意

要了解更多有关等待类型（包括一段时间内聚合的等待统计信息）的详细信息，请参阅 DMV sys.dm_db_wait_stats

使用sys.dm_tran_locks DMV，获取有关查询放置了哪些锁定的更多详细信息。此 DMV 可以返回产品 SQL Server 实例上的大量数据，并且可用于诊断当前保存的锁定。

由于存在内联sys.dm_os_waiting_tasks，以下查询将输出限制为sys.dm_tran_locks仅限于当前被阻止的请求、等待状态及其锁定：

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

借助 DMV，随时间推移存储查询结果将提供数据点，使您能够在指定的时间间隔内查看阻塞，以确定持久阻塞或趋势。 CSS 排查此类问题的导航工具是使用 PSSDiag 数据收集器。此工具使用“SQL Server Perf 统计信息”随时间从上面引用的 DMV 收集结果集。由于此工具不断发展，请查看 GitHub 上 DiagManager 的最新公共版本。

从扩展事件收集信息

除了上述信息，通常还需要捕获服务器上活动的跟踪，以彻底调查 SQL Server 中的阻塞问题。例如，如果会话在事务中执行多个语句，则只会显示提交的最后一个语句。但是，前面的陈述之一可能是锁定仍未解除的原因之一。通过跟踪，可以查看当前事务中会话执行的所有命令。

可通过两种方法在 SQL Server 中捕获跟踪：扩展事件 (XEvents)和 Profiler 跟踪。但是，使用 SQL Server Profiler 的 SQL跟踪已弃用。 XEvents 是一种更新的、卓越的跟踪平台，它的功能性更强，对观测系统的影响更小，并且其界面已集成到 SSMS 中。

预先制作的扩展事件会话已准备好在 SSMS 启动，在 XEvent Profiler 菜单下的对象资源管理器中列出。要了解更多详细信息，请参阅 XEvent Profiler。还可以在 SSMS 中创建自己的自定义扩展事件会话，请参阅扩展事件新会话向导。若要排查阻塞问题，我们通常会捕获：

类别错误：
- 注意
- Blocked_process_report**
- Error_reported（通道管理员）
- Exchange_spill
- Execution_warning

**若要配置生成阻塞进程报告的阈值和频率，请使用 sp_configure 命令配置阻塞进程阈值选项，该选项可在几秒内设置完成。默认情况下，不会生成阻塞的进程报告。

类别警告：
- Hash_warning
- Missing_column_statistics
- Missing_join_predicate
- Sort_warning
类别执行：
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
类别锁定
- Lock_deadlock
类别会话
- Existing_connection
- 登录
- 注销

识别并解决常见的阻塞场景

通过检查上述信息，可以确定大多数阻塞问题的原因。本文的其余部分介绍如何使用此信息来识别和解决一些常见的阻塞场景。本讨论假定已使用阻塞脚本（此前已引用）来捕获有关阻塞 SPID 的信息，并已使用 XEvent 会话捕获应用程序活动。

分析阻塞数据

使用 blocking_these 和 session_id 检查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的输出，以确定阻塞链的头部。这将最清楚地识别哪些请求被阻止，哪些请求正在被阻止。进一步查看被阻止和正在被阻止的会话。阻塞链是否有共同点或根？它们可能共享一个公用表，且阻塞链中涉及的一个或多个会话正在执行写入操作。

检查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的输出以获取阻塞链头部有关 SPID 的信息。查找以下列：

sys.dm_exec_requests.status
此列显示特定请求的状态。通常，休眠状态指示 SPID 已完成执行，正在等待应用程序提交另一个查询或批处理。可运行或正在运行的状态指示 SPID 当前正在处理查询。下表简要介绍了各种状态值。

状态	含义
背景	SPID 正在运行后台任务，例如死锁检测、日志编写器或检查点。
休眠	SPID 当前未执行。这通常指示 SPID 正在等待来自应用程序的命令。
正在运行	SPID 当前在计划程序上运行。
可运行	SPID 位于计划程序的可运行队列中，等待获取计划程序时间。
已暂停	SPID 正在等待资源，例如锁或闩锁。

sys.dm_exec_sessions.open_transaction_count
此列告知此会话中的打开的事务数。如果此值大于 0，则 SPID 位于打开的事务中，并且可能持有事务中任何语句获取的锁。
sys.dm_exec_requests.open_transaction_count
同样，此列告知此请求中打开的事务数。如果此值大于 0，则 SPID 位于打开的事务中，并且可能持有事务中任何语句获取的锁。
sys.dm_exec_requests.wait_type、wait_time 和 last_wait_type
如果 sys.dm_exec_requests.wait_type 为 NULL，则请求当前未等待任何内容，并且 last_wait_type 值指示请求遇到的最后一个 wait_type。有关 sys.dm_os_wait_stats 的详细信息以及最常见等待类型的说明，请参阅 sys.dm_os_wait_stats。 wait_time 值可用于确定请求是否正在进行。当针对 sys.dm_exec_requests 表的查询返回 wait_time 列中的值小于此前查询 sys.dm_exec_requests 的 wait_time 值时，这指示已获取并释放之前的锁，现在正在等待新的锁（假定非零 wait_time）。可以通过比较 wait_resource 和 sys.dm_exec_requests 之间的输出来验证这一点，该输出显示请求正在等待的资源。

sys.dm_exec_requests.wait_resource 此列指示阻止的请求正在等待的资源。下表列出了常见 wait_resource 格式及其含义：

Resource	格式	示例	解释
表格	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	在此案例中，数据库 ID 5 是 pubs 示例数据库， `object_id` 261575970 是标题表，而 1 是聚集索引。
Page	DatabaseID:FileID:PageID	PAGE: 5:1:104	在此案例中，数据库 ID 5 为 pubs，文件 ID 1 是主数据文件，而第 104 页是属于标题表的页面。要标识页面所属的 object_id，请使用动态管理函数 sys.dm_db_page_info，从 `wait_resource` 中传入 DatabaseID、FileId、PageId。
键	DatabaseID:Hobt_id（索引键的哈希值）	键：5:72057594044284928 (3300a4f361aa)	在此案例中，数据库 ID 5 为 Pubs，Hobt_ID 72057594044284928 对应于 object_id 261575970（标题表）的 index_id 2。使用 `sys.partitions` 目录视图将 `hobt_id` 关联到特定 `index_id` 和 `object_id`。无法将索引键哈希解哈希为特定键值。
Row	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	在此案例中，数据库 ID 5 为 pubs，文件 ID 1 是主数据文件，第 104 页是属于标题表的页面，而插槽 3 指示该行在页面上的位置。
编译	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	在此案例中，数据库 ID 5 为 pubs，文件 ID 1 是主数据文件，第 104 页是属于标题表的页面，而插槽 3 指示该行在页面上的位置。

sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV 包含有关打开事务的数据，这些事务可以加入其他 DMV，以获取等待提交或回滚事务的完整信息。使用以下查询返回有关已加入其他 DMV（包括sys.dm_tran_session_transactions）的打开事务的信息。考虑事务的当前状态、transaction_begin_time 和其他情况数据，以评估其是否可能是阻塞源。

SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

其他列

通过 sys.dm_exec_sessions 和 sys.dm_exec_request 中的其余列也可以深入了解问题的根源。它们的用处因问题的具体情况而异。例如，可以确定问题是否仅发生在某些客户端 (hostname)、某些网络库 (client_interface_name)、SPID 提交的最后批处理何时在 sys.dm_exec_sessions 中被 last_request_start_time、请求通过 sys.dm_exec_requests 中的 start_time 的运行时间等。

常见的阻塞场景

下表将常见症状映射到其可能的原因。

wait_type、open_transaction_count 和 status 列是指由 sys.dm_exec_request 返回的信息，而其他列可能由 sys.dm_exec_sessions 返回。 “解决？”列指示阻止是否自行解决，或者是否应通过 KILL 命令终止会话。有关详细信息，请参阅 KILL (Transact-SQL)。

应用场景	Wait_type	Open_Tran	状态	解析？	其他症状
1	非 NULL	>= 0	可运行	是，查询完成时。	在 `sys.dm_exec_sessions`、 `reads`、 `cpu_time` 和/或 `memory_usage` 中，列会随时间推移而增加。完成查询时，查询的持续时间会很长。
2	NULL	>0	休眠	不，但 SPID 可以终止。	此 SPID 的“扩展事件”会话中可能会显示注意信号，指示查询超时或已取消。
3	NULL	>= 0	可运行	不需要。在客户端提取所有行或关闭连接之前，不会进行解析。 SPID 可以被终止，但可能需要长达 30 秒的时间。	如果 open_transaction_count = 0，并且 SPID 在事务隔离级别为默认值 (READ COMMITTED) 时保持锁定，则这可能是一个原因。
4	各不相同	>= 0	可运行	不需要。在客户端取消查询或关闭连接之前，不会进行解析。 SPID 可以被终止，但可能需要长达 30 秒的时间。	阻塞链头部的 SPID 在 `sys.dm_exec_sessions` 中的 `hostname` 列将与它所阻止的 SPID 之一相同。
5	NULL	>0	回滚	是。	此 SPID 的扩展事件会话中可能会显示注意信号，表明发生了查询超时或取消，或者只是发布了回滚语句。
6	NULL	>0	休眠	最终。当 Windows NT 确定会话不再活动时，连接将断开。	`sys.dm_exec_sessions` 中的 `last_request_start_time` 值比当前时间要早得多。

详细的阻止方案

方案 1：由正常运行的查询导致的阻塞，执行时间较长

在此方案中，主动运行的查询已获取锁，并且未释放锁（受事务隔离级别的影响）。因此，其他会话将等待锁，直到它们被释放。

解决方法:

解决此类阻塞问题的方法是寻找优化查询的方法。此类阻塞问题可能是性能问题，需要你根据这种情况进行操作。有关对特定运行缓慢的查询进行故障排除的信息，请参阅如何对 SQL Server 上运行缓慢的查询进行故障排除。有关详细信息，请参阅监视和优化性能。

来自“查询存储”的 SSMS 内置报表（在 SQL Server 2016 中引入）也是一种强烈推荐且宝贵的工具，可用于识别代价最高昂的查询和不理想的执行计划。

如果有一个长期运行的查询阻止其他用户且无法对其进行优化，请考虑将其从 OLTP 环境移动到专用报告系统。还可以使用 Always On 可用性组同步“数据库的只读副本”。

注意

查询执行期间的阻塞可能是由查询升级引起的，这种情况下行锁定或页锁定升级到表锁定。 Microsoft SQL Server 会动态确定何时执行锁升级。防止锁升级的最简单和最安全的方法是保持事务简短，并减少昂贵查询的锁占用量，以免超过锁升级阈值。有关检测和防止过度锁升级的详细信息，请参阅解决锁升级导致的阻塞问题。

方案 2：由具有未提交的事务的休眠 SPID 导致的阻塞

这种类型的阻塞通常可以通过正在休眠或等待事务嵌套级别（@@TRANCOUNT，来自 sys.dm_exec_requests 的 open_transaction_count）大于零的命令的 SPID 来识别。如果应用程序遇到查询超时，或者发出取消语句的同时没有发出所需的 ROLLBACK 和/或 COMMIT 语句数，则可能会发生这种情况。当 SPID 收到查询超时或取消时，它将终止当前查询和批处理，但不会自动回滚或提交事务。应用程序对此负有责任，因为 SQL Server 不能假定由于单个查询被取消而必须回滚整个事务。查询超时或取消将在扩展事件会话中显示为 SPID 的注意信号事件。

若要演示未提交的显式事务，请发出以下查询：

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

然后，在同一窗口中执行此查询：

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

第二个查询的输出结果表明，事务计数为 1。在提交或回滚事务之前，仍会持有在事务中获取的所有锁。如果应用程序显式打开并提交事务，则通信或其他错误可能会使会话及其事务处于打开状态。

使用本文前面基于 sys.dm_tran_active_transactions 的脚本来识别实例中当前未提交的事务。

解决方案：

此外，此类阻塞问题也可能是性能问题，需要你执行这样的操作。如果可以缩短查询执行时间，则不会出现查询超时或取消的情况。应用程序要能够处理超时或取消的情况（如果出现），这点很重要，但你也可以从检查查询的性能中获益。
应用程序必须正确管理事务嵌套级别，否则可能会在以这种方式取消查询后导致阻塞问题。比如以下几种情况：
- 在客户端应用程序的错误处理程序中，在出现任何错误后执行 IF @@TRANCOUNT > 0 ROLLBACK TRAN，即使客户端应用程序不相信某个事务已打开。需要检查打开的事务，因为批处理期间调用的存储过程可能在客户端应用程序不知情的情况下启动事务。某些条件（例如取消查询）会阻止该过程越过当前语句执行，因此即使该过程具有检查 IF @@ERROR <> 0 和中止事务的逻辑，在这种情况下也不会执行此回滚代码。
- 如果在打开连接的应用程序中使用连接池，并在将连接释放回池之前运行一些查询（例如基于 Web 的应用程序），则暂时禁用连接池可能有助于缓解问题，直到修改客户端应用程序以适当地处理错误。通过禁用连接池，释放连接将导致物理断开 SQL Server 连接，从而导致服务器回滚所有打开的事务。
- 将 SET XACT_ABORT ON 用于连接，或用于在任何开始事务且未在错误后进行清理的存储过程。如果出现运行时错误，此设置将中止任何打开的事务，并将控制权返回给客户端。有关详细信息，请查看设置 XACT_ABORT (Transact-SQL)。

注意

连接在从连接池重复使用之前不会重置，因此用户可以打开事务，然后释放与连接池的连接，但在几秒之内可能不会重复使用，在此期间事务将保持打开状态。如果未重复使用连接，则当连接超时并从连接池中删除时，事务将中止。因此，客户端应用程序最好中止其错误处理程序中的事务，或使用 SET XACT_ABORT ON 来避免这种潜在的延迟。

警告

在 SET XACT_ABORT ON 之后，不会执行导致错误的语句之后的 T-SQL 语句。这可能会影响现有代码的预期流。

方案 3：由 SPID 导致的阻塞，其相应的客户端应用程序未提取所有结果行直至完成

将查询发送到服务器后，所有应用程序必须立即提取所有结果行才能完成。如果应用程序未提取所有结果行，则锁可能会停留在表上，从而阻止其他用户。如果使用的应用程序以透明方式将 SQL 语句提交到服务器，则应用程序必须提取所有结果行。如果没有提取所有结果行（并且无法将其配置为执行此操作），则可能无法解决阻塞问题。为避免出现此问题，可以将行为不佳的应用程序限制为报表或决策支持数据库（独立于主 OLTP 数据库）。

解决方法:

必须重写应用程序才能提取所有结果行直至完成。这不排除在查询的 ORDER BY 子句中使用 OFFSET 和 FETCH 来执行服务器端分页。

方案 4：由分布式客户端/服务器死锁导致的阻塞

与传统的死锁不同，使用 RDBMS 锁定管理器无法检测分布式死锁。这是因为死锁中涉及的资源只有一个是 SQL Server 锁。死锁的另一端处于客户端应用程序级别，SQL Server 对此没有控制权。下面两个示例说明如何执行此操作，以及应用程序可以避免这种情况的可能方法。

示例 A：具有单一客户端线程的客户端/服务器分布式死锁

如果客户端有多个打开的连接和单个执行线程，则可能会出现以下分布式死锁。注意，此处使用的术语 dbproc 指的是客户端连接结构。

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

在上面所示案例中，单一客户端应用程序线程有两个打开的连接。它在 dbproc1 上异步提交 SQL 操作。这意味着它不会等待调用返回，然后再继续。然后，应用程序在 dbproc2 上提交另一个 SQL 操作，并等待结果以开始处理返回的数据。当数据开始返回（以最先响应的 dbproc 为准--假定是 dbproc1），它将处理完在该 dbproc 上返回的所有数据。它从 dbproc1 提取结果，直到 SPID1 被 SPID2 持有的锁阻塞（因为这两个查询在服务器上异步运行）。此时，dbproc1 将无限期地等待更多数据。不会在锁上阻止 SPID2，而是尝试将数据发送到其客户端 dbproc2。但是，dbproc2 在应用程序层的 dbproc1 上被有效阻止，因为 dbproc1 正在使用应用程序的单个执行线程。这会导致死锁，同时 SQL Server 无法检测或解析，因为所涉及的资源中只有一个是 SQL Server 资源。

示例 B：客户端/服务器分布式死锁（每个连接都有一个线程）

即使客户端上每个连接都存在单独的线程，此分布式死锁的变体也可能仍会出现，如下所示。

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

这种情况类似于示例 A，不同的是 dbproc2 和 SPID2 正在运行 SELECT 语句，以便执行行处理（一次一行），并通过缓冲区将每行传递给 dbproc1，以获取同一表上的 INSERT、UPDATE 或 DELETE 语句。最终，SPID1（执行 INSERT、UPDATE 或 DELETE）被 SPID2 持有的锁阻塞（执行 SELECT）。 SPID2 将结果行写入客户端 dbproc2。然后，Dbproc2 尝试将缓冲区中的行传递到 dbproc1，但发现 dbproc1 正忙（它在等待 SPID1 完成当前在 SPID2 上被阻塞的 INSERT 时被阻塞）。此时，dbproc2 在应用程序层被 dbproc1 阻塞，而 dbproc1 的 SPID (SPID1) 在数据库级别被 SPID2 阻塞。同样，这会导致死锁，同时 SQL Server 无法检测或解析，因为所涉及资源中只有一个是 SQL Server 资源。

A 和 B 这两个示例都是应用程序开发人员必须注意的基本问题。他们必须对应用程序进行代码编码，以便正确处理这些情况。

解决方法:

提供查询超时后，如果发生分布式死锁，则在超时时会中断。有关使用查询超时的详细信息，请参阅连接提供程序文档。

方案 5：由处于回滚状态的会话导致的阻塞

在用户定义的事务之外终止或取消的数据修改查询将回滚。出现这种情况可能是客户端网络会话断开连接的副作用，或者选择请求作为死锁受害者。这通常可以通过观察 sys.dm_exec_requests 的输出来识别，它可能指示回滚 command，并且 percent_complete 列可能会显示进度。

在用户定义的事务之外终止或取消的数据修改查询将回滚。出现这种情况也可能是客户端计算机重启及其网络会话断开连接的副作用。同样，被选为死锁受害者的查询也会回滚。数据修改查询的回滚速度通常不能比最初应用更改的速度更快。例如，如果 DELETE、INSERT 或 UPDATE 语句已运行一小时，则至少可能需要一个小时才能回滚。这是预期的行为，因为必须回滚所做的更改，否则数据库中的事务和物理完整性将受到损害。由于必须执行此操作，SQL Server 将 SPID 标记为黄金或回滚状态（这意味着它不能被终止或选为死锁受害者）。这通常可以通过观察 sp_who 的输出来识别，这可能指示 ROLLBACK 命令。 sys.dm_exec_sessions 的 status 将指示 ROLLBACK 状态。

注意

启用“加速数据库恢复功能”时，很少出现冗长回滚。此功能已在 SQL Server 2019 中推出。

解决方法:

必须等待会话完成回滚所做的更改。

如果实例在此操作过程中关闭，则数据库在重启时将处于恢复模式，并且在处理所有打开的事务之前将无法访问它。启动恢复每个事务所用的时间与运行时恢复的时间基本相同，在此期间数据库不可访问。因此，强制服务器关闭以修复处于回滚状态的 SPID 通常会适得其反。在启用了“加速数据库恢复”的 2019 SQL Server 中，不应发生这种情况。

要避免这种情况，请勿在 OLTP 系统繁忙期间执行大型批处理写入操作或索引创建或维护操作。如果可能，请在低活动量期间执行此类操作。

方案 6：孤立事务导致的阻塞

这是一个常见的问题方案，部分与方案 2重叠。如果客户端应用程序停止、客户端工作站重启或出现批量中止错误，这些都可能会使事务保持打开状态。如果应用程序未回滚应用程序 CATCH 或 FINALLY 阻止中的事务，或者未处理这种情况，则可能会出现这种情况。

在这种情况下，虽然已取消执行 SQL 批处理，但应用程序会保持 SQL 事务的打开状态。从 SQL Server 实例的角度来看，客户端似乎仍然存在，获取的任何锁仍会保留。

若要演示孤立事务，请执行以下查询，该查询通过将数据插入不存在的表来模拟批量中止错误：

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

然后，在同一窗口中执行此查询：

SELECT @@TRANCOUNT;

第二个查询的输出结果表明，事务计数为 1。在提交或回滚事务之前，仍会持有在事务中获取的所有锁。由于该批处理已被查询中止，因此执行该批处理的应用程序可能会继续在同一会话上运行其他查询，而无需清理仍处于打开状态的事务。锁定将一直保留到会话终止或重启 SQL Server 实例。

解决方案：

防止这种情况的最佳方法是改进应用程序错误/异常处理方式，尤其是针对意外终止的情况。确保在应用程序代码中使用 Try-Catch-Finally 块，并在出现异常时回滚事务。
考虑将 SET XACT_ABORT ON 用于会话，或用于在任何开始事务且未在错误后进行清理的存储过程。如果出现中断批处理的运行时错误，此设置将自动回滚所有打开的事务，并将控制权返回给客户端。有关详细信息，请查看设置 XACT_ABORT (Transact-SQL)。
要解析已断开连接但未适当清理其资源的客户端应用程序的孤立连接，可以使用 KILL 命令终止 SPID。有关参考，请参阅 KILL (Transact-SQL)。

KILL 命令将 SPID 值作为输入。例如，要终止 SPID 9，请运行以下命令：

KILL 99

注意

由于检查 KILL 命令存在时间间隔，因此，KILL 命令最多可能需要 30 秒才能完成。

了解并解决 SQL Server 阻塞定问题

目标

什么是阻塞

应用程序和阻塞

对阻塞问题进行疑难解答

收集阻塞信息

收集来自 DMV 的信息

从扩展事件收集信息

识别并解决常见的阻塞场景

分析阻塞数据

常见的阻塞场景

详细的阻止方案

方案 1：由正常运行的查询导致的阻塞，执行时间较长

方案 2：由具有未提交的事务的休眠 SPID 导致的阻塞

方案 3：由 SPID 导致的阻塞，其相应的客户端应用程序未提取所有结果行直至完成

方案 4：由分布式客户端/服务器死锁导致的阻塞

示例 A：具有单一客户端线程的客户端/服务器分布式死锁

示例 B：客户端/服务器分布式死锁（每个连接都有一个线程）

方案 5：由处于回滚状态的会话导致的阻塞

方案 6：孤立事务导致的阻塞

另请参阅

反馈

反馈

其他资源