SQL触发器锁整表而非单行的原因

许多开发者在遇到触发器锁表时，第一反应往往是“这个触发器写得不好”。实际上，这种看法只对了一半——触发器本身并不会主动锁定整张表，真正导致整张表“冻结”的，是触发器内部执行的SQL语句，特别是那些未使用索引、扫描范围过宽或者在高隔离级别下触发了间隙锁（Gap Lock）的操作。

触发器内UPDATE未使用索引导致全表扫描

这是最常见的情形。例如，触发器中包含如下语句：UPDATE inventory SET stock = stock - 1 WHERE sku = @sku，但问题在于sku字段未创建索引。InnoDB只能进行全表扫描来定位数据。在REPEATABLE READ隔离级别下，系统会对所有扫描到的行及其间隙都加上Next-Key Lock，效果几乎等同于锁住了整张表。

如何排查呢？以下几点值得关注：

• 使用EXPLAIN分析触发器内的SQL语句，检查type字段是否为ALL，若是则表明发生了全表扫描
• 通过SHOW CREATE TABLE inventory确认sku字段是否位于某个索引的最左前缀列
• 函数包裹是常见的“隐形杀手”，例如WHERE UPPER(sku) = UPPER(@sku)会导致索引失效
• 字符集不匹配同样容易忽视：字段使用utf8mb4_0900_as_cs而参数使用utf8mb4_general_ci时，索引无法正常使用

BEFORE与AFTER触发时机对锁持有逻辑的影响

这里有一个容易被忽视的细节：BEFORE触发器执行时，主语句尚未提交，但已经持有目标行的X锁；而AFTER触发器则在变更完成后执行，若此时对其他表执行UPDATE，相当于在同一事务中增加了一把新锁，极易形成锁等待环路。

以下是几个实战要点：

• 在BEFORE触发器中可以安全地修改NEW.column，修改后的值将写入最终结果
• 在AFTER触发器中修改NEW无效，且MySQL 5.7及以上版本明确禁止更新被触发的表，否则会报错Can't update table 't1' in stored function/trigger
• 多个AFTER INSERT触发器同时向同一张统计表写入数据时，可能因主键冲突而引发死锁，高并发场景下尤为常见

触发器调用存储过程或UDF扩大锁范围

有些触发器表面上看似简洁，实则内部隐藏了CALL update_stock_log @sku, @qty这样的调用，而该存储过程又包含INSERT INTO stock_log_history SELECT * FROM inserted——如果这个SELECT没有使用主键限定或未添加WITH (NOLOCK)，就会引发对stock_log_history的全表扫描并加锁，进而阻塞主表的操作。

以下几点值得留意：

• 所有嵌套调用共享同一事务上下文，锁不会自动释放
• 在SQL Server中，如果UDF包含SELECT语句，每次调用都会申请共享锁
• MySQL触发器内禁止使用START TRANSACTION、COMMIT或ROLLBACK，否则会直接报错或引发隐式锁的异常

锁的持续时间并非等于触发器执行时间

这一点许多人都存在误解。触发器导致的锁表时间实际上等于主事务的生命周期，而非触发器执行完毕即释放。即使触发器仅耗时200毫秒，只要事务后续还有SLEEP(30)或者长时间未执行COMMIT，锁就会持续30秒之久。

实际排查时需关注以下方面：

• 如果SHOW PROCESSLIST显示状态为Updating或Waiting for table metadata lock，大概率是事务尚未结束
• ORM框架有时会在无意识中包裹一层事务，例如Django的@transaction.atomic装饰器，即使未显式编写BEGIN，锁仍然会被持有
• 连接池复用时需特别留意：上一个请求忘记执行COMMIT，下一个请求复用同一连接时，锁就会“继承”下来

归根结底，要准确排查问题，需要查询performance_schema.data_locks（MySQL 8.0及以上）或INFORMATION_SCHEMA.INNODB_TRX结合INNODB_LOCK_WAITS——而不是仅关注触发器的代码逻辑。触发器只是事务链路中的一环，真正的锁行为隐藏在其执行的每一条SQL语句之中。