Long's Blog

分享：MSSQL瞬间死锁分析与解决

2023/12/09

问题

现场反馈U9生产订单同步MES时不时出现异常提示：事务(进程 ID XX)与另一个进程被死锁在 锁|通信冲区 资源上，并且已被选作死锁牺性品。请重新运行该事务

image-20231209140721571

分析问题

  • 2台MSSQL服务器（U9和MES各自一台数据库服务器），哪台服务器发生死锁了？

  • 瞬间死锁如何定位？

  • 定位方法：在2台MSSQL服务器创建一个名为 Deadlock_Detection 的事件会话，以监控和记录数据库中的死锁事件

CREATE EVENT SESSION [Deadlock_Detection] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlock_Detection.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
  • 记得启动会话，开始帮我们监控和记录数据库中的死锁事件

image-20231209141617857

定位问题

  • 经过1天收集到的数据来分析

image-20231209141923069

image-20231209142018032

  • 同时也可以借助ChatGPT 4帮忙分析XML死锁数据：

image-20231209142448768

从XML数据我们可以分析出是MES存储过程“APISync_MO_SaveByJson”发生了更新锁，是乙方顾问写了2条开销过大SQL导致：

UPDATE MOProcessItem SET RepeatSN = '1' WHERE ISNULL(RepeatSN, '') = ''

UPDATE dbo.MOProcessItem SET ... FROM dbo.MOProcessItem AS mpi, dbo.Process AS p WHERE mpi.ProcessId = p.ProcessId

解决问题

优化2条开销过大的SQL后，3个多星期未发生类似死锁。