最近业务上连续出现了两次死锁逻辑，两次都是特别简单的SQL语句，分析后才发现自己对InnoDB加锁了解得太浅了。
表结构
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`age` int(11) DEFAULT NULL,
`deleted` int(22) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
主键id和唯一键name
死锁场景一
- SQL语句
select * from user where name='tenmao' for update;
insert into user(`name`) values('tenmao');
- 死锁日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-16 20:23:21 0x7f4b8b596700
*** (1) TRANSACTION:
TRANSACTION 425593, ACTIVE 21 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 65439, OS thread handle 139962440095488, query id 2992052 localhost maibao update
insert into user(`name`) values('tenmao')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 370 page no 4 n bits 72 index uk_name of table `tenmao`.`user` trx id 425593 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 425594, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 65440, OS thread handle 139962437166848, query id 2992053 localhost maibao update
insert into user(`name`) values('tenmao')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 370 page no 4 n bits 72 index uk_name of table `tenmao`.`user` trx id 425594 lock_mode X
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 370 page no 4 n bits 72 index uk_name of table `tenmao`.`user` trx id 425594 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)
- 死锁分析
| T1 | T2 | |
|---|---|---|
select * from user where name='tenmao' for update 记录不存在所以获取gap锁，模式是X |
||
select * from user where name='tenmao' for update 记录不存在所以获取gap锁，模式是X（因为gap锁之间不冲突，所以可以获取） |
||
insert into user(`name`) values('tenmao') 插入需要获取插入意向锁。因为与T2的gap锁冲突，需要等待 | ||
insert into user(`name`) values('tenmao') 插入需要获取插入意向锁。。因为与T1的gap锁冲突，需要等待。死锁！ |
死锁场景二
- SQL语句
insert into user(`name`) values('tenmao');
- 死锁分析
| T1 | T2 |
|---|---|
insert into user(`name`) values('tenmao') 第一阶段，需要判断duplicate key，所以获取S锁，类型是gap |
|
insert into user(`name`) values('tenmao') 第一阶段，需要判断duplicate key，所以获取S锁，类型是gap |
|
| 第二阶段，S锁升级为X锁。等待T2释放S锁 | |
| 第二阶段，S锁升级为X锁。等待T2释放S锁（死锁） |
以上过程，因为S锁升级为X锁的时间间隔很短，所以不是很好复现，一般在高并发的时候出现。不过可以用3个事务来复现：
| T1 | T2 | T3 |
|---|---|---|
insert into user(`name`) values('tenmao'); 先获取S锁判断duplicate key，插入前升级为X锁 |
||
insert into user(`name`) values('tenmao'); 第一阶段，需要判断duplicate key，所以获取S锁，类型是gap，与T1的X锁冲突，等待 |
||
insert into user(`name`) values('tenmao'); 第一阶段，需要判断duplicate key，所以获取S锁，类型是gap，与T1的X锁冲突，等待 |
||
| rollback | 获取到S锁，类型是gap | 获取到S锁，类型是gap |
| 第二阶段，S锁升级为X锁。等待T3释放S锁（死锁） | 第二阶段，S锁升级为X锁。等待T2释放S锁（死锁） |
网友评论