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

php 同时被 3 个专栏收录
224 篇文章 1 订阅
164 篇文章 2 订阅
232 篇文章 1 订阅

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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值