了解和解决 SQL Server 阻塞问题

项目
07/30/2024

适用于：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 语句的锁仅在实际读取时保留在资源上，而不是在查询期间。对于 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 Profiler 已弃用，因此此故障排除指南将重点介绍 XEvents。

从 DMV 收集信息

引用 DMV 来对阻塞进行故障排除的目的是识别阻塞链和 SQL 语句头的 SPID（会话 ID）。查找正在被阻塞的牺牲品 SPID。如果任何 SPID 被另一个 SPID 阻塞，则调查拥有资源的 SPID（阻塞的 SPID）。所有者 SPID 是否也被阻塞？你可以遍历链来找到头阻塞程序，然后调查其锁定的原因。

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

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

还可以使用 DMV 使用更详细的基于查询的方法：

命令sp_whosp_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，则查询当前不会执行。在这种情况下， event_info 该 sys.dm_exec_input_buffer 列将包含传递给 SQL 引擎的最后一个命令字符串。此查询还可以用于标识阻止其他会话的会话，包括每个 session_id 阻止的 session_id 列表。

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];

引用位于 SQL Server 线程/任务层的sys.dm_os_waiting_tasks 。这会返回有关请求当前遇到的 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）和探查器跟踪。但是，已弃用使用 SQL Server Profiler 的 SQL 跟踪。 XEvents 是较新的、优越的跟踪平台，允许对观测系统产生更多多功能性和更少的影响，并且其接口已集成到 SSMS 中。

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

类别错误：
- Attention
- 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
- 登录
- Logout

发现并解决常见阻塞情况

通过检查上述信息，可以确定大多数阻塞问题的原因。本文的其余部分将讨论如何使用这些信息来识别和解决一些常见的阻塞情况。此讨论假定你已使用阻止脚本（前面引用）来捕获有关阻止 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 正在运行一个后台任务，例如死锁检测、日志编写器或检查点。
Sleeping	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_sessions.open_transaction_count，此列将显示 0。
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）。这可以通过比较 sys.dm_exec_requests 输出之间的 wait_resource 来验证，该输出显示了请求正在等待的资源。

sys.dm_exec_requests.wait_resource

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

资源	格式	示例	说明
表	DatabaseID:ObjectID:IndexID	TAB:5:261575970:1	在这种情况下，数据库 ID 5 是 pubs 示例数据库 `object_id` ，261575970是标题表，1 是聚集索引。
页面	DatabaseID:FileID:PageID	页：5:1:104	在此例中，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，页 104 是属于标题表的页。若要标识该页所属的对象，请使用动态管理函数 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`. 无法将索引键散列哈希解哈希为特定的键值。
行	DatabaseID:FileID:PageID:Slot(row)	RID：5:1:104:3	在此例中，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，页 104 是属于标题表的页，且槽 3 指示行在页上的位置。
Compile	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 提交的最后一批处于last_request_start_time运行sys.dm_exec_sessions状态时，请求运行start_timesys.dm_exec_requests的时间，等等。

常见的阻塞情况

下表列出了常见症状及其可能原因。

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

场景	Wait_type	Open_Tran	状态	Resolves?	其他症状
1	NOT 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 秒。	`sys.dm_exec_sessions` 中位于阻塞链头的 SPID 的 `hostname` 列将与它所阻塞的 SPID 相同。
5	Null	>0	回滚	是的。	此 SPID 的扩展事件会话中可能会显示注意信号，指示已发生查询超时或取消，或者只是发出了回滚语句。
6	Null	>0	正在睡眠	最终，当 Windows NT 确定会话不再处于活动状态时，连接将中断。	`sys.dm_exec_sessions` 中的 `last_request_start_time` 值比当前时间早得多。

详细的阻塞场景

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

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

解决方法：

此阻塞问题的解决方法是查找优化查询的方法。此类阻塞问题可能是性能问题，需要你将其视为此类问题。有关对特定慢速运行的查询进行故障排除的信息，请参阅如何对 SQL Server 上慢速运行的查询进行故障排除。有关详细信息，请参阅监视和优化性能。

从查询存储（SQL Server 2016 中引入）内置到 SSMS 的报告也是一种强烈建议且有价值的工具，用于识别成本最高的查询和欠佳的执行计划。

如果长时间运行的查询阻止了其他用户，并且无法对其进行优化，请考虑将其从 OLTP 环境移动到专用报告系统。还可以使用 AlwaysOn 可用性组来同步数据库的只读副本。

注意

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

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

此类阻塞通常由处于睡眠状态或等待事务嵌套级别的命令（@@TRANCOUNTopen_transaction_count从sys.dm_exec_requests中）大于零的 SPID 进行标识。如果应用程序遇到查询超时或取消而不发出所需的 ROLLBACK 和/或 COMMIT 语句数，则可能会出现这种情况。当 SPID 收到查询超时或取消时，它将终止当前查询和批处理，但不会自动回滚或提交事务。应用程序对此负责，因为 SQL Server 无法假定由于取消单个查询而必须回滚整个事务。查询超时或取消将在扩展事件会话中显示为 SPID 的 ATTENTION 信号事件。

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

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 连接或任何开始事务的存储过程，并且不会在发生错误后进行清理。如果发生运行时错误，此设置将中止任何打开的事务并将控制权返回给客户端。有关详细信息，请查看 SET 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 INSERTUPDATE或DELETE语句。最终，SPID1（执行INSERT，UPDATE或DELETE）在 SPID2 持有的锁上被阻止（执行）。SELECT SPID2 将结果行写入客户端 dbproc2。然后，Dbproc2 会尝试将缓冲区中的行传递到 dbproc1，但发现 dbproc1 正忙（它被阻止等待 SPID1 完成当前 INSERT（在 SPID2 上被阻止）。此时，dbproc2 被 DBproc1 阻止在应用程序层，其 SPID （SPID1）被 SPID2 阻止在数据库级别。同样，这会导致 SQL Server 无法检测或解析的死锁，因为仅涉及其中一个资源是 SQL Server 资源。

示例 A 和 B 都是应用程序开发人员必须注意的基本问题。它们必须编写应用程序代码以适当处理这些情况。

解决方法：

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

方案 5：由于会话处于回滚状态而阻止

将回滚在用户定义的事务之外终止或取消的数据修改查询。这也可能是客户端网络会话断开连接的副作用，或者请求被选为死锁牺牲品。这通常可以通过观察 ROLLBACK command的sys.dm_exec_requests输出来标识，列percent_complete可能显示进度。

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

注意

启用加速数据库恢复功能时，较长的回滚很少见。此功能已在 SQL Server 2019 中引入。

解决方法：

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

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

若要避免这种情况，请勿在 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请考虑在会话或任何开始事务的存储过程中使用，并且不会在出现错误后进行清理。如果发生中止批处理的运行时错误，此设置将自动回滚任何打开的事务并将控制权返回到客户端。有关详细信息，请查看 SET XACT_ABORT (Transact-SQL)。
若要解决未适当清理其资源的客户端应用程序的孤立连接，可以使用该命令终止 SPID KILL 。有关参考信息，请参阅 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：孤立事务导致的阻止

另请参阅

反馈

其他资源