mysql lock_mode_MySQL innodb_autoinc_lock_mode的分析

之前由于业务变更，我在使用pt的OSC工具在对一个线上不是很繁忙的数据库做变更时，发现每次都是产生了锁等待，后面排查发现是和OSC的bulk insert和autoinc lock有关，后面大家也对innodb_autoinc_lock_mode这个参数的锁的行为做过一些讨论。本篇文档就为大家介绍下MySQL的innodb_autoinc_lock_mode锁行为机制，以及不同设置下性能的分析。

首先我们对insert语句做个分类，大致上可以分成三类：

1、simple insert 如insert into t(name) values(‘dbarun’)

2、bulk insert 如load data | insert into … select …. from ….

3、mixed insert 如insert into t(id,name) values(1,’a’),(2,’b’),(3,’c’);

其次我们对innodb_autoinc_lock_mode这个参数的值做个了解,这个参数可以设置为0,1,2，其代表的含义如下：

1、设置为0 这个表示tradition (传统模式)

2、设置为1 这个表示consecutive (连续模式)

3、设置为2 这个表示interleaved (交错模式)

tradition(innodb_autoinc_lock_mode=0) 传统模式：

在这一模式下，所有的insert语句(“insertlike”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句。它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句级复制的安全)。由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的写入性能。

consecutive(innodb_autoinc_lock_mode=1) 连续模式：

这一模式下对simpleinsert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的(它保证了基于语句级复制的安全)。这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁。

interleaved(innodb_autoinc_lock_mode=2) 交错模式：

由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

最后我们讨论下，innodb_autoinc_lock_mode这个参数该如何设置？

在大多数情况下，此变量设置为默认值1，此模式下对任何模式(binlog_format=sql/row/mixed)的数据复制都是安全的。

在binlog_format=row时，也可以将其设置为2。对于交错语句，insert语句不使用表级的auto-inc锁，多个语句可以同时执行，从而提高并发性能。但如果在从二进制日志重放SQL语句时使用基于语句的复制或恢复方案，设置为2则是不安全的。还有另一个考虑因素，对于自增ID键强联续性依赖的，请不要设置为2。假如设置为2，这意味着您可以进行三次insert写入，并期望ID为100，101和103，但最终可能会得到100、102和104。

如果您只做简单的插入，设置为2可能对您没有帮助。我用100个线程在AmazonRDS的MySQL5.7上做了一个SysBench测试，发现锁模式1和锁模式2在性能和吞吐量上没有区别。当无法确定行数时(例如使用INSERT INTO…SELECT语句)时，设置为2对您的帮助最大。

最后总结一下，innodb_autoinc_lock_mode通常情况下设置为1即可，安全性和性能平衡下来最好的。通常情况下innodb_autoinc_lock_mode设置为1的性能影响可以忽略，如果有性能问题，需要检视下业务代码逻辑是否正常;当binlog_format=row时，在有大量的bulk insert并且不care自增键的连续性时可以设置为2，可以帮助你提高写入性能。

关注公众号，获得更多干货信息