MySQL学习笔记 —— 锁篇

MySQL中有哪些锁

一、全局锁

执行全局锁后，整个数据库就处于只读状态了。通常用于全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

二、表级锁

表级锁分为以下四种

1. 表锁

• 共享表锁：所有线程只能读表，写操作被阻塞
• 独占表锁：只有当前线程可以读写，其他线程则被阻塞

2. 元数据锁（MDL）

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁，此时修改表结构的请求会阻塞；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁，此时CRUD请求会阻塞；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

3. 意向锁

• 在使用 InnoDB 引擎的表里对某些行记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里对某些行纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

4. AUTO-INC 锁

对于自增主键的数据在插入记录时可保证主键自动有序自增就是通过AUTO-INC 锁实现的，在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉，这样就保证了主键的连续自增。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

三、行级锁

InnoDB 引擎是支持行级锁的，这也是InnoDB引擎优于MyISAM的地方

1. Record Lock （记录锁）

Record Lock 称为记录锁，锁住对应存在的记录。而且记录锁是有 S（共享） 锁和 X（独占） 锁之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

2. Gap Lock （间隙锁）

Gap Lock 称为间隙锁，可使指定范围数据值无法插入。只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁的意义只在于阻止区间被插入，因此是可以共存的，就是说多个事务可以对同一个区间加间隙锁

3. Next-Key Lock（临键锁）

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定范围内记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

4. 插入意向锁

一个事务在insert插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

插入意向锁只表示一个动作，不会真正对数据加锁

四、MySQL是如何加行级锁的

1. select、update和delete语句是如何加行级锁的

select的share模式可对读取的记录加共享行级锁

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

select … for update;、update和delete会对操作的记录加独占行级锁

//对读取的记录加独占锁(X型锁)
select ... for update;
//对操作的记录加独占锁(X型锁)
update table .... where id = 1;
//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

2. 不同隔离级别下加的行级锁类型

在读已提交隔离级别下，就是加记录锁

在可重复读的隔离级别下，加的是临键锁，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， 临键锁就会退化成记录锁或间隙锁。

3. Insert 语句是怎么加行级锁的？

Insert加锁比较特殊

1. 在插入记录前，会向插入记录所在位置申请意向插入意向锁，若则所在位置存在间隙锁需阻塞等待间隙锁释放
2. INSERT操作对新插入的记录默认是隐式锁，当遇到冲突（主键、唯一键冲突）时才会被升级为显式X临键锁
   ————————————————
   参考：
   MySQL 并发insert 唯一键冲突导致的死锁
   https://blog.csdn.net/minghao0508/article/details/129093202

问题1：update的where条件字段没有索引会导致锁全表

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

因此在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，导致性能问题。

问题2：死锁

1. 死锁案例1

如下order_no为二级索引，且不存在1006值之后的记录

• 由于1006值后都不存在，故事务A对（1006， +∞）加临键锁（如果1007是两个数据中间不存在的值则退化为间隙锁）
• 同样事务B对（1006， +∞）加临键锁
• 事务A阻塞等待事务B释放临键锁然后获取插入意向锁
• 事务B也阻塞等待事务A释放临键锁，循环等待形成死锁

对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

2.死锁案例2

对于如上数据表中执行了事务A和事务B

• Time1: 事务A加间隙锁，锁范围是主键值(20, 30)
• Time2: 事务B加间隙锁，锁范围是主键值(20, 30)
• Time3: 事务 A 向事务 B 生成的范围为 (20, 30) 的间隙锁插入一条记录，而插入意向锁和间隙锁是冲突的，所以事务A在获取插入意向锁时就陷入了等待状态，等待事务B释放间隙锁。
• Time4: 事务B也向 (20, 30) 插入，同样要等待事务A释放间隙锁，因此陷入循环等待陷入死锁。

3. 如何避免死锁？

除了在业务逻辑层面避免死锁以外。在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

---

参考：MySQL死锁分析与解决之路