了解和解决 SQL Server 阻塞问题

适用于：SQL Server，Azure SQL 托管实例

原始 KB 数： 224453

总结

阻塞在任何基于锁的关系数据库中都是正常的，但持久性或长时间运行的阻塞会降低吞吐量并导致应用程序超时。 本文介绍SQL Server中阻止的内容、如何捕获来自动态管理视图（DMV）和扩展事件的阻止数据，以及如何识别头阻止程序。 然后，它会演练最常见的阻止方案及其解决方法。

在本文中，术语 连接 是指数据库的单个登录会话。 每个连接显示为会话 ID（SPID）或 session_id 许多 DMV。 每个 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可以在单个服务器上支持数千个同时用户，几乎没有阻塞。

对阻塞进行故障排除

无论处于哪个阻塞情况，故障排除锁定的方法都是相同的。 这些逻辑分隔决定了本文的其余构成。 也就是说，要找到头阻塞程序，并确定查询正在执行的操作以及会阻塞的原因。 确定有问题的查询（即长时间持有锁）后，下一步是分析和确定阻塞发生的原因。 了解原因后，可以通过重新设计查询和事务进行更改。

故障排除步骤：

  1. 标识主阻塞会话（头阻止程序）。

  2. 查找导致阻塞的查询和事务（长时间保留锁的内容）。

  3. 分析和了解长时间阻塞的原因。

  4. 通过重新设计查询和事务来解决阻止问题。

现在让我们深入讨论如何通过适当的数据捕获来确定主阻塞会话。

收集阻塞信息

为了降低对阻塞问题进行疑难解答的难度，数据库管理员可以使用 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_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 列。 当 = 0 时 blocking_session_id ，不会阻止会话。 虽然 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 批处理文本或输入缓冲区文本。 如果 text 的 sys.dm_exec_sql_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;

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。 此 DMV 返回有关当前遇到请求的 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 中。

在 对象资源管理器，在 XEvent Profiler 菜单下，可以找到准备在 SSMS 中启动的预制扩展事件会话。 要了解更多详细信息，请参阅 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 会话捕获应用程序活动。

分析阻塞数据

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

  • 检查 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）。 可以通过比较wait_resourcesys.dm_exec_requests两个输出来验证此条件，该输出显示请求正在等待的资源。

    • 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 指示行在页上的位置。

    • sys.dm_tran_active_transactions sys.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_time 中的 sys.dm_exec_requests 的运行时间等。

常见的阻塞情况

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

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

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

详细的阻塞场景

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

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

解决方法：

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

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

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

注意

查询执行期间的阻塞可能是由查询升级引起的，这种情况下行锁定或页锁定升级到表锁定。 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，以获取同一表上的 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：由处于回滚状态的会话导致的阻塞

在用户定义的事务之外终止或取消数据修改查询时，查询将回滚。 此回滚也可能是客户端网络会话断开连接、客户端计算机重启或选择为死锁受害者的请求的副作用。 通常无法比最初应用的更改更快地回滚数据修改查询。 例如，如果一个DELETE或INSERTUPDATE语句运行了一个小时，则至少需要一个小时才能回滚。 此行为是预期的，因为如果未回滚更改，数据库中的事务性和物理完整性将受到损害。 由于此回滚必须完成，因此SQL Server将会话标记为 KILLED/ROLLBACK 状态，并且不能再次终止会话或将其选为死锁受害者。 通常可以通过观察列报告KILLED/ROLLBACK和percent_complete列显示进度的sys.dm_exec_requestscommand输出来标识此状态。

注意

启用“加速数据库恢复功能”时，很少出现冗长回滚。 此功能已在 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 用于会话，或用于在任何开始事务且未在错误后进行清理的存储过程。 如果发生中止批处理的运行时错误，此设置会自动回滚任何打开的事务，并将控制权返回到客户端。 有关详细信息，请参阅 SET XACT_ABORT (Transact-SQL)。
  • 要解析已断开连接但未适当清理其资源的客户端应用程序的孤立连接，可以使用 KILL 命令终止 SPID。 有关参考，请参阅 KILL (Transact-SQL)。

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

KILL 99

注意

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

相关内容

  • Last updated on