深度整理总结MySQL——MySQL加锁工作原理

前言

上篇我们聊过锁都有哪些类型,那这篇我们聊MySQL什么时候会把锁添加在索引上.
顺便解释一下为什么MySQL加锁会加在索引上.
文章列举的表和数据代码都经过验证,你直接复制粘贴结果不会错(我这个数据库版本下是这样的).

前置知识- 锁为什么加在索引上

MySQL 在执行锁操作时，将锁加在索引上，而不是直接加在表的数据上，这一做法有几个重要的原因。主要是为了提高数据库操作的效率和并发性，减少锁的粒度，从而提高系统的性能。具体原因如下.

锁的粒度优化

MySQL 使用索引加锁，是为了减少锁的粒度，使得锁只作用于相关数据范围，而不是锁定整个表。通过锁定索引，MySQL 能够更精确地定位到需要操作的行，从而仅对需要的行加锁，而不是对整个表加锁。这样可以显著提升并发性能。

提高并发性

加锁索引使得多个事务可以同时在同一张表上进行不同的数据操作，而不会互相干扰。
举个例子，如果两个事务同时查询同一个表，但它们的查询条件不同，并且有索引，MySQL 就可以根据索引定位到不同的数据行，对它们分别加锁，而不需要锁定整个表，这样就能让两个事务同时执行，从而提高并发性能。

避免全表扫描

在没有索引的情况下，MySQL 需要对整个表进行扫描来查找数据，而这个过程会锁住整个表。而如果表上有索引，MySQL 就可以通过索引快速定位到目标数据，从而只锁定满足条件的行。这不仅减少了锁定的范围，还大大提高了查询性能。

优化死锁处理

由于 MySQL 将锁加在索引上，索引的有序性和结构化可以帮助 MySQL 更好地处理死锁问题。在涉及多个事务的并发操作中，通过对索引的加锁，可以确保事务按照一定的顺序进行锁定，这样可以减少死锁发生的几率。

解决幻读问题

在高并发环境下，通过加锁索引，MySQL 可以有效防止幻读现象。
通过对索引的加锁，可以确保在事务过程中，读取的数据范围是稳定的，不会因为其他事务的插入或删除操作而导致不一致。
例如，如果事务 A 查询某个范围的数据，使用了索引扫描，事务 B 插入了一些符合该范围的数据。通过索引加锁，事务 A 可以确保在整个事务期间，数据范围不被改变，从而避免幻读。

什么SQL语句会加行级锁

在说 MySQL 是怎么加行级锁的时候，是在说 InnoDB 引擎是怎么加行级锁的,因为MyISAM 引擎并不支持行级锁。
普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。
如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读.

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

上面这两条语句类型在使用的必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。

update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)

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

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读读互斥, 读写互斥。
共享锁仅仅共享度,独占锁什么都不共享!

MySQL是如何加行级锁

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。
MySQL 的行级锁是通过索引来加锁的。具体来说，MySQL 在执行 SELECT、UPDATE、DELETE 等操作时，会基于索引（主键索引、唯一索引或非唯一索引）来加锁，确保同一时刻只有一个事务对某一行数据进行修改。
但是既然是根据索引类型来加,那就是有规律的,熟悉之后也不算苦难,还是挺有意思的,有种把脑子缠住的美.
有个现象挺有意思的,行级锁上面提到有三类: Record Lock,Gap Lock(里面分纯Gap Lock和Next-key Lock).
你猜猜哪种锁是最常用的.
如果我说这三种锁实际上可以用一种锁来取代,你猜猜是哪种锁.
那肯定是Next-Key Lock.
Next-Key Lock 结合了这两种锁的功能——它既能锁住某一行（像行锁一样），又能锁住行之间的间隙（像间隙锁一样）。这样就能在很多情况下做到锁住整个查询的区域，确保没有其他事务在中间插入数据或者修改数据。

• Next-Key Lock 锁住的是 [X, X] 区间，前闭后闭的范围.这意味着它锁住了目标行的数据以及行之间的空隙，防止其他事务在这个范围内插入新数据或修改现有数据。
• Gap Lock 锁住的是 ( , ) 区间，前开后开的范围.仅仅锁住了行与行之间的空隙，防止其他事务插入数据。
• Record Lock 锁住的是 [X, X]，即特定的一行数据.防止修改或删除。

那如果Next-key 像替代Gap Lock 和Record Lock的话,肯定是要有个退化的,根据不同的场景,退化为Gap Lock 或 Record Lock.
那实际上退化的场景我们大概也能想到:

• 退化为 Gap Lock：如果没有实际的数据行被锁住（比如查询的范围内没有数据），那么 Next-Key Lock 就会退化为锁住空隙，即 Gap Lock。
• 退化为 Record Lock：如果查询范围只包含单个数据行（例如精确查找某一行），那么 Next-Key Lock 会退化为 Record Lock，即仅锁住这行数据.

场景模拟代码

-- 创建表
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2)
);-- 插入一些模拟数据
INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 5000.00),
(2, 'Bob', 6000.00),
(3, 'Charlie', 5500.00),
(4, 'David', 7000.00),
(5, 'Eva', 6500.00),
(6, 'Frank', 7500.00);-- 创建唯一索引
CREATE UNIQUE INDEX idx_id_name ON employees(id, name);-- 创建非唯一索引
CREATE INDEX idx_salary ON employees(salary);

唯一索引等值查询

退化为记录锁

场景:
当查询的记录是「存在」的, next-key lock 会退化成「记录锁」

为什么会退化为记录锁

当我们执行唯一索引等值查询时，如果查询的记录存在，Next-Key Lock 退化为 记录锁（Record Lock）的原因，主要是因为 唯一索引查询 本身能精确定位到单一的记录。这种情况下，MySQL 只需要对该行数据加锁，而无需再加上对间隙的锁定，防止其他事务插入数据。

• 唯一索引查询的性质
  唯一索引查询能精确地定位到一个具体的行。例如，当你查询某个表的主键或者一个唯一索引列时，MySQL 知道查询的结果只会有一条记录。比如：

SELECT * FROM employees WHERE id = 2 FOR UPDATE;

这条查询会根据唯一索引（假设 id 是唯一索引）定位到 id = 2 这一行数据，而 id = 2 只能有一个值。这个查询会直接锁住这一行数据。

• 没有并发插入的风险
  由于唯一索引查询只能返回一个结果，所以如果查询的记录存在，MySQL 不需要担心其他事务插入数据到这个位置，因为该行数据唯一，插入不可能发生在该记录的范围内。也就是说，不存在插入“间隙”的问题。
• 该记录无法删除,因为加了记录锁,其他事务也无法删除该记录,不会出现前后两次查询的结果集不同,也避免了幻读问题.
• 不需要加Gap Lock

1. 对于 唯一索引等值查询，查询的条件足够精确.只会匹配一个唯一的值（比如 id = 2）。因为这个值是唯一的，只有这一行数据存在。
2. Gap Lock 锁的是行与行之间的“空白”区域，防止其他事务在该区域插入新记录。但由于 唯一索引等值查询 只涉及一条数据，MySQL 知道不会有其他事务插入数据在该位置，所以没有必要加锁该行之间的空隙区间。

分析加了什么锁

我们可以通过SELECT * FROM performance_schema.data_locks;语句来查询事务执行SQL过程中加了什么锁.
比如上面我们执行的语句:

SELECT * FROM employees WHERE id = 2 FOR UPDATE;

我们可以在Lock_Type字段里看到一共加了两个锁，分别为：

• 表锁： X类型的意向锁
• 行锁： X类型的记录锁

在Lock_Mode 可以确认是next-key锁,间隙锁,记录锁:

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

为什么会退化为间隙锁

假设事务 执行了这条等值查询语句，查询的记录是「不存在」于表中的.

begin;
SELECT * FROM employees WHERE id = 20 FOR UPDATE;SELECT * FROM performance_schema.data_locks;

执行后获取的结果如下:

从上图可以看到，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
• 行锁：X 类型的间隙锁；
  此时事务 在 id = 20 记录的主键索引上加的是间隙锁，锁住的范围是 (19, 21)

为什么我可以插入id=19的数据?

接下来,如果有其他事务插入19,21这一些记录的话,这些插入的雨具都会阻塞(插入id=19会被阻塞,但是插入id=9不会被阻塞).
注意如果你用本地同一个事务插入是可以成功插入的,即使你在加了间隙锁后成功插入 id = 19，并不意味着这个锁没有生效。实际上，在你插入记录时，锁住的间隙并没有阻止插入操作，因为没有其他事务在此位置竞争。间隙锁主要是防止其他事务插入新记录，而不是阻止当前事务插入。

为什么锁住的范围是(19,21)

• MySQL 根据索引的有序性来推断出加锁的区间，间隙锁的目的是防止在当前查询的区间内插入新的记录。
• 如果该查询所涉及的范围是 id = 20，但没有找到，那么 间隙锁会锁住的是 20 的位置区间，即锁住 (19, 21) 这个区间，防止其他事务插入 id = 20 或者更新 id = 19 和 id = 21 的位置。
• 这并不意味着会锁住 19 或 21 本身，而是锁住了一个范围，阻止其他事务在该范围内插入记录。

唯一索引范围查询