SQL Server触发器删除表旧数据引发死锁问题求助

死锁产生的核心原因 #

• 触发器与触发语句同事务：SQL Server中触发器和触发它的INSERT语句属于同一个原子事务，当前触发器内叠加了两个表的循环删除逻辑，尤其是Table2要删除近1个月的过期数据，循环执行时间长，事务会长期持有操作过程中产生的所有锁，大幅提升了并发场景下的锁冲突概率。
• 资源访问顺序冲突：当前触发器执行顺序为「插入Table2 → 删除Table1过期数据 → 删除Table2过期数据」，并发执行时会出现循环等待：会话A持有Table2的插入锁，等待Table1的删除锁；会话B持有Table1的删除锁，等待Table2的删除锁，双方互相阻塞就会触发死锁。
• 缺失EVENT_TIME索引：如果Table2的EVENT_TIME字段没有建立非聚集索引，执行DELETE时SQL Server需要全表扫描匹配过期行，会产生大量不必要的行锁甚至表锁，同时扫描过程拉长了锁持有时间，进一步放大死锁概率。
• 批量删除触发锁升级：虽然采用了TOP(1000)分批删除的逻辑，但如果单次触发触发器时匹配的过期行数量过多，SQL Server会自动将行锁升级为表锁，一旦有会话持有Table2的表锁，所有后续操作Table2的请求都会被阻塞，极易触发死锁。

可行修复方案 #

最优方案（推荐） #

将过期数据清理逻辑从触发器中完全剥离，改用SQL Server代理定时作业执行：

• 每小时执行一次Table1的过期数据清理
• 每天执行一次Table2的过期数据清理
  触发器仅保留归档插入逻辑，代码简化为：

CREATE TRIGGER trigger_events ON dbo.Table1 
FOR INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.Table2 (id, event_time, resource_type)
SELECT id, event_time, resource_type FROM inserted;
GO

该方案从根源上缩短了INSERT事务的执行时长，基本可以完全避免触发器相关的死锁问题。

次优方案（需保留删除逻辑在触发器中时使用） #

• 调整操作顺序：所有对Table1的操作放在最前，所有对Table2的操作放在最后，保证所有会话的资源访问顺序一致，消除循环等待条件：先删Table1过期数据 → 再插入Table2 → 最后删Table2过期数据
• 给两个表的EVENT_TIME字段分别创建非聚集索引，加速DELETE的行定位，减少锁数量和持有时间
• 给DELETE语句添加行锁提示，避免锁升级：

DELETE TOP(1000) FROM dbo.Table1 WITH (ROWLOCK) WHERE EVENT_TIME < (SELECT cast(DATEDIFF(second,'1970-01-01 00:00:00',(DATEADD(HOUR,-1,GETDATE())))AS bigint)* 1000 );
DELETE TOP(1000) FROM dbo.Table2 WITH (ROWLOCK) WHERE EVENT_TIME < (SELECT cast(DATEDIFF(second,'1970-01-01 00:00:00',(DATEADD(MONTH,-1,GETDATE())))AS bigint)* 1000 );

• 移除inserted表不必要的WITH (NOLOCK)提示，避免引入数据一致性风险。

内容的提问来源于stack exchange，提问作者guyfromnowhere