了解并解决阻止问题

适用于：Azure SQL 数据库Microsoft Fabric SQL 数据库

本文介绍了 Azure SQL 数据库和 Fabric SQL 数据库中的阻塞，并演示了如何排除和解决阻塞。

目标

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

有关死锁故障排除的信息，请参阅 分析和防止 Azure SQL 数据库和 Fabric SQL 数据库中的死锁。

注意

此内容重点介绍了 Azure SQL 数据库。 Azure SQL 数据库基于最新稳定版本的 Microsoft SQL Server 数据库引擎，因此很多内容是相似的，尽管故障排除选项和工具可能有所不同。 有关 SQL Server 中阻塞的详细信息，请参阅了解并解决 SQL Server 阻塞问题。 Fabric SQL 数据库与 Azure SQL 数据库共享许多功能。 有关性能监视的详细信息，请参阅 Microsoft Fabric 中的监视 SQL 数据库。

了解阻塞

对于采用基于锁的并发机制的任何关系数据库管理系统 (RDBMS) 而言，阻塞是不可避免以及设计使然的特征。 在 Azure SQL 数据库中，当一个会话对特定资源持有锁，且第二个会话 ID 尝试对同一资源获取冲突锁类型时，就会发生阻塞。 通常，第一个会话 ID 对资源持有的锁的时间范围较小。 当拥有的会话释放锁时，第二个连接就可以自由地获取自己对资源的锁并继续处理。 此行为是正常的，一天中可能会发生多次，对系统性能没有明显影响。

Azure SQL 数据库中的每个新数据库默认启用了读取已提交的快照 (RCSI) 数据库设置。 在使用行版本控制提高并发的 RCSI 下，读取数据的会话与写入数据的会话之间的阻塞可最大程度地减轻。 但是，Azure SQL 数据库中的数据库仍可能发生阻塞和死锁，因为：

• 修改数据的查询可能相互阻塞。
• 查询可能在增加阻塞的隔离级别下运行。 可以在应用程序连接字符串、查询提示或者 Transact-SQL 的 SET 语句中指定隔离级别。
• 可以禁用 RCSI，使数据库使用共享 (S) 锁来保护在读取提交隔离级别下运行的 SELECT 语句。 这可能会增加阻塞和死锁。

对于 Azure SQL 数据库中的新数据库，默认还会启用快照隔离级别。 快照隔离是一个附加的基于行的隔离级别，它为数据提供事务级一致性，并使用行版本来选择要更新的行。 若要使用快照隔离，查询或连接必须将其事务隔离级别显式设置为 SNAPSHOT。 只能在为数据库启用了快照隔离时执行此操作。

可以使用 Transact-SQL 确定是否启用了 RCSI 和/或快照隔离。 连接到 Azure SQL 数据库中的数据库并运行以下查询：

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

如果启用了 RCSI，则 is_read_committed_snapshot_on 列会返回值 1。 如果启用了快照隔离，则 snapshot_isolation_state_desc 列会返回值 ON。

查询的持续时间和事务上下文决定了其锁的保留时间，以及它们对其他查询的影响。 在 RCSI 下运行的 SELECT 语句不会获取正在读取的数据上的共享 (S) 锁，因此不会阻塞正在修改数据的事务。 对于 INSERT、UPDATE 和 DELETE 语句，在查询过程中会保留锁，以确保数据一致性，并允许在必要时回滚查询。

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

• 数据库引擎中的锁定
• 自定义锁定和行版本控制
• 锁模式
• 锁兼容性
• 事务

当锁定和阻塞持续到某种程度时，会对系统性能产生不利影响，这是由于以下原因之一：

• 会话 ID 会在长时间内对一组资源持有锁，然后才释放这些资源。 这种类型的阻塞会随着时间的推移自行解决，但会导致性能下降。

• 会话 ID 持有一组资源的锁，并且永远不会释放它们。 这种类型的阻塞不会自行解决，并且会无限期地阻止对受影响资源的访问。

在第一个情境中，情况可能变化多端，因为不同的会话 ID 会随着时间推移而阻塞不同的资源，从而形成不断变化的目标。 使用 SQL Server Management Studio 将问题缩小到单个查询，很难解决这些问题。 相反，第二种情况会导致一致的状态，更容易诊断。

优化锁定

优化锁定是一项新的数据库引擎功能，可大幅减少锁内存和写入时并发所需的锁数量。 优化锁定使用两个主要组件：事务 ID (TID) 锁定（也用于其他行版本控制功能）和限定后锁定 (LAQ)。 它不需要任何额外的配置。

本文目前适用于没有优化锁定的数据库引擎的行为。

若要了解详细信息，并了解可在何处使用优化锁定，请参阅优化锁定。

应用程序和阻塞

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

在数据库和应用程序的设计和构造阶段，要注意数据库的性能。 特别是，应为每个查询计算资源消耗、隔离级别和事务路径长度。 每个查询和事务都应尽可能轻量。 必须执行良好的连接管理规则。 如果没有它，应用程序在少量用户上可能具有可接受的性能，但随着用户数的纵向扩展，性能可能会显著下降。

通过对应用程序和查询进行适当设计，Azure SQL 数据库可以在一台服务器上同时支持数千个用户，几乎没有阻塞。

注意

有关更多应用程序开发指南，请参阅 排查连接问题和其他错误 以及 暂时性故障处理。

对阻塞进行故障排除

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

故障排除步骤：

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

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

3. 分析/理解长时间阻塞的原因

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

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

收集阻塞信息

为了攻克对阻塞问题进行故障排除的难题，数据库管理员可以使用 SQL 脚本不断监视 Azure SQL 数据库中数据库的锁定和阻塞状态。 若要收集此数据，实质上有两种方法。

第一种方法是查询动态管理对象 (DMO) 并存储结果，以便随着时间的推移进行比较。 本文中引用的一些对象是动态管理视图 (DMV)，一些是动态管理函数 (DMF)。 第二种方法是使用 XEvent 来捕获正在执行的内容。

从 DMV 收集信息

引用 DMV 排查阻塞问题的目标是识别阻塞链顶部的会话 ID 和 SQL 语句。 查找被阻止的受害者会话 ID。 如果某个会话 ID 被另一个会话 ID 阻止，则调查拥有资源的会话 ID（即阻止的会话 ID）。 是否也阻止了所有者会话 ID？ 你可以遍历链来找到头阻塞程序，然后调查其锁定的原因。

请记得在 Azure SQL 数据库中的目标数据库中运行其中每个脚本。

• 命令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);

• 请参阅 blocking_session_id 中的列 sys.dm_exec_requests。 当 blocking_session_id = 0 时，将不会阻塞会话。 虽然 sys.dm_exec_requests 只列出当前正在执行的请求，但任何连接（活动或不活动）都将列在 sys.dm_exec_sessions 中。 在下一个查询中，在 sys.dm_exec_requests 和 sys.dm_exec_sessions 之间建立此公共联接。

• 使用 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;

• 若要捕获长时间运行或未提交的事务，请使用另一组 DMV 来查看当前打开的事务，包括 sys.dm_tran_database_transactions、sys.dm_tran_session_transactions、sys.dm_exec_connections 以及 sys.dm_exec_sql_text。 有几个 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 的线程/任务层）。 这将返回关于当前经历的请求的 SQL 等待类型的信息。 与 sys.dm_exec_requests 一样，sys.dm_os_waiting_tasks 只返回活动请求。

注意

有关等待类型的详细信息（包括随时间变化的聚合的等待统计信息），请参阅 DMV sys.dm_db_wait_stats。 此 DMV 仅返回当前数据库的聚合等待统计信息。

• 使用 sys.dm_tran_locks DMV，获取有关查询所放置的锁的更详尽信息。 此 DMV 可以返回生产数据库上的大量数据，这对于诊断当前保留的锁非常有用。

由于 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 存储查询结果以提供数据点，方便您在指定时间间隔内分析阻塞情况，从而识别持久阻塞或趋势。

从扩展事件中收集信息

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

在 SQL Server 中捕获跟踪有两种方法：扩展事件 (XEvent) 和探查器跟踪。 但是，SQL Server Profiler 是已被弃用的跟踪技术，Azure SQL 数据库不支持这种技术。 扩展事件 是一种较新的跟踪技术，允许对观察到的系统产生更多多功能性和更少的影响，并且其接口已集成到 SQL Server Management Studio （SSMS） 中。

请参阅说明如何在 SSMS 中使用扩展事件新建会话向导的文档。 但对于 Azure SQL 数据库，SSMS 在对象资源管理器中的每个数据库下都提供了扩展事件子文件夹。 使用扩展事件会话向导捕获这些有用的事件：

• 分类错误：

  • Attention
  • Error_reported
  • Execution_warning

• 分类警告：

  • Missing_join_predicate

• 分类执行：

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• 类别 deadlock_monitor

  • database_xml_deadlock_report

• 类别会话

  • Existing_connection
  • 登录
  • Logout

注意

有关死锁的详细信息，请参阅 分析和防止 Azure SQL 数据库和 Fabric SQL 数据库中的死锁。

发现并解决常见阻塞情况

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

分析阻塞数据

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

• 检查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 输出的会话 ID 信息，以获取有关阻塞链开头的详细信息。 查找以下字段：

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

  状态	含义
  背景	会话 ID 正在运行后台任务，例如死锁检测、日志编写器或检查点。
  Sleeping	会话 ID 当前未执行。 这通常表示会话 ID 正在等待应用程序中的命令。
  运行	会话 ID 当前正在调度程序上运行。
  可运行	会话 ID 位于调度器的可运行队列中，并等待获取调度器时间。
  已挂起	会话 ID 正在等待资源，例如锁或闩锁。

  • sys.dm_exec_sessions.open_transaction_count
    此字段告诉你此会话中打开的事务数。 如果此值大于 0，则会话 ID 位于打开的事务中，并且可能持有事务中任何语句获取的锁。

  • sys.dm_exec_requests.open_transaction_count
    同样，此字段告诉你此请求中打开的事务数。 如果此值大于 0，则会话 ID 位于打开的事务中，并且可能持有事务中任何语句获取的锁。

  • 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 列中返回的值小于前一个 wait_time 查询中的 sys.dm_exec_requests 值时，这表示已获取并释放前一个锁，现在正在等待一个新锁（假设为非零 wait_time）。 这可以通过比较 wait_resource 输出之间的 sys.dm_exec_requests 来验证，该输出显示了请求正在等待的资源。

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

  资源	格式	示例	说明
  表	DatabaseID:ObjectID:IndexID	TAB:5:261575970:1	在此例中，数据库 ID 5 是 pubs 示例数据库，对象 ID 261575970 是标题表，1 是聚集索引。
  页	DatabaseID:FileID:PageID	页：5:1:104	在这种情况下，数据库 ID 5 is 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 对应于 index_id 2 对于 object_id 261575970（titles 表）。 使用 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_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
  , azure_dtc_state = CASE tat.dtc_state 
                      WHEN 1 THEN 'ACTIVE'
                      WHEN 2 THEN 'PREPARED'
                      WHEN 3 THEN 'COMMITTED'
                      WHEN 4 THEN 'ABORTED'
                      WHEN 5 THEN 'RECOVERED' END
  , 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 中的其余列来了解问题的根源。 它们的用处因问题的具体情况而异。 例如，可以确定问题是否仅发生在特定客户端（主机名）、特定网络库（net_library）、会话 ID last_request_start_timesys.dm_exec_sessions提交的最后一批、请求运行start_timesys.dm_exec_requests时间等。

常见的阻塞情况

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

Waittype、Open_Tran和Status列指的是sys.dm_exec_request返回的信息。 其他列可能由 sys.dm_exec_sessions返回。 “Resolves？”列指示阻塞是否自行解除，还是应通过 KILL 命令杀死会话。 有关详细信息，请参阅 KILL。

方案	Waittype	Open_Tran	状态	Resolves?	其他症状
1	不可为空	>= 0	可运行	是，当查询完成时。	在 sys.dm_exec_sessions、 reads、 cpu_time和/或 memory_usage 列中，随着时间推移而增加。 完成查询所需的时间很长。
2	Null	>0	正在睡眠	否，但会话 ID 可以终止。	此会话 ID 的扩展事件会话中可能会显示一个警告信号，表明查询超时或已取消。
3	Null	>= 0	可运行	否。 在客户端提取所有行或关闭连接之前无法解析。 会话 ID 可以终止，但最长可能需要 30 秒。	如果open_transaction_count = 0，并且会话 ID 在事务隔离级别为默认值（READ COMMITTED）时保留锁，则这可能是原因。
4	多种多样	>= 0	可运行	否。 在客户端取消查询或关闭连接之前无法解析。 会话 ID 可以被终止，但可能需要多达 30 秒。	在阻塞链首部的会话 ID 所在的hostname列与它阻止的某个会话 ID 相同。
5	Null	>0	回滚	是的。	此会话 ID 的扩展事件会话中可能会出现一个警告信号，指示查询超时、取消，或只是发出了回滚语句。
6	Null	>0	正在睡眠	最终， 当 Windows 确定会话不再处于活动状态时，Azure SQL 数据库连接将中断。	last_request_start_time 中的 sys.dm_exec_sessions 值比当前时间早得多。

详细的阻塞场景

1. 由执行时间长的正常运行查询引起的阻塞

   解决方法：此类阻塞问题的解决方案是寻找优化查询的方法。 实际上，这类阻塞问题可能只是一个性能问题，需要你按照性能问题来处理。 有关对特定慢速运行的查询进行故障排除的信息，请参阅如何对 SQL Server 上慢速运行的查询进行故障排除。 有关详细信息，请参阅监视和优化性能。

   来自 SSMS 中的查询存储的报表也是一个高度推荐的有用工具，可用于识别成本最高的查询、欠佳的执行计划。 另请查看 查询性能见解。

   如果查询仅执行 SELECT 操作，请考虑在快照隔离下运行该语句（如果在数据库中启用了快照隔离），尤其是在已禁用 RCSI 的情况下。 与启用了 RCSI 时一样，读取数据的查询不需要快照隔离级别下的共享 (S) 锁。 此外，快照隔离为显式多语句事务中的所有语句提供事务级一致性。 快照隔离可能已在你的数据库中启用。 还可以对执行修改的查询使用快照隔离，但你需要处理更新冲突。

   如果有一个长时间运行的查询正在阻塞其他用户，并且无法进行优化，请考虑将其从 OLTP 环境移动到专用的报表系统，即数据库的同步只读副本。

2. 由具有未提交的事务的休眠会话 ID 导致的阻塞

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

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

   CREATE TABLE #test (col1 INT);
   INSERT INTO #test SELECT 1;
   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 的应用程序）之前运行一些查询，则暂时禁用连接池可能有助于缓解问题，直到修改客户端应用程序以适当处理错误。 通过禁用连接池，释放连接会导致 Azure SQL 数据库连接的物理断开，从而导致服务器回滚任何打开的事务。
     • 将 SET XACT_ABORT ON 用于连接，或用于开始事务且在出现错误后未进行清理的任何存储过程中。 如果发生运行时错误，此设置会中止任何打开的事务并将控制权返回给客户端。 有关详细信息，请查看 SET XACT_ABORT。

   注意

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

   注意

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

3. 由于客户端应用程序没有完全提取所有结果行，会话 ID 导致了阻塞。

   向服务器发送查询后，所有应用程序必须立即将所有结果行提取到完成。 如果应用程序没有提取所有结果行，则表上可能会留下锁，从而阻塞其他用户。 如果使用的应用程序透明地向服务器提交 SQL 语句，则应用程序必须提取所有结果行。 如果没有（如果无法配置为这样做），则可能无法解决阻塞问题。 若要避免此问题，你可以将性能不佳的应用程序限制在报表或决策支持数据库中，与主 OLTP 数据库分离。

   在数据库上启用读取已提交的快照（这是 Azure SQL 数据库中的默认配置）时，可以减轻此方案的影响。 请在本文的了解阻塞部分了解详细信息。

   注意

   有关连接到 Azure SQL 数据库的应用程序，请参阅重试逻辑指南。

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

4. 由处于回滚状态的会话导致的阻塞

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

   由于 2019 年引入的加速数据库恢复，较长的回滚应该很少见。

   解决方法：等待会话 ID 完成回滚所做的更改。

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

5. 由孤立连接导致的阻塞

   如果客户端应用程序捕获错误或客户端工作站重新启动，则在某些情况下，与服务器的网络会话可能不会立即取消。 从 Azure SQL 数据库的角度来看，客户端似乎仍然存在，并且获得的任何锁可能仍然会保留。 有关详细信息，请参阅如何对 SQL Server 中的孤立用户进行故障排除。

   解决方法：如果客户端应用程序断开连接而不适当地清理其资源，则可以使用 KILL 命令终止会话 ID。 KILL 命令采用会话 ID 值作为输入。 例如，若要终止会话 ID 99，请发出以下命令：

   KILL 99
   

相关内容

• 分析和防止 Azure SQL 数据库和 Fabric SQL 数据库中的死锁
• Azure SQL 数据库与 Azure SQL 托管实例中的监视和性能优化
• 使用查询存储来监视性能
• 事务锁定和行版本控制指南
• SET TRANSACTION ISOLATION LEVEL （Transact-SQL）
• 快速入门：扩展事件
• Azure SQL 数据库：通过自动优化改进性能优化
• 使用 Azure SQL 提供一致的性能
• 连接问题及其他错误排查
• 暂时性故障处理
• 在 Azure SQL 数据库中配置最大并行度 (MAXDOP)
• 在 Microsoft Fabric 中诊断和排查 Azure SQL 数据库和 SQL 数据库上的高 CPU 问题