文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

并发insert操作导致的dead lock该怎么办

2024-04-02 19:55

关注

这期内容当中小编将会给大家带来有关并发insert操作导致的dead lock该怎么办,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

说明

线上某业务最近经常会出现dead lock,相关信息如下:

  2016-06-15 20:28:25 7f72c0043700InnoDB: transactions deadlock detected, dumping detailed information.

    2016-06-15 20:28:25 7f72c0043700
*** (1) TRANSACTION:
TRANSACTION 151506716, ACTIVE 30 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 1467337, OS thread handle 0x7f72a84d6700, query id 308125831 IP地址1 fold-sys update
insert into t ( a,b,c, addtime )
        values
         (63, 27451092,120609109,now())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_key` of table `dbname`.`t` trx id 151506716 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 151506715, ACTIVE 30 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 1477334, OS thread handle 0x7f72c0043700, query id 308125813 IP地址2 fold-sys update
insert into t ( a,b,c, addtime )
        values
         (63, 27451092,120609109,now())
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_folder_fk_video_seq` of table `folder`.`t_mapping_folder_video` trx id 151506715 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 46 page no 693076 n bits 664 index`unq_fk_key` of table `dbname`.`t` trx  id 151506715 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

初步分析

1、 122和120 在同一时刻发起了相同的insert 操作  数据一模一样 而 a,b,c 刚好是uniq key

2、咱们是RC 级别  出现了 GAP lock 这个有点疑问?查阅了下文档 

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable theinnodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

设置innodb_locks_unsafe_for_binlog或者RC级别来关闭gap  

后面部分 可以理解为 RC级别下的 外键和重复检查的时候也会产生GAP呢

重现此deadlock

5.5.19-55-log Percona Server (GPL), Release rel24.0, Revision 204

tx_isolation=READ-COMMITTED 

innodb_locks_unsafe_for_binlog=OFF

 

创建实验表

CREATE TABLE `deadlock` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `a` smallint(5) unsigned NOT NULL DEFAULT '0',

  `b` int(11) NOT NULL DEFAULT '0',

  `c` int(11) NOT NULL DEFAULT '0',

  `d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`),

  UNIQUE KEY `unq_b_c_a` (`b`,`c`,`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

事务T1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql>insert into deadlock(a,b,c) values(1,2,3);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

事务和锁

#此时表deadlock上被加了一把意向排它锁(IX)

---TRANSACTION 4F23D, ACTIVE 20 sec

1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX

事务T2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into deadlock(a,b,c) select 1,2,3;

#此处会处于等待

事务和锁

---TRANSACTION 4F23E, ACTIVE 3 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update

insert into deadlock(a,b,c) values(1,2,3)

#事务T2对表deadlock加了一把意向排它锁(IX),而对unq_b_c_a唯一约束检查时需要获取对应的共享锁,但是对应记录被T1加了X锁,此处等待获取S锁(#注意,insert进行的是当前读,所以读会被X锁阻塞。如果是快照读的话,不需要等待X锁)

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting

------------------

TABLE LOCK table `yujx`.`deadlock` trx id 4F237 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting

---TRANSACTION 4F23D, ACTIVE 37 sec

#事务T1对表deadlock加了一把意向排它锁(IX)和记录锁(X)

2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F236 lock_mode X locks rec but not gap

----------------------------

事务T3

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into deadlock(a,b,c) values(1,2,3);

#此处会处于等待

事务和锁

---TRANSACTION 4F23F, ACTIVE 3 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 8, OS thread handle 0x41976940, query id 245 localhost root update

insert into deadlock(a,b,c) values(1,2,3)

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

#同样,事务T3与上面的事务T2的事务和锁等待一样,事务T1造成了T2和T3的等待

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F238 lock mode S waiting

------------------

TABLE LOCK table `yujx`.`deadlock` trx id 4F238 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F238 lock mode S waiting

---TRANSACTION 4F23E, ACTIVE 31 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update

insert into deadlock(a,b,c) values(1,2,3)

------- TRX HAS BEEN WAITING 31 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting

------------------

TABLE LOCK table `yujx`.`deadlock` trx id 4F237 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting

---TRANSACTION 4F23D, ACTIVE 65 sec

2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F236 lock_mode X locks rec but not gap

事务T1进行rollback

#事务T1进行rollback;

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

#事务T2的insert成功

mysql> insert into deadlock(a,b,c) values(1,2,3);

Query OK, 1 row affected (10.30 sec)

#事务T3返回deadlock错误

mysql> insert into deadlock(a,b,c) values(1,2,3);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

DEADLOCK信息

------------------------

LATEST DETECTED DEADLOCK

------------------------

160620 11:38:14

*** (1) TRANSACTION:

TRANSACTION 4F23E, ACTIVE 48 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 297 localhost root update

insert into deadlock(a,b,c) values(1,2,3)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23E lock_mode X insert intention waiting

*** (2) TRANSACTION:

TRANSACTION 4F23F, ACTIVE 30 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1

MySQL thread id 8, OS thread handle 0x41976940, query id 300 localhost root update

insert into deadlock(a,b,c) values(1,2,3)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23F lock mode S

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23F lock_mode X insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

如上,只能看到事务T2和事务T3最终导致了deadlock;T2等待获取unq_b_c_a唯一key对应的记录锁(X lock),T3在`unq_b_c_a`对应的记录上持有S锁,并且T3也在等待获取对应的X锁。最终T3被ROLL BACK了,并且发回了DEAD LOCK的提示信息

综上

1、SHOW ENGINE INNODB STATUS\G 看到的DEADLOCK相关信息,只会返回最后的2个事务的信息,而其实有可能有更多的事务才最终导致的死锁

2、当有3个(或以上)事务对相同的表进行insert操作,如果insert对应的字段上有uniq key约束并且第一个事务rollback了,那其中一个将返回死锁错误信息。

3、死锁的原因

l   T1 获得 X 锁并 insert 成功

l   T2 试图 insert, 检查重复键需要获得 S 锁, 但试图获得 S 锁失败, 加入等待队列, 等待 T1

l   T3 试图 insert, 检查重复键需要获得 S 锁, 但试图获得 S 锁失败, 加入等待队列, 等待 T1

l  T1 rollback, T1 释放锁, 此后 T2, T3 获得 S 锁成功, 检查 duplicate-key, 之后 INSERT 试图获得 X 锁, 但 T2, T3 都已经获得 S 锁, 导致 T2, T3 死锁

4、避免此DEADLOCK我们都知道死锁的问题通常都是业务处理的逻辑造成的,既然是uniq key,同时多台不同服务器上的相同程序对其insert一模一样的value,这本身逻辑就不太完美。故解决此问题:

a、保证业务程序别再同一时间点并发的插入相同的值到相同的uniq key的表中

b、上述实验可知,是由于第一个事务rollback了才产生的deadlock,查明rollback的原因

c、尽量减少完成事务的时间

 
最终结论

         当有3个(或以上)事务对相同的表进行insert操作,如果insert对应的字段上有uniq key约束并且第一个事务rollback了,那其中一个将返回死锁错误信息。

上述就是小编为大家分享的并发insert操作导致的dead lock该怎么办了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯