WR王锐.dev
管理
← 博客
技术2026-09-0953 次阅读

SQL Server 死锁排查实录：MES 高并发采集的锁优化实战

# SQL Server# 死锁# MES# EF Core# 性能优化# 并发

背景：MES 高并发采集下的死锁噩梦

上周产线反馈：采集服务每隔几小时就报一次 SqlException，错误信息是 "事务(进程 ID xx)在资源上被另一个进程死锁"。产线看板数据卡住不刷新，班组长大发雷霆。

这套 MES 采集服务部署在 Windows Server 上，后端是 .NET 8 + EF Core，数据库是 SQL Server 2019。产线 20 台设备每 5 秒上报一次生产数据，采集服务并发写入 UPH 表和报警表。单看 QPS 不高（约 400/s），但死锁偏偏在高产期扎堆出现。

这篇文章记录我从定位死锁 → 分析根因 → 优化锁粒度 → 彻底消除的完整排查路径，给遇到同样问题的同行参考。

第一步：抓取死锁日志

SQL Server 默认不保留死锁详情，得先开 Trace Flag 1222 或用 Extended Events 抓取。我选了后者，对性能影响更小：

-- 创建 Extended Events 会话抓死锁
CREATE EVENT SESSION [deadlock_capture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.sql_text, sqlserver.client_app_name))
ADD TARGET package0.event_file(
    SET filename = 'C:\Logs\deadlock.xel',
    max_file_size_mb = 10, max_rollover_files = 5);
ALTER EVENT SESSION [deadlock_capture] ON SERVER STATE = START;

等了半天，果然抓到了。用以下查询读取 XEL 文件内容：

-- 读取死锁 XML 报告
SELECT
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS [time],
    event_data.value('(event/data[@name="deadlock_xml"]/value)[1]', 'nvarchar(max)') AS [deadlock_xml]
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Logs\deadlock*.xel', NULL, NULL, NULL)
) t
WHERE event_data.exist('(event[@name="xml_deadlock_report"])') = 1;

XML 里清清楚楚写明了两个进程各自持有什么锁、等什么锁。关键信息：两个 UPDATE 语句在同一行上互相等对方释放锁。

第二步：定位死锁根因

看了 XML 报告后，问题出在采集服务的这段 EF Core 代码：

// 问题代码：先查再改，中间有间隙
public async Task UpdateDeviceStatus(string deviceCode, int status)
{
    var device = await _db.Devices
        .Where(d => d.Code == deviceCode)
        .FirstOrDefaultAsync();

    device.Status = status;
    device.LastReportTime = DateTime.Now;

    await _db.SaveChangesAsync(); // 死锁高发点
}

根因有三个：

  • 先查后改：SELECT 拿到 S 锁，UPDATE 升级为 X 锁，两个请求同时执行时互相卡死
  • 事务范围太大：EF Core 默认在 SaveChanges 时才提交，但查询在事务内已持有锁
  • 缺少合适的索引：Code 字段没有唯一索引，SQL Server 用了行级锁 + 键查找，锁粒度变大

第三步：优化锁粒度与事务结构

改造分三刀：

刀 1：用 UPDATE 直接改，不先 SELECT

// 直接 UPDATE，避免先查后改的锁升级窗口
public async Task UpdateDeviceStatus(string deviceCode, int status)
{
    await _db.Devices
        .Where(d => d.Code == deviceCode)
        .ExecuteUpdateAsync(s => s
            .SetProperty(d => d.Status, status)
            .SetProperty(d => d.LastReportTime, DateTime.Now));
}

ExecuteUpdateAsync 直接翻译成一条 UPDATE 语句，不经过 EntityTracker，不持有中间 S 锁。

刀 2：加唯一索引，收窄锁范围

-- 给 Code 字段加唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX IX_Devices_Code
ON dbo.Devices(Code)
INCLUDE (Status, LastReportTime);

加了覆盖索引后，UPDATE 只需锁定索引行，不再通过 RID/键查找回表，锁粒度从页级缩到行级。

刀 3：批量写入合并，减少事务次数

// 把 20 台设备的上报合并成一次写入
public async Task BatchUpdateStatus(List reports)
{
    // 用 DataTable 做参数化批量更新
    var dt = new DataTable();
    dt.Columns.Add("Code", typeof(string));
    dt.Columns.Add("Status", typeof(int));
    dt.Columns.Add("Time", typeof(DateTime));
    foreach (var r in reports)
        dt.Rows.Add(r.Code, r.Status, r.Time);

    var param = new SqlParameter("@devices", SqlDbType.Structured)
    { TypeName = "dbo.DeviceReportTable", Value = dt };

    await _db.Database.ExecuteSqlRawAsync(
        "EXEC sp_UpdateDeviceBatch @devices", param);
}

用 Table-Valued Parameter 把 20 次 UPDATE 合并成 1 次存储过程调用，事务持有时间从 ~200ms 降到 ~15ms。

第四步：验证与监控

改完部署后，用以下方式验证效果：

-- 检查死锁是否消失（XE 会话还在运行）
SELECT COUNT(*) AS deadlock_count
FROM sys.fn_xe_file_target_read_file('C:\Logs\deadlock*.xel', NULL, NULL, NULL)
CROSS APPLY event_data.nodes('//event[@name="xml_deadlock_report"]') AS t(event)
WHERE event.exist('.') = 1
  AND event.value('(@timestamp)[1]', 'datetime2') > DATEADD(hour, -24, GETDATE());

改造前 24 小时死锁 37 次，改造后 连续 72 小时零死锁。采集服务 CPU 从平均 45% 降到 12%。

踩坑记录

  • 坑 1：ExecuteUpdateAsync 绕过了 SaveChanges——它不会触发 EF Core 的拦截器和审计日志。如果系统依赖 ChangeTracker 做审计，改完发现审计记录没了。解决：手动补一条日志记录。
  • 坑 2：TVP 参数的列名大小写敏感——SQL Server 的 TVP 类型列名必须和 DataTable 列名完全匹配，少一个字母或大小写不对直接报 "Column 'xxx' not found"。排查了半小时才发现列名拼错。
  • 坑 3：加唯一索引时数据有重复——建索引直接报错，因为历史数据里 Code 有重复（设备更换后旧记录没清）。解决：先清理脏数据再建索引。
  • 坑 4：sp_UpdateDeviceBatch 存储过程的死锁监控——存储过程内部的死锁 XE 抓到的 sql_text 是存储过程名，不是具体语句。要在存储过程里加 SET DEADLOCK_PRIORITY LOW 让批量更新优先被牺牲，保护单条采集请求。

总结

SQL Server 死锁排查的核心思路：先抓证据（XE/Trace Flag），再分析锁链（XML 报告），最后从"减少持锁时间"和"收窄锁范围"两个维度优化。

本次实战中三刀组合拳的效果：

  • ExecuteUpdateAsync 消除先查后改的锁升级窗口
  • 覆盖索引把锁从页级收到行级
  • 批量合并把事务持有时间从 200ms 降到 15ms

死锁从 37 次/天降到 0，采集服务稳定性显著提升。做 MES 采集系统，永远要记住：并发越高，事务越要短。

评论 (0)