Quartz 5

MySQL 数据库 11（Day59）

Properties1
tags来源, 数据库, mysql, innodb, 锁, mvcc, 事务, data

9分钟阅读

MySQL 数据库 11（Day59）

主题：InnoDB 锁索引原理、Record Lock/GAP Lock/Next-Key Lock、不同索引和查询条件下的锁行为、死锁分析、事务隔离级别与 MVCC

结构总览

MySQL 数据库 11
  ├─ 锁机制复习与并发读现象
  ├─ InnoDB 锁索引树而非抽象数据行
  ├─ Record Lock、Gap Lock、Next-Key Lock
  ├─ 唯一/非唯一索引与等值/范围查询的锁行为
  ├─ 交叉锁定、辅助索引与死锁
  └─ 事务隔离级别、快照读、当前读与 MVCC

关键要点

InnoDB 锁定索引

InnoDB 的行级锁实际作用于索引记录和索引间隙。数据行由索引树组织，因此锁定索引访问路径就能控制对应数据的并发访问。

InnoDB 的锁 → 索引树 → 对应数据记录

主键查询通常直接锁定聚集索引；命中辅助索引时，InnoDB 需要沿辅助索引定位，并根据主键访问聚集索引。有索引不等于一定命中索引：函数运算、隐式类型转换、前置 %、不满足联合索引最左前缀等情况都可能使优化器无法有效使用该索引。

应通过 EXPLAIN 检查实际访问路径。未命中合适索引时，InnoDB 可能扫描并锁定大量记录，锁范围显著扩大；具体锁模式仍需结合索引、隔离级别、查询类型和执行计划分析，不能简单把所有情况概括成传统意义上的“表锁”。

三种行锁算法

算法含义主要作用
Record Lock记录锁，只锁定索引记录本身精确保护匹配行
Gap Lock间隙锁，只锁定索引记录之间的间隙阻止其他事务在间隙插入
Next-Key Lock临键锁，Record Lock + Gap Lock同时锁定记录及其索引间隙，抑制幻读

假设索引值为 1、3、5、8、11，索引区间可理解为：(-∞,1]、(1,3]、(3,5]、(5,8]、(8,11]、(11,+∞)。实际锁区间与边界还需结合 MySQL 版本、隔离级别和优化器执行计划确认。

索引类型与查询条件

以 InnoDB、加锁读和常见 REPEATABLE READ 语境为例，可用下表理解典型规律：

索引与条件常见锁算法说明
唯一索引 + 等值命中Record Lock精确命中已存在记录时通常只锁记录
唯一索引 + 范围查询Next-Key/GAP 等范围锁需要保护扫描范围，具体边界依执行计划而定
非唯一索引 + 等值查询Next-Key Lock 等范围锁除匹配记录外还需关注相邻间隙
非唯一索引 + 范围查询Next-Key Lock锁定匹配记录及相关范围
未使用合适索引锁范围可能扩大可能扫描大量记录，不宜简单等同为固定表锁
-- 唯一索引等值查询：通常是精确记录锁
SELECT * FROM t1 WHERE id = 5 FOR UPDATE;
 
-- 非唯一索引等值查询：常涉及记录及间隙
SELECT * FROM t1 WHERE name = 'egon' FOR UPDATE;
 
-- 范围查询：锁定范围内的索引记录和间隙
SELECT * FROM t1 WHERE id > 3 AND id < 10 FOR UPDATE;

索引设计会影响锁竞争：高选择性的唯一索引有助于缩小访问和锁定范围；更新条件缺少索引时，大范围扫描可能降低并发能力。

死锁

死锁是多个事务互相持有对方需要的锁，形成循环等待。最典型的交叉访问场景：

-- 事务一先锁 id=1，再请求 id=5
BEGIN;
SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
UPDATE t1 SET name = 'EGON' WHERE id = 5;
 
-- 事务二先锁 id=5，再请求 id=1
BEGIN;
SELECT * FROM t1 WHERE id = 5 FOR UPDATE;
DELETE FROM t1 WHERE id = 1;

辅助索引也可能增加锁依赖：事务沿辅助索引定位后还要访问聚集索引，在高并发且访问顺序不一致时可能形成循环等待。InnoDB 通常会检测死锁，选择回滚其中一个事务，让其他事务继续；应用应捕获死锁错误并重试完整事务。

降低死锁风险：

  • 多个事务按统一顺序访问记录和表。
  • 缩小事务范围，尽快提交或回滚。
  • 为查询和更新条件建立合适索引。
  • 尽量使用选择性更高、可精确定位的索引。
  • 避免在事务中执行无关的网络或长时间业务操作。
  • 使用死锁日志和锁等待信息定位实际阻塞关系。
SHOW ENGINE INNODB STATUS\G;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_TRX;

隔离级别与 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制） 通过 Undo Log 保存记录的历史版本，使一致性读在很多场景下不必等待写锁。

读类型读取内容是否通常加锁
快照读 Snapshot Read按事务可见性规则读取历史版本不加锁的一致性读
当前读 Current Read读取最新版本通常需要加锁，如 FOR UPDATE、UPDATE、DELETE

典型隔离级别与机制：

隔离级别脏读不可重复读幻读控制方式
READ UNCOMMITTED可能可能控制最弱
READ COMMITTED避免可能快照读 + 当前读/锁分析
REPEATABLE READ避免避免快照读下的重复读取当前读结合 Next-Key Lock；快照读依赖 MVCC 视图
SERIALIZABLE避免避免读操作也采用更强的锁控制
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- 快照读
SELECT * FROM t1 WHERE id = 5;
 
-- 当前读
SELECT * FROM t1 WHERE id = 5 FOR UPDATE;

需要区分两个层面：MVCC 主要解决一致性快照读与并发写之间的读隔离；当前读需要看到最新数据，因此会使用锁；REPEATABLE READ 下的幻读控制还依赖当前读的间隙/临键锁。实际结果必须结合事务启动时机、SQL 类型、索引和版本进行实验。

当日总结

  • InnoDB 的锁依赖索引访问路径；缺少合适索引会使扫描和锁定范围扩大。
  • Record Lock 锁记录，Gap Lock 锁间隙，Next-Key Lock 锁记录及间隙。
  • 唯一索引等值命中通常最容易获得精确记录锁，非唯一索引和范围查询通常涉及更大的范围锁。
  • 死锁源于循环等待；统一访问顺序、缩短事务和合理建索引可以降低风险。
  • InnoDB 会检测并回滚死锁中的一个事务，应用需要做好重试。
  • MVCC 通过 Undo Log 支持快照读；当前读读取最新版本并通常加锁。
  • 隔离级别、索引、读类型和 MySQL 版本都会影响最终锁行为，应以执行计划和并发实验为准。

相关页面

最近更新

关系图谱

反向链接