InnoDB 事务处理机制

文章目录

- 前言
- 1. 事务处理挑战
- - 1.1 事务机制处理的问题
  - 1.2 并发事务带来的问题
- 2. InnodDB 和 ACID 模型
- - 2.1 Innodb Buffer Pool
  - 2.2 Redo log
  - 2.3 Undo log
  - 2.4 应用案例
- 3. 隔离级别和锁机制
- - 3.1 事务隔离级别
  - - 3.1.1 READ UNCOMMITTED
    - 3.1.2 READ COMMITTED
    - 3.1.3 REPEATABLE READ
    - 3.1.4 SERIALIZABLE
  - 3.2 多版本并发控制
  - - 3.2.1 两种读取方式
    - 3.2.2 Read View
  - 3.3 Innodb 锁机制
  - - 3.3.1 两阶段锁协议
    - 3.3.2 表锁
    - 3.3.3 共享锁与排它锁
    - 3.3.4 意向锁
    - 3.3.5 记录锁
    - 3.3.6 Gap 锁
    - 3.3.7 Next-Key 锁
    - 3.3.8 半一致性读取
    - 3.3.9 可重复读的锁定案例
    - - 案例一：唯一索引等值查询不存在的记录
      - 案例二：唯一索引范围扫描
      - 案例三：非唯一索引等值覆盖查询
      - 案例四：非唯一索引范围扫描
      - 案例五：非唯一索引等值查询
      - 案例六：limit 语句加锁
      - 案例七：Next-key lock 实现方式
    - 3.3.10 死锁案例
    - - 案例一：多线程批量更新场景
  - 3.4 metadata lock
- 后记

前言

事务的起源可以追溯到 6000 年以前，当时苏美尔人（Sumerians）就发明了事务处理和记录方法。已知最早的记录是写在土块上的，上面记录的是皇家的税收、土地、谷物、牲畜、奴隶和黄金，明确地记下了每笔交易。这种早期的系统已经具备了事务处理的关键特征。记录使用的土块和记号方式就是数据库，每次土块的加入都会造成 数据状态的变化，在今天可以称为事务。
在这里插入图片描述

PS：事务是数据库关系系统中的一系列操作的一个逻辑单位。

这种基于土块的事务处理系统的技术演变了数千年，经历了纸莎草纸、羊皮纸、最后到我们现在用的纸。在 19 世纪后期，出现了打孔卡片计算机系统，应用到 1890 年美国人口普查的数据存储和记录。20 世纪前半叶，对事务处理的需求促进了打孔卡片计算机的发展，提升了查询和更新速度，1 秒可以处理一张卡片，这种系统被应用于库存控制和记账。

直到 20 世纪后半叶事务处理出现了重大的进展，基于磁性存储介质的成批事务处理以及随后基于电子存储和计算机网络的联机事务处理。事务处理的速度成几何倍增长，这两种技术极大的促进了计算机工业的发展。
在这里插入图片描述

今天，事务处理被应用于各个核心领域，典型使用场景包括：银行交易、电子订单、火车预定、股票交易、自动收款、作业和存货规划、记账等。随着事务技术的发展和被广泛应用，对事务处理的性能和安全的要求也越来越高。数据库系统中事务处理机制，要在满足性能的前提下，保证用户的数据操作动作对数据是安全的。

本篇文章，将介绍 MySQL InnoDB 引擎中的事务处理机制，探究它是如何保障用户数据操作的安全。

1. 事务处理挑战

在这里插入图片描述

1.1 事务机制处理的问题

上面提到事务处理机制，是为了保证用户对数据库的操作对数据是安全的。如何保障事务安全呢？在 1970 年 IBM 研究员发表了《关系模型的数据库管理系统》论文，首次提出 ACID 事务模型。数据只有在带有 ACID 四个特性的事务处理机制的保障下，才可以认为是安全的。

原子性（Atomicity）：事务被视为一个原子操作，要么全部执行成功，要么全部执行失败，如果事务中的任何一部分操作失败，那么整个事务将回滚到初始状态，不会留下部分执行的结果。
一致性（Consistency）：事务要保证数据库整体数据的完整性和业务数据的一致性，事务成功提交整体数据修改，事务错误则回滚到数据回到原来的状态。
隔离性（Isolation）：隔离性是说两个事务的执行都是独立隔离开来的，事务之前不会相互影响，多个事务操作一个对象时会以串行等待的方式保证事务相互之间是隔离的。
持久性（Durability）：一旦事务提交成功，其结果将永久保存在数据库中，并且对于系统故障或崩溃是持久的。即使在系统故障后进行恢复，数据库也能保持事务的持久性。

事务模型是商业世界运作的基石，主要体现在交易处理电子化。作为一个软件系统，数据库系统会遭遇一些故障，如我们常说的硬件故障、系统故障、介质故障等。对于数据库系统，因为数据实在太为重要，数据库系统应该能够保证在出现故障时，数据的流动依然满足 ACID 特性。

1.2 并发事务带来的问题

在 1990 年以前，数据库是非常简单的，隔离级别以及并发控制是没有标准的，用户需要处理无数的数据异常情况。为了解决这个问题，ASNI 美国国家标准学会推出 SQL-92 standard 基于并发带来的异象，首次尝试对隔离级别进行统一定义。

在这里插入图片描述

写在前，读在后：脏读；
读在前，写在后：不可重复读；
读在前，写在后，再读：幻读。

2. InnodDB 和 ACID 模型

事务 ACID 模型是一种数据库设计原则，InnoDB 引擎是 MySQL 默认且支持事务的存储引擎，它严格遵循 ACID 模型，结果也不会因软件崩溃和硬件故障等特殊情况而失真。依靠符合 ACID 标准的功能，就不需要重新设计一致性检查和崩溃恢复机制。当然，用户如果有额外的软件保障措施和超可靠的硬件，或者应用可容忍少量数据丢失或失去 ACID 模型保护，可根据 MySQL 提供的参数进行配置，从而换取更高的性能和更高的吞吐。

在 MySQL 中涉及 ACID 模型组件非常多，贯穿内存结构设计、磁盘结构设计以及事务和锁定模型等等。接下来，我们将介绍一些 InnoDB 引擎实现 ACID 模型的一些关键技术。

在这里插入图片描述

2.1 Innodb Buffer Pool

InnoDB 存储引擎将数据都存储在表空间中，表空间是存储引擎对文件系统上的一个文件或者几个文件的抽象。InnoDB 引擎在处理用户请求时，会把需要访问页面加载到内存中，页大小一般为 16k 即使只访问一个页面中的一行记录，依然需要加载整个页面。在访问结束后 InnoDB 引擎并不会立即释放掉页面，而是将其缓存起来，内存页面淘汰策略由 LRU 算法控制。下次需要访问时就不需要进行磁盘读，从而提高数据库的读写性能。这部分内存的大小由 innodb_buffer_pool_size 控制。

show variables like 'innodb_buffer_pool_size';

2.2 Redo log

为了取得更好的读写性能，InnoDB 会将数据缓存在内存中（InnoDB Buffer Pool）对磁盘数据的修改也会落后于内存，这时如果进程或机器崩溃，会导致内存数据丢失，为了保证数据库本身的一致性和持久性，InnoDB 维护了 Redo log。修改 Page 之前需要先将修改的内容记录到 Redo 中，并保证 Redo log 早于对应的 Page 落盘，也就是常说的 WAL（Write Ahead Log）日志优先写，Redo log 的写入是顺序 IO，可以获得更高的 IOPS 从而提升数据库的性能。当故障发生导致内存数据丢失后，InnoDB 会在重启时，通过重放 Redo，将 Page 恢复到崩溃前的状态。

在这里插入图片描述

2.3 Undo log

undo 日志是 innodb 引擎非常重要的部分，它的设计贯穿事务管理、并发控制和故障恢复等。当开启一个事务涉及到数据变动的时候，就会为事务分配 undo 段以 “逻辑日志” 的形式保存事务的信息，用于回滚。事务的回滚，分为三种情况：

用户事务管理 rollback 回滚。
锁等待及死锁回滚。
Crash Recovery 故障恢复回滚。

数据库在运行过程中，随时可能会因为某种情况异常中断，软件 bug、硬件故障运维操作等等，这个时候可能已经有未提交的事务有部分已经刷新到磁盘中，如果不加以处理会违反事务的原子性，要么全部提交，要么全部回滚。针对这种问题，直观的想法是事务在提交后再进行刷盘。也就是 no-steal 策略。显而易见，这种方式会造成很大的内存空间压力，另一方面事务提交时的随机 IO 会影响性能。

Innodb 事务处理采用的是 steal 策略，允许一个 uncommitted 的事务将修改刷新到磁盘。为了保障事务原子性，事务执行过程中会持续的写入 undo log 来记录事务变更的历史值。当 Crash 真正发生时，可以在 Recovery 过程中通过回放 Undo 将未提交事务的修改抹掉。

需要注意的是，因为 Innodb 事务执行过程中产生的 Undo log 也需要进行持久化操作，所以 Undo log 也会产生 Redo log。由于Undo log 的完整性和可靠性需要 Redo log 来保证，因此数据库崩溃时需要先做 Redo log 数据恢复，然后做 Undo log 回滚。

2.4 应用案例

MySQL 社区开源物理热备工具 Xtrabackup 就是基于 MySQL 故障恢复实现的。在 copy 数据文件阶段，会一直监听并备份 redo log。在备份恢复阶段 Xtrabackup 需要执行一个 prepare 命令，该阶段就是启动一个临时实例，依赖 innodb 恢复机制应用刚才备份的 redo 文件，相当于应用一遍备份期间的增量数据。

3. 隔离级别和锁机制

MySQL 5.7 及以上的版本，默认的隔离级别是 RR 可通过下方 SQL 查看或修改隔离级别。

-- 查看隔离级别
show variables like '%transaction_isolation%';

-- 修改全局隔离级别 global 替换为 session 表示修改当前会话隔离级别
set global transaction_isolation = 'READ-UNCOMMITTED' | 
    'READ-COMMITTED' |
    'REPEATABLE-READ' |
    'SERIALIZABLE';

ISO SQL标准基于事务是否可能出现的几种异象来定义隔离级别，4 种隔离级别的定义见下表：

级别	脏读	不可重复读	幻读
读未提交（READ-UNCOMMITTED）	✅	✅	✅
不可重复读（READ-COMMITTED）	❌	✅	✅
可重复读（REPEATABLE-READ）	❌	❌	✅
串行化（SERIALIZABLE）	❌	❌	❌

隔离级别越严格，事务的并发度就越小，因此很多时候，业务都需要在二者之间选择一个平衡点。

3.1 事务隔离级别

以下是测试隔离级别需要用的演示数据。

-- 创建测试表
create table account(
    id bigint auto_increment primary key ,
    name varchar(20) not null ,
    balance int not null
);
-- 插入测试数据
insert into account(name, balance) VALUES ('张三', 300),('李四', 400),('王五', 500);

3.1.1 READ UNCOMMITTED

RU 隔离级别也叫读未提交，会读取到其他会话中，未提交的事务修改的数据。

调整参数，修改数据库隔离级别为 RU。

set global transaction_isolation = 'READ-UNCOMMITTED';

Session 1：在 RU 隔离级别下，开启一个事务：

-- 开启事务
begin;
-- 查询数据
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     300 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2：在 RU 隔离级别下，开启一个事务：

-- 开启事务
begin;
-- 给张三账户余额加 1000000
update account set balance = balance + 100 where id = 1;

Session 1：查询张三余额：

-- 查询余额
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     400 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2：因为某种原因需要回滚事务：

rollback;

Session 1：张三余额减少 100：

-- 张三余额减少 100 并提交事务
update account set balance = balance - 100 where id = 1;commit;

-- 查询余额
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     200 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 1 中，查询张三余额为 400 时，然后对张三的余额减 100，事务提交后余额又变成了 200，因为程序中并不知道其他会话回滚过事务，这就是脏读的案例，一般生产环境没有理由使用 RU 作为隔离级别。

3.1.2 READ COMMITTED

RC 隔离级别也叫读已提交，事务中会读取到其他事务已提交到数据，这种隔离级别下会有不可重复读和幻读的问题。

调整参数，修改数据库隔离级别为 RC：

set global transaction_isolation = 'READ-COMMITTED';

Session 1：在 RC 隔离级别下，开启一个事务：

-- 开启事务
begin；
-- 查询数据
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     300 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2：开启一个事务，给张三余额加 100。

-- 开启事务
begin；
-- 修改张三余额
update account set balance = balance + 100 where id = 1;

Session 1：再次查询张三余额，发现余额依然为 300，在 RC 隔离级别下，不会读取到未提交到事务。

-- 查询数据
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     300 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2：提交事务：

commit；

Session 1：再次查询张三余额：

-- 查询数据
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     400 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

可以看到 Session 1 在事务中不同时刻读取了张三的余额，得到的结果不一样，这是因期间 Session 2 对张三的余额进行了改动并提交事务，产生不可重复读的问题。

3.1.3 REPEATABLE READ

RR 隔离级别也叫可重复读，指在一个事务内，无论何时查询到的数据都与开始查询到的数据一致，是 InnoDB 引擎默认的隔离级别，通过更严格的锁机制，一定程度上可以避免幻读。

调整参数，修改数据库隔离级别为 RR：

set global transaction_isolation = 'REPEATABLE-READ';

Session 1：在 RR 隔离级别下，开启一个事务：

-- 开启事务
begin;
-- 查询数据
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     300 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2：开启事务，给张三余额添加 100 并提交事务：

-- 开启事务
begin;
update account set balance = balance + 100 where id = 1;
commit;
-- 张三余额被修改为 400
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     400 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 1：查询表中数据：

select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     300 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2 的事务已经提交，Session 1 再次读取，前后的数据没有发生变化，不存在不可重复读的问题。

Session 1：为张三余额加 100 提交事务。

-- 为张三加 100
update account set balance = balance + 100 where id = 1;

-- 查询
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     500 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

发现最终张三的余额为 500，而不是 400 元，数据的一致性没有被破坏。RR 隔离级别下，使用了 MVCC 多版本并发控制的机制 Select 查询不会更新更新版本号，是快照读，而 DML 语句操作表中的数据，会更新版本号，是当前读。

Session 2：不显式开启事务，插入一条记录。

insert into account(name, balance) VALUES ('赵六', 600);

Session 1：再次查询。

select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     500 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
+----+--------+---------+

Session 2 已经插入一条记录并提交，Session 1 没有读取到 Session 2 插入的记录，没有发生幻读。

Session 1：更新赵六的记录，触发一次当前读：

-- 为赵六余额增加 100
update account set balance = balance + 100 where id = 4;
-- 再次查询
select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |     500 |
|  2 | 李四   |     400 |
|  3 | 王五   |     500 |
|  4 | 赵六   |     700 |
+----+--------+---------+

Session 1 未提交的状态下，对 Session 2 插入的新纪录触发一次当前读，行版本发生了更新，出现了幻读的情况。

3.1.4 SERIALIZABLE

SERIALIZABLE 可串行化隔离级别下，所有的读写操作都需要加锁，并发度很小，避免了幻读。

-- 设置隔离级别为 SERIALIZABLE
set global transaction_isolation = 'SERIALIZABLE';

Session 1：开启事务，读取一行数据。

select * from account where id = 4;

Session 2：尝试修改刚才读取的记录。

update account set balance = balance + 100 where id = 5;
-- 锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.2 多版本并发控制

数据库的核心方向就是高并发，Innodb 引擎通过并发控制技术来维护高并发的环境下数据的一致性和数据安全。Innodb 并发控制有两种技术方案锁机制(Locking) 和多版本并发控制 (MVCC) 。

并发场景分为四类 读-读、读-写、写-读、写-写 通过锁机制可以帮助我们解决 写-写 场景下的并发问题，而 MVCC 侧重优化 读-写 和 写-读 业务的高并发场景，可以解决写操作时堵塞读操作的并发问题。

Innodb 引擎使用 MVCC 机制来保障事务的隔离性，实现数据库的隔离级别。

3.2.1 两种读取方式

快照读(Snapshot Read) 也叫一致性非锁定读取，简称 “一致性读”。一种读操作，指 innodb 引擎通过多版本并发控制的方式来读取，使用 Read View 基于某个时间点呈现查询结果。读取正在进行 update 或 delete 操作的行，不会等待锁释放，而是会读取该行的快照数据。快照就是指该行之前的版本数据，主要靠 undo 日志来实现，而 undo 是用于数据库回滚，因此快照读本身是没有开销的。后台 Purge 线程也会自动清理一些不需要的 undo 数据。
当前读(Current Read) 如果 select 语句后加上 for share、for update，或者执行的是 update、delete、insert into t2 select from t1 这类语句（这几种类型的 SQL 需要读取最新版本的数据，被称为当前读）MySQL会根据 where 条件和 SQL 的执行计划，加上相应的锁，阻止其他会话修改满足过滤条件的记录。

3.2.2 Read View

Read View 是实现 MySQL 一致性读的一种机制，对于一条查询来讲，不同隔离级别可以读取到的版本是不同的。RC 和 RR 隔离级别在实现上最根本的区别就是创建 READ VIEW 的时间不一样。对于 RC 隔离级别的事务，每次执行一致性查询时，都会创建一个新的 Read View，这样 SELECT 就能获取系统中当前已经提交的最新数据。对于 RR 隔离级别的事务，在第一次执行一致性读取时创建 READ VIEW。事务中后续再次执行 SELECT 时，会重用已经创建好的 Read View，这样同一个 SQL 后续重复执行时就能获取到相同的数据。

3.3 Innodb 锁机制

锁用来控制多个并发的进程或线程对共享资源的访问。在MySQL数据库中，共享资源包括：

内存中的链表结构，如会话列表、活跃事务列表、InnoDB Buffer Pool 中 LRU 链表、脏页 Flush 链表等等。
元数据，包括表、SCHEMA、存储过程等。
表和表中的记录。

3.3.1 两阶段锁协议

Exploring Two-Phase Locking (2PL) 指事务加锁和解锁分为两个阶段，事务持续期间持有的所有锁，在事务提交的时候释放。

-- 开启事务
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;

3.3.2 表锁

InnoDB 引擎为用户提供 LOCK TABLE…READ/WRITE 语法，可以手动为表加上读锁或写锁。

# 关闭隐式提交
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     |
+-------------+----------------+------------------+-------------+

只加 MySQL 引擎层，元数据锁的语法：

FLUSH TABLES test_semi WITH READ LOCK;

释放锁的语法：

# 会释放掉该会话持有的所有表锁
unlock tables;

解锁操作，只能释放自己当前会话持有的锁，不能解开其他会话持有的表锁。直接结束会话，该会话所持有的锁会释放。

3.3.3 共享锁与排它锁

InnoDB 引擎行锁有两种模式，分别为共享锁(S) 和排他锁(X)。

3.3.4 意向锁

由于 InnoDB 引擎支持表和记录粒度的锁，
为什么需要意向锁？

3.3.5 记录锁

InnoDB 记录锁的模式跟事务的隔离级别、执行的 SQL 语句、语句的执行计划都有关系。

在 RC 隔离级别下，允许幻读发生，所以不需要 Gap 锁，一般只锁定行。本文通过几个案例讨论在 RR 级别下 InnoDB 的加锁规则。

3.3.6 Gap 锁

在 RR 或更高的隔离级别下，为了避免出现幻读，InnoDB使用了 GAP 锁，阻止其他事务往被锁定的区间内插入数据。

根据 3.1.3 小节的测试，在 RR 隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在 “当前读” 下才会出现。

-- 当前读，锁定一个范围
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        |
+--------+---------------+-------------+-----------+-----------+-----------+

不仅会锁住对应的记录，也会给记录之间的间隙加上 Gap 锁，此时如果执行一个写入操作会被堵塞。

-- 写入一条记录，a = 6
insert into test_semi value(6, 1, 2);

另外，间隙锁与间隙锁之间可以共存，一个事务不会阻止另一个事务给同一个间隙加间隙锁，因为它们的目标是共同保护一个间隙，不允许新插入值。

Session 1：查询一个不存在的范围。

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        |
+--------+---------------+-------------+-----------+-----------+-----------+

3.3.7 Next-Key 锁

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间，可以避免幻读。

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                     |
+--------+---------------+-------------+-----------+-----------+------------------------+

如上方查询，整个表所有记录锁起来，就形成了 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 是索引记录锁加上索引记录之前的间隙锁。

3.3.8 半一致性读取

Update 或 Delete 在执行的时候，会根据表中的索引情况扫描定位数据，在 RR 隔离级别下，扫描到的数据都会加锁，直到会话结束后释放，所以过滤字段无索引的 update 扫描，几乎锁定了整张表。如果有二级索引，通过索引定位数据，扫描数据的范围变小，锁定的范围也变小了。

在 RC 隔离级别下，扫描到的记录也会先加锁，不过确认是不匹配的行后，会主动释放掉锁。

接下来通过实验，创建测试表并填充数据：

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_ci

insert into test_semi values(10, 1, 0),(11, 2, 0),(12,1,0),(13,2,0),(14,1,0);

当前隔离级别为 RR，开启一个 Session 会话，执行如下 SQL：

begin;
update test_semi set c = 22 where b = 1;

由于 b 字段无索引，所以通过主键索引进行扫描定位记录，查询锁持有情况：

+-------------+------------+-----------+-----------+------------------------+
| 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                     |
+-------------+------------+-----------+-----------+------------------------+

在 RR 隔离级别下，由于 b 字段无索引，Update 会为读取的每一行数据都上锁，并且不会释放。如果过滤字段有二级索引，那么只会锁定匹配的记录，以及防止幻读的 Next-Key 锁。

+-------------+------------+-----------+---------------+-----------+
| 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     |
+-------------+------------+-----------+---------------+-----------+

接下来，在 RC 隔离级别下，复现一次上面的实验：

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        |
+-------------+------------+-----------+---------------+-----------+

在 RC 隔离级别下，由于不符合过滤条件的行会被快速释放锁，所以相比 RR 隔离级别，锁的持有时间更短，锁的冲突也更少。除此之外，在 RC 隔离级别下，对于 Update 语句，如果某行已经被锁定，则 InnoDB 将执行 “半一致性读”，将最新提交的版本返回给 MySQL，用于判断是否匹配 where 条件，如果匹配的话，表示必须更新改行，那么会再次读取改行，这次 InnoDB 要锁定它，或者等待它持有的锁释放。

继续刚才的实验，目前 b = 1 的记录已经被锁定，且 b 字段无索引，需要通过主键索引进行记录匹配，主键索引已经有记录被锁定了，此时会用到半一致性读取，读取锁定行的版本，用于判断是否匹配过滤条件，无需等待锁定行的锁释放。

-- 另一个 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 |
+----+------+------+

3.3.9 可重复读的锁定案例

下方 SQL 是本次实验实验的表结构：

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);

案例一：唯一索引等值查询不存在的记录

Session 1：通过主键字段访问一行不存在的记录。

begin;
select * from t where id = 7 for update;

Session 2：尝试向间隙插入一条记录，被堵塞。

insert into t value (8, 8, 8);
-- blocked

Session 3：尝试修改 id = 10 的记录，发现是可以执行成功。

update t set d = d + 1 where id = 10;
-- Rows matched: 1  Changed: 1  Warnings: 0

在这里插入图片描述
Session 1 访问了一个不存在的 id = 7 记录，会锁定 (5, 10] 的间隙，由于 id = 10 不符合过滤条件，Next-key lock 退化为间隙锁 (5, 10) ，所以 id = 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    | 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        |
+--------+---------------+-------------+-----------+------------+---------------+-----------+

范围查询则会额外添加一个间隙锁，锁定的范围是 (10, 15)。
在这里插入图片描述

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        |
+--------+---------------+-------------+-----------+------------+---------------+-----------+

案例三：非唯一索引等值覆盖查询

普通索引查询，由于没有唯一性约束，在扫描的时候都会向右再多查一个记录，所以 (5, 10) 的间隙也被锁住。

+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    6 |
| 10 |   10 |   11 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+

Session 1：通过二级索引 c 等值查询。

begin;
select id from t where c = 5 for share;

Session 2：试探一下间隙锁，此时锁住的间隙是 (-∞, 5)，(5, 10)。

insert into t value(6, 6, 6);
-- blocked

Session 3：通过主键更新一条记录，此时发现是可以执行成功的。

update t set d = d + 1 where id = 5;

由于 Session 1 无需回表，只查询索引即可。主键上没有任何锁，所以 Session 2 才能正常更新。

+--------+---------------+-------------+-----------+------------+-----------+-----------+
| 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    |
+--------+---------------+-------------+-----------+------------+-----------+-----------+

如果是当前读，InnoDB 也会把主键锁定。因为当前读一般用于 DML 意味着要更新数据，所以即使没有访问到主键，也会锁定主键。

+--------+---------------+-------------+-----------+------------+---------------+-----------+
| 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    |
+--------+---------------+-------------+-----------+------------+---------------+-----------+

案例四：非唯一索引范围扫描

Session 1：非唯一索引范围查询。

select * from t where c >= 10 and c < 11 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
| 10 |   10 |   11 |
+----+------+------+

Session 2：插入一条记录，由于间隙锁被堵塞。

insert into t value(8, 8, 8);
-- blocked

Session 3：尝试更新一条记录，发现 Next-key lock。

update t set d = d + 1 where c = 15;

在这里插入图片描述
InnoDB 搜索过程中，访问到的对象会加锁。首先 SQL 会通过 c = 10 使用 Next-key lock 锁定 (5, 10]，由于是非唯一索引，需要扫描到 C = 15 的位置才会结束扫描，所以 (10, 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 |
+----+------+------+

Session 1：通过索引 c = 10 当前读两条记录。

begin;
select * from t where c = 10 for update;

InnoDB 会先定位 c = 10 条件，然后锁定 c 索引上 (5, 10] 间隙，由于是二级索引，需要向右再扫描 c = 15 这行结束，索引中 (10, 15] 被锁定，由于是等值查询，Next-key lock 退化为 Gap 锁，最终锁定的范围是 (5, 10)、(10, 15)。

在这里插入图片描述

Session 2：尝试向间隙中插入一条记录。

insert into t value(12, 12, 12);
-- blocked

Session 3：更新一条记录，可以正常执行，验证了 Next-key 退化为 Gap 锁的推论。

update t set d = d + 1 where c = 15;

与案例四，形成对比，二级索引范围扫描与二级索引等值扫描，都需要先前额外扫描一条记录，区别是范围扫描会使用 Next-key 锁定该条记录，等值查询则不会。

案例六：limit 语句加锁

Session 1：开启一个事务，使用与案例五相同的 SQL，区别是添加了 limit 2。

begin;
select * from t where c = 10 limit 2 for update;

Session 2：尝试插入数据，执行成功，案例的区别就是 limit。

insert into t value(12, 12, 12);
Query OK, 1 row affected (0.01 sec)

在这里插入图片描述
由于加了 limit，扫描到第二行记录就结束了，所以相比于案例五，没有加 (10, 15) 的间隙锁，因为没有继续向右扫描。由此可见，给 SQL 添加 limit 可以减小加锁的范围。

案例七：Next-key lock 实现方式

Session 1：二级索引等值查询，锁定的范围是 (5, 10]，(10, 15)。

begin;
select * from t where c = 10 for share;

Session 2：二级索引更新一条记录，需要给 (5, 10] 范围加 x 锁，由于 Session 1 持 s 锁，出现堵塞情况。

update t set d = d + 1 where c = 10;

Session 1：事务内，再插入一条记录，竟然出现死锁。

insert into t value(8, 8, 8);

Session 2：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

由此案例，可以看出 Next-key lock 是 Gap lock 与行锁的组合。Session 2 虽然被堵塞，其实 Gap lock 已锁定 (5, 10)，只是在等待 c = 10 的行锁。所以 Session 1 插入的时候，系统检测出了死锁情况。

3.3.10 死锁案例

死锁是指不同事务无法继续进行的情况，因为每个事务都持有另一个事务需要的锁。因为两个事务都在等待资源变得可用，所以都不会释放它所持有的锁。

案例一：多线程批量更新场景

死锁的等待关系如图，死锁日志中，只提供了当前堵塞的 SQL 语句，无法得到完整的事务 SQL，所以出现死锁往往需要找研发了解业务逻辑，事务内的其他 SQL 语句。

现在模拟一个业务真实遇到的死锁场景，一个批量刷新商品展示图片的状态信息，业务使用的是主键多线程更新。使用之前 test_semi 表来模拟当时的业务情况。

事务 1，按照主键字段，批量更新。

begin;
update test_semi set b = 0 where a = 11;
update test_semi set b = 0 where a = 12;

事务 2，更新了事务 1 已经锁定的记录。

begin；
update test_semi set b = 0 where a = 13;
update test_semi set b = 0 where a = 12;

事务 1，此时还是持续更新，更新的行是事务 2 已经锁定的记录。

update test_semi set b = 0 where a = 13;

事务 2 被回滚，事务 1 执行成功。

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

请添加图片描述

场景描述：该业务场景是一个更新一个商品的展示图片的状态场景，一个商品有多张图片，更新时都放在一个事务中。任务是从上游 kafka 生产的，可能由于意料之外的场景，出现了一个商品被重复消费的情况，就造成上了上方的死锁问题。

3.4 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 也处于等待状态，后面的增删改查都处于等待状态。也就是说这张表完全不可用了，连查询也被锁定了。