并发insert操作导致的dead lock该咋整
发布时间：2021-12-18 13:53:57 所属栏目：MySql教程 来源：互联网
导读：这期内容当中小编将会给大家带来有关并发insert操作导致的dead lock该怎么办，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 说明 线上某业务最近经常会出现dead lock，相关信息如下： 2016-06-15 20:28:25 7f72c004370
|
这期内容当中小编将会给大家带来有关并发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 #事务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) 如上，只能看到事务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 STATUSG 看到的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该怎么办了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。 （编辑：92站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐