MySQL 各种锁

MySQL知识总结

在 Github 上查看

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

22 锁

事务一致性问题

出现并发事务对同一数据进行读写、写写时会出现一致性问题，对于写写情况，多个未提交的事务对于同一记录进行改动时，需要串行执行，实现的方式是对记录进行加锁。

写写情况

事务对记录进行修改时，查看内存中是否有锁结构与该记录关联，锁的基本信息有trx信息（与锁关联的事务）、is_waitting（标志是否等待），持有锁的事务提交后，会唤醒等待中事务。

读写情况

不同隔离级别下可以解决不同的事务并发问题，MySQL的可重复读隔离级别下，很大程度解决了幻读，MySQL解决脏读、不可重复读、幻读的方案有：

1. MVCC：通过生成Read View，控制了事务对记录版本的可见性，使得事务只能读取到Read View生成之前已提交的事务做的修改，MVCC中，读取记录的历史版本与改动记录的最新版本并不冲突，读写不冲突。
2. 加锁：有些业务场景下不允许读取记录的历史版本，而要读取记录的最新版本，对于这种需求可以使用加锁完成，这将使得读写操作需要串行执行。

提示

使用加锁解决幻读的方式会有点复杂，因为需要找到新插入的记录。

一致性读

事务利用MVCC的读取操作称为一致性读、快照读，读已提交、可重复读的 隔离级别下，普通select语句都是这种方式。这种方式不会对任何记录加锁。

锁定读

MySQL中的锁有

• 共享锁（Shared Lock，S锁）：读取记录时，需要获取记录的S锁，S锁与X锁不兼容。
• 排它锁（Exclusive Lock，X锁）：修改记录时，需要获取记录的X锁，X锁与S锁、X锁不兼容。

锁定读的语句

对读取的记录加S锁

select *
from single_table lock in share mode;

对读取的记录加X锁

select *
from single_table for update;

写操作

写操作包括insert、update、delete，执行这些操作会加锁

• update：对记录进行update操作有3种情况
  • 没修改键值且更新的列占用的存储空间不变，需要在B+树定位到记录的位置，再获取记录的X锁，然后在记录的原位置进行修改
  • 没修改键值但至少1个更新的列占用的空间有变化，需要定位到该记录，获取记录的X锁，将记录删除，最后再插入一条记录，被删除的记录上的锁会被转移到新插入的记录上
  • 更新了键值，就需要对原记录进行delete、执行insert操作插入更新后的记录
• delete：在B+树定位到记录的位置，再获取记录的X锁，执行delete mark操作
• insert：一般情况下，新插入一条记录受隐式锁保护，不在内存中创建锁结构

在B+树定位到被修改记录的位置，再获取记录的X锁看做成是获取X锁的锁定读操作。

一般情况下，事务提交或终止时，事务加的锁才会释放。

多粒度锁

一个事务可以对表加锁，表锁的粒度比行锁小，性能消耗低，表锁有

• S锁，别的事务可以获取表中记录S锁、表S锁
• X锁，与S锁、X锁不兼容

表的X锁与其他锁不兼容，在加表X锁时，需要判断表是否有记录S、X锁，为了提高效率，InnoDB设计了意向锁：

• 意向共享锁（Intention Shared Lock，IS锁）：事务准备加记录S锁时，向表加一个IS锁
• 意向排它锁（Intention Exclusive Lock，IX锁）：事务准备加记录X锁时，向表加一个IX锁
• IS锁与IX锁兼容
• 表X锁与任何锁不兼容

MySQL的行锁与表锁

InnoDB表锁

表S、X锁

对表进行select、insert、update、delete操作时，不会加表锁。

alter table、drop table的DDL语句和增删改查操作，这2类语句操作不能同时执行，其中的一类会被阻塞，但这是通过server层使用元数据锁实现的，并不是使用InnoDB的表锁实现的。

InnoDB的表锁只有在特殊情况下会使用，在系统变量autocommit = 0、innodb_table_locks = 1情况下，可以执行一下语句：

• lock tables single_table read;：表S锁
• lock tables single_table write;：表X锁
• 使用InnoDB的表锁会降低并发能力

表IS、IX锁

即前文介绍的锁

表级Auto-inc锁

系统给auto_increment修饰的列进行递增赋值的方式有2个：

• Auto-inc锁：插入语句执行时要加锁，然后为插入语句分配递增值，没有持有Auto-inc锁的事务执行插入操作会被阻塞，插入语句完成后就释放锁（插入的记录数量不能确定，一般使用这种方式）
• 轻量级锁：插入语句生成auto_increment修改列的分配的值时加锁，生成需要的值后就将锁释放（插入记录的值可以确定，一般采用这种方式）

innodb_autoinc_lock_mode 系统变量的值

• 0：一律使用Auto-inc锁
• 1：2中方式混用
• 2：一律使用轻量级锁（可能造成不同事务的自增值是交叉的，主从复制场景不安全）

InnoDB行锁

行锁有Record Lock、Gap Lock、Next-Key Lock、Insert Intention Lock

Record Lock (lock_rec_not_gap)

对一条记录加的锁

Gap Lock (lock_gap)

MySQL在可重复读隔离级别下，很大程度解决了幻读，解决的方式除了MVCC外，还有加间隙锁，给记录加间隙锁，不允许事务在范围(上一条记录, 被加锁记录)插入记录，只有持有了间隙锁的事务提交后，将锁释放，其它事务才可以在该范围内插入记录。

间隙锁有S、X之分，但它们的作用是一样的，防止幻影记录的插入。

如何保证范围(最后一条记录, +∞)不插入幻影记录？在数据页中，有2条伪记录，对应上界（页面最小的记录）、下界（页面最大的记录），只要对下界加上间隙锁，就可以保证该范围不出现幻读。

Next-Key Lock (lock_ordinary)

临键锁本质是记录锁与间隙锁的组合，能保护记录且阻止新记录插入到记录前。

Insert Intention Lock (lock_insert_Intention)

事务在插入一条记录时，需要判断插入位置是否有间隙锁，如果有，就需要在内存生成一个插入意向锁并等待，持有意向锁的事务不会互相阻塞，意向锁不会阻止其他事务继续在该记录上加任何类型的锁。

隐式锁

在内存生成锁有一定的成本，InnoDB有隐式锁，一般情况下，插入操作不会在内存中生成锁，但这会出现一些问题需要解决：

• 有事务立即执行锁定读，如果允许这种情况，就出现脏读
• 有事务立即执行对这条记录继续更新操作，如果允许这种情况，就出现脏写

解决上面的问题需要依靠事务Id：

• 聚簇索引记录：记录上有trx_id记录着最后改动此记录的事务Id，如果要对记录加锁，首先会判断事务Id对应的事务是否是活跃状态，如果不是则正常读取，如果是则帮助该事务对记录加X锁，并给自己加对应的锁，设置为等待状态。
• 二级索引记录：索引记录上没有trx_id，但二级索引页面Page Header有属性page_max_trx_id记录了最近对页面更改的最大事务Id，如果当前事务Id大于该值，说明对该页面修改的事务已经提交，否则需要回表在聚簇索引执行上面的判断

隐式锁起到了延迟创建锁的作用，避免部分消耗，它起到的作用是一样的

InnoDB锁内存结构

InnoDB在对多条记录加锁时，记录的锁可以放到一个锁结果，但要符合以下条件：

1. 在同一个事务进行的加锁
2. 被加锁的记录在同一个页面
3. 加锁类型一样
4. 等待状态一样

锁的结构

1. 锁的事务信息：指针，可以得到事务的相关信息

2. 索引信息：对行锁来说，要记录记录所属的索引

3. 表锁/行锁信息

   • 表锁：对哪个表加锁

   • 行锁

     • Space Id：记录所在的表空间
     • Page Number：记录所在页面的页号
     • n_bits：比特位的个数，对应下方的一堆比特位

4. type_mode：32位的比特位，分为3部分

   • lock_mode：低4位

     • lock_IS
     • lock_IX
     • lock_S
     • lock_X
     • lock_auto_inc

   • lock_type：5~8位

     • lock_table
     • lock_rec

   • rec_lock_type：其余高位

     • lock_rec_not_gap
     • lock_gap
     • lock_ordinary
     • lock_insert_intention
     • lock_wait：表示锁中is_waitting，对应的比特位为1，则为等待状态
     • 其他类型

5. 其他信息：管理系统运行过程中的各种锁结构，而设计了各种哈希表、链表

6. 一堆比特位：对行锁来说，比特位对应着页面中的记录，比特位映射记录头信息中heap_no`，为了编码方便，映射的方式比较特殊

提示

假如事务T1对记录R加锁后，事务T2对记录R加锁，但状态会被设置为等待状态，并不再继续对其他记录加锁（T2要对多条记录加锁，但一开始对记录R加锁就等待），当T1释放记录R的锁后，T2可以获得记录R的锁，并继续对其他记录加锁，并且能复用T1对记录R加锁的锁结构。

语句加锁过程

• 普通select语句
• 锁定读语句
• 半一致性语句
• insert语句

表结构

create table hero (
    id      int          not null primary key,
    name    varchar(100) null,
    country varchar(100) null
);

create index idx_name
    on hero (name);

insert into hero
values (1, 'l刘备', '蜀'),
       (3, 'z诸葛亮', '蜀'),
       (8, 'c曹操', '魏'),
       (15, 'x荀彧', '魏'),
       (20, 's孙权', '吴');

普通select语句

在不同的隔离级别下，普通select语句具有不同的表现，说MySQL的可重复读隔离级别级别下很大程度避免了幻读，出现下面这种情况会出现幻读。

begin;
select * from hero where id =30;

# 事务T2执行并提交 insert into hero values(30,'x','x');

update hero set name='xx' where id=30;

# 幻读 可以查询到id为30的记录
select * from hero where id =30;

序列化隔离级别

系统变量autocommit = 0时，普通的select语句会被转换为S锁的锁定读，如果该变量为1，普通的select语句不会加锁，因为自动提交默认一个事务只有一个语句，只执行一条语句不会出现不可重复读、幻读。

锁定读

锁定读包括：

• select ... lock in share mode
• select ... for update
• update
• delete

为记录加锁会受其他因素影响

1. 事务的隔离级别
2. 语句执行时使用的索引的类型
3. 语句类型
4. 匹配模式：扫描区间是单点扫描区间，那么匹配模式就是精确匹配
5. 是否是唯一性搜索：在扫描某个区间的记录前，就确定了扫描区间只有一条记录称为唯一性搜索，查询需要符合一下条件：
   • 匹配模式是精确匹配
   • 使用的索引是主键索引、唯一索引（查询条件不可以是索引列 is Null的形式，索引包含多个列，需要利用所有列）

一般语句加锁过程

事务执行过程中加的锁一般需要在事务提交、终止时才会释放锁，但在隔离级别在读已提交及以下，会提前将不符合查询条件记录的锁释放。

把锁定读的执行看作是依次读取若干个扫描区间记录，一般情况下，读取扫描区间记录的过程是：

1. 在B+树子节点定位扫描区间的第一条记录，作为当前记录

2. 对记录加锁：一般情况下，可重复读级别及以上加临键锁，读已提交及以下加记录锁

3. 判断索引条件下推的条件是否成立：条件索引下推只适用于select语句

   • 这个过程不会释放锁
   • 这个过程会判断是否符合扫描区间，不符合扫描区间则向server层返回查询完成信息
   • 符合索引条件下推条件：执行4
   • 不符合索引条件下推条件：执行7

4. 回表操作：使用的是二级索引则回表，对聚簇索引记录加记录锁

5. 判断边界条件是否成立

   • 不符合边界条件：向server层返回查询完成的信息（读已提交及以下会释放锁）

6. server层判断其他条件是否成立

   • 其他条件成立：将记录发送给客户端
   • 其他条件不成立：读已提交及以下会释放锁

7. 获取下一条记录，重新执行2

提示

update、delete语句，对被更新、被删除的二级索引记录加的锁是隐式锁，读已提交隔离级别及以下采用的是半一致性读。

查询的匹配模式是精确匹配

• 隔离级别读已提交及以下：不会为扫描区间后的下一条记录加锁
• 隔离级别可重复读及以上：为下一条记录加间隙锁，扫描区间没有记录也加间隙锁（不是精确匹配，则加临键锁）

select *
from hero
where name = 'c曹操' for update;

name	id
c曹操 临键锁（可重复读）	8
l刘备 间隙锁（可重复读）	1
s孙权	20
x荀彧	15
z诸葛亮	3

扫描区间没有记录加间隙锁（l刘备记录前）

select *
from hero
where name = 'g关羽' for update;

name	id
c曹操	8
l刘备 间隙锁（可重复读）	1
s孙权	20
x荀彧	15
z诸葛亮	3

不是精确匹配，则加临键锁

select *
from hero
where name 'd'
  and name < 'l' for update; 

name	id
c曹操	8
l刘备 临键锁（可重复读）	1
s孙权	20
x荀彧	15
z诸葛亮	3

查询使用聚簇索引，扫描区间是左闭右开则定位的扫描区间的第一条记录将使用记录锁

select *
from hero
where id = 8 for update;

id	name
1	l刘备
3	z诸葛亮
8 记录锁（可重复读）	c曹操
15 临键锁（可重复读）	x荀彧
20 临键锁（可重复读）	s孙权
下界 临键锁（可重复读）

查询是唯一性搜索，只要记录没有被delete mark，则加记录锁

select *
from hero
where id = 8 for update;

从右到左扫描且隔离级别可重复读及以上，则匹配的第一条记录的下一条记录加间隙锁

select *
from hero force index (idx_name)
where name 'c曹操'
  and name <= 'x荀彧'
  and country != '吴'
order by name desc for update;

name	id
c曹操	8
l刘备 临键锁（可重复读）	1
s孙权 临键锁（可重复读）	20
x荀彧 临键锁（可重复读）	15
z诸葛亮 间隙锁（可重复读）	3

半一致性读

隔离级别读已提交及以下执行update语句时将使用半一致性读，当update语句读取已经被其他事务加锁的记录时，InnoDB会将该记录最新版本读出来，判断版本是否符合update的查询条件，如果符合，则对其加锁（防止update语句被别的事务阻塞），如果不符合则跳过

Insert语句

insert语句一般情况下是依靠隐式锁保护插入的记录的，事务在插入一条记录前，需要定位到B+树的位置，如果下一条记录加了间隙锁，事务就会为该记录加上插入意向锁，事务进入等待状态。

特殊情况

重复键

插入一条新记录时，需要确定新记录在B+树中的位置，在确定位置时发现记录中的主键、唯一索引列与待插入的记录的主键、唯一索引列相同时，就会报错。在报错前，会为该记录将锁（记录锁或临键锁）。

外键检查

设置外键后，对子表进行插入时需要进行外键检查

• 在主表上对应列上找到值：往子表插入记录前，会对主表的对应记录加S记录锁
• 在主表上对应列上找不到值：插入失败，需要对记录加间隙锁（可重复读）

查看事务加锁情况

INNODB_LOCKS、INNODB_LOCK_WAITS表在MySQL 8被移除

select *
from information_schema.INNODB_TRX;

select *
from information_schema.INNODB_LOCKS;

select *
from information_schema.INNODB_LOCK_WAITS;

show engine innodb status;

# 设置系统变量，可以查看更多内容
set global innodb_status_output_locks = on;

# 事务部分
------------
TRANSACTIONS
------------
Trx id counter 63843
Purge done for trx's n:o < 63783 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284106697672496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 63842, ACTIVE 428 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 16316, query id 2700 localhost 127.0.0.1 root


#事务部分，更多内容
------------
TRANSACTIONS
------------
Trx id counter 63845
Purge done for trx's n:o < 63783 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284106697672496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 63843, ACTIVE 201 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 16316, query id 2793 localhost 127.0.0.1 root

# 给hero表加上了意向独占锁
TABLE LOCK table `mysql_run`.`hero` trx id 63843 lock mode IX

# 锁的结构 Space Id是384，页号3，n_bits是80，索引为主键索引
RECORD LOCKS space id 384 page no 3 n bits 80 index PRIMARY of table 

# 锁是记录锁
`mysql_run`.`hero` trx id 63843 lock_mode X locks rec but not gap


Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000f66d; asc      m;;
 2: len 7; hex eb000001c40110; asc        ;;
 3: len 7; hex 6ce58898e5a487; asc l      ;;#6ce58898e5a487 是 ‘l刘备’的utf8编码
 4: len 3; hex e89c80; asc    ;;