死锁案例2-只插入也会产生锁

RC隔离级别下最容易出现的死锁。

CREATE TABLE `t5` (
`id` int(11) NOT NULL,
`name` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t5 values(1,1000);

 

insert into t5 values(2,10);    insert into t5 values(3,10);      unique-key冲突，加的什么锁 ？锁的范围是？

insert into t5 values(4,9);死锁

insert into t5 values(5,11); 可以插入

insert into t5 values(6,10); UNIQUE KEY冲突，不会死锁。

 

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion。

select ENGINE,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE ,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks ;

按照顺序分析：加锁类型+加锁范围

RC隔离级别
begin; insert into t5 values(2,10);
	begin;  insert into t5 values(3,10);
insert into t5 values(4,9);可以插入	死锁
insert into t5 values(5,11);可以插入	未死锁
insert into t5 values(6,10);报UNIQUE KEY冲突	未死锁

 

一、begin; insert into t5 values(2,10);

 

 

二、begin;  insert into t5 values(3,10);

 

 锁的范围是uk_name(-无穷，10)，锁是S锁，状态为等待。 （2，10）这一行加X锁。

 

 

ession1是有操作权限的，锁的是（2，10）这一行，非（3，10），所以不会触发死锁。

三、insert into t5 values(4,9);可以插入 ，触发死锁。事务2回滚。 session1申请插入意向锁，和session2的S锁冲突。 X锁和S锁冲突。

 

 

死锁信息如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-19 16:16:48 139988650534656
*** (1) TRANSACTION:
TRANSACTION 4951, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 25, OS thread handle 139990166230784, query id 253 localhost dball update
insert into t5 values(3,10)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 72 index uk_name of table `test`.`t5` trx id 4951 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 80000002; asc ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 72 index uk_name of table `test`.`t5` trx id 4951 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 80000002; asc ;;

*** (2) TRANSACTION:
TRANSACTION 4950, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 24, OS thread handle 139990166521600, query id 254 localhost dball update
insert into t5 values(4,9)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 72 index uk_name of table `test`.`t5` trx id 4950 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 80000002; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 72 index uk_name of table `test`.`t5` trx id 4950 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 80000002; asc ;;

*** WE ROLL BACK TRANSACTION (1)