适用于:SQL Server、Azure SQL 托管实例
原始 KB 数: 224453
总结
阻塞在任何基于锁的关系数据库中都是正常的,但持久性或长时间运行的阻塞会降低吞吐量并导致应用程序超时。 本文介绍 SQL Server 中什么是阻塞、如何从动态管理视图(DMV)和扩展事件中捕获阻塞数据,以及如何识别阻塞头。 然后,它会演练最常见的阻止方案及其解决方法。
在本文中,术语 连接 是指数据库的单个登录会话。 每个连接在许多 DMV 中显示为会话 ID(SPID)或 session_id。 每个 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可以在单个服务器上支持数千个同时用户,几乎没有阻塞。
阻塞故障排除
无论处于哪个阻塞情况,故障排除锁定的方法都是相同的。 这些逻辑分隔决定了本文的其余构成。 也就是说,要找到头阻塞会话,并确定该查询正在执行的操作以及它发生阻塞的原因。 一旦确定了有问题的查询(即长时间持有锁的查询),下一步就是分析并确定发生阻塞的原因。 了解原因后,可以通过重新设计查询和事务进行更改。
故障排除步骤:
识别主阻塞会话(head blocker)。
查找导致阻塞的查询和事务(长时间保留锁的内容)。
分析和了解长时间阻塞的原因。
通过重新设计查询和事务来解决阻塞问题。
现在让我们深入讨论如何通过适当的数据捕获来确定主阻塞会话。
收集阻塞信息
为了降低对阻塞问题进行疑难解答的难度,数据库管理员可以使用 SQL 脚本持续监视 SQL Server 上的锁定和阻塞状态。 若要收集这些数据,请使用两种补充方法。
第一种方法是查询动态管理对象(DMOs),并存储结果以供随时间推移进行比较。 本文中引用的一些对象是动态管理视图 (DMV),一些是动态管理函数 (DMF)。
第二种方法是使用 扩展事件 (XEvents)捕获正在执行的内容。 将 SQL LogScout 与 GeneralPerf 或 DetailedPerf 方案配合使用来收集此数据。 已弃用 SQL 跟踪和 SQL Server Profiler。
从 DMV 收集信息
若要排查阻塞问题,请使用 DMV 标识阻塞链头和 SQL 语句的 SPID(会话 ID)。 查找被阻塞的作为牺牲品的 SPID。 如果某个 SPID 被另一个 SPID 阻塞,请调查拥有该资源的 SPID(阻塞 SPID)。 该所有者 SPID 是否也被阻塞? 可以按照链找到头部阻塞进程,然后调查其持续持有该锁的原因。
为此,请使用下列方法之一:
在 SQL Server Management Studio (SSMS) 的 对象资源管理器 中,右键单击顶层服务器对象,展开 报表,再展开 标准报表,然后选择 活动 - 所有阻塞事务。 此报表显示阻塞链头的当前事务。 如果展开事务,报表将显示被头部事务阻止的事务。 此报表还将显示造成阻塞的 SQL 语句和已阻塞的 SQL 语句。
在 SSMS 中打开活动监视器并查看 “被阻止者” 列。 有关详细信息,请参阅 活动监视器。
通过 DMV 还可获取更详细的基于查询的方法:
命令
sp_who和sp_who2是显示所有当前会话的较旧命令。 DMVsys.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 批处理文本或输入缓冲区文本。 如果
text的sys.dm_exec_sql_text列返回的数据为 NULL,则当前未执行查询。 在这种情况下,sys.dm_exec_input_buffer的event_info列将包含传递给 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。 此 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 查询结果,以识别持续存在的阻塞或变化趋势。 建议用于收集此数据的工具是 SQL LogScout。 它使用 SQL Server Perf 统计信息脚本定期从前面的 DMV 收集结果。 使用 LightPerf、 GeneralPerf 或 DetailedPerf 方案。 有关详细信息,请参阅 SQL LogScout 方案。
从扩展事件收集信息
除了上述信息,你通常需要捕获服务器上的活动的跟踪,以彻底调查SQL Server中的阻塞问题。 例如,如果某个会话在一个事务中执行了多条语句,则只会显示该会话提交的最后一条语句。 但是,前面的语句之一可能是锁仍未释放的原因之一。 通过跟踪,可以查看会话在当前事务中执行的所有命令。
将 SQL LogScout 与 GeneralPerf 或 DetailedPerf 方案配合使用,以进行阻塞诊断时捕获扩展事件。 与已弃用的 SQL Server Profiler 不同,扩展事件提供了一个对被观察系统影响较小的跟踪平台。 你还可以在 SSMS 中管理扩展事件。
在 对象资源管理器,在 XEvent Profiler 菜单下,可以找到准备在 SSMS 中启动的预制扩展事件会话。 要了解更多详细信息,请参阅 XEvent Profiler。 你还可以在 SSMS 中创建自定义的扩展事件会话。 有关详细信息,请参阅 扩展事件新建会话向导。 若要排查阻塞问题,通常捕获:
- 类别错误:
- 注意
- Blocked_process_report**
- Error_reported (通道管理员)
- Exchange_spill
- Execution_warning
**若要配置生成阻塞进程报告的阈值和频率,请使用 sp_configure 命令配置阻塞进程阈值选项,该选项可在几秒内设置完成。 默认情况下,不生成阻塞的进程报告。
分类警告:
- Hash_warning
- 缺少列统计信息
- 缺少联接谓词
- Sort_warning
类别执行:
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
类别锁
- Lock_deadlock
会话类别
- Existing_connection
- 登录
- 注销
发现并解决常见阻塞情况
通过查看前述信息,可以确定大多数阻塞问题的原因。 本文其余部分将介绍如何利用这些信息来识别并解决一些常见的阻塞场景。 本讨论假定你使用阻止脚本(前面引用)捕获有关阻止 SPID 的信息,并使用 XEvent 会话捕获应用程序活动。
分析阻塞数据
使用
sys.dm_exec_requests和sys.dm_exec_sessions检查 DMVblocking_these和session_id的输出,以确定阻塞链头。 此输出清楚地标识了哪些请求被阻止,哪些请求造成阻塞。 进一步查看被阻塞和造成阻塞的会话。 阻塞链是否有共同点或根源? 它们可能共享一个公共表,并且阻塞链中涉及的一个或多个会话正在执行写入操作。检查 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_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上一个查询wait_time的值的列中的值sys.dm_exec_requests时,此条件表示先前的锁已获取并释放,并且现在正在等待新锁(假设为非零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_id261575970 表示 titles 表,1 表示聚集索引。页面 DatabaseID:FileID:PageID 页: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。 无法将索引键散列哈希解哈希为特定的键值。行 DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 在这种情况下,数据库 ID 5 是 pubs,文件 ID 1 是主数据文件,页 104 是属于 titles 表的一个页,而槽 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_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 | 状态 | 是否已解决? | 其他症状 |
|---|---|---|---|---|---|
| 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 上慢速运行的查询进行故障排除。 有关详细信息,请参阅监视和优化性能。
SSMS 中内置的来自 查询存储(在 SQL Server 2016 中引入)的报告,也是一个非常推荐且很有价值的工具,用于识别成本最高的查询和欠佳的执行计划。
如果长时间运行的查询阻止了其他用户,并且无法对其进行优化,请考虑将其从 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、INSERT 或 UPDATE 语句运行了一个小时,则至少需要一个小时才能回滚。 此行为是预期的,因为如果未回滚更改,数据库中的事务性和物理完整性将受到损害。 由于此回滚必须完成,因此SQL Server将会话标记为 KILLED/ROLLBACK 状态,并且不能再次终止会话或将其选为死锁受害者。 通常可以通过观察列报告sys.dm_exec_requests和command列显示进度的KILLED/ROLLBACKpercent_complete输出来标识此状态。
解决方法:
等待会话完成回滚更改。
如果在此操作中间关闭实例,则在重新启动时数据库处于恢复模式,并且该实例在处理所有打开的事务之前不可访问。 对于每个事务,启动恢复所需的时间与运行时恢复基本相同,并且在此期间数据库无法访问。 因此,强制服务器在回滚状态下修复 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 秒才能完成。