MySQL 的 InnoDB 存储引擎中，innodb_autoinc_lock_mode 系统变量控制着为 AUTO_INCREMENT 列生成值时所使用的锁策略。三种模式的区别如下：

• 0（传统模式）：使用表级的 AUTO-INC 锁，在整个插入语句执行期间持有该锁，直到语句结束才释放。这保证了每个语句生成的 AUTO_INCREMENT 值是连续且单调递增的，但并发性较差。
• 1（连续模式，默认）：对于插入行数确定的语句（如简单的 INSERT ... VALUES (...)），使用轻量级锁（互斥量）快速分配；对于插入行数不确定的语句（如 INSERT ... SELECT），使用 AUTO-INC 锁。这种模式在保证并发的同时，也能确保基于语句的复制安全。
• 2（交错模式）：所有插入语句都使用轻量级锁，允许多个插入语句同时执行，AUTO_INCREMENT 值可能交错分配，导致同一语句生成的值不连续，且不同语句的值可能交叉。

为什么 innodb_autoinc_lock_mode=2 在主从复制（基于语句）中不安全？

1. 基于语句的复制（Statement-Based Replication）原理

在基于语句的复制模式下，主库执行的 SQL 语句会被原样记录到二进制日志（binlog）中，然后从库重放这些语句以达到数据同步。对于包含 AUTO_INCREMENT 列的插入语句，从库在执行时也需要自己生成相应的自增值。

2. 模式 2 带来的问题

• 值分配交错：在模式 2 下，多个并发的插入语句可以同时获得轻量级锁，导致 AUTO_INCREMENT 值分配是交错的。例如：

  • 事务 A 插入一行，获得值 1；
  • 事务 B 插入一行，获得值 2；
  • 事务 A 再插入一行，获得值 3。
    最终，事务 A 的两条记录值可能为 (1, 3)，事务 B 的值为 2。这些值在时间顺序上是交错的，依赖于并发执行的顺序。

• 从库无法重现相同的分配序列：从库重放这些语句时，其并发环境（如线程调度、锁竞争）与主库通常不同，因此无法保证相同的交错顺序。如果从库也按照同样的语句并行执行，那么它可能产生不同的 AUTO_INCREMENT 值分配结果，例如：

  • 从库可能先执行事务 B 再执行事务 A，导致事务 A 获得的值变为 (2, 3)，事务 B 获得值 1。
    这样，主从数据就会不一致（例如主库上某条记录的 AUTO_INCREMENT 值可能被其他表用作外键，导致引用错误）。

3. 为什么模式 0 和 1 是安全的？

• 模式 0：每个插入语句都持有 AUTO-INC 锁直到语句结束，因此语句内部生成的值是连续的，且不同语句之间严格串行化，不会交错。从库重放时，只要按顺序执行语句，就能得到与主库完全相同的值序列。
• 模式 1：虽然允许并发轻量级锁，但它保证了对于行数确定的语句，每个语句内部的值是连续的，并且语句之间的值不会交叉（因为不确定行数的语句仍使用 AUTO-INC 锁，确定了边界）。这样从库重放时，只要按语句顺序执行，就能得到与主库一致的值。

4. 模式 2 在哪些情况下是安全的？

如果使用基于行的复制（Row-Based Replication），binlog 中记录的是每一行修改后的实际数据值（包括 AUTO_INCREMENT 列的值），而不是重新生成。此时从库直接应用这些值，不再依赖生成过程，因此模式 2 是安全的，并且可以提升并发性能。

总结

innodb_autoinc_lock_mode=2 之所以在基于语句的复制中不安全，是因为它允许并发插入语句的 AUTO_INCREMENT 值交错分配，导致同一个语句在不同执行环境下可能产生不同的值序列，从而破坏主从数据一致性。因此，在生产环境中，如果使用基于语句的复制，应避免设置该值为 2，而应使用默认值 1 或 0。若必须使用模式 2，则建议配合基于行的复制使用。