mysql lock 实验

逻辑织雪使
• 阅读 685

实验准备一

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....

mysql
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
5214
0
0
Stella981 Stella981
•
3年前
List的Select 和Select().tolist()
List<PersondelpnewList<Person{newPerson{Id1,Name"小明1",Age11,Sign0},newPerson{Id2,Name"小明2",Age12,
1411
0
0
Stella981 Stella981
•
3年前
Python3：sqlalchemy对mysql数据库操作，非sql语句
Python3：sqlalchemy对mysql数据库操作，非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
1510
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1752
0
0
Wesley13 Wesley13
•
3年前
PHP创建多级树型结构
<!lang:php<?php$areaarray(array('id'1,'pid'0,'name''中国'),array('id'5,'pid'0,'name''美国'),array('id'2,'pid'1,'name''吉林'),array('id'4,'pid'2,'n
1623
0
0
Wesley13 Wesley13
•
3年前
Java日期时间API系列36
　　十二时辰，古代劳动人民把一昼夜划分成十二个时段，每一个时段叫一个时辰。二十四小时和十二时辰对照表：时辰时间24时制子时深夜11：00凌晨01：0023：0001：00丑时上午01：00上午03：0001：0003：00寅时上午03：00上午0
1790
0
0
Stella981 Stella981
•
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
1323
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
2030
0
0
京东云开发者 京东云开发者
•
2年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1361
0
0
Python进阶者 Python进阶者
•
1年前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
898
0
0
美凌格栋栋酱 美凌格栋栋酱
•
5个月前
Oracle 分组与拼接字符串同时使用
SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(
1937
0
0
逻辑织雪使
逻辑织雪使
Lv1
就算步伐很小，也要步步前进。
文章
7
粉丝
0
获赞
0
热门文章
【python学习】执行shell命令
热度3年猛增20倍，Serverless&云开发的技术架构全解析
每日一题之数据结构-数组篇
MDC-traceId在分布式环境中使用步骤
JS模块化 -- CMD