自增锁的养成计划
所以自增id的锁并非事务锁，而是每次申请完就马上释放，其它事务可以再申请。其实，在MySQL 5.1版本之前，并不是这样的。
MySQL 5.0时，自增锁的范围是语句级别：若一个语句申请了一个表自增锁，该锁会等语句执行结束以后才释放。显然，这样影响并发度
MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值1。该参数的值为0时，表示采用5.0的策略，设置为1时：
- 普通insert语句
申请后，马上释放； - 类似insert … select 这样的批量插入语句
等语句结束后，才释放
设置为2时，所有的申请自增主键的动作都是申请后就释放锁。
为什么默认设置下的insert … select 偏偏要使用语句级锁？为什么该参数默认值不是2？
为了数据的一致性。
看个案例：批量插入数据的自增锁
若session2申请了自增值后，马上释放自增锁，则可能发生：
- session2先插入了两个记录，(1,1,1)、(2,2,2)
- 然后，session1来申请自增id得到id=3，插入（3,5,5)
- session2继续执行，插入两条记录(4,3,3)、 (5,4,4)
这好像也没关系吧，毕竟session 2语义本身就没有要求t2的所有行数据都和session1相同。
从数据逻辑角度看是对的。但若此时binlog_format=statement，binlog会怎么记录呢？
先看看 MySQL 此时的告警：
mysql> insert into t2(c,d) select c,d from t; Query OK, 4 rows affected, 1 warning (0.01 sec) Records: 4 Duplicates: 0 Warnings: 1 mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
由于两个session同时执行插入数据命令，所以binlog里对表t2的更新日志只有两种情况：要么先记session1，要么先记session2。
但无论哪种，这个binlog拿去从库执行或用来恢复临时实例，备库和临时实例里面，session2这个语句执行出来，生成的结果里，id都是连续的。 此时该库就发生了数据不一致。
因为原库session2的insert语句，生成的id不连续。这个不连续的id，用statement格式的binlog来串行执行，是执行不出来的。
要解决该问题，有如下方案：
- 让原库的批量插入数据语句，固定生成连续id值
所以，自增锁直到语句执行结束才释放，就是为了达此目的
- 在binlog里把插入数据的操作都如实记录进来，到备库执行时，不依赖自增主键去生成
其实就是innodb_autoinc_lock_mode=2，同时binlog_format=row。
所以生产上有insert … select这种批量插入场景时，从并发插入的性能考虑，推荐设置：innodb_autoinc_lock_mode=2 && binlog_format=row，既能提升并发性，又不会出现数据一致性问题。
这里的“批量插入数据”，包含如下语句类型：
- insert … select
- replace … select
- load data
在普通insert语句包含多个value值的场景，即使innodb_autoinc_lock_mode=1，也不会等语句执行完成才释放锁。因为这类语句在申请自增id时，可以精确计算出需要多少个id，然后一次性申请，申请完成后锁即可释放。
即批量插入数据的语句，之所以需要这么设置，是因为“不知道要预先申请多少个id”。
既然不知道要申请多少个自增id，那么最简单的就是需要一个时申请一个。但若一个select … insert要插入10万行数据，就要申请10w次，速度慢还影响并发插入性能。
因此，对于批量插入数据语句，MySQL提供了批量申请自增id的策略：
- 语句执行过程中，第一次申请自增id，会分配1个
- 1个用完以后，这个语句第二次申请自增id，会分配2个
- 2个用完以后，还是这个语句，第三次申请自增id，会分配4个
依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。
看案例：
mysql> create table t2 like t; mysql> insert into t2(c,d) select c,d from t; Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1 mysql> insert into t2 values(null, 5,5); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+------+------+ | id | c | d | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 8 | 5 | 5 | +----+------+------+ 5 rows in set (0.00 sec)
insert…select实际上往t2中插入4行数据。但这四行数据是分三次申请的自增id，第一次申请到id=1，第二次id=2和id=3， 第三次id=4到id=7。
由于该语句实际只用上了4个id，所以id=5到id=7就被浪费了。之后，再执行
insert into t2 values(null, 5,5)
实际上插入的数据是（8,5,5)。这是主键自增id不连续的三大原因。
