页面启动中 . . .

MySQL锁详解

MySQL 八股
Java开发
发布日期:   2024-05-06
更新日期:   2024-05-08
文章字数:   8.6k
阅读时长:   30 分
阅读次数:  

八股太多了，零零散散整理又遗忘，现在还是没找到合适的实习，有点沮丧；但是终归是要面对的，再把基础打牢一点吧，MySQL的锁也是关键点，目前也是记得七七八八，这里系统整理~

一、MySQL中的锁有哪些？

  • 在MySQL中根据加锁的范围，可以分为全局锁、表级锁和行级锁三类。大纲如下图所示：

    大纲

1.1 全局锁

  • 为了使用全局锁需要执行下面的指令：flush tables with read lock
  • 执行之后，整个数据库就处于只读状态了，这时候其他线程执行下面的操作，就会被阻塞：
    1. 对数据的增删改操作：比如insert、delete、update等语句；
    2. 对表结构的更改操作，比如alter table、drop table等语句；
  • 如果需要释放锁，只需要执行：unlock tables

全局锁应用场景是什么？

全局锁应用作为全库逻辑备份，这样的备份数据库期间，不会因为数据或者表结构的更新，而出现备份文件的数据与预期的不一样；

加上全局锁又会带来什么缺点？

加上全局锁，意味着整个数据库都是只读状态；

那么如果数据库里面有很多数据，备份就会花掉很多的时间，关键是备份期间，业务只能读取数据，而不能更新数据，这样会造成业务停滞。

针对上述的备份数据库数据的时候，会使用全局锁影响业务，那么使用什么方式可以避免？

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

备份数据库的工具是mysqldump，在使用mysqldump的基础上加上single-transaction参数，会在备份数据库之前开启事务。这种方法适用于支持【可重复读隔离级别的事务】的存储引擎；

Innodb引擎默认的事务隔离级别是RR（可重复度读），因此是可以采用方式备份数据库；

但是，对于MyISAM这种不支持事务的引擎，在备份数据库时候就要使用全局锁的方法；

​

1.2 表级锁

MySQL中的表级锁有下面几种：

  • 表锁

  • 元数据锁（MDL）

  • 意向锁

  • AUTO-INC锁

    ​

表锁
  • 表级锁除了会限制别的线程的读写之外，也会限制本线程接下来的读写操作；
-- 表级别的共享锁，也就是读锁
lock tables t_student read;

-- 表级别的独占锁，也就是写锁
lock tables t_student write;
  • 如果此时本线程对学生表加上了[共享表锁]，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时候会被阻塞，直到锁被释放。
  • 需要释放表锁，可以使用下面的指令，代表的是会释放当前会话的所有表锁：
unlock tables

综上所述：表锁的颗粒度还是偏大的，会影响读写操作，其实也就是并发功能，Innodb中实现的是锁粒度更细的行级锁；

​

元数据锁

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

  • 对一张表进行CRUD操作时候，加上的是MDL读锁；
  • 对一张表做结构变更时候，加上的是MDL写锁；

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

  1. 当有线程在执行select语句中（加上MDL读锁）期间，如果可以有其他线程要更改该表的结构（比如说是申请MDL写锁），那么这个将会被阻塞，直到执行完select语句为止（释放MDL读锁）
  2. 反之，当有线程对表结构进行变更（加MDL写锁）的期间，如果有其他线程执行了CRUD操作（申请MDL读锁），那么这时候就会被阻塞，直到表结构变更完成（释放MDL写锁）；

MDL 不需要显示调用，那么它是在什么时候释放的呢？

  • MDL是在事务提交后才会被释放，这意味着事务执行期间，MDL是一直持有的；

如果数据库有一个长事务（其实就是开启了事务，但是一直还没提交），那么这对表结构做出变更操作时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

  1. 首先，线程A先开启了事务（但是一直不提交），然后执行一条select语句，此时就先对该表加上MDL读锁；
  2. 然后，线程B也同样执行了select语句，此时并不会阻塞，因为【读读】并不冲突；
  3. 接着，线程C修改了表字段，此时由于线程A的事务并没有提交，也就是MDL读锁还在占用着，这时候线程C无法申请到MDL写锁，就会被阻塞罢了。

那么在线程C阻塞之后，后续对该表的select语句，就都会被阻塞，如果此时有大量该表的select语句的请求到来了，就会有大量的线程被阻塞住，这时候数据库的线程很快就会爆满了。

为什么线程C因为申请不到MDL写锁，而导致后续申请读锁的查询操作就会被阻塞？

  • 这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

​

意向锁

两个场景：

  1. 在使用InnoDB引擎的表里对某些记录【共享锁】之前，需要先在表级别加上一个【意向共享锁】；
  2. 在使用InnoDB引擎的表里面对某些记录加上【独占锁】之前，需要先在表级别加上一个【意向独占锁】；

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

-- select也是可以对记录加上共享锁和独占锁的，具体如下：
-- 先在表上加上意向共享锁，然后对读取的记录加上共享锁；
select...lock in share mode;

-- 先表上加上意向独占锁，然后对读取的记录加独占锁；
select...for update;

  • 意向共享锁和意向独占锁都是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables…read）和独占表锁（lock tables…write）发生冲突

    ​

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

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

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

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

​

AUTO-INC 锁

表中的主键通常都会设置成自增，这是通过对主键字段声明 AUTP_INDREMENT属性实现的；

之后可以在插入数据时候，可以不指定主键的值，数据库会自动给主键赋值递增的值，这里主要是通过AUTO-INC锁实现的。

  • AUTO-INC锁是特殊的表锁机制，不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放；
  • 在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。所以其他的事务如果不按照此递增排序顺序插入、修改数据的话，就会被阻塞。（不足之处在于，如果需要处理大量数据进行插入时候，会影响另一个事务的插入被阻塞）

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

  • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
  • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
  • 当 innodb_autoinc_lock_mode = 1：
    • 普通 insert 语句，自增锁在申请之后就马上释放；
    • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

  • 简单来说就是不同的session创建相同结构的表，存在插入数据先后问题，从而导致，某一个session中的insert语句生成的id是并不连续的；

  • binlog 日志格式要设置为 row，这样在 binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。

  • 当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

    ​

1.3 行级锁

在Innodb引擎中是支持行级锁的，但是MyISAM并不支持行级锁；

普通的select语句是不会对记录加锁的，因为它属于快照读。如果要在查询时候对记录加行锁，确实可以使用下面的这两个方式，这种查询会加锁的语句称为锁定读；

-- 对读取的记录加共享锁
select ... lock in share mode;

-- 对读取的记录加独占锁
select ... for update;
  • 上述的这两个语句必须放在同一个事务中，因为当事务提交了，所就会被提前释放，所以在使用这两条语句的时候，要加上begin、start transaction或者是set autocommit = 0。

共享锁和独占锁。共享锁（S锁）是读读共享、读写互斥；独占锁（X锁）满足写写互斥、读写互斥；

共享锁和独占锁

  • 行级锁分为三类，分别是Record Lock（称为记录锁）、Gap Lock（间隙锁）、Next-Key Lock（就是上述两种锁的结合，也称为临键锁）下面就这三个展开说说：

    ​

Record Lock

称为记录锁，锁住的是一条记录，分为共享锁S型和互斥锁X型，满足上述的读写条件（共享锁读读共享、读写互斥；互斥锁写写互斥、读写互斥）

代码用法：

begin;
select * from t_test where id = 1 for update;
案例

​

Gap Lock

也成为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的情况。

案例

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock

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

案例

所以，next-key lock既可以保护该记录，又可以组织其他事务将新记录插入到被保护记录前面的间隙中去。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

​

插入意向锁

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

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

案例

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

​

二、MySQL是怎么加锁的？

2.1 什么SQL语句会加上行级锁？

InnoDB引擎是支持行级锁的，而MyISAM引擎并不支持行级锁，所以后面的内容都是基于InnoDB引擎的；本质上还是InnoDB引擎决定加行级锁的操作；

普通的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锁）满足写写互斥、读写互斥。

2.2 行级锁的种类分别？

上述已经讲述了具体的分类，这里简单总结即可。

在【读已提交隔离级别】，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

在【可重复读隔离级别】，行级锁的种类除了有记录所，还有间隙锁（目的是为了避免幻读），所以行级锁的种类主要有三类：

  1. Record Lock，记录锁，也就是仅仅把一条记录锁上；(S型共享锁，X型记录锁等)
  2. Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  3. Next-Key Lock：Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身。

2.3 MySQL是怎么加上行级锁的？

行级锁加锁规则比较复杂，不同场景，加锁的形式是不同的；

加锁的对象是索引，加锁的基本单位是临键锁（Next- Key Lock），它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

  • 比如针对这里面的加锁范围：假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。

但是，next-key Lock在一些场景下退化成记录锁或者间隙锁；总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

  • 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
  1. 「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。
  2. 如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」。

1. 记录存在的情况

举例：

-- 假设此时的事务A执行了这条等值查询的语句，查询的记录是存在于当前的表中的；
select * from user where id = 1 for update;
  • 这里的事务A会id为1的这条记录就会加上X型的记录锁；，加上去了后，其他事务就无法对id为1的这条记录进行更新和删除的操作了；

为什么说唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。所以：要避免幻读就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新记录，这样前后两次查询的结果集就不会出现不相同的情况；

  • 由于主键具有的唯一性，所以其他事务插入id = 1的时候，会因为主键冲突，导致无法插入id = 1的新记录。这样的事务A在多次查询 id = 1 的记录时候，不会出现前后两次查询的结果集不同情况，也就避免了幻读的问题。
  • 由于对 id = 1 加了记录锁，其他事务无法删除该记录，这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。

2. 记录不存在的情况

举例：

-- 假设事务 A 执行了这条等值查询语句，查询的记录是「不存在」于表中的。
select * from user where id = 2 for update;

此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)；注意这里是前开后开。

  • 如何确定的查询范围情况？
  • 如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围「右边界」，此次的事务 A 的 LOCK_DATA 是 5。然后锁范围的「左边界」是表中 id 为 5 的上一条记录的 id 值，即 1。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

原因在于唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题？

  • 为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录就行了。

  • 为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

    ​

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

  • 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。只有存在等值的情况下才会发生退化情况，如果还是范围的话，则直接加上的是临键锁；

    ​

  • 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：

    • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

    举例：

    -- 事务A执行下面的这条范围查询语句，需要注意的是id为6的数据并不存在于表中，小于或者小于等于加锁情况是一样的（id为6的数据并不存在）
select * from user where id < 6 for update;

    给出加锁的情况如下：

    案例

    ​

    • 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；

    举例：

    -- 如果事务 A 的查询语句是小于的范围查询，且查询条件值的记录（id 为 5）存在于表中。
select * from user where id < 5 for update;

    如下图所示：

    案例
    • 当条件值的记录在表中，如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

    举例：

    -- 假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 5）存在于表中
select * from user where id <= 5 for update;

    如下图所示：

    案例

    ​

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个主键索引，一个非唯一索引（二级索引），所以在加锁的时候，两个都会加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁；

1. 记录不存在的情况

-- 事务 A 对非唯一索引（age）进行了等值查询，且表中不存在 age = 25 的记录
select * from user where ag = 25 for update;
img

加锁情况：

  • 定位到第一条不符合查询条件的二级索引记录，即扫描到 age = 39，于是该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。
  • 停止查询

​

问题：当有一个事务持有二级索引的间隙锁（22，39）时候，什么情况下，可以让其他事务的插入age = 22 或者 age = 39 记录的语句成功？又是什么情况下，插入时候会被阻塞？

首先需要直到的是，什么情况下插入语句会发生阻塞情况？

插入语句在插入一条记录之前，需要定位到该记录在B+树的为止，如果插入位置的吓一跳记录的索引上有间隙锁，才会发生阻塞情况；

二级索引树是按照二级索引值（age列）按顺序存放的，在相同的二级索引值情况下， 再按主键 id 的顺序存放。知道了这个前提，我们才能知道执行插入语句的时候，插入的位置的下一条记录是谁。

基于前面的实验，事务 A 是在 age = 39 记录的二级索引上，加了 X 型的间隙锁，范围是 (22, 39)。

比如：插入 age = 22 记录的成功和失败的情况分别如下：

  • 当其他事务插入一条 age = 22，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 10、age = 22 的记录，该记录的二级索引上没有间隙锁，所以这条插入语句可以执行成功。

  • 当其他事务插入一条 age = 22，id = 12 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功。

    ​

2. 记录存在的情况

举例：

-- 事务A对非唯一索引age进行等值查询的时候，查询值存在的情况；
select * from user where age = 22 for update;

这里的事务A加锁的变化过程如下：

img

注意：那么当在 age = 39 这条记录的二级索引索引上加了范围为 (22, 39) 的间隙锁后，其他事务是无法插入一个 age = 22，id = 12 的新记录，因为当其他事务插入一条 age = 22，id = 12 的新记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功，这样就避免幻读现象的发生。

​

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

-- 比如下面这个事务A的范围查询语句
 select * from user where age >= 22  for update;
img

可以看到，事务 A 对主键索引和二级索引都加了 X 型的锁；

  • 主键索引（id 列）：
    • 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。
    • 在 id = 20 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 20 的这一行记录。
  • 二级索引（age 列）：
    • 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
    • 在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23、24、25、…、38 的这一些新记录。不过对于是否可以插入 age = 22 和 age = 39 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
    • 在特殊记录的二级索引上，加上了范围为(39，+∞]的next-key锁，意味着其他事务是无法插入age大于39的这些新记录；

在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？

本质上在于这里的age字段并不是唯一索引，不具有唯一性，如果加了记录锁（无法防止插入，只能防止删除或者修改），就会导致可能出现两个age = 22 的记录情况，从而导致前后两次查询的结果集不同、幻读的情况；

​

没有加索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

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

总结

关于MySQL行级锁的加锁规则：

唯一索引等值查询：

  • 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

  • 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  • 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

  • 唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
  • 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

其实理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，这样就很容易理解这些加锁的规则了。

还有一件很重要的事情，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

​

主键唯一索引加锁流程图：

img

​

非唯一索引加锁流程图：

img

文章作者: XKJ
文章链接: https://shakeJ96.github.io/2024/05/06/mysql-suo-xiang-jie/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 XKJ !
MySQL 八股
赏
  目录