MySQL自增字段并发插入导致死锁

逃离我推掉我的手 2023-10-05 10:41 101阅读 0赞

## MySQL带自增字段的表在并发插入时发生死锁 ##

### 问题 ###

'int' object has no attribute 'encode'", <class 'mysql.connector.errors.InternalError'>, (1213, '1213 (40001): Deadlock found when trying to get lock; try restarting transaction', u'40001'))

*  1

tbl\_a有一个自增字段id（PRI，auto\_increment），在python脚本中通过线程池并发执行insert into语句

insert into tbl_a (name) select name from tbl_b where name = '%s';

*  1

发生死锁错误：  
(1213, ‘1213 (40001): Deadlock found when trying to get lock; try restarting transaction’, u’40001’))

### 原理 ###

#### AUTO-INC ####

MySQL对于自增字段有一种锁机制：AUTO-INC Locking，从MySQL用户手册可以看到：

InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements

*  1

对于自增字段的插入会触发表级的AUTO-INC锁，这个锁作用于语句的而不是事务(即语句执行完了锁就会被释放)。使用这种锁是为了确保自增列的值的可预见性和可重复性。可预见性是说当一条insert语句作用于多行时，这些行的自增列基于第一行来说是可预见的；可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致。  
带自增字段的表插入在并发数大于208以上可能出现很多死锁

#### innodb\_autoinc\_lock\_mode ####

在5.1.22版本之后，MySQL InnoDB存储引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。参数innodb\_autoinc\_lock\_mode，用于控制向有auto\_increment 列的表插入数据时相关锁的行为，实现性能与安全(主从的数据一致性)的平衡。

*  先对自增长字段的插入进行分类，如下：
    
     *  insert-like：指所有的插入语句，如insert，replace，insert—select,replace—select,load data等。
     *  simple insert：指能在插入之前就确定插入行数的语句。这些语句包含insert、replace等，需要注意的是：simple inserts不包含insert—on duplicater key update这类SQL语句，例如insert into t(name) values(‘test’)。
     *  bulk inserts：指在插入之前不能确定得到插入行数的语句，如insert—select，replace–select，load data。
     *  mixed-mode inserts：指插入中有一部分的值是自增长的，有一部分是确定的。例如insert into t(id,name) values(1,‘a’),(null,‘b’),(5,‘c’);
 *  innodb\_autoinc\_lock\_mode的取值范围是0， 1， 2，默认值是1：
    
     *  0（traditonal模式）：兼容MySQL5.1.22版本之前的自增长实现方式，即通过表锁的AUTO-INC Locking方式。
     *  1（consecutive连续模式）：对于simple inserts，该值会用互斥量去对内存中的计数器进行累加的操作，对于bulk inserts，还是使用传统表锁的AUTO-INC Locking方式。在这种配置下，如果不考虑回滚操作，对于自增长列的增长还是连续的，并且在这种方式下，statement-based方式的replication还是能很好地工作。需要注意的是，如果已经使用AUTO-INC Locking方式去产生自增长的值，而这时需要进行simple inserts的操作时，还是需要等待AUTO-INC Locking的释放。
     *  2（interleaved交错模式）：在这个模式下，对于所有的insert-like自增长的产生都是通过互斥量，而不是通过AUTO-INC Locking的方式，显然这时性能最高的方式。然而会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。最重要的是，基于Statment-base replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication，这样才能保证最大的并发性能及replication主从数据的一致。

*  innodb\_autoinc\_lock\_mode = 2 不安全，但是在 binlog\_format=ROW，transaction-isolation=READ-COMMITTED ， innodb\_autoinc\_lock\_mode = 2 是非常安全的。

### 回顾 ###

查了一下测试环境MySQL参数，innodb\_autoinc\_lock\_mode果然是1。

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7

不过因为是脚本工具，仅执行一次，将并发量改成10之后问题消失了。根本的解决方法还是将innodb\_autoinc\_lock\_mode设置为2。

### 参考 ###

http://www.xuchanggang.cn/archives/1087.html  
https://blog.csdn.net/poxiaonie/article/details/72899975  
https://www.cnblogs.com/JiangLe/p/6362770.html