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

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。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

• 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