# MySQL

锁基础原理

锁的分类

1. 操作类型区分

可分为读锁和写锁。

读锁（S锁、共享锁）

读读兼容，针对同一份数据,   多个读操作可以同时加上读锁，而不会互相影响。

其他事务可以对该记录追加S锁，但是不能追加X锁，要追加X锁，需要等记录的S锁全部释放。

写锁（X锁、排它锁）

读写/写写均不兼容，当前事务获取X锁后，会阻塞其它事务获取S锁和X锁。

意向锁(表级锁)

  • IS: 意向共享锁，已加S锁的表，肯定会有IS锁。但有IS锁的表，不一定会有S锁。

  • IX:意向排它锁，已加X锁的表，肯定会有IX锁。但有IX锁的表，不一定会有X锁。

操作

锁类型

解释

普通select语句

无锁

基于MVCC机制的快照读。

select…
lock in share mode

S锁

当前读，显示读锁。
对某条索引的记录上锁后，其它事务可以加S锁，但阻塞其它事务加X锁。

select…
for update

X锁

显式写锁。
上锁后，阻塞其它事务对锁定的索引记录上S或X锁

insert/update/delete

X锁

隐式写锁。
上锁后，阻塞其它事务对锁定的索引记录上S或X锁

2. 操作粒度分类

可分为表级锁、行级锁和页级锁。

表级锁

应用在MyISAM、InnoDB、BDB 等存储引擎中

特点：

  • 开销小，加锁快

  • 不会出现死锁

  • 锁定粒度大，并发度最低，锁冲突的概率最高

行级锁

应用在InnoDB 存储引擎中

特点：

  • 开销大，加锁慢

  • 会出现死锁

  • 锁定粒度小，并发度最高，锁冲突的概率最低

  1. InnoDB 引擎支持行锁和表锁，而 MyISAM 引擎只支持表锁。

  2. MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

  3. InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。

  4. 如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

3. InnoDB存储引擎三种行锁

主要有3种：Record Lock(行锁)、Gap Lock(间隙锁) 、Next-key Lock(临键锁)

RC：读已提交

RR：可重复读（默认）

Record Lock锁(RC、RR支持)

  • 记录锁，锁定单个行记录的锁。仅锁住索引记录，而非数据本身。

  • 如果表上没有任何索引，则会隐式创建一个聚集主键索引，上锁时，锁住该索引。

  • 当一条sql没有命中任何索引时，会依次锁住每一个聚集索引。

Gap Lock锁(RC不支持，RR支持)

  • 间隙锁，是一个范围锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。

  • 解决了RR级别下的幻读问题，防止在区间内插入数据。

  • 是一个开区间，在索引记录之间、或某条记录之前、或某条记录之后加锁。

-- 栗子：当前存在索引值：1，3
-- 索引范围：(-∞，1)，（1，3），（3，+∞）
-- -∞：下界限    +∞：上界限
-- 界限是一个伪值，并不是真正的索引记录

-- 在 RR 隔离级别下，以下查询会锁定 (1, 3)
START TRANSACTION;
SELECT * FROM table WHERE id = 2 FOR UPDATE; -- id=2 不存在，锁定间隙 (1, 3)
-- 其他事务尝试插入 id=2 会被阻塞
INSERT INTO table (id) VALUES (2); -- 等待 Gap Lock 释放
COMMIT;

-- 如果是 id = 3 ，则不会使用Gap Lock锁，而是Next-key Lock
-- 锁的行为，需要根据 索引类型 和 记录是否存在、记录存在一条、记录存在多条 
-- 进行综合判断！！！

Next-key Lock 锁(RC不支持，RR支持)

  • 记录锁 和 间隙锁 组合，锁住数据本身 及其 前后范围。

  • 当sql查询条件包含唯一索引时，会被优化成行锁，而不是锁住范围。

  • 是一个左开右闭区间。

栗子：当前存在索引值：1，3

索引范围：

(-∞，1)，（1，3]，（3，+∞）

-- 栗子：当前存在索引值：1,3
-- 索引范围：(-∞，1)，（1，3]，（3，+∞）
-- -∞：下界限    +∞：上界限
-- 界限是一个伪值，并不是真正的索引记录

-- 在 RR 隔离级别下，以下查询会锁定 (1, 3)
START TRANSACTION;
SELECT * FROM table WHERE id = 3 FOR UPDATE; -- id=3 存在，锁定间隙 (1, 3]
-- 其他事务尝试插入 id=2 会被阻塞
INSERT INTO table (id) VALUES (2); -- 等待 Next-key Lock 释放
COMMIT;

4. 快照读、当前读

快照读：简单的select查询，一般不加锁。

当前读：

select... lock in share mode;

select... from insert/update/delete;

插入/更新/删除操作，需要加锁。

快照读原理：

基于MVCC+undo-log实现，读取历史数据，而不是最新的数据。

当前读原理：

直接访问Buffer缓冲池的Data Buffer或物理存储的最新数据。

总是访问其他事务已提交的数据。

测试：可以新建多个sql脚本用于区分不同的事务。

-- 设置 当前session/全局 的下一次事务隔离级别为 --
SET [SESSION | GLOBAL] TRANSACTION ISOLATION 
LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
-- 事务A
-- 设置当前session的下一次事务隔离级别为RR
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
select * from person p where id = 1;-- 多次select name=张三
COMMIT;MMIT;
-- 事务B
-- 设置当前session的下一次事务隔离级别为RR
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION;
select * from person p ;
update person set name = '张三三' where id = 1;
COMMIT;

  1. name=张三

  2. A开始

  3. B开始

  4. A select name=张三

  5. B set name = 张三三

  6. A select 多次 name=张三

  7. B 提交

  8. A select 多次 name=张三

5. 加锁规则分析

脚本准备：

-- 建表
create table person(
    id int primary key,
    name varchar(255),
    age int ,
    user_no int,
    index index_age(age),
    unique index index_no(user_no)
);

-- 插入数据
insert into person values
(1,'张三',10,1),
(5,'李四',20,2),
(10,'王五',20,8),
(20,'赵六',30,10);

-- 查看系统隔离级别
SELECT @@global.transaction_isolation;

-- 查看全局事务隔离级别
-- 5.0+
SELECT @@tx_isolation;
-- 8.0+
SELECT @@transaction_isolation;

-- 修改事务隔离级别 为RR
-- 隔离级别可选值：
-- READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置下一次事务的隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


-- 开启锁的监视器：全局生效，但是mysql重启后失效
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

-- 显示监视器的开启状态，都是ON即可
show variables like '%innodb_status_output%';

聚簇索引

在MySQL的InnoDB存储引擎中,聚簇索引、聚族索引、主键索引是同一个东西

聚簇索引：聚簇索引决定了表中数据的物理存储顺序。也就是说，表中的数据按照聚簇索引的键值顺序存储在磁盘上。

  • 每个InnoDB表有且只有一个聚簇索引。

  • 叶子节点存储的是整行数据（即数据页），因此通过聚簇索引可以直接获取行数据，无需二次查找（回表）。

创建规则：

  1. 如果表定义了主键（PRIMARY KEY），则主键就是聚簇索引。

  2. 如果没有主键，则选择第一个非空唯一索引（UNIQUE NOT NULL）作为聚簇索引。

  3. 如果既没有主键也没有非空唯一索引，InnoDB会生成一个隐藏的列（DB_ROW_ID）作为聚簇索引。

二级索引（也叫非聚簇索引）

定义：除了聚簇索引以外的其他索引都是二级索引（如唯一索引、普通索引）。

特点：

  • 二级索引的叶子节点存储的是该行的主键值（而不是行数据的物理地址）。

  • 当通过二级索引查询数据时，需要先查找到主键值，然后再通过主键值去聚簇索引中查找整行数据（这个过程称为回表查询）。

MySQL的InnoDB存储引擎：每个表最多可以有64个二级索引。

注意：

  • 64个二级索引不包括主键索引（聚簇索引），因为每个表只有一个聚簇索引。

  • 总索引数 = 1个聚簇索引 + 64个二级索引 = 65个索引。

  • InnoDB允许每个复合索引最多包含16个列。

聚簇索引 - 等值匹配
-- 1.开启事务
START TRANSACTION;
-- 2.更新语句
update test.person set name = '张三' where id= 1;
-- 3.查看锁状态
show engine innodb status;
-- 4.暂时不提交事务，方便分析锁
-- commit;

输出结果：

---TRANSACTION 79490, ACTIVE 8 sec

2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 20, OS thread handle 14788, query id 330 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table test.person trx id 79490 lock mode IX
RECORD LOCKS space id 531 page no 4 n bits 80 index PRIMARY 
of table test.person trx id 79490 lock_mode X locks rec but not gap

Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000013680; asc 6 ;;
2: len 7; hex 0200000159101e; asc Y ;;
3: len 6; hex e5bca0e4b889; asc ;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 80000001; asc ;;

事务id：79490

2 lock struct(s)：两个锁结构(一个表锁和一个行锁）

1 row lock(s)：有1个行锁

MySQL thread id 20：MySQL线程ID

OS thread handle 14788：操作系统线程句柄

trx id 79490 lock mode IX：一个表锁-共享X锁

lock_mode X locks rec but not gap：上了一个行级排他锁

Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;   # 字段1: 主键ID=1的记录
1: len 6; hex 000000013680; asc     6 ;; # InnoDB内部事务ID
2: len 7; hex 0200000159101e; asc     Y  ;; # 回滚指针
3: len 6; hex e5bca0e4b889; asc       ;; # 字段2: 姓名="张三"
4: len 4; hex 8000000a; asc     ;;   # 字段3: 年龄=10
5: len 4; hex 80000001; asc     ;;   # 字段4: 其他字段=1

RECORD LOCKS后面的内容

  • locks rec but not gap：记录锁，非间隙锁

  • locks gap before rec：间隙锁

  • 什么都没有：默认的临键锁

总结：聚集索引 等值匹配时，临键锁 会降级成 记录锁，会上一把记录锁。

聚簇索引 - 无值匹配
START TRANSACTION;
update test.person set name = '张三' where id= 15;
show engine innodb status;
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 20, OS thread handle 14788, query id 335 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table `test`.`person` trx id 79491 lock mode IX
RECORD LOCKS space id 531 page no 4 n bits 80 index PRIM
ARY of table `test`.`person` trx id 79491 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013671; asc     6q;;
 2: len 7; hex 820000009a011d; asc        ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

trx id 79491 lock mode IX RECORD LOCKS:持有意向排他锁（IX),会阻塞其他事务的读和写，但会阻塞其他事务加表级排他锁（X）

lock_mode X locks gap before:一个间隙锁（gap lock），锁定在记录之前的间隙。

锁定区间：（1，5），不包括5。

0: len 4; hex 80000005; asc ;;   -> 主键值为5的记录
1: len 6; hex 000000013671; asc 6q;;  -> 事务ID（十六进制13671，即十进制79489）
2: len 7; hex 820000009a011d; asc;;  -> 回滚指针
3: len 6; hex e69d8ee59b9b; asc;;  -> 这里是UTF-8编码的字符串，解码为“李四”
4: len 4; hex 80000014; asc ;;   -> 整数20（十六进制14）
5: len 4; hex 80000002; asc;;   -> 整数2

如果更新的id=100，则会显示上界。此时锁定区间:(20，+∞)

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

在锁定区间内，开启另一个事务执行插入语句：

insert into test.person values(31,'测试',10,5); 则会发现一直阻塞状态，直至锁释放。

insert into test.person values(19,'测试',10,5); 则会直接插入成功，其他插入成功的数据在提交之后在本事务中可见。

如果是空表，更新一条id不存在的语句，则在事务提交之前，其他事务不能插入数据。

总结：聚集索引 等值查询 无值匹配时，会上一把 间隙锁。

唯一索引 - 等值匹配
select * from test.person p ;
START TRANSACTION;
update test.person set name = '张三' where user_no = 2;
---TRANSACTION 79584, ACTIVE 6 sec
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 29, OS thread handle 14788, query id 487 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table `test`.`person` trx id 79584 lock mode IX RECORD LOCKS space id 532 page no 5 n bits 72 index index_no of table `test`.`person` trx id 79584 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 80000002; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 72 index PRIMARY of table `test`.`person` trx id 79584 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000136e0; asc     6 ;;
 2: len 7; hex 01000001350967; asc     5 g;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;00001; asc     ;;

3 lock struct(s）:3个锁结构，一个表锁，两个行锁

undo log entries 1:事务已执行 1 次数据修改（UPDATE/DELETE）

index index_no of table:二级索引 index_no

第一个行锁：二级索引（唯一索引上）

二级索引由两部分组成：n_fields=2

0: len 4; hex 80000002; asc ;;：聚集索引 id = 2

1: len 4; hex 80000005; asc;;:唯一索引user_no = 5

第二个行锁：聚集索引

index PRIMARY of table：索引类型：主键聚集索引

lock_mode X locks rec but not gap:行锁，只锁记录，不锁间隙,发生了降级

索引结构：

二级索引 (index_no)            主键索引 (PRIMARY)
┌───────────┬──────────┐       ┌──────────┬───────────────┐
│ 索引值=2  │ 主键ID=5 │ ---→  │ 主键ID=5 │ 完整行数据... │
└───────────┴──────────┘       └──────────┴───────────────┘

总结：唯一索引 等值查询 等值匹配，1-锁一个唯一索引记录，2-锁一个聚集索引记录。

因为唯一索引具有唯一性，降级为记录锁。

唯一索引 - 无值匹配
select * from test.person p ;
START TRANSACTION;
update test.person set name = '张三' where user_no = 8;
---TRANSACTION 79609, ACTIVE 1 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 29, OS thread handle 14788, query id 550 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table `test`.`person` trx id 79609 lock mode IX
RECORD LOCKS space id 532 page no 5 n bits 80 index index_no of table `test`.`person` trx id 79609 lock_mode X locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000014; asc     ;;

间隙锁区间：(6,10),区间内不允许其他事务插入值。

总结：唯一索引 等值查询 无值匹配时，会上一把间隙锁。

普通索引 - 等值匹配 - 一个值
START TRANSACTION;
update test.person set name = '张三' where age = 10;
show engine innodb status;
4 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 29, OS thread handle 14788, query id 582 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table `test`.`person` trx id 79626 lock mode IX
RECORD LOCKS space id 532 page no 6 n bits 72 index index_age of table `test`.`person` trx id 79626 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 4; hex 80000001; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79626 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 532 page no 6 n bits 72 index index_age of table `test`.`person` trx id 79626 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000005; asc     ;;

一共三把行锁：

index index_age:普通索引(age = 10)上 加了 一把临键锁 - （-∞，10]

index PRIMARY：聚集索引(id = 1) 加了 一把记录锁 - 单条记录

index index_age：普通索引(age=20) 加了 一把 间隙锁 - （10,20）

-- 验证
-- 在age -> (-∞，10]无法插入
insert into test.person values(9,'测试',19,100);
-- 在age -> (10，20) 无法插入
insert into test.person values(9,'测试',19,100);
-- id = 1 无法更新
update test.person set name = '张三三' where id = 1;
普通索引 - 等值匹配 - 多个值
START TRANSACTION;
update test.person set name = '张三' where age = 20;
show engine innodb status;
4 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 1
MySQL thread id 29, OS thread handle 14788, query id 616 localhost 127.0.0.1 root starting
/* ApplicationName=DBeaver 24.2.5 - SQLEditor <test-事务1.sql> */ show engine innodb status
TABLE LOCK table `test`.`person` trx id 79640 lock mode IX
RECORD LOCKS space id 532 page no 6 n bits 72 index index_age of table `test`.`person` trx id 79640 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000005; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79640 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000136e0; asc     6 ;;
 2: len 7; hex 01000001350967; asc     5 g;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 532 page no 6 n bits 72 index index_age of table `test`.`person` trx id 79640 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 8000001e; asc     ;;
 1: len 4; hex 80000014; asc     ;;

一共5把行锁：

index index_age:age=20,id=5的普通索引记录 加了临键锁（10，20]

index index_age:age=20,id=10的普通索引记录 加了临键锁（10，20]

index PRIMARY：id = 5 的聚集索引上了 记录锁 单条记录

index PRIMARY：id = 10 的聚集索引上了 记录锁 单条记录

index index_age:age=20的普通索引记录 加了间隙锁（20，30)

总结：

上锁范围：

  • 所有匹配的记录的聚集索引 加上 记录锁

  • 普通索引的临键锁

  • 普通索引向右扫描的第一个间隙锁

普通索引 - 等值匹配 - 无值
START TRANSACTION;
update test.person set name = '张三' where age = 15;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79694 lock mode IX
RECORD LOCKS space id 532 page no 6 n bits 80 index index_age of table `test`.`person` trx id 79694 lock_mode X locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000005; asc     ;;

age=15上了一把间隙锁

范围:(10,20)

-- 插入成功
insert into test.person values(0,'测试',10,101);
-- 插入失败
insert into test.person values(6,'测试',10,101);
-- 原因：持有age=10的记录的最小id= 1，
-- 当插入的id>1时，会认为当前的id应在1后面，导致无法拆入，但是<1的记录可以
-- 上述几种情况同样适用！！！！！！！！！！！！！！！

锁定范围：

现有索引：10 (id=1) 
锁定区间开始 → 20 (id=2)
^
└─ 新插入的 age=10 (id>1) 落在此区间

总结： 普通索引 在等值查询 无值匹配时，会上一把间隙锁。

6. 聚簇索引加锁总结

有值匹配：

  1. 聚簇索引：唯一匹配的索引记录上加了 记录锁。

  2. 唯一索引：唯一索引的索引记录 和 相应的聚簇索引的索引记录上也加上了 记录锁。

  3. 普通索引：索引匹配的 普通索引的 索引记录加了临键锁，对应的聚簇索引加了 记录锁，对匹配的索引的 索引记录加了间隙锁。

无值匹配：

  1. 聚簇索引、唯一索引、普通索引：只上一把间隙锁，锁住的是条件匹配的间隙。

范围查询加锁规则分析
聚簇索引-小于一个不存在的值
START TRANSACTION;
update test.person set name = '张三' where id < 6;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79742 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79742 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79742 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

id = 1,5的记录上了临键锁，

id=10 上了间隙锁

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • 向右扫描聚集索引，第一个不匹配的索引记录加上 间隙锁

-- 执行
START TRANSACTION;
-- 会被阻塞
update test.person set name = '张三三' where id = 1;
-- 会被阻塞
insert into test.person values(3,'测试',10,100);
-- 不会被阻塞
update test.person set name = '张三三' where id = 3;
原因：插入操作需要获取插入意向锁，这是一种特殊的间隙锁，
插入意向锁与现有间隙锁冲突，需等待
而更新操作的间隙锁是共享的，不会被阻塞。
聚簇索引-小于一个存在的值
START TRANSACTION;
update test.person set name = '张三' where id < 5;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79747 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79747 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79747 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;002; asc     ;;

id = 1 上了临键锁

id = 5 上了间隙锁

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • 向右扫描聚集索引，第一个不匹配的索引记录加上 间隙锁 。5就是第一个不匹配的值！！！5<5 = false

聚簇索引-小于等于一个存在的值
START TRANSACTION;
update test.person set name = '张三' where id <=5;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79750 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79750 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

id = 1,5 上了 临键锁，next-key锁是左开右闭区间

-- 阻塞
insert into test.person values(5,'测试',10,100);
-- 成功
insert into test.person values(6,'测试',10,100);

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

聚簇索引-小于等于一个不存在的值
START TRANSACTION;
update test.person set name = '张三' where id <=6;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79769 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79769 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79769 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

id = 1,5 临键锁

id = 10 间隙锁

锁住范围：(-∞,1],(1,5],(6,10)

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • 向右扫描聚集索引，第一个不匹配的索引记录加上 间隙锁 。

聚簇索引-大于一个存在的值
START TRANSACTION;
update test.person set name = '张三' where id >5;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79773 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79773 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;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000001379d; asc     7 ;;
 2: len 7; hex 01000000f12eb3; asc      . ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

id = 20,10 临键锁

锁住范围:

(5,10]:由id=10的临键锁确定

(10,20]:由id=20的临键锁确定

(20,-∞):由 supremum 伪记录锁定

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • supremum伪记录锁定

聚簇索引-大于一个不存在的值
START TRANSACTION;
update test.person set name = '张三' where id >6;
show engine innodb statu
TABLE LOCK table `test`.`person` trx id 79789 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79789 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;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 0000000137ad; asc     7 ;;
 2: len 7; hex 010000014e1fce; asc     N  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

锁住范围：(6,+∞)

聚簇索引-大于等于一个存在的值
START TRANSACTION;
update test.person set name = '张三' where id >=5;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79843 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79843 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79843 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;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 0000000137e3; asc     7 ;;
 2: len 7; hex 020000018d302f; asc      0/;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;8000000a; asc     ;;

id = 5 记录锁

id = 10 ,20，supernum 临键锁 （5，-无穷）

总结：

  • 匹配的值上记录锁

  • 聚集索引 匹配的 索引记录上了 临键锁

  • supremum伪记录锁定

聚簇索引-大于等于一个不存在的值
START TRANSACTION;
update test.person set name = '张三' where id >=4;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79845 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79845 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;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 0000000137e5; asc     7 ;;
 2: len 7; hex 01000001261584; asc     &  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

supremum:临键锁

id = 5,10,20临键锁

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • supremum伪记录锁定

聚簇索引-范围组合
START TRANSACTION;
update test.person set name = '张三' where id > 4 and id < 30;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79847 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79847 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;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000013746; asc     7F;;
 2: len 7; hex 01000001361170; asc     6 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 0000000137e7; asc     7 ;;
 2: len 7; hex 02000001641815; asc     d  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000013718; asc     7 ;;
 2: len 7; hex 02000001b11b46; asc       F;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

supremum:临键锁

id = 5,10,20临键锁

总结：

  • 聚集索引 匹配的 索引记录上了 临键锁

  • supremum伪记录锁定

总结：

对于聚簇索引，无论是否组合，

  • 存在=时，有值匹配时上记录锁，无值上临键锁

  • < 和 <=：向右扫描聚集索引，直到找到 不匹配 的 索引记录 上临键锁

  • > 和 >=，会对supremum (上界限伪值) 上临键锁：锁的是 聚集索引 最大值 后面的 间隙;

非聚簇索引
START TRANSACTION;
update test.person set name = '张三' where age > 10;
show engine innodb status;
TABLE LOCK table `test`.`person` trx id 79858 lock mode IX
RECORD LOCKS space id 532 page no 6 n bits 80 index index_age of table `test`.`person` trx id 79858 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;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

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

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79858 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000137f2; asc     7 ;;
 2: len 7; hex 02000001330270; asc     3 p;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 0000000137f2; asc     7 ;;
 2: len 7; hex 020000013302b8; asc     3  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000137f2; asc     7 ;;
 2: len 7; hex 02000001330294; asc     3  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

  1. age = 10 ,20，30，supernum 上临键锁

  2. id = 5，10，20 上记录锁

无索引
START TRANSACTION;
update test.person set name = '张三' where name  != '张三';
show engine innodb status;
rollback;
TABLE LOCK table `test`.`person` trx id 79884 lock mode IX
RECORD LOCKS space id 532 page no 4 n bits 80 index PRIMARY of table `test`.`person` trx id 79884 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000136ce; asc     6 ;;
 2: len 7; hex 81000000bc0110; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000001380c; asc     8 ;;
 2: len 7; hex 010000016a10ea; asc     j  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000001380c; asc     8 ;;
 2: len 7; hex 010000016a1132; asc     j 2;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 4; hex 8000000a; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000001380c; asc     8 ;;
 2: len 7; hex 010000016a110e; asc     j  ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 4; hex 80000006; asc     ;;

所有 聚集索引 上记录锁！

总结：如果使用了普通索引，

  1. 所有匹配的索引记录上临键锁，对应的聚集索引上记录锁

  2. 对于 < 和 <=，会在该索引上向右扫描， 直到找到 不匹配的索引记录 上临键锁

  3. 对于 > 和 >=，会对supremum (上界限伪值) 上 临键锁

  4. 如果欸有走普通索引，则会把所有 聚集索引记录 和 间隙 都锁上，就是所谓的锁表，或叫行锁升表锁

7. 行锁升级表锁

存在两种情况：没有使用索引、索引失效

略......