PostgreSQL与MariaDB隔离级别差异：事务锁问题求助

场景复现 #

以下是同一数据库两个不同连接上的SQL执行序列：

连接1操作 #

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE Person SET LastLogin=NOW() WHERE IDPerson=1;
-- 事务暂停，未提交
COMMIT; -- 后续恢复并提交

连接2操作 #

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO Log (FKIDPerson, Hash) VALUES (1, '95baa69a26a95db2');
COMMIT;

Log表的FKIDPerson字段为外键，引用Person表主键IDPerson，约束规则为ON UPDATE NO ACTION ON DELETE CASCADE。该序列在PostgreSQL中可正常执行，但迁移至MariaDB后，连接2的INSERT语句会触发锁等待。

问题需求 #

需保留原语句序列及嵌套逻辑，即使将隔离级别降至READ UNCOMMITTED也无法解决锁等待，如何让MariaDB允许该操作？

更新补充 #

将LastLogin移至单独表可解决当前问题，但属于治标不治本；Log表必须支持独立事务写入，未来可能需拆分Person表的多个甚至全部属性。

考虑仅在MariaDB版本中移除该外键约束，改用Person表的触发器删除关联Log记录？此方案虽感觉不妥，但唯一风险是产生少量可轻松清理的过期记录，请问该方案是否可行？

解决方案分析 #

1. 锁等待根源：PostgreSQL与MariaDB的外键锁差异 #

Serializable隔离级别下，两款数据库的外键检查逻辑不同：

• PostgreSQL插入外键关联记录时，仅校验关联主键的存在性，不会对主键行加锁；
• MariaDB在此级别下执行INSERT INTO Log时，会尝试获取Person表IDPerson=1行的共享锁，而连接1的UPDATE已持有该行排他锁，因此触发锁等待。

即使降级到READ UNCOMMITTED，MariaDB的外键约束检查仍会触发锁（需确保引用行存在，避免幻读），因此无法解决问题。

2. 无侵入式解决方案：调整隔离级别为REPEATABLE READ #

MariaDB默认隔离级别为REPEATABLE READ，此级别下外键检查会利用MVCC快照：插入Log时仅读取Person表的快照数据，只要快照中存在IDPerson=1的行，即可完成插入，不会被连接1的UPDATE锁阻塞。

该方案无需修改数据模型或业务逻辑，是最安全的优先选择。

3. 打破外键+触发器方案的可行性 #

此方案完全可行，但需注意以下细节：

• 数据一致性风险：若Person行删除时触发器执行失败（如事务回滚、触发器报错），会产生孤立Log记录，需配套定时清理任务（例如每日删除FKIDPerson不在Person表中的Log行）；
• 性能影响：删除Person行时的触发器会增加少量IO操作，若删除频率较低，影响可忽略；
• 业务适配：若业务允许短暂的数据不一致（孤立记录可事后清理），该方案能彻底解决锁等待问题。

结论 #

优先推荐调整事务隔离级别为REPEATABLE READ；若业务强制要求使用Serializable隔离级别，可采用移除外键+触发器+定期清理的方案，风险可控。

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