第4节 MySQL 锁的分类及实现原理 2021-12-25

Java组件总目录

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

一、Upate 语句执行整体流程

二、MySQL锁介绍

1.锁的粒度分类

• 全局锁：锁的是整个database。由MySQL的SQL layer层实现的
• 表级锁：锁的是某个table。由MySQL的SQL layer层实现的
• 行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

2.锁的功能分类

共享锁Shared Locks（S锁）:

1、兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁
2、加锁方式：select…lock in share mode

排他锁Exclusive Locks（X锁）:

1、兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁
2、加锁方式：select…for update

---

三、 全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

# 加全局锁
mysql> flush tables with read lock;
# 释放全局锁
mysql> unlock tables；

说到全局锁用于备份这个事情，还是很危险的。因为如果在主库上加全局锁，则整个数据库将不能写
入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。

对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用–single-transaction参数，利用
mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。

---

四、MySQL表级锁

MySQL的表级锁有四种：
1、表读、写锁。
2、元数据锁（meta data lock，MDL)。
3、意向锁 Intention Locks（InnoDB）
4、自增锁(AUTO-INC Locks)

1. 表读、写锁

读锁：

给一个表加锁，让表处于只读的状态，只能对表进行读操作，不能修改数据。可以同时多个回话给同一个资源加锁。

写锁

排他锁，一旦加锁之后只能由加锁的session享有资源的独占权。其他回话不允许对资源进行读写操作和加锁操作。

# MySQL 实现的表级锁定的争用状态变量：
show status like 'table%';
- table_locks_immediate：产生表级锁定的次数； 
- table_locks_waited：出现表级锁定争用而发生等待的次数；

# 手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
# 查看表锁情况
show open tables;
# 删除表锁
unlock tables;

2. 元数据锁

MDL（元数据锁）不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

3. 意向锁

行锁中详细说明。

4. 自增锁

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

当使用mysql的自增主键时，生成主键的过程中加自增锁。

---

五、MySQL行级锁

1 行锁介绍

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

按照锁定范围划分

• 记录锁（Record Locks）:锁定索引中一条记录。
• 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索 引记录后面的值。
• 临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录 锁）。
• 插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

按照功能划分

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

# 共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
# 排他锁
SELECT * FROM table_name WHERE ... FOR UPDATE

2 意向锁 Intention Locks

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

1. 表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
2. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存，。
3. 例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
4. 1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁 2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

2 作用

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；
（1）如果意向锁是行锁，则需要遍历每一行数据去确认；
（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

3 记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

4 间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录
本身。
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

session1: 
begin; 
select * from t1_simple where id > 4 for update; 
--------------------------------------------------------- 
session2: 
insert into t1_simple values (7,100); --阻塞 
insert into t1_simple values (3,100); --成功

5 临键锁(Next-Key Locks)

（1）record lock + gap lock, 左开右闭区间，例如（5,8]。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:

6 插入意向锁

插入数据后，在对应的主键上将未提交的主键信息进行锁定，防止其他回话插入相同主键的记录。这就是插入意向锁。

• 插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

• 假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

7 行锁加锁规则

主键索引

1. 等值查询
   （1）命中记录，加记录锁。
   （2）未命中记录，加间隙锁。
2. 范围查询
   （1）没有命中任何一条记录时，加间隙锁。
   （2）命中1条或者多条，包含where条件的临键区间，加临键锁

辅助索引

1. 等值查询
   （1）命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。
   （2）未命中记录，加间隙锁
2. 范围查询
   （1）没有命中任何一条记录时，加间隙锁。
   （2）命中1条或者多条，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。

8 锁相关参数

nnodb所使用的行级锁定争用状态查看：

show status like 'innodb_row_lock%';
- -Innodb_row_lock_current_waits：当前正在等待锁定的数量； 
- -Innodb_row_lock_time：从系统启动到现在锁定总时间长度； （等待总时长）
- -Innodb_row_lock_time_avg：每次等待所花平均时间； （等待平均时长）
- -Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间； 
- -Innodb_row_lock_waits：系统启动后到现在总共等待的次数；（等待总次数）

# 查看事务、锁的sql
select * from information_schema.innodb_locks; 
select * from information_schema.innodb_lock_waits; 
select * from information_schema.innodb_trx;

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

六、行锁分析实战

示例1

这个SQL加什么锁？

delete from t1 where id = 10;

前提一：id列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：id列如果不是主键，那么id列上有索引吗？
前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

1. id主键+RC：

id是主键时，此SQL只需要在id=10这条记录上加X锁即可。不命中加间隙锁。

2. id非唯一索引+RC

若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

3. id无索引+RC

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因 此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

4. id非唯一索引+RR

首先，通过辅助索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后 加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记 录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

5 id无索引+RR

六 死锁原理与分析

本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：

• 可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
• 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

产生死锁的原因

	两个事物互相持有对方想要获得的资源，就发生了死锁。
mysql发生死锁的后：
	mysql会自动检测死锁，一旦检测到死锁发生，会自动回滚代价比较小的一方，来解除死锁。

1、注意程序的逻辑
根本的原因是程序逻辑的顺序，最常见的是交差更新
Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
Transaction获得两个资源

尽可能减少占用资源的时间，尽快释放锁。
2、保持事务的轻量
越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
3、提高运行的速度
避免使用子查询，尽量使用主键等等
4、尽量快提交事务，减少持有锁的时间
越早提交事务，锁就越早释放