如何缩短SQL触发器引起的表锁定时间？

本文共计1029个文字，预计阅读时间需要5分钟。

触发展器本身不加锁，但其执行被绑定在主事务中——即锁时间过长，取决于整个事务何时结束，而不是触发展器跑完就释放。

触发器锁表时间 = 主事务生命周期

你执行一条 UPDATE t1 SET x=1 WHERE id=100，哪怕只改一行，只要它后面跟着一个 AFTER UPDATE 触发器，且该触发器里又执行了 INSERT INTO log_table 或 UPDATE t2，那这一整条链路的所有锁（包括 t1 的行锁、t2 的行锁、log_table 的插入意向锁）都会一直挂着，直到你显式 COMMIT 或连接断开回滚。

常见错误现象：SHOW PROCESSLIST 显示状态是 Updating 或 Waiting for table metadata lock，同时 SELECT ... FOR UPDATE、ALTER TABLE 全部卡住。

• 别以为“触发器 200ms 跑完了就没事了”——只要事务没提交，锁就在
• 应用层 ORM（如 Django 的 @transaction.atomic）可能自动包了一层事务，你根本没写 BEGIN，锁也早挂上了
• 连接池复用时，上个请求忘了 COMMIT，下个请求接着用同一个连接，锁直接继承

BEFORE vs AFTER：锁行为和修改能力完全不同

BEFORE 和 AFTER 触发器不仅执行时机不同，对锁的持有方式和数据可操作性也有硬性差异：

• BEFORE INSERT/UPDATE 中可以安全赋值 NEW.created_at = NOW()，这个值会真正写入目标表；而 AFTER 里改 NEW 完全无效
• BEFORE 执行时，原语句还没落盘，但已持有了目标行的 X 锁；AFTER 则一定发生在变更之后，若它再去 UPDATE users SET balance = balance - 100，等于在同一事务里多加一次锁，极易触发死锁
• MySQL 5.7+ 明确禁止 AFTER 触发器修改触发它的表，否则报错：Can't update table 't1' in stored function/trigger

哪些操作会让触发器拖慢整个事务

触发器内任何“看起来无害”的操作，一旦放在事务上下文中，都可能把毫秒级锁拉长成秒级甚至分钟级：

• 调用外部 HTTP 接口（超时设置缺失或网络抖动 → 事务卡住）
• 写本地日志文件（磁盘 I/O 阻塞、权限问题、满盘）
• INSERT INTO audit_log SELECT * FROM big_table WHERE ...（全表扫描 + 大量插入 → 行锁升级为间隙锁甚至表锁）
• 嵌套子查询未走索引，比如 WHERE user_id IN (SELECT id FROM users WHERE status = 'active') 没给 status 加索引 → 全表扫描锁定所有匹配行

怎么快速定位是哪个触发器在拖后腿

别靠猜。直接查元数据 + 性能视图组合定位：

• 先看这张表挂了哪些触发器：SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE = 'your_table'
• 确认 performance_schema 是否开启：SELECT VARIABLE_VALUE FROM performance_schema.setup_consumers WHERE NAME = 'events_statements_history_long'，若为 NO，需提前设为 YES
• 查最近慢事务链路：SELECT SQL_TEXT, TIMER_WAIT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%your_table%' ORDER BY TIMER_WAIT DESC LIMIT 5
• 更直接的办法：临时打开通用日志：SET GLOBAL general_log = ON，复现问题后搜日志中耗时最长的触发器调用段

真正容易被忽略的是：锁持续时间跟触发器逻辑快慢无关，只跟它所处的事务生命周期有关。哪怕触发器空跑 1ms，只要主事务后面还跟着一个 SLEEP(30) 或未关闭的 HTTP 连接，锁就真会卡满 30 秒。