mysql lock 实验

实验准备一

CREATE TABLE `c` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `c` VALUES ('10');
INSERT INTO `c` VALUES ('11');
INSERT INTO `c` VALUES ('13');
INSERT INTO `c` VALUES ('20');

设置事务级别 重复读（repeatable-read）

set global tx_isolation = 'repeatable-read';

查询语句

select * from c where a <= 13 for update

primary key

TABLE LOCK table `test`.`c` trx id 1226827 lock mode IX
RECORD LOCKS space id 492 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226827 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b0110; asc     [  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b011f; asc     [  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b012e; asc     [ .;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b013d; asc     [ =;;

(-∞,10]
(10,11]
(11,13]
(13,20]

unique key

TABLE LOCK table `test`.`c` trx id 1226844 lock mode IX
RECORD LOCKS space id 493 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226844 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b0110; asc     [  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b011f; asc     [  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b012e; asc     [ .;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b013d; asc     [ =;;

(-∞,10]
(10,11]
(11,13]
(13,20]

index key

TABLE LOCK table `test`.`c` trx id 1226873 lock mode IX
RECORD LOCKS space id 494 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226873 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000043b; asc      ;;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000000043c; asc      <;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000000043d; asc      =;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000000043e; asc      >;;

RECORD LOCKS space id 494 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226873 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000043b; asc      ;;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b8000002490110; asc     I  ;;
 3: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000043c; asc      <;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b800000249011f; asc     I  ;;
 3: len 4; hex 8000000b; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000043d; asc      =;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b800000249012e; asc     I .;;
 3: len 4; hex 8000000d; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000043e; asc      >;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b800000249013d; asc     I =;;
 3: len 4; hex 80000014; asc     ;;

(-∞,10]
(10,11]
(11,13]
(13,20]

查询语句

select * from c where a = 13

primary key

TABLE LOCK table `test`.`c` trx id 1226898 lock mode IX
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226898 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b800000249012e; asc     I .;;

[13]

unique key

TABLE LOCK table `test`.`c` trx id 1226915 lock mode IX
RECORD LOCKS space id 496 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226915 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b861; asc      a;;
 2: len 7; hex b800000249012e; asc     I .;;

[13]

index key

TABLE LOCK table `test`.`c` trx id 1226584 lock mode IX
RECORD LOCKS space id 484 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226584 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000000420; asc       ;;

RECORD LOCKS space id 484 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226584 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000420; asc       ;;
 1: len 6; hex 00000012b73c; asc      <;;
 2: len 7; hex d800000159012e; asc     Y .;;
 3: len 4; hex 8000000d; asc     ;;

RECORD LOCKS space id 484 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226584 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000421; asc      !;;

(11,13]
(13,20)

设置事务级别 读提交（READ-COMMITTED）

set global tx_isolation = 'READ-COMMITTED';

查询语句

select * from c where a <= 13 for update

primary key

TABLE LOCK table `test`.`c` trx id 1226611 lock mode IX
RECORD LOCKS space id 485 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226611 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000012b73c; asc      <;;
 2: len 7; hex d8000001590110; asc     Y  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000012b73c; asc      <;;
 2: len 7; hex d800000159011f; asc     Y  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b73c; asc      <;;
 2: len 7; hex d800000159012e; asc     Y .;;

[10,11,13]

unique key

TABLE LOCK table `test`.`c` trx id 1226671 lock mode IX
RECORD LOCKS space id 487 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226671 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000012b787; asc       ;;
 2: len 7; hex a6000001500110; asc     P  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000012b787; asc       ;;
 2: len 7; hex a600000150011f; asc     P  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b787; asc       ;;
 2: len 7; hex a600000150012e; asc     P .;;

[10,11,13]

index key

TABLE LOCK table `test`.`c` trx id 1226710 lock mode IX
RECORD LOCKS space id 488 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226710 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000042a; asc      *;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000000042b; asc      +;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000000042c; asc      ,;;

[10,11,13]

查询语句

select * from c where a = 13 for update

primary key

TABLE LOCK table `test`.`c` trx id 1226740 lock mode IX
RECORD LOCKS space id 489 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226740 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b7ba; asc       ;;
 2: len 7; hex a80000011c012e; asc       .;;

[13]

unique key

TABLE LOCK table `test`.`c` trx id 1226763 lock mode IX
RECORD LOCKS space id 490 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226763 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000012b7ba; asc       ;;
 2: len 7; hex a80000011c012e; asc       .;;

[13]

index key

TABLE LOCK table `test`.`c` trx id 1226797 lock mode IX
RECORD LOCKS space id 491 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226797 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000000433; asc      3;;

RECORD LOCKS space id 491 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226797 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000433; asc      3;;
 1: len 6; hex 00000012b815; asc       ;;
 2: len 7; hex e50000025b012e; asc     [ .;;
 3: len 4; hex 8000000d; asc     ;;

[13]

实验准备二

CREATE TABLE `z` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `z` VALUES ('1', '1');
INSERT INTO `z` VALUES ('3', '1');
INSERT INTO `z` VALUES ('5', '3');
INSERT INTO `z` VALUES ('7', '6');
INSERT INTO `z` VALUES ('10', '8');

查询语句

select * from c where b = 3 for update

锁的过程

TABLE LOCK table `test`.`z` trx id 1228308 lock mode IX
RECORD LOCKS space id 499 page no 4 n bits 80 index b of table `test`.`z` trx id 1228308 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 499 page no 3 n bits 80 index PRIMARY of table `test`.`z` trx id 1228308 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000012b8f6; asc       ;;
 2: len 7; hex 80000002670110; asc     g  ;;
 3: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 499 page no 4 n bits 80 index b of table `test`.`z` trx id 1228308 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000007; asc     ;;

锁的区间
(1,3]
(3,6)

分析过程：

过程1：

index b of table xx.z.. lock_mode X

index b: 锁加在的普通索引 index b上，记录值为(3,5)
lock_mode X: 代表是 next key lock
next key lock = Gap Lock + Record Lock
(3,5) 这条记录加了Record Lock，并且上一条记录 （1,3）- (3,5),中间添加了Gap Lock

过程2：

PRIMARY of table test.z trx id 1228308 lock_mode X locks rec but not gap
PRIMARY：主键索引
lock_mode X locks rec but not gap： Record Lock
（5，3），这条记录，还有记录锁

过程3：

index b of table xxx.zlock_mode X locks gap before rec
index b: 锁加在的普通索引 index b上，记录值为(6,7)
lock_mode X locks gap before rec：(3,5) - (6,7) 中间添加了Gap Lock

插入测试

insert into z values (6,6)

mysql> insert into z values (8,6);
Query OK, 1 row affected (0.00 sec)

(8,6) ,插入成功

insert into z values (6,6)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 499 page no 4 n bits 80 index b of table `test`.`z` trx id 1228309 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000007; asc     ;;

------------------
TABLE LOCK table `test`.`z` trx id 1228309 lock mode IX
RECORD LOCKS space id 499 page no 4 n bits 80 index b of table `test`.`z` trx id 1228309 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000007; asc     ;;

插入(6,6) 锁住了，产生了插入意向锁

分析原因

思考方案一：
以普通索引 b 为维度，(1,3)-(3,6) ，都有Gap锁，插入6，那都应该锁起来，然而没有

思考方案二：
数学区间排列

记录：(以普通索引排列数学区间)
(1,1) (1,3) (3,5) (6,7) (8,10)

select * from c where b = 3 for update

锁住的区间 (3,5) - (6,7)
插入 (6,6) 在区间中，区间本身有Gap Lock，所以产生了insert intention waiting
插入 (6,8)，在(6,7) 这条记录后，不在(3,5) - (6,7) 区间范围内，所以正常插入

引用
https://www.bilibili.com/video/BV1rW41147QT/?spm_id_from=333....