InnoDB秘籍：MVCC机制与行锁的深度探索（3）
03 Innodb 锁机制 锁用来控制多个并发的进程或线程对共享资源的访问，在 MySQL 数据库中，共享资源包括： 内存中的链表结构，如会话列表、活跃事务列表、InnoDB Buffer Pool 中 LRU 链表、脏页 Flush 链表等等。 元数据，包括表、SCHEMA、存储过程等。 表和表中的记录。 1 两阶段锁协议 Exploring Two-Phase Locking (2PL) 指事务加锁和解锁分为两个阶段，事务持续期间持有的所有锁，在事务提交的时候释放。 如下图，库存是一个共享资源，可能会有多个用户同时更新库存，而用户账户一般不会并发锁定。当需要获取多组锁的时候，一般会把热点表放在事务最后，这样可以减少锁的时间，显著提高吞吐量。 2 表锁 只加 MySQL 引擎层，元数据锁的语法： 释放锁的语法： 解锁操作，只能释放自己当前会话持有的锁，不能解开其他会话持有的表锁。直接结束会话，该会话所持有的锁会释放。 3 共享锁与排它锁 InnoDB 引擎行锁有两种模式，分别为 共享锁(S) 和 排他锁(X)。 共享锁(S)：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁。 排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。 4 意向锁 MySQL 支持两种粒度的锁，分别是行锁和表锁，意向锁是用来解决两种粒度锁共存问题。 假设一个场景：有两个事务 A 和 B，事务 A 锁住了表中的一行记录，加了行锁 S，既这一行只能读不能写。之后事务 B 想要申请整张表的写锁，那么理论上事务 B 可以修改表中的任何一行数据，包括事务 A 锁定的那行记录，这种情况下是和事务 A 的行锁冲突的。所以加表锁前，需要有一种机制来检测当前表中是否有行锁。如果加表锁前，去判断表中每行记录是否被锁定的话，这种代价非常大，而且随着表中数据量的增长，代价越来越大，显然是不可以接受的。所以就有了意向锁。 事务 A 先申请表的共享意向锁，成功后再申请一行的行锁。 事务 B 要申请表级写锁的话，发现事务 A 已经持有意向锁，那么就会进入等待。 意向锁不会阻止除全表请求之外的任何内容（例如，LOCK TABLES … WRITE）意向锁的主要目的是表明有人正在锁定一行，或者将要锁定表中的一行。 5 记录锁 InnoDB 记录锁的模式跟事务的隔离级别、执行的 SQL 语句、语句的执行计划都有关系。 在 RC 隔离级别下，允许幻读发生，所以不需要 Gap 锁，一般只锁定行。本文通过几个案例讨论在 RR 级别下 InnoDB 的加锁规则。 6 Gap 锁 7 Next-Key 锁 如上方查询，整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。 InnoDB 引擎行锁是加在索引上的，Next-key lock 是 gap 锁与行锁的组合。由上图，Next-key lock 是索引记录锁加上索引记录之间的间隙锁。 8 半一致性读取 Update 或 Delete 在执行的时候，会根据表中的索引情况扫描定位数据，在 RR 隔离级别下，扫描到的数据都会加锁，直到会话结束后释放，所以过滤字段无索引的 update 扫描，几乎锁定了整张表。如果有二级索引，通过索引定位数据，扫描数据的范围变小，锁定的范围也变小了。 在 RC 隔离级别下，扫描到的记录也会先加锁，但确认是不匹配的行后，会主动释放掉锁。 接下来通过实验，创建测试表并填充数据： 当前隔离级别为 RR，开启一个 Session 会话，执行如下 SQL： 由于 b 字段无索引，所以通过主键索引进行扫描定位记录，查询锁持有情况： 在 RR 隔离级别下，由于 b 字段无索引，Update 会为读取的每一行数据都上锁，并且不会释放。如果过滤字段有二级索引，那么只会锁定匹配的记录，以及防止幻读的 Next-Key 锁。 接下来，在 RC 隔离级别下，复现一次上面的实验： 查看锁持有情况： 在 RC 隔离级别下，由于不符合过滤条件的行会被快速释放锁，所以相比 RR 隔离级别，锁的持有时间更短，锁的冲突也更少。除此之外，在 RC 隔离级别下，对于 Update 语句，如果某行已经被锁定，则 InnoDB 将执行 “半一致性读”，将最新提交的版本返回给 MySQL，用于判断是否匹配 where 条件，如果匹配的话，表示必须更新改行，那么会再次读取改行，这次 InnoDB 要锁定它，或者等待它持有的锁释放。 继续刚才的实验，目前 b = 1 的记录已经被锁定，且 b 字段无索引，需要通过主键索引进行记录匹配，主键索引已经有记录被锁定了，此时会用到半一致性读取，读取锁定行的版本，用于判断是否匹配过滤条件，无需等待锁定行的锁释放。 9 可重复读的锁定案例 下方 SQL 是本次实验实验的表结构： Session 1：通过主键字段访问一行不存在的记录。 Session 2：尝试向间隙插入一条记录，被堵塞。 Session 3：尝试修改 id = 10 的记录，发现是可以执行成功。 Session 1 访问了一个不存在的 id = 7 记录，会锁定 (5, 10] 的间隙，由于 id = 10 不符合过滤条件，Next-key lock 退化为间隙锁 (5, 10) ，所以 id = 10 的行可以正常被更新。 下方两条查询得到的结果都相同，但是获取的锁不同。等值查询只加一个行锁。 范围查询则会额外添加一个间隙锁，锁定的范围是 (10, 15)。 普通索引查询，由于没有唯一性约束，在扫描的时候都会向右再多查一个记录，所以 (5, 10) 的间隙也被锁住。 Session 1：通过二级索引 c 等值查询。 Session 2：试探一下间隙锁，此时锁住的间隙是 (-∞, 5]，(5, 10)。 Session 3：通过主键更新一条记录，此时发现是可以执行成功的。 由于 Session 1 无需回表，只查询索引即可。主键上没有任何锁，所以 Session 2 才能正常更新。 如果是当前读，InnoDB 也会把主键锁定。因为当前读一般用于 DML 意味着要更新数据，所以即使没有访问到主键，也会锁定主键。 Session 1：非唯一索引范围查询。 Session 2：插入一条记录，由于间隙锁被堵塞。 Session 3：尝试更新一条记录，发现 Next-key lock。 InnoDB 搜索过程中，访问到的对象会加锁。首先 SQL 会通过 c = 10 使用 Next-key lock 锁定 (5, 10]，由于是非唯一索引，需要扫描到 C = 15 的位置才会结束扫描，所以 (10, 15] 区间也被锁住。 新插入一条记录，此时表中的数据如下。 Session 1：通过索引 c = 10 当前读两条记录。 InnoDB 会先定位 c = 10 条件，然后锁定 c 索引上 (5, 10] 间隙，由于是二级索引，需要向右再扫描 c = 15 这行结束，索引中 (10, 15] 被锁定，由于是等值查询，Next-key lock 退化为 Gap 锁，最终锁定的范围是 (5, 10)、(10, 15)。 Session 2：尝试向间隙中插入一条记录。 Session 3：更新一条记录，可以正常执行，验证了 Next-key 退化为 Gap 锁的推论。 与案例四，形成对比，二级索引范围扫描与二级索引等值扫描，都需要先前额外扫描一条记录，区别是范围扫描会使用 Next-key 锁定该条记录，等值查询则不会。 Session 1：开启一个事务，使用与案例五相同的 SQL，区别是添加了 limit 2。 Session 2：尝试插入数据，执行成功，案例的区别就是 limit。 由于加了 limit，扫描到第二行记录就结束了，所以相比于案例五，没有加 (10, 15) 的间隙锁，因为没有继续向右扫描。由此可见，给 SQL 添加 limit 可以减小加锁的范围。 Session 1：二级索引等值查询，锁定的范围是 (5, 10]，(10, 15)。 Session 2：二级索引更新一条记录，需要给 (5, 10] 范围加 x 锁，由于 Session 1 持 s 锁，出现堵塞情况。 Session 1：事务内，再插入一条记录，竟然出现死锁。 Session 2： 由此案例，可以看出 Next-key lock 是 Gap lock 与行锁的组合。Session 2 虽然被堵塞，其实 Gap lock 已锁定 (5, 10)，只是在等待 c = 10 的行锁。所以 Session 1 插入的时候，系统检测出了死锁情况。 10 死锁案例 死锁是指不同事务无法继续进行的情况，因为每个事务都持有另一个事务需要的锁。因为两个事务都在等待资源变得可用，所以都不会释放它所持有的锁。 死锁的等待关系如图，死锁日志中，只提供了当前堵塞的 SQL 语句，无法得到完整的事务 SQL，所以出现死锁往往需要找研发了解业务逻辑，事务内的其他 SQL 语句。 现在模拟一个业务真实遇到的死锁场景，一个批量刷新商品展示图片的状态信息，业务使用的是主键多线程更新。使用之前 test_semi 表来模拟当时的业务情况。 事务 1，按照主键字段，批量更新。 事务 2，更新了事务 1 已经锁定的记录。 事务 1，此时还是持续更新，更新的行是事务 2 已经锁定的记录。 事务 2 被回滚，事务 1 执行成功。 场景描述：该业务场景是一个更新一个商品的展示图片的状态场景，一个商品有多张图片，更新时都放在一个事务中。任务是从上游 kafka 生产的，可能由于意料之外的场景，出现了一个商品被重复消费的情况，就造成上了上方的死锁问题。 04 metadata lock MySQL 中还有一种特殊的表锁，MDL(metadata lock) 元数据锁，是在 MySQL Server 加的一种锁，其作用是维护 DDL 变更数据的安全性和正确性。 当对一个表进行 DML 时，需要加 MDL 读锁，当需要对一张表结构进行变更时，需要加 MDL 写锁。读锁之间不互斥，即可以多个线程对一张表进行并发增删改。读写锁与写锁，之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。 从上面我们可以了解到，读操作与写操作，都需要加 MDL 读锁，而 DDL 需要加 MDL 写锁，两者互斥的，那么 Online DDL 如何保障并发 DML 呢，这里就要介绍 Online DDL 加锁过程： 首先，在开始进行 DDL 时，需要拿到对应表的 MDL 写锁，然后进行一系列的准备工作； 然后MDL 写锁降级为MDL 读锁，开始真正的 DDL； 最后再次将 MDL 读锁升级为 MDL 写锁，完成 DDL 操作，释放 MDL 锁； 其中第二阶段占用了 DDL 整个过程的大量时间，在这段时间 DDL 才是真正的 online。 如果有一个大查询持有 MDL 读锁，那么我们此时进行一个 DDL 操作后，因为查询持有读锁，DDL 需要加写锁，所以变更必须等待查询结束才能进行，此时再进行一个查询就会被卡住，请看案例。 Session 1 Session 2 Session 3 Session 4 begin;select * from sbtest1 limit 5; select * from sbtest1 limit 5; alter table sbtest1 drop column fantasy ,ALGORITHM=INPLACE, LOCK=NONE; ❌ select * from sbtest1 limit 5; ❌ Session 1：开启一个事物，执行一条查询，此时 Session 1 持有 MDL 读锁； Session 2：也执行一条查询，执行一条查询，正常执行； Session 3：执行一条 DDL 语句，因为需要 MDL 写锁 被 Session 1 读锁； Session 4：执行一条查询，因为需要读锁，但是因为 Session 3 也处于等待状态，后面的增删改查都处于等待状态。也就是说这张表完全不可用了，连查询也被锁定了。 后记-- 开启事务begin;-- 更新一行数据update test_semi set c = 1 where a = 10;-- 查看锁持有情况+-------------+------------+-----------+---------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_data |+-------------+------------+-----------+---------------+-----------+| test_semi | NULL | TABLE | IX | NULL || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 |+-------------+------------+-----------+---------------+-----------+-- 再更新一行update test_semi set c = 1 where a = 11;-- 查看锁持有情况+-------------+------------+-----------+---------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_data |+-------------+------------+-----------+---------------+-----------+| test_semi | NULL | TABLE | IX | NULL || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 11 |+-------------+------------+-----------+---------------+-----------+-- 提交或回滚后，释放所有的锁commit;
# 关闭隐式提交set autocommit = 0;# 给 test_semi 加上表锁lock table test_semi read;# 查看 InnoDB 层锁模式，表锁的 lock_type = TABLE，锁的模式可以分为 S 读锁 X 写锁。+--------+---------------+-------------+-----------+-----------+| engine | object_schema | object_name | lock_type | lock_mode |+--------+---------------+-------------+-----------+-----------+| INNODB | test | test_semi | TABLE | S |+--------+---------------+-------------+-----------+-----------+# 查看 MySQL 层表锁 - MDL 元数据锁，可分为 SHARED_NO_READ_WRITE 与 SHARED_READ_ONLY+-------------+----------------+------------------+-------------+| object_type | object_name | lock_type | lock_status |+-------------+----------------+------------------+-------------+| TABLE | test_semi | SHARED_READ_ONLY | GRANTED || TABLE | metadata_locks | SHARED_READ | GRANTED |+-------------+----------------+------------------+-------------+
FLUSH TABLES test_semi WITH READ LOCK;
# 会释放掉该会话持有的所有表锁unlock tables;
-- 当前读，锁定一个范围select * from test_semi where a < 11 for update;+----+------+------+| a | b | c |+----+------+------+| 10 | 1 | 2 |+----+------+------+-- 查看锁持有情况+--------+---------------+-------------+-----------+-----------+-----------+| engine | object_schema | object_name | lock_type | lock_mode | lock_data |+--------+---------------+-------------+-----------+-----------+-----------+| INNODB | test | test_semi | TABLE | IX | NULL || INNODB | test | test_semi | RECORD | X | 10 || INNODB | test | test_semi | RECORD | X,GAP | 11 |+--------+---------------+-------------+-----------+-----------+-----------+-- 写入一条记录，a = 6insert into test_semi value(6, 1, 2);
select * from test_semi where a < 6 for update;-- 此时的间隙锁，加在 10～-∞ 间隙+--------+---------------+-------------+-----------+-----------+-----------+| engine | object_schema | object_name | lock_type | lock_mode | lock_data |+--------+---------------+-------------+-----------+-----------+-----------+| INNODB | test | test_semi | TABLE | IX | NULL || INNODB | test | test_semi | RECORD | X,GAP | 10 |+--------+---------------+-------------+-----------+-----------+-----------+-- 此时再次查询一行不存在的记录select * from test_semi where a = 8 for update;-- 10～-∞ 又多了一把间隙锁+--------+---------------+-------------+-----------+-----------+-----------+| engine | object_schema | object_name | lock_type | lock_mode | lock_data |+--------+---------------+-------------+-----------+-----------+-----------+| INNODB | test | test_semi | TABLE | IX | NULL || INNODB | test | test_semi | RECORD | X,GAP | 10 || INNODB | test | test_semi | TABLE | IX | NULL || INNODB | test | test_semi | RECORD | X,GAP | 10 |+--------+---------------+-------------+-----------+-----------+-----------+select * from t for update;+----+------+------+| id | c | d |+----+------+------+| 0 | 0 | 0 || 5 | 5 | 5 || 10 | 10 | 10 || 15 | 15 | 15 || 20 | 20 | 20 || 25 | 25 | 25 |+----+------+------+-- 锁的模式+--------+---------------+-------------+-----------+-----------+------------------------+| engine | object_schema | object_name | lock_type | lock_mode | lock_data |+--------+---------------+-------------+-----------+-----------+------------------------+| INNODB | test | t | TABLE | IX | NULL || INNODB | test | t | RECORD | X | supremum pseudo-record || INNODB | test | t | RECORD | X | 0 || INNODB | test | t | RECORD | X | 5 || INNODB | test | t | RECORD | X | 10 || INNODB | test | t | RECORD | X | 15 || INNODB | test | t | RECORD | X | 20 || INNODB | test | t | RECORD | X | 25 |+--------+---------------+-------------+-----------+-----------+------------------------+
CREATE TABLE `test_semi` ( `a` int NOT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciinsert into test_semi values(10, 1, 0),(11, 2, 0),(12,1,0),(13,2,0),(14,1,0);begin;update test_semi set c = 22 where b = 1;
+-------------+------------+-----------+-----------+------------------------+| object_name | index_name | lock_type | lock_mode | lock_data |+-------------+------------+-----------+-----------+------------------------+| test_semi | NULL | TABLE | IX | NULL || test_semi | PRIMARY | RECORD | X | supremum pseudo-record || test_semi | PRIMARY | RECORD | X | 10 || test_semi | PRIMARY | RECORD | X | 11 || test_semi | PRIMARY | RECORD | X | 12 || test_semi | PRIMARY | RECORD | X | 13 || test_semi | PRIMARY | RECORD | X | 14 |+-------------+------------+-----------+-----------+------------------------+
+-------------+------------+-----------+---------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_data |+-------------+------------+-----------+---------------+-----------+| test_semi | NULL | TABLE | IX | NULL || test_semi | idx_b | RECORD | X | 1, 10 || test_semi | idx_b | RECORD | X | 1, 12 || test_semi | idx_b | RECORD | X | 1, 14 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 12 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 14 || test_semi | idx_b | RECORD | X,GAP | 2, 11 |+-------------+------------+-----------+---------------+-----------+
begin;update test_semi set c = 22 where b = 1;
+-------------+------------+-----------+---------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_data |+-------------+------------+-----------+---------------+-----------+| test_semi | NULL | TABLE | IX | NULL || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 12 || test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 14 |+-------------+------------+-----------+---------------+-----------+
-- 另一个 Session 再开启一个事务begin;update test_semi set c = 22 where b = 2;-- 查询select * from test_semi;+----+------+------+| a | b | c |+----+------+------+| 10 | 1 | 0 || 11 | 2 | 6 || 12 | 1 | 0 || 13 | 2 | 6 || 14 | 1 | 0 |+----+------+------+CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);案例一：唯一索引等值查询不存在的记录
begin;select * from t where id = 7 for update;
insert into t value (8, 8, 8);-- blocked
+--------+---------------+-------------+-----------+------------+-----------+-----------+| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |+--------+---------------+-------------+-----------+------------+-----------+-----------+| INNODB | test | t | TABLE | NULL | IX | NULL || INNODB | test | t | RECORD | PRIMARY | X,GAP | 10 |+--------+---------------+-------------+-----------+------------+-----------+-----------+
案例二：唯一索引范围扫描
select * from t where id = 10 for update;+--------+---------------+-------------+-----------+------------+---------------+-----------+| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |+--------+---------------+-------------+-----------+------------+---------------+-----------+| INNODB | test | t | TABLE | NULL | IX | NULL || INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 10 |+--------+---------------+-------------+-----------+------------+---------------+-----------+
select * from t where id >= 10 and id < 11 for update;+--------+---------------+-------------+-----------+------------+---------------+-----------+| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |+--------+---------------+-------------+-----------+------------+---------------+-----------+| INNODB | test | t | TABLE | NULL | IX | NULL || INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 10 || INNODB | test | t | RECORD | PRIMARY | X,GAP | 15 |+--------+---------------+-------------+-----------+------------+---------------+-----------+
案例三：非唯一索引等值覆盖查询
+----+------+------+| id | c | d |+----+------+------+| 0 | 0 | 0 || 5 | 5 | 6 || 10 | 10 | 11 || 15 | 15 | 15 || 20 | 20 | 20 || 25 | 25 | 25 |+----+------+------+
begin;select id from t where c = 5 for share;
insert into t value(6, 6, 6);-- blocked
update t set d = d + 1 where id = 5;
+--------+---------------+-------------+-----------+------------+-----------+-----------+| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |+--------+---------------+-------------+-----------+------------+-----------+-----------+| INNODB | test | t | TABLE | NULL | IS | NULL || INNODB | test | t | RECORD | c | S | 5, 5 || INNODB | test | t | RECORD | c | S,GAP | 10, 10 |+--------+---------------+-------------+-----------+------------+-----------+-----------+
+--------+---------------+-------------+-----------+------------+---------------+-----------+| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |+--------+---------------+-------------+-----------+------------+---------------+-----------+| INNODB | test | t | TABLE | NULL | IX | NULL || INNODB | test | t | RECORD | c | X | 5, 5 || INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 5 || INNODB | test | t | RECORD | c | X,GAP | 10, 10 |+--------+---------------+-------------+-----------+------------+---------------+-----------+
案例四：非唯一索引范围扫描
select * from t where c >= 10 and c < 11 for update;+----+------+------+| id | c | d |+----+------+------+| 10 | 10 | 11 |+----+------+------+
insert into t value(8, 8, 8);-- blocked
update t set d = d + 1 where c = 15;
案例五：非唯一索引等值查询
+----+------+------+| id | c | d |+----+------+------+| 0 | 0 | 0 || 5 | 5 | 5 || 10 | 10 | 10 || 15 | 15 | 15 || 20 | 20 | 20 || 25 | 25 | 25 || 30 | 10 | 30 |+----+------+------+
begin;select * from t where c = 10 for update;
insert into t value(12, 12, 12);-- blocked
update t set d = d + 1 where c = 15;
案例六：limit 语句加锁
begin;select * from t where c = 10 limit 2 for update;
insert into t value(12, 12, 12);Query OK, 1 row affected (0.01 sec)
案例七：Next-key lock 实现方式
begin;select * from t where c = 10 for share;
update t set d = d + 1 where c = 10;
insert into t value(8, 8, 8);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
案例一：多线程批量更新场景
begin;update test_semi set b = 0 where a = 11;update test_semi set b = 0 where a = 12;
begin；update test_semi set b = 0 where a = 13;update test_semi set b = 0 where a = 12;
update test_semi set b = 0 where a = 13;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
本文介绍了 InnoDB 引擎是如何应对事务并发和事务安全两种挑战的，首先从宏观角度介绍事务面临的挑战和背景，然后分别引入 ACID 模型和隔离级别以及 InnoDB 核心模块。最后通过大量锁定案例，介绍 InnoDB 引擎的加锁方式。
