MariaDB INNO_DB表执行替换操作时偶发表不存在错误如何解决？

• 核心原因是DDL语句的隐式提交特性：DROP TABLE属于DDL操作，在MariaDB/MySQL中执行任意DDL都会隐式提交当前事务，你把DROP和RENAME放在同一个事务里的逻辑实际是失效的，执行DROP的瞬间事务就自动提交，XY表直接被删除，而RENAME操作还未执行，这个时间间隙里用户访问自然会报表不存在的错误。
• 加普通事务锁不生效的原因：InnoDB的行锁、表级事务锁仅对DML操作生效，无法限制DDL操作，且DDL的隐式提交会直接释放你之前加的所有锁，自然无法解决问题。

最优方案：使用原子RENAME TABLE替换两步操作 #

不要先删除原表，直接在单条RENAME语句中完成新旧表的替换，该操作是完全原子的，要么全部成功要么全部失败，不存在表缺失的中间状态：

-- 同时完成旧表归档、新表上线，全程原子无间隙
RENAME TABLE XY TO XY_archived, 你的临时表名 TO XY;
-- 后续可单独删除归档的旧表，完全不影响业务访问
DROP TABLE IF EXISTS XY_archived;

用户访问时要么读到旧XY表的数据，要么读到新XY表的数据，不会出现表不存在的情况，性能损耗也几乎可以忽略。

可选适配方案 #

如果有特殊场景不能用上述原子重命名逻辑，可以选择以下方案：

• 使用CREATE OR REPLACE TABLE语法：MariaDB 10.2+已经支持该语法，表替换过程也是原子的，适合需要保留原表部分属性的场景。
• 显式申请元数据排他锁：执行替换逻辑前先申请XY表的MDL排他锁，确保替换过程中没有其他查询访问表，但操作复杂度远高于原子重命名，非必要不推荐。

InnoDB完全支持锁机制，你之前的认知存在偏差：

• InnoDB的锁分为三类：行级锁（共享/排他锁，用于DML操作的行粒度并发控制）、表级意向锁（协调行锁和表操作的冲突）、元数据锁（MDL锁，用于控制表元数据的并发修改，所有访问表的操作都需要先申请MDL读锁，DDL操作需要申请MDL写锁），上述原子重命名的能力就是依赖MDL锁实现的。
• 你认为InnoDB不支持锁，大概率是混淆了MyISAM引擎的显式LOCK TABLES命令，该命令InnoDB也兼容，但会破坏事务的ACID特性，和InnoDB的事务锁体系兼容性很差，非特殊场景不建议使用。

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