mysql auto_increment锁带来的表锁举例剖析

这篇文章主要介绍“mysql auto_increment锁带来的表锁举例分析”，在日常操作中，相信很多人在mysql auto_increment锁带来的表锁举例分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql auto_increment锁带来的表锁举例分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

案例描述:

线上一张表有大概2亿条,50个G左右大小的数据,业务进行重新规划,需要将绝大部分数据进行历史归档.当时为了方便,就新建一张相同结构的表,然后快速的rename成线上表,然后将备份表需要导入到线上表的数据进行insert into select操作.结果,线上表产生表锁,业务全部堵住,发现情况时,已经没办法撤销insert操作(因为已经插入很多了.在回滚,估计代价更高),所以就只有坐等insert完毕了.

create table new_table like old_table; --创建一个跟线上表结构一样的新表;

alter table new_table auto_increment=xxxx --将新表的自增值设大一些,目的是为了跟老表数据留下空间和区别;

rename table old_tale to old_table_bak;

renmae table new_table to online_table;

-- 这两行一起执行,减小切换表的时间,尽量减小对线上数据的影响;

insert into online_table select * from old_table_bak where xxxxxxx; --将历史表中需要的数据导入新表;

案例分析:

也算是自己麻痹大意了,以为innodb的insert只会加行级锁,没考虑到auto_increment的自增锁.产生了表锁,影响了整个业务.

下面分享下auto_increment自增锁的一些信息.

讲自增锁,就讲一下innodb_autoinc_lock_mode参数:

 在mysql5.1.22之前，mysql的“INSERT-like”语句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住，直到整个语句结束（而不是事务结束）。

 因此在使用INSERT…SELECT、INSERT…values(…),values(…)时，LOAD DATA等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的“INSERT-like”、Update等语句，推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间。

 mysql5.1.22之后mysql进行了改进，引入了参数 innodb_autoinc_lock_mode，通过这个参数控制mysql的锁表逻辑。

 在介绍这个之前先引入几个术语，方便说明 innodb_autoinc_lock_mode。

 1.“INSERT-like”：

 INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

 2.“Simple inserts”：

 就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()

 3.“Bulk inserts”：

 就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

 4.“Mixed-mode inserts”：

 不确定是否需要分配auto_increment id，一般是下面两种情况

 INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

 INSERT … ON DUPLICATE KEY UPDATE

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mod，传统模式)。

 这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差。

 二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode，连续模式)。

 这种方式是新版本中的默认方式，推荐使用，并发性相对较高，特点是“consecutive”，即保证同一条insert语句中新插入的auto_increment id都是连续的。

 这种模式下：

 “Simple inserts”：直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。

 “Bulk inserts”：因为不能确定插入的数量，因此使用和以前的模式相同的表级锁定。

 “Mixed-mode inserts”：直接分析语句，获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。

 需要注意的是，这种方式下，会分配过多的id，而导致“浪费”。

 比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id，而不管用户是否指定了部分id；

 INSERT … ON DUPLICATE KEY UPDATE一次性分配，而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。

 注意：当master mysql版本<5.1.22，slave mysql版本>=5.1.22时，slave需要将innodb_autoinc_lock_mode设置为0，因为默认的innodb_autoinc_lock_mode为1，对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同，现实环境一般会使用INSERT … ON DUPLICATE KEY UPDATE。

 三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode，交叉模式)。

 这种模式是来一个分配一个，而不会锁表，只会锁住分配id的过程，和innodb_autoinc_lock_mode = 1的区别在于，不会预分配多个，这种方式并发性最高。

 但是在replication中当binlog_format为statement-based时（简称SBR statement-based replication）存在问题，因为是来一个分配一个，这样当并发执行时，“Bulk inserts”在分配的时会同时向其他的INSERT分配，会出现主从不一致（从库执行结果和主库执行结果不一样），因为binlog只会记录开始的insert id。

 测试SBR，执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。

 但是row-based replication RBR时不会存在问题。

 另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete（update多条语句，delete多条语句）时，日志会比SBR大很多；假如实际语句中这样语句不是很多的时候（现实中存在很多这样的情况），推荐使用RBR配合innodb_autoinc_lock_mode，不过话说回来，现实生产中“Bulk inserts”本来就很少，因此innodb_autoinc_lock_mode = 1应该是够用了。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!