03-三、MySQL 面试题 InnoDB AUTO_INCREMENT ( 中 )

上一章节中，我们介绍了 innodb_autoinc_lock_mode = 0 传统锁模式，知道了在传统锁模式下，所有的 「 insert like 」 语句都会获得一个特殊的 表级 AUTO-INC 锁，这种锁会自动添加到 SQL 语句的末尾 ( 不是事务的末尾 )，以确保以可预测且可重复的顺序为给定的 INSERT 语句序列分配自增值，并确保为任何给定语句分配的自增值都是连续的

今天，我们就来看看剩下的两种模式是怎么样的

innodb_autoinc_lock_mode = 1 连续锁模式

在此 「 连续锁 」 模式下，「 批量插入 」会首先获取一个特殊的表级 AUTO_INC 锁，并一直保持到所有语句执行结束才释放。连续锁模式适用于所有的批量插入语句，包括 INSERT ... SELECT、REPLACE ... SELECT 和 LOAD DATA 语句。

Innodb 存储引擎一次只会执行一个持有 AUTO-INC 锁的 SQL 语句。

如果批量插入操作的源表与目标表不同，则会先在源表中选择的第一行上执行共享锁，然后对目标表执行 AUTO-INC 锁。如果批量插入操作的源表和目标表是同一个表，则会先对所有选定的行执行共享锁之后再执行 AUTO-INC 锁

简单插入 「 Simple inserts 」 ( 即预先知道要插入的行数 ） 则是不同的，它会在互斥锁 ( 一个轻量级的锁 ) 的控制下获得所需数量的自增量值来避免表级 AUTO-INC 锁定。但这种获得自增值的互斥锁只在分配过程的持续时间内持有，而不是在语句完成之前。除非另一个事务首先持有 AUTO-INC 锁，否则不使用表级 AUTO-INC 锁。如果另一个事务持有 AUTO-INC 锁，则 「 简单插入 」会一直等待直到自己获得 AUTO-INC 锁，就好像它自己也是批量插入一样

这种锁定模式可以确保，在存在 INSERT 语句情况下，纵使事先不知道行数 ( 以及在语句执行时分配的自增值数 )，任何由 「 INSERT-like 」 分配的所有自增值都是连续的，且确保对基于语句的复制操作是安全的。

简而言之，这种锁模式显着提高了可伸缩性，同时可以安全地进行基于语句的复制。此外，与 「 传统锁」 模式一样，可以确保为任何给定语句分配的自增值数字是连续的。

从某些方面说，与 「 传统锁 」 模式相比，对于任何使用自增值的语句，语义并没有变化，除了下面这个重要的例外

这个例外就是 「 混合模式插入 」 (mixed-mode inserts ) ， 在混合模式插入中，那些多行的 「 简单插入 」中的某些行 ( 但不是所有行 ) 会显式为 AUTO\_INCREMENT 列提供一个值。对与这种插入模式，InnoDB 会分配比要插入的行数更多的自增值。

但这样也存在一个问题 ( 可以被忽略的问题 )，因为自动分配的所有值都是连续生成，因此可能高于最后执行的语句生成的自增值，也就是超过的没用到的自增值则被丢失了。

innodb_autoinc_lock_mode = 2 交错锁模式

在 「 交错锁 」模式下，任何 「 INSERT-like 」语句都不会使用表级 AUTO-INC 锁，且多个语句可以同时执行。

这是最快且可扩展性最强的锁模式，但在二进制日志重放 SQL 语句中，会让使用基于语句

的复制或恢复方案变得不安全。

这种锁模式，会确保自增值是唯一的，且可以在所有同时执行的 「 INSERT-like 」 语句中保持单调递增

当然了，这种锁模式也是有缺点的，因为多个语句可以同时生成数字 (即自增值数字的分配会在语句之间交错进行 ) ，会造成任何给定语句插入的行生成的值可能不是连续的

结束语

好复杂的 AUTO-INC 锁模式，翻译的我都头痛了，虽然在翻译的时候知道是啥意思，但是自己写出来，真的是一头雾水。

简单理解这三种锁模式

1、 传统锁模式 – 不管三七二十一，先用表级的 AUTO-INC 锁，直到语句插入完成，然后释放锁
2、 连续锁模式 – 不管三七二十一，先用互斥锁，然后生成所有插入行需要的自增值，然后释放互斥锁，最后使用这些自增值来插入数据。对于行数未知的，那就只能使用 「 传统锁 」 模式了，先锁起来，执行完毕了再释放，因为人家不知道要生成多少自增值啊
3、 交错模式 – 管它刮风下雨，需要的时候再生成，也管它连续与否，用了就是了…

希望读者能够给小编留言，也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")