如何解决SQL触发器引发的锁等待-SQL-PHP中文网

sql触发器本身不产生锁等待，但会延长主事务锁持有时间、扩大锁范围、隐藏锁路径，导致update卡在lock wait状态；定位需查innodb_trx与innodb_lock_waits关联分析trx_query，重点排查触发器内非业务表dml、全表扫描、for update及跨表操作。

如何解决sql触发器引发的锁等待

SQL触发器本身不产生锁等待，但它会让主事务的锁持有时间变长、范围变大、路径不可见——这才是锁等待的真正源头。

你看到INNODB_TRX里某条UPDATE状态是LOCK WAIT，背后往往不是那条语句本身慢，而是它触发的触发器正在等另一把锁。比如：

主事务已对orders表中id = 1001加了X锁，AFTER INSERT触发器紧接着执行UPDATE log_table SET status = 'done' WHERE order_id = 1001，但该语句走的是idx_order_id索引，而另一个事务正拿着log_table上同一间隙的gap lock
触发器里调用了一个存储过程，里面又SELECT ... FOR UPDATE锁了inventory表，而另一条业务路径先锁inventory再锁orders，形成ABBA循环
触发器内用了WHERE module = 'payment'更新配置表，但module字段没索引 → 全表扫描 → 每行都加记录锁 + 大量间隙锁 → 后续所有INSERT/UPDATE都被堵住

别只盯着报错SQL，要进数据库看实时锁关系：

查SELECT * FROM information_schema.INNODB_TRX ORDER BY TRX_STARTED DESC LIMIT 5，找TRX_STATE = 'LOCK WAIT'的事务，记下它的TRX_WAITING_TRX_ID
用这个ID关联INNODB_LOCK_WAITS，拿到BLOCKING_TRX_ID，再查对应事务的TRX_QUERY——如果出现UPDATE t_log或INSERT INTO audit_这类非业务主表操作，基本就是触发器干的
重点看TRX_ROWS_LOCKED和TRX_ROWS_MODIFIED：如果后者很小（比如1），前者却上千，说明触发器在扫范围、加间隙锁
执行SHOW ENGINE INNODB STATUS\G，在LATEST DETECTED DEADLOCK段里搜t_log、audit_history、summary_buffer等典型日志表名，有就坐实了

这些写法在并发稍高时，几乎等于主动申请锁等待：

UPDATE config SET value = ? WHERE module = ? —— module无索引 → 全表X锁；改成PRIMARY KEY或加唯一索引
SELECT balance FROM accounts WHERE user_id = NEW.user_id FOR UPDATE —— 触发器里不该出现FOR UPDATE；改用普通SELECT，或把锁提前到主事务开头
INSERT INTO summary SELECT COUNT(*) FROM detail WHERE order_id = NEW.id —— 范围扫描+间隙锁；拆成单行查，或移出触发器
CALL sp_update_stock(NEW.sku) —— 存储过程若含事务或跨表DML，锁范围不可控；检查该过程是否真需要START TRANSACTION，否则删掉

保留触发器逻辑，但让它只做“确定性、窄范围、可预测”的事：

所有DML必须走PRIMARY KEY或唯一索引：比如UPDATE t_log SET status = 'done' WHERE id = ?，而不是WHERE order_id = ?（除非order_id是唯一键）
把同步写日志改成异步缓冲：INSERT IGNORE INTO log_buffer (order_id, event) VALUES (?, 'created')，再由定时任务批量刷入正式表
如果必须同步更新主表字段（如订单状态），用BEFORE INSERT里的SET NEW.status = 'pending'，不发额外SQL
对触发器内每条UPDATE/INSERT，用真实参数跑一遍EXPLAIN FORMAT=TRADITIONAL，确认type是const或ref，rows_examined ≤ 1