这篇文章发布于 432 天前,部分信息可能已经发生变化。
MSSQL 2016数据库基本每天死锁,用户反馈U9生产订单操作卡死。
SELECT sqltext.text,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.blocking_session_id
FROM sys.dm_exec_requests req (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
果然200多条SQL阻塞
SELECT blocking_session_id,COUNT(*) AS 阻塞数量 FROM
(SELECT sqltext.text,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.blocking_session_id
FROM sys.dm_exec_requests req (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext)
T1
GROUP BY blocking_session_id
ORDER BY COUNT(*) DESC
484 -> 121 -> 398 -> 267 ->134
388 ->267 -> 134
431 -> 400 ->388 -> 267 -> 134
272 -> 406 -> 388 ->267 ->134
400 -> 388 -> 267 -> 134
果然session134对应SQL是BE插件对表MO_MO高消耗UPDATE语句导致生产订单表死锁。
优化BE插件SQL,经过观察20多天,数据库没有再次出现死锁。