SqlServer死锁问题的监控与预防措施

一、死锁到底是什么鬼？

咱们先来打个比方。想象一下两个人在狭窄的走廊里迎面相遇，都想让对方先走，结果谁都不让谁，就这么僵持住了。数据库里的死锁也是这个道理 - 两个或多个事务互相等待对方释放资源，结果谁都进行不下去。

在SQL Server中，死锁可不是什么稀罕事。当多个事务同时操作数据库时，如果它们获取锁的顺序不一致，就很容易出现这种"你等我，我等你"的尴尬局面。

举个简单的例子：

-- 事务1
BEGIN TRANSACTION;
UPDATE 用户表 SET 余额 = 余额 - 100 WHERE 用户ID = 1;
-- 这里事务1持有了用户ID=1的行的锁

-- 同时，事务2也在运行
BEGIN TRANSACTION;
UPDATE 用户表 SET 余额 = 余额 + 50 WHERE 用户ID = 2;
-- 事务2持有了用户ID=2的行的锁

-- 接着事务1尝试访问事务2锁住的资源
UPDATE 用户表 SET 余额 = 余额 + 100 WHERE 用户ID = 2;
-- 事务1被阻塞，等待事务2释放锁

-- 同时事务2也尝试访问事务1锁住的资源
UPDATE 用户表 SET 余额 = 余额 - 50 WHERE 用户ID = 1;
-- 事务2被阻塞，等待事务1释放锁

-- 现在好了，两个事务互相等待，死锁形成！

二、SQL Server死锁监控三板斧

1. 使用SQL Server Profiler抓现行

SQL Server Profiler就像数据库的监控摄像头，能实时记录死锁事件。配置方法如下：

-- 1. 打开SQL Server Profiler
-- 2. 新建跟踪，选择"TSQL"模板
-- 3. 在事件选择中，添加以下事件：
--    - Locks: Deadlock graph
--    - Locks: Deadlock
--    - Errors and Warnings: Deadlock
-- 4. 运行跟踪，死锁发生时就能捕获详细信息

Profiler的优点是直观易用，缺点是会对服务器性能造成一定影响，不适合长期在生产环境使用。

2. 系统视图和扩展事件

更高级的做法是使用系统视图和扩展事件(XEvents)：

-- 查询最近发生的死锁
SELECT * FROM sys.dm_tran_locks;
SELECT * FROM sys.dm_os_waiting_tasks;

-- 设置扩展事件会话监控死锁
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'DeadlockMonitor')
GO

-- 启动会话
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;
GO

扩展事件的优点是性能开销小，可以长期运行，还能捕获更详细的死锁信息。

3. 使用死锁图分析工具

SQL Server生成的死锁图就像犯罪现场的示意图，能清晰展示死锁的来龙去脉：

-- 从错误日志中提取死锁图
EXEC sp_readerrorlog 0, 1, 'deadlock'

-- 或者从扩展事件会话中获取
SELECT CAST(event_data AS XML) AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file(
    'DeadlockMonitor*.xel',
    NULL, NULL, NULL
)
WHERE CAST(event_data AS XML).value('(event/@name)[1]', 'varchar(100)') = 'xml_deadlock_report'

把获取到的XML保存为.xdl文件，用SQL Server Management Studio打开就能看到直观的死锁图了。

三、预防死锁的六大绝招

1. 统一访问顺序

死锁往往因为事务访问资源的顺序不一致导致。解决方法很简单 - 约定俗成：

-- 不好的做法：不同事务以不同顺序更新表
-- 事务1
BEGIN TRANSACTION;
UPDATE 表A SET ...;
UPDATE 表B SET ...;
COMMIT;

-- 事务2
BEGIN TRANSACTION;
UPDATE 表B SET ...;
UPDATE 表A SET ...;
COMMIT;

-- 好的做法：统一按照表A→表B的顺序访问
BEGIN TRANSACTION;
UPDATE 表A SET ...;
UPDATE 表B SET ...;
COMMIT;

2. 减小事务范围和持续时间

大事务是死锁的温床，尽量拆分成小事务：

-- 不好的做法：一个大事务做所有事
BEGIN TRANSACTION;
-- 几十个更新操作...
COMMIT;

-- 好的做法：拆分小事务
BEGIN TRANSACTION;
-- 只做必要的几个相关操作
COMMIT;

BEGIN TRANSACTION;
-- 下一批相关操作
COMMIT;

3. 使用适当的隔离级别

不是所有场景都需要最高的隔离级别：

-- 默认的READ COMMITTED在大多数场景已经足够
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 读多写少的报表查询可以使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- 只有在必要时才使用可序列化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4. 添加索引减少锁范围

缺少索引会导致锁升级，增加死锁概率：

-- 查询条件字段没有索引会导致表扫描，锁住整个表
SELECT * FROM 订单表 WHERE 客户ID = 100 AND 订单状态 = '待付款';

-- 添加适当的索引
CREATE INDEX IX_订单表_客户ID_订单状态 ON 订单表(客户ID, 订单状态);

-- 现在查询只锁定相关行，减少死锁可能

5. 使用NOLOCK提示（谨慎使用）

对于允许脏读的查询，可以使用NOLOCK：

-- 报表查询可以使用NOLOCK避免阻塞
SELECT * FROM 大表 WITH(NOLOCK) WHERE 日期 > '2023-01-01';

-- 但要注意：NOLOCK可能读到未提交的数据，不适合财务等关键业务

6. 设置死锁优先级

可以让不太重要的事务主动退出死锁：

-- 设置当前会话的死锁优先级较低
SET DEADLOCK_PRIORITY LOW;

-- 或者让特定事务在死锁时优先被牺牲
BEGIN TRANSACTION;
SET DEADLOCK_PRIORITY LOW;
-- 事务操作...
COMMIT;

四、实战案例分析

案例1：订单系统中的经典死锁

场景描述：两个用户同时修改自己的订单和地址信息，导致死锁。

-- 用户A的操作
BEGIN TRANSACTION;
-- 先更新自己的地址
UPDATE 用户地址 SET 电话 = '13800138000' WHERE 用户ID = 1;
-- 然后更新订单联系人信息
UPDATE 订单 SET 联系人电话 = '13800138000' WHERE 用户ID = 1;
COMMIT;

-- 同时用户B的操作
BEGIN TRANSACTION;
-- 先更新订单状态
UPDATE 订单 SET 状态 = '已付款' WHERE 用户ID = 2;
-- 然后更新地址
UPDATE 用户地址 SET 默认地址 = 1 WHERE 用户ID = 2;
COMMIT;

解决方案：

统一按照"先地址后订单"的顺序访问
为用户ID添加索引
减小事务范围，分开更新

案例2：库存管理中的并发死锁

-- 事务1：从仓库A调货到仓库B
BEGIN TRANSACTION;
UPDATE 库存 SET 数量 = 数量 - 10 WHERE 仓库ID = 1 AND 商品ID = 100;
UPDATE 库存 SET 数量 = 数量 + 10 WHERE 仓库ID = 2 AND 商品ID = 100;
COMMIT;

-- 事务2：从仓库B调货到仓库A
BEGIN TRANSACTION;
UPDATE 库存 SET 数量 = 数量 - 5 WHERE 仓库ID = 2 AND 商品ID = 100;
UPDATE 库存 SET 数量 = 数量 + 5 WHERE 仓库ID = 1 AND 商品ID = 100;
COMMIT;

解决方案：

按照仓库ID排序，统一先操作ID小的仓库
使用存储过程封装调货逻辑
添加适当的重试机制

五、高级技巧与工具

1. 使用TRY...CATCH处理死锁

BEGIN TRY
    BEGIN TRANSACTION;
    -- 业务操作...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205  -- 死锁错误号
    BEGIN
        -- 记录死锁信息
        INSERT INTO 死锁日志(错误信息, 发生时间)
        VALUES (ERROR_MESSAGE(), GETDATE());
        
        -- 等待随机时间后重试
        DECLARE @retry INT = 0;
        WHILE @retry < 3
        BEGIN
            WAITFOR DELAY '00:00:' + RIGHT('0' + CAST(CAST(RAND() * 5 AS INT) AS VARCHAR), 2);
            BEGIN TRY
                BEGIN TRANSACTION;
                -- 重试业务操作...
                COMMIT TRANSACTION;
                BREAK;
            END TRY
            BEGIN CATCH
                SET @retry = @retry + 1;
                IF @retry = 3
                    THROW;  -- 重试3次后放弃
            END CATCH
        END
    END
    ELSE
        THROW;  -- 非死锁错误直接抛出
END CATCH

2. 使用内存优化表

SQL Server的内存优化表使用乐观并发控制，可以避免锁争用：

-- 创建内存优化表
CREATE TABLE 内存订单 (
    订单ID INT IDENTITY PRIMARY KEY NONCLUSTERED,
    客户ID INT NOT NULL INDEX IX_客户ID HASH WITH (BUCKET_COUNT = 100000),
    订单金额 MONEY NOT NULL,
    订单状态 NVARCHAR(20) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- 内存表操作使用原子块，不会产生死锁
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
    -- 业务操作...
END

3. 使用Service Broker队列

将可能产生冲突的操作改为异步处理：

-- 创建队列
CREATE QUEUE 订单处理队列;

-- 创建服务
CREATE SERVICE 订单处理服务 ON QUEUE 订单处理队列;

-- 发送消息到队列
DECLARE @dialog UNIQUEIDENTIFIER;
BEGIN DIALOG @dialog
    FROM SERVICE [订单处理服务]
    TO SERVICE '订单处理服务'
    ON CONTRACT [DEFAULT];

SEND ON CONVERSATION @dialog
MESSAGE TYPE [DEFAULT] ('<订单操作>...</订单操作>');

六、总结与最佳实践

经过上面的分析和案例，我们可以总结出以下最佳实践：

监控先行：没有监控就谈不上优化，建立完善的死锁监控体系
小即是美：事务越小越好，持续时间越短越好
顺序一致：统一资源访问顺序，避免交叉等待
合理索引：合适的索引能减少锁竞争
重试机制：对死锁错误实现自动重试逻辑
架构优化：考虑使用内存表、队列等高级特性

记住，死锁不可能完全避免，但通过合理的预防措施和快速的响应机制，我们可以将其影响降到最低。

最后给个小贴士：定期检查sys.dm_os_performance_counters中的锁相关计数器，可以提前发现潜在的锁争用问题，防患于未然。

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Lock%'
AND cntr_value > 0;

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