MySQL Insert 死锁

insert 死锁

insert 上锁步骤

insert语句上锁的大致过程如下:

1、在行所在的间隙上申请“意向插入锁”。2、申请所要插入行的“排他锁”。3、如果在第二步的时候引发了唯一键冲突,那么陷入冲突的事务,要把上锁的过程分两步,先申请行的“共享锁”,然后再申请“排他锁”; 如果有多个事物陷入冲突,那么他们一定都会申请到“共享锁”,然后在申请排他锁的相互等待(死锁),这个时候 MySQL 会选择牺牲掉其中一些事务,让其中的一个完成。


复现 insert 死锁

按理论一步一步的复现 insert 死锁的场景。

会话一 会话二 会话三
create table t(x int primary key);    
start transaction;    
insert into t(x) values(1024);    
  insert into t(x) values(1024); insert into t(x) values(1024);
rollback commit successful ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

show engine innodb status 输出中关于死锁的信息如下。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-03 19:29:30 0x7f9ab00b4700
*** (1) TRANSACTION:
TRANSACTION 2759830, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140302402717440, query id 1221 127.0.0.1 root update
insert into t(x) values(1024)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 2759830 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 2759831, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 14, OS thread handle 140302355220224, query id 1222 127.0.0.1 root update
insert into t(x) values(1024)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 16 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 2759831 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 2759831 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

MySQL 为什么要这么做

大前提是这样的,两个事务插入同一条记录,必定有一个要失败。死锁也是必定要有一个失败,那么何不把并发插入也合并到死锁的处理流程中去呢?想到就要做到,那把上行锁的过程拆一下, 先上“S 共享锁” 再上“X 排他锁”,由于“S”可以共享所以两个都能完成这一步,第二小要“X”所以两个事务就相互等待(死锁)。


解决方案

如果你在业务上还真遇到了上面的场景,然而 MySQL 死锁了。你又不想让它死锁,那怎么办呢?MySQL 不是把上锁的步骤拆开了吗?那我们把它合起来。

select ... for update 直接对行上排他锁,通过它我们可以把分开的两步合起来,on duplicate key update 当遇到冲突时会直接更新,不会再报错;结合这两大神器代码可以改成如下的形式。

会话一 会话二 会话三
create table t(x int primary key);    
start transaction;    
select x from t for update; insert into t(x) values(1024) on duplicate key update x = 1024;    
  select x from t for update; insert into t(x) values(1024) on duplicate key update x = 1024; select x from t for update; insert into t(x) values(1024) on duplicate key update x = 1024;
rollback commit successful commit successful

副作用

由于 select for update 是排他锁,所以并发性上会有些问题,建议与read-committed一起使用。


彩蛋

根据分析可以知道问题发生在行锁上,把隔离级别调整成 READ-COMMITTED 也只会影响 gap 锁,我以为这个隔离级别下不会再有插入意向锁(一种特别的 gap)了,没想到还有。

mysql> show variables like ‘%iso%‘;
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

执行同样的操作。

会话一 会话二 会话三
create table t(x int primary key);    
start transaction;    
insert into t(x) values(1024);    
  insert into t(x) values(1024); insert into t(x) values(1024);
rollback commit successful ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-11 13:57:15 0x7ff1bccf1700
*** (1) TRANSACTION:
TRANSACTION 6946, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 9, OS thread handle 140676621076224, query id 237 127.0.0.1 root update
insert into t(x) values(1024)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 6946 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 6946 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 6947, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140676151592704, query id 258 127.0.0.1 root update
insert into t(x) values(1024)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 6947 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `tempdb`.`t` trx id 6947 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

READ-COMMITTED 隔离级别下已经没有 gap 锁了,然而官方文档上说 insert intention 是 gap 的一种,所以 insert intention 应该不存在才对呀。

MySQL Insert 死锁

上一篇:解决使用OCI连接oracle LNK2019: 无法解析的外部符号的问题


下一篇:深入NoSQL 中文版 [Shashank Tiwari著] 高清PDF扫描版[41MB]下载