SQL触发器在高并发下的可靠性设计_优化触发锁竞争范围-SQL-PHP中文网

MySQL/PG触发器中应避免全表操作、非确定性函数及跨表更新，优先用NEW字段赋值、应用层传参、异步消息；须严格控制锁粒度并压测验证。

sql触发器在高并发下的可靠性设计_优化触发锁竞争范围

触发器里别写 UPDATE 或 INSERT 全表操作

高并发下最常见崩点：触发器里执行 UPDATE orders SET status = 'processed' WHERE user_id = NEW.user_id 这类语句，表面看逻辑合理，实际会锁住整个 user_id 索引范围，甚至升级成表锁。InnoDB 的 next-key lock 在范围条件上非常激进，尤其当 user_id 索引不是唯一时。

实操建议：

把「查+改」拆开：先用 SELECT ... FOR UPDATE 显式加锁，但只锁住真正要改的那几行（例如通过主键 id）
避免在触发器中调用存储过程，除非该过程内部已做行级锁粒度控制
如果只是更新当前行，直接用 SET NEW.status = 'processed'，不发额外 SQL
对 MySQL 8.0+，可考虑用 INSERT ... ON DUPLICATE KEY UPDATE 替代先查后更，减少锁持有时间

MySQL 触发器中慎用 NOW()、UUID() 等非确定性函数

这类函数在复制环境或并行 DML 下可能引发主从不一致，更隐蔽的问题是：它们会阻止触发器被标记为「safe」，导致 binlog 格式被迫降级为 MIXED 或 STATEMENT，进一步放大锁竞争和回放延迟。

实操建议：

时间戳统一由应用层传入，作为字段值写入，触发器只做校验或衍生计算
需要唯一 ID 时，优先用 NEW.id（自增主键）或 UUID_TO_BIN(UUID(), TRUE)（MySQL 8.0+），避免在触发器内生成
若必须用 NOW()，确认 binlog_format = ROW 且已开启 binlog_row_image = FULL

触发器嵌套与递归更新引发的死锁链

比如 AFTER INSERT ON orders 触发器去更新 users 表，而 users 上又有触发器反向更新 orders，高并发下极易形成循环等待。错误现象通常是 Deadlock found when trying to get lock，但堆栈里看不到业务代码，只看到 TRIGGER 关键字。

实操建议：

用 SHOW ENGINE INNODB STATUS\G 查看最近死锁，重点关注 TRANSACTION 块中的 trigger 和 table 名称
禁用递归触发：SET GLOBAL log_bin_trust_function_creators = 1 不解决根本问题，应设 max_sp_recursion_depth = 0 并在触发器开头加 IF @in_trigger THEN LEAVE; END IF;
跨表更新尽量走异步消息（如写入 trigger_queue 表，另起定时任务处理），把强一致性降级为最终一致

PostgreSQL 中触发器比 MySQL 更易踩的锁坑

PG 的 BEFORE ROW 触发器默认不加锁，但一旦你在里面执行 SELECT ... FOR UPDATE，就会立刻申请行锁——而此时事务还没真正开始修改目标表，锁却已经持有了。更麻烦的是，PG 不支持 SELECT ... FOR UPDATE SKIP LOCKED 在触发器中使用（会报错 ERROR: cursor "SPI_cursor" does not exist）。

实操建议：

PG 触发器中所有 FOR UPDATE 必须搭配明确的 WHERE 条件，且该条件必须命中索引，否则退化为全表扫描+锁表
避免在 BEFORE 触发器中做任何 DML；改用 AFTER + DEFERRABLE 约束或 pg_notify 推送事件
测试时用 pg_locks 实时查锁：SELECT * FROM pg_locks WHERE pid IN (SELECT pid FROM pg_stat_activity WHERE application_name = 'your_app');