SqlServer阻塞与死锁监控工具使用与问题分析

一、当数据库“堵车”了：理解阻塞与死锁

想象一下，你开车进入了一个只有一个车位的窄巷子。你停进去，准备卸货（执行一个操作）。这时，后面又来了一辆车，它也想进这个车位，但你还没出来，它就只能等着。在SqlServer里，这就叫阻塞。你的会话（Session）占着某个资源（比如一条数据记录），另一个会话也想用，就只能排队。

那什么是死锁呢？接着上面的例子，假设巷子里有两个车位A和B，但是入口很窄，一次只能进一辆车。现在有两辆车：车1停进了车位A，车2停进了车位B。然后，车1想挪到车位B去，车2想挪到车位A去。车1等着车2从B开走，车2等着车1从A开走。结果就是，两辆车谁也动不了，永远僵持下去。SqlServer的死锁就是这样，两个或以上的会话互相等待对方释放资源，形成了一个循环等待的闭环。

阻塞是常态，短时间的阻塞是并发控制的必然结果（就像等红灯）。但长时间的阻塞或频繁的死锁，就是性能问题甚至程序Bug的信号了。我们的目标不是消除所有阻塞，而是监控、分析并解决那些异常的、影响系统的阻塞和死锁。

二、必备的监控“仪表盘”：系统视图与动态管理视图

SqlServer非常贴心，为我们准备了一系列内置的“仪表盘”来查看数据库内部的运行状况，主要就是系统视图和动态管理视图。我们不需要额外安装工具，用SQL语句查询它们就能获得第一手信息。

技术栈：Microsoft SQL Server T-SQL

核心视图简介：

sys.dm_exec_requests: 查看当前正在执行的所有请求，是发现阻塞的起点。
sys.dm_exec_sessions: 查看所有会话信息。
sys.dm_tran_locks: 查看当前活动的锁信息，非常详细。
sys.dm_os_waiting_tasks: 查看正在等待资源（如锁、I/O）的任务。
sys.dm_exec_sql_text: 根据SQL句柄获取具体的SQL文本。
sys.dm_exec_connections: 查看连接信息。

三、实战演练：如何发现并解决阻塞

我们通过一个完整的例子来演示如何监控和分析一个阻塞链。

步骤1：模拟一个阻塞场景 首先，我们开两个查询窗口（会话A和会话B）。

-- 技术栈：Microsoft SQL Server T-SQL
-- 会话 A (模拟一个长时间运行的事务，不提交)
BEGIN TRANSACTION;
    UPDATE [订单表] SET 状态 = '处理中' WHERE 订单ID = 1001;
    -- 注意：这里没有 COMMIT 或 ROLLBACK，事务保持打开，锁也一直持有。
    -- 在实际中，可能是程序逻辑复杂、网络延迟或忘记提交。

-- 技术栈：Microsoft SQL Server T-SQL
-- 会话 B (尝试修改同一条记录，会被阻塞)
UPDATE [订单表] SET 状态 = '已完成' WHERE 订单ID = 1001;
-- 这条语句会一直转圈等待，因为会话A持有该记录的排他锁(X锁)。

步骤2：使用查询发现阻塞链 现在，我们在第三个窗口运行监控查询。

-- 技术栈：Microsoft SQL Server T-SQL
-- 经典阻塞链查询：找出谁阻塞了谁，以及它们在执行什么SQL
SELECT
    -- 被阻塞的会话/请求信息
    blocking.session_id AS [被阻塞会话ID],
    blocking_status = '被阻塞',
    blocking.wait_type AS [等待类型],
    blocking.wait_time AS [等待时间(ms)],
    blocking_text.text AS [被阻塞会话的SQL],
    -- 阻塞头的会话/请求信息
    head.session_id AS [阻塞头会话ID],
    head_status = '正在阻塞他人',
    head.wait_type AS [阻塞头等待类型],
    head_text.text AS [阻塞头会话的SQL],
    -- 阻塞链关系
    '会话 ' + CAST(head.session_id AS VARCHAR(10)) + ' 正在阻塞会话 ' + CAST(blocking.session_id AS VARCHAR(10)) AS [阻塞关系]
FROM sys.dm_exec_requests blocking -- 当前被阻塞的请求
OUTER APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text -- 获取被阻塞SQL
INNER JOIN sys.dm_exec_requests head -- 查找阻塞源头
    ON blocking.blocking_session_id = head.session_id
OUTER APPLY sys.dm_exec_sql_text(head.sql_handle) head_text -- 获取阻塞源头SQL
WHERE blocking.blocking_session_id > 0; -- blocking_session_id大于0表示它被阻塞了

运行这个查询，你会清晰地看到类似这样的结果：会话A（假设ID是55）正在执行一个UPDATE语句，它阻塞了会话B（假设ID是56）的UPDATE语句。这样我们就定位到了问题源头。

步骤3：进一步分析阻塞源头 知道了阻塞头会话ID（比如55），我们可以查看它更详细的信息，比如它打开了什么事务，运行了多久。

-- 技术栈：Microsoft SQL Server T-SQL
-- 查看特定阻塞会话的详细信息
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name, -- 来自哪个客户端程序（如SSMS、你的应用）
    t.transaction_id,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS [事务开启时长(秒)],
    t.transaction_begin_time,
    CASE t.transaction_type
        WHEN 1 THEN '读/写'
        WHEN 2 THEN '只读'
        WHEN 3 THEN '系统'
        WHEN 4 THEN '分布式'
        END AS [事务类型],
    request_text.text AS [最后执行的批处理SQL],
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
LEFT JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) request_text
WHERE s.session_id = 55; -- 替换为实际的阻塞头会话ID

步骤4：解决问题 根据分析结果，解决方法通常有几种：

联系源头会话所有者：通过program_name、host_name等信息，找到对应的开发或运维人员，请其检查程序逻辑，尽快提交或回滚事务（在我们的例子里，就是让会话A执行COMMIT或ROLLBACK）。
紧急情况下手动终止：如果源头会话是一个失控的查询或测试遗留，且确认可以终止，可以使用KILL命令。
```
KILL 55; -- 终止会话ID为55的会话，请谨慎操作！
```
优化程序：这是根本解决之道。检查被阻塞的SQL和阻塞头的SQL，是否可以通过优化索引、减少事务粒度、调整隔离级别（如使用READ COMMITTED SNAPSHOT）、或修改业务逻辑来避免长时间锁竞争。

四、死锁的捕获与分析：让死锁自己“开口说话”

死锁是瞬间发生的，SqlServer会自动检测并选择一个“牺牲品”回滚来打破僵局。我们的任务是捕获死锁发生时的详细信息。

方法1：启用跟踪标志和错误日志（传统方法）

-- 技术栈：Microsoft SQL Server T-SQL
-- 将死锁图形事件输出到错误日志
DBCC TRACEON (1222, -1); -- 在全局启用跟踪标志1222
-- 发生死锁后，信息会记录在SQL Server错误日志中。
-- 查看错误日志：SQL Server Management Studio -> 管理 -> SQL Server日志

这种方法信息是文本格式，可读性较差。

方法2：使用SQL Server Profiler（图形化工具，已逐渐被淘汰） 可以创建跟踪模板，捕获Deadlock graph事件。但Profiler对生产服务器有性能影响，且工具本身不再更新。

方法3：使用扩展事件（推荐，轻量且强大） 扩展事件是SqlServer新一代的诊断系统，性能开销极低。

-- 技术栈：Microsoft SQL Server T-SQL
-- 创建一个扩展事件会话来捕获死锁
CREATE EVENT SESSION [捕获死锁] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report -- 关键事件：以XML格式报告死锁
ADD TARGET package0.event_file -- 输出到文件
(SET filename = N'D:\XELogs\死锁捕获.xel', max_file_size = (10)) -- 指定文件路径和大小
WITH (STARTUP_STATE = ON); -- 服务器启动时自动启动该会话
GO
-- 启动事件会话
ALTER EVENT SESSION [捕获死锁] ON SERVER STATE = START;
GO

当死锁发生时，信息会被写入指定文件。我们可以在SSMS中“管理”->“扩展事件”下，右键会话“查看目标数据”，就能看到一个图形化的死锁图。这个图非常直观，会显示哪些会话（椭圆形）参与了死锁，它们各自持有（实线箭头）和等待（虚线箭头）哪些资源（长方形），以及它们执行的SQL语句。通过分析这个图，我们能精确找到是哪些对象（表、索引、行）和哪些SQL语句导致了循环等待。

五、关联技术：事务隔离级别的深入理解

阻塞和死锁与一个核心概念息息相关：事务隔离级别。它决定了一个事务在多大程度上“看得到”其他并发事务的修改。不同的级别在数据一致性和并发性能之间做了不同的权衡，也直接影响锁的行为和持有时间。

读未提交：能读到别人没提交的数据，基本不阻塞读，但会读到“脏数据”。几乎不用。
读已提交：只能读到已提交的数据。这是SqlServer的默认级别。读操作会请求共享锁，读完就释放（可能不是语句结束，而是读完一行就放一行），写操作会持有排他锁直到事务结束。这是大多数阻塞发生的地方。
可重复读：保证在一个事务里，两次读同一数据结果一致。共享锁会持有到事务结束，更容易导致阻塞。
可序列化：最严格的级别，通过范围锁等方式避免幻读，并发度最低，死锁风险也增加。

一个重要的优化：读已提交快照 SqlServer提供了一个叫READ_COMMITTED_SNAPSHOT的数据库选项。启用后，在读已提交隔离级别下，读操作不再请求共享锁，而是去读数据行的一个“快照”版本（存储在tempdb）。这能极大地减少读-写阻塞！写操作依然正常加锁。

-- 技术栈：Microsoft SQL Server T-SQL
-- 启用读已提交快照隔离
ALTER DATABASE [你的数据库名] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

注意：这会增加tempdb的负载，并且写操作之间、以及使用更高隔离级别的事务依然可能发生阻塞和死锁。

六、应用场景、优缺点与注意事项

应用场景：

日常性能监控与巡检：DBA定期运行阻塞查询脚本，检查系统健康度。
生产问题应急响应：当用户报告“系统卡住”、“提交没反应”时，立即使用上述工具定位阻塞源头。
性能优化与容量规划：通过分析长期的死锁和阻塞模式，发现数据库设计（如缺失索引）、应用程序逻辑（如事务过大）或架构上的瓶颈。
新功能上线前压测：在压力测试中监控阻塞和死锁，评估新代码的并发承受能力。

技术优缺点：

优点：
- 原生支持，无需成本：所有工具都内置于SqlServer。
- 信息全面：从会话、SQL、锁、事务多维度提供信息。
- 扩展事件高效：对生产系统性能影响极小，是理想的监控手段。
- 灵活性强：通过T-SQL可以定制复杂的监控和分析脚本。
缺点：
- 需要专业知识：对DBA或开发者的SqlServer内部机制理解有一定要求。
- 实时性依赖主动查询：除非配置了警报（如基于扩展事件或代理作业），否则需要人工介入查询才能发现问题。
- 历史分析需自行归档：系统视图只存当前信息，需要自己设计机制来保存历史阻塞/死锁数据以供分析趋势。

注意事项：

谨慎使用KILL命令：终止一个生产会话可能导致数据不一致或业务中断，务必先确认该会话的任务性质。
理解业务上下文：不要只看SQL。结合program_name、host_name和业务知识判断阻塞是否合理。一个夜间批处理作业长时间持有锁可能是设计如此。
关注tempdb：如果使用了快照隔离（如RCSI），务必监控tempdb的性能和空间。
治标更要治本：手动KILL和调整超时时间是“治标”，优化索引、重构事务逻辑、合理使用隔离级别才是“治本”。
建立监控基线：了解你的系统在正常情况下的阻塞情况，才能快速识别异常。

七、总结

处理SqlServer的阻塞和死锁，就像一位经验丰富的交通警察处理城市拥堵。核心工具就是系统动态管理视图（DMV）和扩展事件。通过DMV，我们可以实时“看到”哪里堵了、谁在堵谁；通过扩展事件，我们可以为“交通事故”（死锁）安装一个自动记录仪。

解决问题的流程通常是：发现阻塞 -> 定位源头 -> 分析原因（看SQL、看事务、看锁）-> 采取行动（优化或终止）。整个过程需要我们不仅懂技术，还要懂业务。

最重要的是，我们要建立起预防意识。通过合理的索引设计、精简的事务范围、正确的隔离级别选择（如考虑启用READ_COMMITTED_SNAPSHOT），以及良好的编程习惯（如及时提交事务），可以从源头上减少严重阻塞和死锁的发生。将这些监控手段融入日常运维，就能让我们的数据库系统运行得更加顺畅、稳定。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。