死锁案例3-辅助索引经典案例

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
6 rows in set (0.00 sec)

RR隔离级别
session 1	session2	session 3
begin; delete from t where c=5;
	begin; delete from t where c=5; (block)
		begin; insert into t values(7,7,7); (block)
rollback;	rollback;	rollback;
session 1	session2
begin; delete from t where c=5;
	begin; delete from t where c=5; (block)
insert into t values(7,7,7);(可以插入)	无死锁
insert into t values(2,2,2);(可以插入)	deadlock

delete from t where c=5; 辅助索引等值查询，查到第一条满足要求的行，还会向右遍历。

 

 

 
一、RR隔离级别，begin; delete from t where c=5;     LOCK_MORE是X代表next-key lock,  索引c上加锁，（0，5】；索引c上加GAP锁（5，10）；主键上加行锁，5；

 

 

二、session2:delete from t where c=5; 锁等待，只是一个next-key lock, 锁的范围是（0，5】.

 

 

三、session1；insert into t values(2,2,2);(可以插入) ，session2的SQL回滚，报死锁。

 

 

死锁信息：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-19 17:48:19 139988650534656
*** (1) TRANSACTION:
TRANSACTION 4958, ACTIVE 448 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 31, OS thread handle 139990166521600, query id 324 localhost dball updating
delete from t where c=5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 5 n bits 80 index c of table `test`.`t` trx id 4958 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000005; asc ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 80 index c of table `test`.`t` trx id 4958 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000005; asc ;;

*** (2) TRANSACTION:
TRANSACTION 4957, ACTIVE 917 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 2
MySQL thread id 30, OS thread handle 139986808600320, query id 325 localhost dball update
insert into t values(2,2,2)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 5 n bits 80 index c of table `test`.`t` trx id 4957 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000005; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 80 index c of table `test`.`t` trx id 4957 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000005; asc ;;

*** WE ROLL BACK TRANSACTION (1)