SQL触发器是数据库中与表事件绑定的特殊程序，当执行插入、更新、删除等操作时自动触发执行。如果触发器内部逻辑复杂、涉及大量数据扫描或者未合理控制事务范围，就可能导致关联的表被长时间锁定，影响其他业务的正常执行。
SQL触发器导致表锁定的常见原因
触发器引发表锁定的核心原因通常和事务生命周期、操作数据量、锁粒度有关，常见场景如下：
- 触发器内包含长事务逻辑：如果触发器内部执行了多步数据操作，或者调用了其他耗时存储过程，且事务没有及时提交，锁会一直持有直到事务结束。
- 操作大批量数据：触发器中对整表进行扫描、更新大量行时，会升级为表级锁，锁定时间随数据量增长而增加。
- 未合理使用索引：触发器内的查询没有命中索引，导致全表扫描，不仅耗时还会扩大锁定范围。
- 触发器递归触发：触发器操作触发了同表的其他触发器，形成递归执行，成倍增加执行时长和锁定时间。
表被锁定的大致时长规律
SQL触发器导致的表锁定时长没有固定值，和具体场景直接相关：
| 场景 | 大致锁定时长 |
|---|---|
| 触发器仅执行简单单行操作，事务及时提交 | 毫秒级，几乎无感知 |
| 触发器处理千级数据量，无复杂逻辑 | 1-3秒 |
| 触发器处理万级数据量，或包含多步关联操作 | 10秒到数分钟 |
| 触发器内事务未提交，或触发死锁等待 | 直到事务超时或手动结束，可能长达数十分钟 |
如何检查长时间运行的业务逻辑
1. 查询数据库系统视图定位活跃事务
以SQL Server为例，可以通过系统视图查询当前正在执行的事务和锁信息：
-- 查询当前所有活跃事务及关联的锁信息
SELECT
t.transaction_id,
t.begin_time,
t.status,
l.resource_type,
l.resource_associated_entity_id,
OBJECT_NAME(p.object_id) AS table_name,
t.transaction_state
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_locks l ON t.transaction_id = l.request_owner_id
LEFT JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE t.begin_time < DATEADD(SECOND, -5, GETDATE()) -- 筛选运行超过5秒的事务
ORDER BY t.begin_time ASC
通过返回的结果可以找到长时间运行的事务关联的表，再进一步排查是否是触发器导致。
2. 查看触发器执行日志与执行计划
可以在触发器内部添加日志记录，记录触发时间、执行时长：
-- 示例：在触发器内添加执行时长日志
CREATE TRIGGER trg_update_user_score
ON user_info
AFTER UPDATE
AS
BEGIN
DECLARE @start_time DATETIME = GETDATE()
-- 触发器原有逻辑
UPDATE user_score SET score = score + 10 WHERE user_id IN (SELECT id FROM inserted)
-- 记录执行时长到日志表
INSERT INTO trigger_log(trigger_name, execute_time, duration_ms)
VALUES('trg_update_user_score', GETDATE(), DATEDIFF(MILLISECOND, @start_time, GETDATE()))
END
同时可以通过SET STATISTICS TIME ON开启执行时间统计，查看触发器执行的具体耗时：
SET STATISTICS TIME ON -- 执行会触发触发器的更新操作 UPDATE user_info SET level = 2 WHERE id = 1001 SET STATISTICS TIME OFF
3. 排查业务逻辑中的慢操作
如果确认是触发器导致的锁定，需要检查触发器内部的逻辑：
- 检查是否有不必要的全表操作，尽量限制操作的数据范围。
- 确认所有查询都命中了合适的索引，避免全表扫描。
- 检查是否存在触发器递归调用的情况，必要时关闭递归触发选项。
- 尽量缩短触发器的事务范围，避免将非必要的操作放到触发器内执行。
优化建议
为了减少SQL触发器导致的表锁定问题，建议遵循以下原则：
- 触发器的逻辑尽量简单，只做必要的校验和轻量操作，复杂业务逻辑放到应用层处理。
- 避免在触发器中执行大批量数据操作，若必须处理可以拆分批次执行。
- 定期审查触发器的执行效率，清理无用的触发器。
- 为触发器内涉及的查询字段建立合适的索引，提升执行速度。
注意：不同数据库（MySQL、Oracle、PostgreSQL）的触发器实现和锁机制存在差异，排查时需要参考对应数据库的官方文档，使用对应的系统视图和工具。