插入users表触发存储函数/触发器更新报错该如何解决？

你遇到的报错是MySQL的表锁占用冲突限制导致的：当执行INSERT INTO users语句时，该操作会暂时持有users表的写锁，低版本MySQL对同表同触发时机的多个独立触发器调度存在bug，会误判后续触发器需要修改users表，因此抛出你看到的报错。单触发器可正常运行是因为只有一个触发器时不会触发该调度bug。

最优解决办法：合并两个触发器为同一个 #

将两个关联表插入逻辑整合到同一个AFTER INSERT触发器中，即可规避多触发器调度冲突：

• 先删除已有的两个触发器，避免冲突：

DROP TRIGGER IF EXISTS `addDefaultRole`;
DROP TRIGGER IF EXISTS `addDefaultIcon`;

• 创建合并后的统一触发器：

CREATE TRIGGER `addUserDefaultAttr` AFTER INSERT ON `users`
FOR EACH ROW BEGIN
    -- 写入默认角色
    INSERT INTO user_role (userID, roleID) VALUES (NEW.id, 1);
    -- 写入默认头像
    INSERT INTO user_icon (userID, iconID) VALUES (NEW.id, 1);
END

可选排查验证项 #

如果合并触发器后仍然报错，可检查以下两点：

• 确认user_role、user_icon表关联users.id的外键没有配置ON UPDATE CASCADE/ON DELETE CASCADE这类会反向修改users表的规则
• 若你使用的MySQL版本低于5.7.2，升级到5.7及以上的稳定版本即可修复该已知多触发器调度bug

内容的提问来源于stack exchange，提问作者Napakalaking Halimaw