跨服务器三级触发器引发SQL Server死锁问题求解决方案

我在Server-A的Database-A的Table-A上创建了Insert触发器，用于将插入的数据同步到Server-B的Database-B的Table-B；接着在Server-B的Database-B的Table-B上创建Insert触发器，利用插入数据计算后将结果插入同库的Table-C；最后在Server-B的Database-B的Table-C上创建Update触发器，根据计算结果自动发送邮件。现在SQL Server频繁出现死锁现象，希望得到可行的解决方案。

说明：第一个触发器负责数据同步，第二个触发器实现核心业务逻辑，第三个触发器根据业务结果发送邮件。

现有触发器代码 #

Server-A Database-A Table-A 插入触发器 #

USE [Database-A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRIGGER_INSERT_A] ON [dbo].[Table-A]
FOR INSERT
AS
BEGIN
INSERT INTO [Server-B].[Database-B].[dbo].[Table-B]
(
[Server-B].[dbo].[Table-B].[field1],
[Server-B].[dbo].[Table-B].[field2],
[Server-B].[dbo].[Table-B].[field3],
[Server-B].[dbo].[Table-B].[field4],
[Server-B].[dbo].[Table-B].[field5],
[Server-B].[dbo].[Table-B].[field6],
[Server-B].[dbo].[Table-B].[field7]
)
SELECT [field1], [field2], [field3], [field4], [field5], [field6], [field7]
FROM inserted
END
GO

Server-B Database-B Table-B 插入触发器 #

USE [Database-B]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRIGGER_INSERT_B] ON [dbo].[Table-B]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- 逻辑部分已省略
-- UPDATE [dbo].[Table-B] OR INSERT [dbo].[Table-B]
END
GO

Server-B Database-B Table-C 更新触发器 #

USE [Database-B]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIGGER_UPDATE_C] ON [dbo].[Table-C]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @recipients VARCHAR(MAX)
DECLARE @subject NVARCHAR(255)
DECLARE @body NVARCHAR(MAX)
DECLARE @body_format VARCHAR(20)
DECLARE @importance VARCHAR(6)
DECLARE @sensitivity VARCHAR(12)
DECLARE @query NVARCHAR(MAX)
DECLARE @execute_query_database NVARCHAR(128)
DECLARE @attach_query_result_as_file BIT
DECLARE @query_attachment_filename NVARCHAR(260)
DECLARE @query_result_header BIT
DECLARE @query_result_width INT
DECLARE @query_result_separator CHAR(1)
DECLARE @exclude_query_output BIT
DECLARE @append_query_error BIT
DECLARE @query_no_truncate BIT

SELECT @recipients = [recipients], @subject = [subject], @body = [body], @body_format = [body_format], @importance = [importance], @sensitivity = [sensitivity]
FROM [dbo].[Table-D]

SET @query = '查询部分已省略'
SET @execute_query_database = 'Database-B'
SET @attach_query_result_as_file = 1
SET @query_attachment_filename = 'Automatic alarm.txt'
SET @query_result_header = 1
SET @query_result_width = 256
SET @query_result_separator = ' '
SET @exclude_query_output = 0
SET @append_query_error = 0
SET @query_no_truncate = 0

-- 此处更新[dbo].[Table-C]
EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'profile_name', @recipients = @recipients, @subject = @subject, @body = @body, @body_format = @body_format, @importance = @importance, @sensitivity = @sensitivity, @query = @query, @execute_query_database = @execute_query_database, @attach_query_result_as_file = @attach_query_result_as_file, @query_attachment_filename = @query_attachment_filename, @query_result_header = @query_result_header, @query_result_width = @query_result_width, @query_result_separator = @query_result_separator, @exclude_query_output = @exclude_query_output, @append_query_error = @append_query_error, @query_no_truncate = @query_no_truncate
END

可行的死锁解决方案 #

1. 把跨服务器同步逻辑改为异步，移除触发器内的分布式操作 #

你现在的第一个触发器是在Table-A的插入事务中直接跨服务器写入Table-B，这会触发分布式事务，锁持有时间被拉长，而且跨服务器的网络延迟也会让事务持续更久，大大增加死锁概率。

替代方案：

• 使用SQL Server事务复制：配置从Server-A的Database-A.Table-A到Server-B的Database-B.Table-B的事务复制，由SQL Server后台异步同步数据，不需要自己写触发器，原插入事务只需要处理本地表，锁持有时间极短。
• 改用本地队列+异步同步：在Server-A创建一个同步队列表（比如Table_A_Sync_Queue），触发器里只把要同步的数据插入这个队列表，然后用SQL Agent作业定时读取队列，批量同步到Server-B的Table-B，或者用Service Broker实现实时异步同步。

2. 移除触发器内的耗时操作（比如发送邮件） #

第三个触发器里直接调用sp_send_dbmail是非常危险的，发邮件涉及外部系统的IO操作，耗时不确定，会让Table-C的更新事务一直处于打开状态，持有锁的时间大幅增加，很容易引发死锁。

优化方案：

• 创建一个邮件队列表（比如Email_Queue），触发器里只把邮件所需的参数（收件人、主题、查询语句等）插入这个队列表，然后用SQL Agent作业定时扫描队列，调用sp_send_dbmail发送邮件；
• 用Service Broker实现异步发邮件：当Table-C更新时，触发器向Service Broker队列发送消息，由激活的存储过程异步处理邮件发送。

3. 优化触发器的事务范围与锁粒度 #

当前的触发器链是在同一个事务中完成“插入Table-A → 插入Table-B → 更新/插入Table-B → 插入Table-C → 更新Table-C → 发邮件”，整个事务链路太长，锁持有时间太久。

优化建议：

• 拆分事务：把Table-B的计算逻辑从触发器中剥离，改用异步处理（比如用Service Broker队列），让Table-B的插入事务尽快完成，释放锁；
• 开启快照隔离级别：在Database-B中开启READ_COMMITTED_SNAPSHOT ON，这样读操作不会加共享锁，减少锁冲突的概率；
• 检查Table-B和Table-C的索引：确保触发器内的查询和更新操作使用合适的索引，避免全表扫描导致的表级锁，尽量使用行级锁。

4. 捕获死锁图精准定位冲突点 #

死锁的原因可能有很多，建议先通过工具捕获死锁图，明确是哪些资源在竞争：

• 使用Extended Events创建死锁捕获会话，或者用SQL Server Profiler（虽然Profiler已被弃用，但简单场景下仍可用）；
• 分析死锁图中的等待资源和持有资源，确定是Table-B的插入与更新冲突，还是跨服务器同步的锁冲突，或者是Table-C的更新与其他操作冲突，针对性优化。

5. 避免在触发器中修改触发表本身 #

从Table-B的触发器代码注释看，里面可能有UPDATE [dbo].[Table-B]的操作，在Insert触发器中修改触发表本身，会导致额外的锁竞争，而且容易引发递归触发器或者死锁。

优化建议：

• 如果是要插入时同步更新Table-B的某些字段，考虑把逻辑放到插入语句中（比如INSERT ... OUTPUT或者直接计算后插入），避免在触发器中更新触发表；
• 必须在触发器中修改的话，确保操作的锁粒度最小，比如使用主键或唯一键定位行，避免更新大范围数据。

内容的提问来源于stack exchange，提问作者王肖毅