SQL存储过程死锁是指两个或多个事务在执行过程中，互相持有对方需要的锁资源且不释放，导致所有事务都无法继续推进的阻塞状态，会直接造成事务回滚、业务请求失败。要解决这个问题，核心是通过运行日志找到冲突的语句，再针对性调整逻辑。
SQL存储过程死锁的常见原因
存储过程死锁通常由以下几类场景触发：
- 多个存储过程以不同顺序访问相同的表资源，比如过程A先锁表1再锁表2，过程B先锁表2再锁表1，并发执行时就容易形成死锁
- 存储过程内部事务持有锁的时间过长，比如长时间执行非必要的查询、计算逻辑，增加锁冲突的概率
- 索引缺失导致查询时触发全表扫描，占用范围锁，提升和其他事务的锁冲突可能性
开启SQL Server运行日志捕获死锁信息
以SQL Server为例，需要先开启死锁跟踪功能，才能捕获到对应的运行日志。可以通过以下语句开启标准死锁跟踪：
-- 开启死锁跟踪，跟踪编号为0的是默认系统跟踪 EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'blocked process threshold (s)', 5; -- 事务阻塞超过5秒时记录日志 RECONFIGURE;
开启后，当发生死锁时，系统会自动将死锁相关的信息写入默认跟踪文件，也可以通过扩展事件捕获更详细的死锁日志：
-- 创建扩展事件会话捕获死锁 CREATE EVENT SESSION [Deadlock_Capture] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename = N'C:DeadlockLogsdeadlock.xel', max_file_size = 50) WITH (STARTUP_STATE = ON); GO -- 启动扩展事件会话 ALTER EVENT SESSION [Deadlock_Capture] ON SERVER STATE = START;
分析运行日志定位冲突语句
获取到死锁日志后，可以按照以下步骤定位冲突的具体语句：
步骤1：读取死锁日志内容
如果是扩展事件生成的xel文件，可以通过以下语句读取内容：
-- 读取死锁扩展事件日志
SELECT
n.value('(./@timestamp)', 'datetime2') AS DeadlockTime,
n.value('(./data[@name="xml_report"]/value)[1]', 'xml') AS DeadlockXml
FROM
sys.fn_xe_file_target_read_file('C:DeadlockLogsdeadlock*.xel', NULL, NULL, NULL) AS x
CROSS APPLY x.event_data.nodes('/event') AS q(n);
步骤2：解析死锁XML内容
死锁日志以XML格式存储，核心的冲突信息在<deadlock>节点下，重点关注两个<process>节点，每个节点代表一个参与死锁的事务：
- <inputbuf>标签内就是该事务正在执行的语句，也就是冲突的存储过程或者SQL语句
- <resource-list>节点下会显示两个事务分别持有和等待的锁资源，对应到具体的表、索引
示例解析逻辑如下：
-- 解析死锁XML中的冲突语句
DECLARE @DeadlockXml XML = N'
<deadlock>
<process>
<inputbuf>
EXEC Update_Order_Status 1001, 2
</inputbuf>
</process>
<process>
<inputbuf>
EXEC Update_User_Balance 1001, 50
</inputbuf>
</process>
<resource-list>
<objectlock objectname="Orders" />
<objectlock objectname="Users" />
</resource-list>
</deadlock>';
SELECT
c.value('(./inputbuf)[1]', 'nvarchar(max)') AS ConflictStatement
FROM
@DeadlockXml.nodes('/deadlock/process') AS t(c);
步骤3：确认冲突语句所属存储过程
如果<inputbuf>内容直接是存储过程调用语句，就可以通过系统存储过程查看存储过程的定义，确认具体的冲突逻辑：
-- 查看存储过程定义 EXEC sp_helptext 'Update_Order_Status'; EXEC sp_helptext 'Update_User_Balance';
解决存储过程死锁的优化方案
定位到冲突语句后，可以通过以下方式优化避免死锁：
- 统一多个存储过程访问表的顺序，比如所有涉及Orders和Users表的过程，都先访问Orders再访问Users
- 缩短事务执行时间，把存储过程中非必要的查询、计算逻辑移到事务外部，减少锁持有时间
- 为存储过程中频繁查询的字段添加合适的索引，避免全表扫描触发范围锁
- 在存储过程内部添加重试逻辑，当捕获到死锁错误号1205时，自动重试事务执行
以下是添加死锁重试逻辑的存储过程示例：
CREATE PROCEDURE Update_Order_Status
@OrderId INT,
@Status INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RetryCount INT = 0;
DECLARE @MaxRetry INT = 3;
DECLARE @ErrorNumber INT;
WHILE @RetryCount <= @MaxRetry
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- 更新订单状态的逻辑
UPDATE Orders SET Status = @Status WHERE OrderId = @OrderId;
COMMIT TRANSACTION;
BREAK; -- 执行成功则跳出循环
END TRY
BEGIN CATCH
SET @ErrorNumber = ERROR_NUMBER();
IF @ErrorNumber = 1205 -- 死锁错误号
BEGIN
SET @RetryCount = @RetryCount + 1;
IF @RetryCount > @MaxRetry
BEGIN
THROW; -- 超过重试次数则抛出异常
END
-- 等待随机时间后重试，避免同时重试再次冲突
WAITFOR DELAY '00:00:00.0' + CAST(ABS(CHECKSUM(NEWID())) % 100 AS VARCHAR);
END
ELSE
BEGIN
THROW; -- 其他错误直接抛出
END
END CATCH
END
END
注意事项
分析日志时需要注意，部分死锁可能是临时并发峰值导致，单次出现的死锁可以先观察，频繁出现的死锁才需要做逻辑优化。另外不同数据库的运行日志格式有差异，MySQL可以通过开启innodb_print_all_deadlocks参数将死锁信息写入错误日志，PostgreSQL可以查询pg_stat_activity视图结合日志定位冲突语句，核心思路都是先获取死锁相关的日志，再解析出冲突的事务和语句。