MySQL数据库自增锁问题

背景

执行归档任务，把mcc_customer_consume_small表数据导入到mcc_customer_consume_archive表中，多线程并发执行insert ... select语句

INSERT mcc_customer_consume_archive 

SELECT * FROM mcc_customer_consume_small 

WHERE id in (?, ?, ?, ?, ?, ?, ?, ? ...)

问题

部分线程发生死锁，插入失败

异常信息：

MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

分析

insert种类

(1) INSERT-like

所有可以向表中增加行的语句,包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。

包括“simple-inserts”, “bulk-inserts”, and “mixed-mode inserts”.

(2) Simple inserts

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

没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE

(3) Bulk inserts

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

InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。

(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

自增锁 (auto_inc锁）

如果存在自增字段，MySQL会维护一个自增锁，和自增锁相关的一个参数为innodb_autoinc_lock_mode，可以设定3个值，0，1，2。

show variables like '%autoinc_lock_mode%'

innodb_autoinc_lock_mode=0

所有的insert语句在语句开始时得到一个表级的自增锁，语句结束的时释放锁，一个一个分配值（MyIsam引擎的默认值）

innodb_autoinc_lock_mode=1

轻量锁

批量插入时，会加一个自增锁，分配一段连续的id值，只要语句得到了响应的值后就可以提前释放锁，不需要保持到语句结束，可能会造成id不连续的情况（1，3，2）

Simple inserts 因为能确定插入的数据数量，MySQL能一次性生成确定数量的ID用于分配给语句，只要语句得到了相应的值后就可以提前释放自增锁，可能会出现id不连续的情况。

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

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

innodb_autoinc_lock_mode=2

无自增锁，性能最优

• 来一个分配一个，而不会锁表，只会锁住分配id的过程；
• 适用于row复制（RBR）
• 但会造成基于statement的复制（SBR）出问题，主要会造成从库的主键冲突;

解决方式

（1）innodb row复制时，可将innodb_autoinc_lock_mode设置为2，这时可在所有insert情况下表获得最大并发度

（2）另一种方式：去除mcc_customer_consume_archive主键的auto_increment特性

其他：

innodb statement复制时，可将innodb_autoinc_lock_mode设置为1，保证复制安全的同时，获得简单insert语句的最大并发度
myisam引擎情况下，无论什么样自增id锁都是表级锁，设置innodb_autoinc_lock_mode参数无效