MySQL 高级锁机制拆解，看完吊打面试官

王木风
95 阅读20分钟

基础部分

并发事务问题

问题类型定义发生条件
脏读读到未提交的数据事务 B 修改但未提交，事务 A 读取
不可重复读同一行数据多次读取结果不同事务 B 修改并提交，事务 A 再次读取
幻读查询结果集行数变化事务 B 插入/删除记录，事务 A 再次查询

事务隔离级别

类型存在的问题
读未提交脏读、幻读、不可重复读
读已提交幻读、不可重复读
可重复度无脏读、无不可重复读，可能幻读（但MySQL中通过间隙锁避免）
串行化无并发问题，但性能最低

为什么会出现这些问题

读未提交：

事务A修改id=20的数据，name从“李四”改为“张三”，还未提交事务 事务B查询id=20的数据，name结果是“张三” 事务A回滚，此时事务B的“张三”就是脏读

读已提交：

幻读： 事务A删除id>20，name=“张三”的数据 事务B读取id>20，数据有10条 事务A提交事务 事务B再读取id>20，数据有9条

不可重复读： 事务B读取id=20的数据，读到name等于“李四” 事务A修改id=20，name从“李四”改为“张三”，并提交事务 事务B读取id=20的数据，读到name等于“张三”

可重复读：

幻读： 在可重复读隔离级别下，事务A使用MVCC快照读看不到事务B在快照创建后提交的数据，但事务A的更新操作会使用当前读，可能看到这些数据，从而产生幻读现象。

脏读和不可重复读的区别是什么

脏读针对的是A事务读到了B事务还未提交的数据。不可重复读是A事务分别读取到了B事务提交前和提交后的数据，导致的不一致。重点区别在于B的事务是否提交

不可重复读和幻读的区别是什么

不可重复读针对的是数据内容，幻读针对的是数据条目

锁类型

共享锁和独占锁

实现标准的行级锁定，其中存在两种类型的锁，即共享（ S ）锁和独占（ X ）锁。

  • 共享锁允许持有该锁的事务读取一行数据。（S锁）
  • 独占锁（X锁）允许持有该锁的事务读取和修改一行数据

如果事务  T1  对行  r  持有共享（ S ）锁，那么来自某个不同事务  T2  对行  r  锁的请求将按如下方式处理：

  • T2  对  r  的  S  锁请求可以立即获得批准。因此， T1  和  T2  都持有  r  的  S  锁。
  • T2  请求的  X  锁无法立即授予。

如果事务  T1  对行  r  持有排他性（ X ）锁，那么来自某个不同事务  T2  对  r  的任何类型锁的请求都不能立即获得批准。相反，事务  T2  必须等待事务  T1  释放其对行  r  的锁。

意向锁

InnoDB  支持多种粒度的锁定，允许行锁和表锁共存。例如，像  LOCK TABLES ... WRITE  这样的语句会对指定的表获取一个排他锁（一个  X  锁）。为了使多粒度级别的锁定切实可行， InnoDB  使用意向锁。 意向锁是表级锁，用于表明事务稍后对表中的行需要哪种类型的锁（共享锁或排他锁）。意向锁有两种类型：

  • 共享意向锁（ IS ）表示事务打算在表中的单个行上设置共享锁。
  • 意向排他锁（ IX ）表示事务打算在表中的单个行上设置排他锁。

意向锁定协议如下：

  • 在事务能够获取表中某一行的共享锁之前，它必须首先获取该表的 IS 锁或更强的锁。
  • 在事务能够获取表中某一行的排他锁之前，它必须首先获取该表的 IX锁 。

表级锁类型的兼容性总结在以下矩阵中。

XIXSIS
XConflict 冲突Conflict 冲突Conflict 冲突Conflict 冲突
IXConflict 冲突Compatible 兼容的Conflict 冲突Compatible 兼容的
SConflict 冲突Conflict 冲突Compatible 兼容的Compatible 兼容的
ISConflict 冲突Compatible 兼容的Compatible 兼容的Compatible 兼容的

如果请求的事务锁与现有的锁兼容，则会授予该锁；但如果与现有的锁冲突，则不会授予。事务会一直等待，直到冲突的现有锁被释放。如果锁请求与现有锁冲突且由于会导致死锁而无法授予，则会发生错误。

意向锁除了会阻塞对整个表的请求（例如， LOCK TABLES ... WRITE ）之外，不会阻塞任何其他请求。意向锁的主要作用在于表明有人正在锁定表中的某一行，或者即将锁定某一行。

记录锁

阻止对现有记录的修改 —— 不可重复读

记录锁是对索引记录的锁定。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;  阻止任何其他事务在  t.c1  的值为  10  的行上执行插入、更新或删除操作。

即使表未定义任何索引，记录锁始终锁定索引记录。对于这种情况， InnoDB  会创建一个隐藏的聚集索引，并使用此索引进行记录锁定。

间隙锁

阻止在间隙中插入新记录 —— 为了解决幻读问题

间隙锁是一种锁定索引记录之间间隙的锁，或者是锁定第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如， SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;  会阻止其他事务向列  t.c1  中插入值  15 ，无论该列中是否已存在这样的值，因为该范围内所有现有值之间的间隔都被锁定。

一个间隔可能跨越单个索引值、多个索引值，甚至为空。

间隙锁是性能与并发之间权衡的一部分，在某些事务隔离级别中使用，而在其他级别中则不使用。

对于使用唯一索引锁定行以查找唯一行的语句，不需要间隙锁定。（这不包括搜索条件仅包含多列唯一索引中某些列的情况；在这种情况下，间隙锁定确实会发生。）例如，如果  id  列具有唯一索引，则以下语句仅对  id  值为 100 的行使用索引记录锁定，而且无论其他会话是否在前面的间隙中插入行，这都无关紧要

如果  id  未被索引或具有非唯一索引，则该语句确实会锁定前面的区间。

这里还值得注意的是，不同的事务可以在一个间隔上持有冲突的锁。例如，事务 A 可以在一个间隔上持有共享间隔锁（间隔 S 锁），而事务 B 则持有同一个间隔上的排他间隔锁（间隔 X 锁）。允许持有冲突的间隔锁的原因在于，如果从索引中删除一条记录，那么由不同事务对该记录持有的间隔锁必须合并。

在  InnoDB  中的间隙锁是“纯粹的抑制性”锁，这意味着它们的唯一目的是防止其他事务向间隙中插入数据。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享间隙锁和独占间隙锁之间没有区别。它们彼此之间不会产生冲突，并且执行相同的功能。

间隙锁定可以被显式禁用。这种情况发生在您将事务隔离级别更改为 READ COMMITTED  或启用 innodb_locks_unsafe_for_binlog 系统变量（该变量现已弃用）时。在这种情况下，间隙锁定仅用于外键约束检查和重复键检查，而不会用于搜索和索引扫描。

使用 隔离级别或启用功能还有其他一些影响。对于不匹配的行，记录锁在 MySQL 评估了条件之后即被释放。为了 这些语句执行“半一致性”读取，即返回给 MySQL 最新的已提交版本，以便 MySQL 能够确定该行是否符合  UPDATE  的  WHERE  条件。

临键锁

索引记录上的记录锁与该索引记录之前间隙上的间隙锁的组合。

InnoDB  在执行行级锁定时，当它搜索或扫描表索引时，会对遇到的索引记录设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。对索引记录的下一个键锁也会影响该索引记录之前的“间隙”。也就是说，下一个键锁是索引记录锁加上该索引记录之前间隙的间隙锁。如果一个会话在索引中的记录 R 上持有共享锁或排他锁，那么另一个会话就不能在索引顺序中位于 R 之前的间隙中插入新的索引记录。

假设一个索引包含值 10、11、13 和 20。 针对此索引的可能的下一个键锁涵盖以下区间，其中圆括号表示区间端点不包含在内，方括号表示区间端点包含在内：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个区间，下一个键锁锁定索引中最大值上方的间隙以及具有高于索引中任何实际值的“上确界”伪记录。上确界并非真正的索引记录，因此实际上，此下一个键锁仅锁定最大索引值之后的间隙。

默认情况下， InnoDB  以 REPEATABLE READ 事务隔离级别运行。在这种情况下， InnoDB  对搜索和索引扫描使用间隙锁，这可以防止幻读。

插入意向锁

插入意向锁是一种在插入行之前由 INSERT 操作设置的间隙锁类型。这种锁以一种方式表明插入意图，即如果多个事务插入到同一个索引间隙中，但不是在间隙内的同一位置插入，则它们无需相互等待。假设存在索引记录值分别为 4 和 7。分别尝试插入值为 5 和 6 的不同事务，在获取插入行的排他锁之前，每个事务都会用插入意向锁锁定 4 和 7 之间的间隙，但由于这些行不冲突，所以它们不会相互阻塞。

以下示例展示了一个事务在获取插入记录的排他锁之前先获取插入意向锁的情况。该示例涉及两个客户端，A 和 B。

客户 A 创建了一个包含两条索引记录（90 和 102）的表，然后启动了一个事务，该事务对 ID 大于 100 的索引记录加排他锁。该排他锁包含记录 102 之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户 B 开始一项向间隙插入记录的事务。 在等待获取排他锁期间，该事务会持有插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

在  SHOW ENGINE INNODB STATUS  和 InnoDB 监视器输出中，插入意向锁的事务数据类似于以下内容：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

自增锁

一种  AUTO-INC  锁是一种特殊的表级锁，由向具有  AUTO_INCREMENT  列的表中插入数据的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待才能向该表执行自己的插入操作，以便第一个事务插入的行能获得连续的主键值。

变量  innodb_autoinc_lock_mode  控制用于自动递增锁定的算法。它允许您在自动递增值的可预测序列和插入操作的最大并发性之间进行权衡选择。

空间锁

InnoDB  支持对包含空间数据的列进行索引

对于涉及 索引 的操作，若要处理锁定问题，行级 锁定和 可重复读 事务隔离级别使用 间隙锁定 时效果不佳。在多维数据中不存在绝对的排序概念，因此无法明确哪个是“下一个”键。

为了支持具有 空间 索引的表的隔离级别，MySQL 使用谓词锁。空间 索引包含最小边界矩形（MBR）值，因此 MySQL 通过在查询所使用的 MBR 值上设置谓词锁来强制对索引的一致性读取。其他事务无法插入或修改与查询条件匹配的行。

非mysql官方的锁

悲观锁

悲观锁假定并发冲突很可能发生，因此在访问数据之前，先获取锁，阻止其他线程访问，直到当前操作完成。

乐观锁

乐观锁假定并发冲突很少发生，因此不会先加锁，而是在更新时检查数据是否被其他线程修改过。

思考

乐观锁不会开启事务吗，开启事务的话mysql不会自动申请x锁吗？

锁类型SELECT阶段UPDATE阶段锁持有时间
悲观锁申请并持有X锁继续持有X锁整个事务期间
乐观锁不加锁短暂持有X锁仅UPDATE执行期间

只有一条更新语句，那岂不是持有X锁的时间一致？

没错：

  • 在只有一条UPDATE语句的简单场景中，两种锁机制持有X锁的时间确实相近
  • 乐观锁的UPDATE语句同样需要短暂的X锁来保证原子性

但乐观锁的真正优势在于：

  1. 读操作的完全并发：SELECT语句不加锁，支持高并发读取
  2. 复杂业务逻辑：在应用层计算期间不持有数据库锁
  3. 减少锁竞争：多个事务可以并行读取，只在最后更新时短暂竞争

SELECT语句不加S锁吗？

在MySQL的InnoDB存储引擎中，普通的SELECT语句在默认的"可重复读"隔离级别下不会加任何锁，包括S锁（共享锁）。

MySQL通过MVCC机制来实现隔离级别，而不是通过加锁

SQL Server：

  • 在"读已提交"隔离级别下，SELECT语句会加S锁，但读取后立即释放
  • 在"可重复读"隔离级别下，SELECT语句会加S锁并持有到事务结束

mysql除了主动申请以外什么时候会加S锁？

在MySQL中自动加S锁的主要场景：

  1. ✅ 外键约束检查 - 最常见的自动S锁场景
  2. ✅ 唯一性约束检查 - 插入/更新时检查唯一索引
  3. ✅ 系统内部操作 - 死锁检测、元数据查询等
  4. ✅ 在线DDL - 某些表结构变更操作

场景分析

读未提交隔离级别场景下，事务A查询，事务B修改，都加什么锁？

事务B加锁过程：

  1. 表级IX锁（意向排他锁）
  2. 行级X锁（排他记录锁）
  3. 实际锁定id=1的记录

事务A读取过程

  1. 不加任何锁
  2. 不使用MVCC版本检查
  3. 直接读取数据页上的最新物理数据

为什么事务A能读到被锁定的数据？

需要明确：锁阻止的是冲突操作，不是所有操作

锁类型阻止的操作不阻止的操作
排他锁(X)UPDATE、DELETE、SELECT...FOR UPDATE普通SELECT（在读未提交下）
共享锁(S)UPDATE、DELETESELECT、SELECT...LOCK IN SHARE MODE

读已提交隔离级别下，事务A查询，事务B修改age大于10的数据，age上有索引，加锁过程什么样？

事务B加锁过程：

  1. 表级IX锁（意向排他锁）

  2. 索引扫描过程中的锁行为：

    • 通过idx_age索引快速定位age>10的记录
    • 对所有满足条件的记录加行级X锁（排他记录锁）
    • 在主键索引和二级索引上都加记录锁

  3. 具体锁定：

    • 在idx_age索引上锁定：age=15, age=20对应的索引条目
    • 在主键索引上锁定：id=2, id=3的记录
    • 不加任何间隙锁

事务A读取过程：

  1. 使用MVCC读取已提交的数据快照
  2. 不加任何锁
  3. 看到的是语句开始时的已提交数据版本

读已提交隔离级别下，事务A和事务B同时插入，加锁过程什么样？

事务A插入过程：

  1. 表级IX锁（意向排他锁）

  2. 重复键检查：

    • 对即将插入的位置加共享锁检查唯一性
    • 如果是自增主键，可能涉及AUTO-INC锁

  3. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）
    • 如果插入成功，立即持有该记录的X锁

事务B插入过程：

  1. 表级IX锁（意向排他锁）

  2. 重复键检查：

    • 对即将插入的位置加共享锁检查唯一性
    • 如果是自增主键，可能涉及AUTO-INC锁

  3. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）
    • 如果插入成功，立即持有该记录的X锁

读已提交隔离级别下，事务A查询，事务B修改age大于10的数据，age上无索引，加锁过程什么样？

事务B加锁过程：

  1. 表级IX锁（意向排他锁）

    • 表明事务打算在表中修改行

  2. 全表扫描过程中的锁行为：

    • 逐行扫描表，对每行检查age > 10条件
    • 对满足条件的行（age>10）加行级X锁（排他记录锁）
    • 对不满足条件的行，在读已提交下立即释放锁

  3. 具体锁定：

    • 锁定id=2（age=15）和id=3（age=20）的记录
    • 不锁定id=1（age=5）和id=4（age=8）的记录
    • 不加任何间隙锁

事务A读取过程：

  1. 使用MVCC读取已提交的数据快照
  2. 不加任何锁
  3. 看到的是语句开始时的已提交数据版本

读已提交隔离级别下，事务A和事务B同时插入，加锁过程什么样？

事务A插入过程：

  1. 表级IX锁（意向排他锁）

  2. 重复键检查：

    • 对即将插入的位置加共享锁检查唯一性
    • 如果是自增主键，可能涉及AUTO-INC锁

  3. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）
    • 如果插入成功，立即持有该记录的X锁

事务B插入过程：

  1. 表级IX锁（意向排他锁）

  2. 重复键检查：

    • 对即将插入的位置加共享锁检查唯一性
    • 如果是自增主键，可能涉及AUTO-INC锁

  3. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）
    • 如果插入成功，立即持有该记录的X锁

关键点：

  • 两个事务插入不同记录时，不会相互阻塞
  • 两个事务插入相同主键时，第二个事务会在重复键检查时被阻塞
  • 自增主键的AUTO-INC锁行为取决于innodb_autoinc_lock_mode设置

可重复读隔离级别下，事务A查询，事务B修改age大于10的数据，age上无索引，加锁过程什么样？

事务B加锁过程：

  1. 表级IX锁（意向排他锁）

    • 表明事务打算在表中修改行

  2. 全表扫描过程中的锁行为：

    • 由于无索引，必须进行全表扫描
    • 对扫描到的所有记录加临键锁（Next-Key Lock）
    • 临键锁 = 记录锁 + 间隙锁

  3. 具体锁定范围：

    • 锁定整个表的所有间隙和记录
    • 包括：(-∞, 1], (1, 2], (2, 3], (3, 4], (4, +∞)
    • 实际上相当于锁全表，阻止任何插入操作

  4. 最终处理：

    • 对满足条件的记录（age>10）进行实际修改
    • 但锁定范围是整个表

事务A读取过程：

  1. 使用MVCC读取事务开始时的数据快照
  2. 不加任何锁
  3. 看到的是事务开始时的已提交数据版本
  4. 保证可重复读，即使事务B修改了数据

可重复读隔离级别下，事务A查询，事务B修改age大于10的数据，age上有索引，加锁过程什么样？

事务B加锁过程：

  1. 表级IX锁（意向排他锁）

  2. 索引扫描过程中的锁行为：

    • 通过idx_age索引定位age>10的记录
    • 对所有扫描到的索引范围加临键锁（Next-Key Lock）
    • 包括匹配的记录和它们之间的间隙

  3. 具体锁定范围（在idx_age索引上）：

    • 锁定age>10的所有记录和间隙
    • 具体范围：(8, 15], (15, 20], (20, +∞)
    • 同时对应的主键记录也会加记录锁

  4. 防止幻读：

    • 间隙锁阻止在age>10范围内插入新记录
    • 保证事务执行期间查询结果一致

事务A读取过程：

  1. 使用MVCC读取事务开始时的数据快照
  2. 不加任何锁
  3. 看到的是事务开始时的已提交数据版本
  4. 保证可重复读和防止幻读

可重复读隔离级别下，事务A和事务B同时插入，加锁过程什么样？

事务A插入过程：

  1. 表级IX锁（意向排他锁）

  2. 插入意向锁（Insert Intention Lock）：

    • 在目标间隙上加插入意向锁
    • 与现有的间隙锁兼容，但与其他插入意向锁可能冲突

  3. 重复键检查：

    • 对插入位置加共享锁检查唯一性

  4. AUTO-INC锁（如果使用自增主键）：

    • 在可重复读下，通常使用较轻量的自增锁机制
    • 取决于innodb_autoinc_lock_mode设置

  5. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）

事务B插入过程：

  1. 表级IX锁（意向排他锁）

  2. 插入意向锁（Insert Intention Lock）：

    • 在目标间隙上加插入意向锁

  3. 重复键检查：

    • 对插入位置加共享锁检查唯一性

  4. AUTO-INC锁：

    • 自增主键的锁管理

  5. 实际插入：

    • 对新插入的记录加行级X锁（排他记录锁）

关键点：

  • 两个事务插入不同间隙时，不会相互阻塞
  • 两个事务插入相同间隙的不同位置时，插入意向锁兼容，可以并发插入
  • 两个事务插入完全相同的主键时，第二个事务会在重复键检查时被阻塞
  • 插入意向锁的主要作用是提高并发插入性能

可重复读 vs 读已提交的关键区别

特性可重复读读已提交
MVCC快照事务级快照语句级快照
间隙锁使用间隙锁防止幻读不使用间隙锁（允许幻读）
临键锁默认使用临键锁只使用记录锁
防止幻读✅ 是❌ 否
防止不可重复读✅ 是❌ 否
并发性能较低（锁范围大）较高（锁范围小）

所有可重复读场景的共同点：

  • 使用临键锁作为默认锁算法
  • 普通SELECT使用MVCC，不加锁
  • 写操作使用间隙锁防止幻读
  • 通过插入意向锁优化并发插入性能
avatar
文章
阅读
粉丝