【面试突击】数据库面试实战-MySQL锁（加更）

🌈🌈🌈🌈🌈🌈🌈🌈
欢迎关注公众号（通过文章导读关注：【11来了】），及时收到 AI 前沿项目工具及新技术 的推送
发送 资料 可领取 深入理解 Redis 系列文章结合电商场景讲解 Redis 使用场景、中间件系列笔记和编程高频电子书！

文章导读地址：点击查看文章导读！

感谢你的关注！

🍁🍁🍁🍁🍁🍁🍁🍁

MySQL 的锁

为什么需要问 MySQL 中锁的问题呢？

如果在线上系统中，在高并发的访问之下，出现了 死锁问题 或者 等待锁时间过长导致超时，那么碰到这些情况，就可能问你锁相关的问题

MySQL 这一块锁的内容还是比较复杂的，需要写一些功夫来学习，接下来我尽量写的简单易懂一些

首先还是先给锁分类，之后再来逐个了解：

按照功能划分：

• 共享锁：也叫 S 锁 或 读锁，是共享的，不互斥

  加锁方式：select ... lock in share mode

• 排他锁：也叫 X 锁 或 写锁，写锁阻塞其他锁

  加锁方式：select ... for update

按照锁的粒度划分：

• 全局锁：锁整个数据库
• 表级锁：锁整个表
• 行级锁：锁一行记录的索引
  • 记录锁：锁定索引的一条记录
  • 间隙锁：锁定一个索引区间
  • 临键锁：记录锁和间隙锁的结合，解决幻读问题
  • 插入意向锁：执行 insert 时添加的行记录 id 的锁
  • 意向锁：存储引擎级别的“表级锁”

全局锁

全局锁是对整个数据库实例加锁，加锁后整个数据库实例就处于只读状态

什么时候会用到全局锁呢？

在 全库逻辑备份 的时候，对整个数据库实例上锁，不允许再插入新的数据

相关命令：

-- 加锁
flush tables with read lock;
-- 释放锁
unlock tables;

表级锁

表级锁中又分为以下几种：

• 表读锁：阻塞对当前表的写，但不阻塞读
• 表写锁：阻塞队当前表的读和写
• 元数据锁：这个锁不需要我们手动去添加，在访问表的时候，会自动加上，这个锁是为了保证读写的正确
  • 当对表做 增删改查 时，会自动添加元数据读锁
  • 当对表做 结构变更 时，会自动添加元数据写锁
• 自增锁：是一种特殊的表级锁，自增列事务执行插入操作时产生

查看表级锁的命令：

-- 查看表锁定状态
show status like 'table_locks%';
-- 添加表读锁
lock table user read;
-- 添加表写锁
lock table user write;
-- 查看表锁情况
show open tables;
-- 删除表锁
unlock tables;

行级锁

MySQL 的行级锁是由存储引擎是实现的，InnoDB 的行锁就是通过给 索引加锁 来实现

注意：InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁

行锁根据 范围 分为：记录锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）、插入意向锁（Insert Intention Locks）

行锁根据 功能 分为：读锁和写锁

什么时候会添加行锁呢？

• 对于 update、insert 语句，InnoDB 会自动添加写锁（具体添加哪一种锁会根据 where 条件判断，后边会提到 加锁规则）
• 对于 select 不会添加锁
• 事务手动给 select 记录集添加读锁或写锁

接下来对记录锁、间隙锁、临键锁、插入意向锁来一个一个解释，这几个锁还是比较重要的，一定要学习！

记录锁：

记录锁：锁的是一行索引，而不是记录

那么可能有人会有疑问了，如果这一行数据上没有索引怎么办呢？

其实如果一行数据没有索引，InnoDB 会自动创建一个隐藏列 ROWID 的聚簇索引，因此每一行记录是一定有一个索引的

下边给出记录锁的一些命令：

-- 加记录读锁
select * from user where id = 1 lock in share mode;
-- 加记录写锁
select * from user where id = 1 for update;
-- 新增、修改、删除会自动添加记录写锁
insert into user values (1, "lisi");
update user set name = "zhangsan" where id = 1;
delete from user where id = 1;

间隙锁

间隙锁用于锁定一个索引区间，开区间，不包括两边端点，用于在索引记录的间隙中加锁，不包括索引记录本身

间隙锁的作用是 防止幻读，保证索引记录的间隙不会被插入数据

间隙锁在 可重复读 隔离级别下才会生效

如下：

select * from users where id between 1 and 10 for update;

间隙锁、临键锁区间图

这里将间隙锁和临键锁（下边会讲到）在主键索引 id 列和普通索引 num 列上的区间图画出来，方便通过图片更加直观的学习

首先，表字段和表中数据如下：

对于这两个字段，他们的间隙锁和临键锁的区间如下（红色部分）：

临键锁

临键锁是记录锁和间隙锁的组合，这里之所以称临键锁是这两个锁的组合是因为它会锁住一个左开右闭的区间（间隙锁是两边都是开区间，通过记录锁锁住由边的记录，成为左开右闭的区间），可以看上边的图片来查看临键锁的范围

默认情况下，InnoDB 使用临键锁来锁定记录，但会在不同场景中退化

• 使用唯一索引（Unique index）等值（=）且记录存在，退化为 记录锁
• 使用唯一索引（Unique index）等值（=）且记录不存在，退化为 间隙锁
• 使用唯一索引（Unique index）范围（>、<），使用 临键锁
• 非唯一索引字段，默认是 临键锁

每个数据行上的 非唯一索引 都会存在一把临键锁，但某个事务持有这个临键锁时，会锁一段左开右闭区间的数据

插入意向锁

间隙锁在一定程度上可以解决幻读问题，但是如果一个间隙锁锁定的区间范围是（10，100），那么在这个范围内的 id 都不可以插入，锁的范围很大，导致很容易发生锁冲突的问题

插入意向锁就是用来解决这个问题

插入意向锁是在 Insert 操作之前设置的一种 特殊的间隙锁，表示一种插入意图，即当多个不同的事务同时向同一个索引的同一个间隙中插入数据时，不需要等待

插入意向锁不会阻塞 插入意向锁，但是会阻塞其他的 间隙写锁、记录锁

举个例子：就比如说，现在有两个事务，插入值为 50 和值为 60 的记录，每个事务都使用 插入意向锁 去锁定 （10，100）之间的间隙，这两个事务之间不会相互阻塞！

加锁规则【重要】

加锁规则非常重要，要了解 MySQL 会在哪种情况下去加什么锁，避免我们使用不当导致加锁范围很大，影响写操作性能

对于 主键索引 来说：

• 等值条件，命中，则加记录锁
• 等值条件，未命中，则加间隙锁
• 范围条件，命中，对包含 where 条件的临建区间加临键锁
• 范围条件，没有命中，加间隙锁

对于 辅助索引 来说：

• 等值条件，命中，则对命中的记录的 辅助索引项 和 主键索引项 加 记录锁 ，辅助索引项两侧加 间隙锁
• 等值条件，未命中，则加间隙锁
• 范围条件，命中，对包含 where 条件的临建区间加临键锁，对命中纪录的 id 索引项加记录锁
• 范围条件，没有命中，加间隙锁

行锁变成表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁：

假如 account 表有 3 个字段（id, name, balance），我们在 name、balance 字段上并没有设置索引

session1 执行：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | zs   |     777 |
|  2 | ls   |     800 |
|  3 | ww   |     777 |
|  4 | abc  |     999 |
| 10 | zzz  |    2000 |
| 20 | mc   |    1500 |
+----+------+---------+
6 rows in set (0.01 sec)

mysql> update account set balance = 666 where name='zs';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时 session2 执行（发现执行阻塞，经过一段时间后，返回结果锁等待超时，证明 session1 在没有索引的字段上加锁，导致行锁升级为表锁，因此 session2 无法对表中其他数据做修改）：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = 111 where name='abc';
RROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁

行锁分析实战【重要】

这里主要对下边这两条 sql 进行分析，判断看到底会添加什么样的行锁：

-- sql1
select * from user where id = 5;
-- sql2
delete from user where id = 5;

而对于 sql1 来说，select 查询是快照读，不会加锁，因此下边主要是对 sql2 进行分析

其实只通过 sql 是没有办法去分析到底会添加什么样的行锁，还需要结合 where 后边的条件，还有索引的字段来综合分析

以下分析基于 可重复读 隔离级别进行分析

情况1：id 列是主键

当 id 列是主键的时候，delete 操作对 id=5 的数据删除，此时根据【加锁规则】，只需要对 id=5 这条记录加上 记录写锁 即可

只对这一条记录加锁，比较简单，这里就不画图了

情况2：id 列是二级唯一索引

如果 id 列是二级唯一索引的话，此时根据【加锁规则】，那么需要对 id=5 这条记录加上 记录写锁，再通过这个二级唯一索引去 主键索引 中找到对应的记录，也加上 记录写锁，添加的锁如下图：

为什么主键索引中也需要加锁呢？

如果另一个并发的 sql 通过主键索引来更新这条记录：update user set id = 11 where name = 'a';，而 delete 没有对主键索引上的记录加锁，就会导致这条 update 语句并不知道 delete 在对这条数据进行操作

情况3：id 列是二级非唯一索引

在 可重复读 隔离级别下，通过间隙锁去避免了幻读的问题，虽然还有可能出现幻读，还是大多数情况下不会出现

如何通过添加 间隙锁 去避免幻读问题呢？

当删除 id = 5 的数据时，由于 id 是二级非唯一索引（辅助索引），由上边的加锁规则可以知道，会对命中的记录的 辅助索引项 和 主键索引项 加 记录锁 ，辅助索引项两侧加 间隙锁，加的锁如下图红色所示：

情况4：id 列上没有索引

如果 id 列上没有索引，那么就只能全表扫描，因此会给整个表都加上写锁，也就是锁上 表的所有记录 和 聚簇索引的所有间隙

那么如果表中有 上千万条数据，那么在这么大的表上，除了不加锁的快照读操作，无法执行其他任何需要加锁的操作，那么在整个表上锁的期间，执行 SQL 的并发度是很低的，导致性能很差

因此，一定要注意，尽量避免在没有索引的字段上进行加锁操作，否则行锁升级为表锁，导致性能大大降低

死锁分析

死锁 造成的原因：两个及以上会话的 加锁顺序不当 导致死锁

死锁案例：两个会话都持有一把锁，并且去争用对方的锁，从而导致死锁

如何排查和避免死锁问题：

通过 sql 查询最近一次死锁日志：

show engine innodb status;

MySQL 默认会主动探知死锁，并回滚某一个影响最小的事务，等另一个事务执行完毕后，在重新执行回滚的事务

可以从以下几个方面降低死锁问题出现的概率：

• 尽量减小锁的粒度，保持事务的轻量，可以降低发生死锁的概率
• 尽量避免交叉更新的代码逻辑
• 尽快提交事务，减少锁的持有时间