MySQL自增锁模式innodb_autoinc_lock_mode参数理解调优

╰半橙微兮° 2022-10-19 12:45 163阅读 0赞

前段时间某数据表运行过程中，出现自增字段突然跳跃式增长的问题，潜心研究发现，问题导致原因可能是因为并发写入导致

于是通过各种途径查阅是因为innodb\_autoinc\_lock\_mode参数设置的不同表现所在，于是进行了调整，在此对该参数的理解记录一二。

官方原文地址：[https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html\#innodb-auto-increment-initialization][https_dev.mysql.com_doc_refman_8.0_en_innodb-auto-increment-handling.html_innodb-auto-increment-initialization]

中文翻译地址：[https://blog.csdn.net/ashic/article/details/53810319][https_blog.csdn.net_ashic_article_details_53810319]

其文章中主要提及，mysql的3种插入方式以及innodb\_autoinc\_lock\_mode的3种参数选择相关内容

**三种模式简要说明**

首先，该参数仅在InnoDB引擎下生效，myisam引擎下，无论什么样自增id锁都是表级锁  
0：traditonal （每次都会产生表锁）  
1：consecutive （mysql的默认模式，会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）  
2：interleaved （不会锁表，来一个处理一个，并发最高）

**如何理解，以及参数如何选择？**

1、innodb\_autoinc\_lock\_mode为0时的，也就是官方说的traditional级别

该自增锁是表锁级别，且必须等待当前SQL执行完成后或者回滚掉才会释放，这样在高并发的情况下可想而知自增锁竞争是比较大的

2、innodb\_autoinc\_lock\_mode为1时的，也就是官方说的consecutive级别

这时如果是单一的insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其他事务中已经有session获取了自增锁）。另外当SQL是一些批量insert sql时，比如insert into ...select ...,load data,replace ..select..时，这时还是表级锁，可以理解成退化为必须等待当前SQL执行完才释放。可以认为，该值为1时是相对比较轻量的锁，也不会对复制产生影响，唯一的缺陷是产生的自增值不一定是完全连续的

3、innodb\_autoinc\_lock\_mode为2时，也就是官方说的interleaved 级别

所有insert种类的SQL都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当binlog\_format为statement时，这时的复制没法保证安全，因为批量的insert，比如insert ..select..语句在这个情况下，也可以立马获取到一大批的自增id值，不必锁整个表，slave在回放这个sql时必然会产生错乱

**问题分析**

在此我们看到mysql的默认模式是2，官方表示其是连续插入，但实际上在这一模式simple insert做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句，而只要语句得到了相应的值后就可以提前释放锁，这就导致插入是连续的，但如果插入的语句存在错误的话（本次的值已经被分配，下次会增加），实际上自增的字段可能不连续

也就是说该模式只保证插入的连续性，但自增字段的连续性无法保证，而我们遇到的问题恰恰是要保证自增字段的连续性。

于是综合考虑之后我们启用了0模式，观察一段时间之后发现满足我们的需求，保证了字段的连续性。

至此问题得到解决。

[https_dev.mysql.com_doc_refman_8.0_en_innodb-auto-increment-handling.html_innodb-auto-increment-initialization]: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
[https_blog.csdn.net_ashic_article_details_53810319]: https://blog.csdn.net/ashic/article/details/53810319