MySQL InnoDB Locking and Transaction Model

1. InnoDB 存储引擎简介
2. InnoDB 锁
3. InnoDB 事务
- 3.1. 事务的实现
  - 3.1.1. Redo Log（物理日志，记录对每个页的修改）
    - 3.1.1.1. 参数 innodb_flush_log_at_trx_commit 对性能的影响
    - 3.1.1.2. Redo Log 和 binlog
  - 3.1.2. Undo Log（逻辑日志，根据每行记录进行记录）
- 3.2. InnoDB 事务隔离级别
  - 3.2.1. InnoDB 的 4 种隔离级别
  - 3.2.2. 查看和设置隔离级别
4. 参考

1. InnoDB 存储引擎简介

MySQL 支持很多存储引擎（参考：Comparison of MySQL database engines），如 MyISAM、NDB、InnoDB 等等。InnoDB 是其中最优秀的一个。从 MySQL 5.5.8 开始，InnoDB 成为了 MySQL 的默认存储引擎。

当使用 CREATE TABLE 语句创建一个数据库表，不指定 ENGINE= 语句或者指定 ENGINE=InnoDB 时都将创建一个“InnoDB 表”。 存储引擎是基于表的，而不是数据库。不同的表可以使用不同的存储引擎。 通过 SHOW CREATE TABLE your_tbl_name; 可以查看表所使用的存储引擎。

参考：
MySQL 5.7 Reference Manual, Chapter 14 The InnoDB Storage Engine
MySQL 5.7 Reference Manual, 14.5 InnoDB Locking and Transaction Model
《MySQL 技术内幕：InnoDB 存储引擎，姜承尧著，第 2 版》第 6 章，第 7 章

2. InnoDB 锁

数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。本节介绍 InnoDB 存储引擎对表中数据的锁定，同时分析 InnoDB 存储引擎会以怎样的粒度锁定数据。

2.1. Lock 和 Latch

在数据库中，Lock 与 Latch 都可以成为锁，但两者有截然不同的含义。

Latch 一般称为闩锁（轻量级的锁），因为其要求锁定的时间非常短，若持续时间长，则应用性能非常差，在 InnoDB 存储引擎中，Latch 有可以分为 mutex（互斥锁）和 rwlock（读写锁）。其目的用来保证并发线程操作临界资源的正确性，并且没有死锁检测的机制。

Lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 Lock 对象仅在事务 commit 或 rollback 后进行释放（不同事务隔离级别释放的时间可能不同），此外 Lock 正如大多数数据库中一样，是有死锁检测机制的。表 1 显示了 Lock 与 Latch 的不同。

Table 1: MySQL 中 Lock 与 Latch 的比较
	Lock	Latch
对象	事务	线程
保护	数据库内容	内存数据结构
持续时间	整个事务过程	临界资源
模式	行锁、表锁、意向锁	读写锁、互斥量
死锁	通过 waits-for graph、time out 等机制进行死锁检测与处理	无死锁检测与处理机制。仅通过应用程序加锁的顺序保证无死锁的情况发生
存在于	Lock Manager 的哈希表中	每个数据结构的对象中

通过执行 show engine innodb mutex; 可以查看 Latch，如：

mysql> show engine innodb mutex;      -- 查看系统中的Latch
+--------+------------------------+---------+
| Type   | Name                   | Status  |
+--------+------------------------+---------+
| InnoDB | rwlock: log0log.cc:838 | waits=2 |
+--------+------------------------+---------+
1 row in set (0.00 sec)

对 Latch 的介绍到此结束，本文将重点介绍 Lock。通过执行 show engine innodb status\G 及查看 information_schema 模式中表 innodb_trx、innodb_locks、innodb_lock_waits 可观察 Lock 的信息，后文有详细介绍。

2.2. 锁的类型

2.2.1. 共享锁、排他锁

InnoDB 存储引擎实现了下面两种标准的行级锁：
1、共享锁（S Lock）：允许事务读一行数据。
2、排他锁（X Lock）：允许事务删除或更新一行数据。

如果一个事务 T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变行 r 的数据，称这种情况为“锁兼容”（Lock Compatible）。但若有其他的事务 T3 想获得行 r 的排他锁，则其必须等待事务 T1、T2 释放行 r 上的共享锁——这种情况称为“锁不兼容”。表 2 显示了共享锁和排他锁的兼容性。

Table 2: 排他锁和共享锁的兼容性
	X	S
X	不兼容	不兼容
S	不兼容	兼容

从表 2 可以发现 X 锁与任何的锁都不兼容，而 S 锁仅和 S 锁兼容。需要特别注意的是， S 和 X 锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

2.2.2. 意向锁

InnoDB 存储引擎支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。 意向锁将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，如图 1 所示。

若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。例如图 1 ，如果需要对页上的记录 r 进行上 X 锁，那么分别需要对数据库 A、表、页上意向锁 IX，最后对记录 r 上 X 锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。

Figure 1: 层次结构

如果意向锁工作在“表级别”，则它的含义是：
1、 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁；
2、 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁。

InnoDB 存储引擎中意向锁和行级锁的兼容性如表 3 所示。

Table 3: InnoDB 存储引擎中锁的兼容性
	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

2.2.2.1. 为什么要意向锁

意向锁可以加快冲突的检测。比如，我们想对表 t1 增加 X 锁，如果没有（表级别的）意向锁，我们需要检测表 t1 上每一行记录有没有加 S 或者 X 锁；有了意向锁，我们只要发现表 t1 上有意向锁，就立刻知道不能对表 t1 增加 X 锁了。

2.3. 查看正在运行的事务和锁（3个重要的表）

2.3.1. 正在运行的事务（information_schema.innodb_trx）

要查看正在运行的事务，可以查看表 information_schema.innodb_trx ，即：

select * from information_schema.innodb_trx\G  -- 查看正在运行的事务

表 4 是表 innodb_trx 的几个重要字段的说明：

Table 4: 表 innodb_trx 的几个重要字段的说明
字段名	说明
trx_id	InnoDB 存储引擎内部唯一的事务 ID
trx_state	当前事务的状态
trx_started	事务的开始时间
trx_requested_lock_id	等待事务的锁 ID。如 trx_state 的状态为 LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的 ID。若 trx_ state 不是 LOCK WAIT，则该值为 NULL
trx_wait_started	事务等待开始的时间
trx_weight	事务的权重，反映了一个事务修改和锁住的行数。在 InnoDB 存储引擎中，当发生死锁需要回滚时，InnoDB 存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id	MySQL 中的线程 ID，SHOW PROCESSLIST 显示的结果
trx_query	事务运行的 SQL 语句

2.3.2. 每张表上锁的情况（performance_schema.data_locks）

要查看每张表上锁的情况，可以查看表 information_schema.innodb_locks （MySQL 8.0.1 及以后版本需要查看表 performance_schema.data_locks ），即：

select * from information_schema.innodb_locks\G  -- 查看每张表上锁的情况；MySQL 8.0.1以前版本
select * from performance_schema.data_locks\G    -- 查看每张表上锁的情况；MySQL 8.0.1及以后版本

注：performance_schema 不一定被启用，通过执行下面 SQL 语句可以查看 performance_schema 是否启用：

MySQL [(none)]> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | OFF   |
+--------------------+-------+
1 row in set (0.01 sec)

如果没有启用 performance_schema（如上面例子就没有启用），想启用它，在服务器配置文件（my.cnf）中增加下面内容再重启服务器即可：

[mysqld]
performance_schema=ON

2.3.3. 事务的相互等待情况（performance_schema.data_lock_waits）

在通过表 information_schema.innodb_locks （或者 performance_schema.data_locks）查看了每张表上锁的情况后，用户就可以来判断由此引发的等待情况了。当事务较小时，用户就可以人为地、直观地进行判断了。但是当事务量非常大，其中锁和等待也时常发生，这个时候就不这么容易判断。但是通过查看表 information_schema.innodb_lock_waits（MySQL 8.0.1 及以后版本需要查看表 performance_schema.data_lock_waits ），可以很直观地反映当前事务的等待（即哪个事务阻塞了另一个事务），即：

select * from information_schema.innodb_lock_waits\G  -- 查看事务的等待；MySQL 8.0.1以前版本
select * from performance_schema.data_lock_waits\G    -- 查看事务的等待；MySQL 8.0.1及以后版本

2.3.4. 实例：分析当前事务和锁

前面介绍了用于分析当前事务和锁的 3 个表，下面通过具体实例来说明它们的使用。

首先，使用下面 SQL 语句准备数据：

create table t1 (id int not null auto_increment, primary key (id));
insert into t1 (id) values (1);

然后，打开两个 session，都执行下面 SQL 语句：

mysql> begin;                                      -- 开启事务
mysql> select * from t1 where id = 1 for update;   -- 对id=1的记录加X锁

其中，一个 session 会成功获得 id=1 记录的 X 锁（由于它没有执行 commit;语句，所以锁一直没有释放）；另一个 session 执行“select ... for update”时会处于等待锁的状态，当等待超过 innodb_lock_wait_timeout（默认为 50 秒）时间后，如果还不能获得锁，则这个事务会失败，提示错误“Lock wait timeout exceeded”。

这时，打开另个一个窗口，执行下面 SQL 语句可查看正在运行的事务（重点关注字段 trx_state 和 trx_query）：

 mysql> select * from information_schema.innodb_trx\G
 *************************** 1. row ***************************
                     trx_id: 22575
                  trx_state: LOCK WAIT
                trx_started: 2019-01-15 00:39:33
      trx_requested_lock_id: 22575:48:4:2
           trx_wait_started: 2019-01-15 00:39:33
                 trx_weight: 2
        trx_mysql_thread_id: 16
                  trx_query: select * from t1 where id = 1 for update
        trx_operation_state: starting index read
          trx_tables_in_use: 1
          trx_tables_locked: 1
           trx_lock_structs: 2
      trx_lock_memory_bytes: 1136
            trx_rows_locked: 1
          trx_rows_modified: 0
    trx_concurrency_tickets: 0
        trx_isolation_level: REPEATABLE READ
          trx_unique_checks: 1
     trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
  trx_adaptive_hash_latched: 0
  trx_adaptive_hash_timeout: 0
           trx_is_read_only: 0
 trx_autocommit_non_locking: 0
 *************************** 2. row ***************************
                     trx_id: 22574
                  trx_state: RUNNING
                trx_started: 2019-01-15 00:39:31
      trx_requested_lock_id: NULL
           trx_wait_started: NULL
                 trx_weight: 2
        trx_mysql_thread_id: 15
                  trx_query: NULL
        trx_operation_state: NULL
          trx_tables_in_use: 0
          trx_tables_locked: 1
           trx_lock_structs: 2
      trx_lock_memory_bytes: 1136
            trx_rows_locked: 1
          trx_rows_modified: 0
    trx_concurrency_tickets: 0
        trx_isolation_level: REPEATABLE READ
          trx_unique_checks: 1
     trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
  trx_adaptive_hash_latched: 0
  trx_adaptive_hash_timeout: 0
           trx_is_read_only: 0
 trx_autocommit_non_locking: 0
 2 rows in set (0.00 sec)

使用下面 SQL 语句可以查看每张表上锁的情况（通过 LOCK_TYPE 和 LOCK_MODE 可以知道具体加的是什么锁）：

 mysql> select * from performance_schema.data_locks\G
 *************************** 1. row ***************************
                ENGINE: INNODB
        ENGINE_LOCK_ID: 22573:1105
 ENGINE_TRANSACTION_ID: 22573
             THREAD_ID: 50
              EVENT_ID: 29
         OBJECT_SCHEMA: db1
           OBJECT_NAME: t1
        PARTITION_NAME: NULL
     SUBPARTITION_NAME: NULL
            INDEX_NAME: NULL
 OBJECT_INSTANCE_BEGIN: 140214832260168
             LOCK_TYPE: TABLE
             LOCK_MODE: IX
           LOCK_STATUS: GRANTED
             LOCK_DATA: NULL
 *************************** 2. row ***************************
                ENGINE: INNODB
        ENGINE_LOCK_ID: 22573:48:4:2
 ENGINE_TRANSACTION_ID: 22573
             THREAD_ID: 50
              EVENT_ID: 29
         OBJECT_SCHEMA: db1
           OBJECT_NAME: t1
        PARTITION_NAME: NULL
     SUBPARTITION_NAME: NULL
            INDEX_NAME: PRIMARY
 OBJECT_INSTANCE_BEGIN: 140214836799000
             LOCK_TYPE: RECORD
             LOCK_MODE: X
           LOCK_STATUS: WAITING
             LOCK_DATA: 1
 *************************** 3. row ***************************
                ENGINE: INNODB
        ENGINE_LOCK_ID: 22572:1105
 ENGINE_TRANSACTION_ID: 22572
             THREAD_ID: 51
              EVENT_ID: 31
         OBJECT_SCHEMA: db1
           OBJECT_NAME: t1
        PARTITION_NAME: NULL
     SUBPARTITION_NAME: NULL
            INDEX_NAME: NULL
 OBJECT_INSTANCE_BEGIN: 140214832262136
             LOCK_TYPE: TABLE
             LOCK_MODE: IX
           LOCK_STATUS: GRANTED
             LOCK_DATA: NULL
 *************************** 4. row ***************************
                ENGINE: INNODB
        ENGINE_LOCK_ID: 22572:48:4:2
 ENGINE_TRANSACTION_ID: 22572
             THREAD_ID: 51
              EVENT_ID: 31
         OBJECT_SCHEMA: db1
           OBJECT_NAME: t1
        PARTITION_NAME: NULL
     SUBPARTITION_NAME: NULL
            INDEX_NAME: PRIMARY
 OBJECT_INSTANCE_BEGIN: 140214836802072
             LOCK_TYPE: RECORD
             LOCK_MODE: X
           LOCK_STATUS: GRANTED
             LOCK_DATA: 1
 4 rows in set (0.01 sec)

使用下面 SQL 语句可以查看事务的相互等待情况：

 mysql> select * from performance_schema.data_lock_waits\G
 *************************** 1. row ***************************
                           ENGINE: INNODB
        REQUESTING_ENGINE_LOCK_ID: 22573:48:4:2
 REQUESTING_ENGINE_TRANSACTION_ID: 22573
             REQUESTING_THREAD_ID: 50
              REQUESTING_EVENT_ID: 29
 REQUESTING_OBJECT_INSTANCE_BEGIN: 140214836799000
          BLOCKING_ENGINE_LOCK_ID: 22572:48:4:2
   BLOCKING_ENGINE_TRANSACTION_ID: 22572
               BLOCKING_THREAD_ID: 51
                BLOCKING_EVENT_ID: 31
   BLOCKING_OBJECT_INSTANCE_BEGIN: 140214836802072
 1 row in set (0.01 sec)

2.3.5. 实例：检测死锁

假设有两个事务，一个事务执行“select * from t1 where id = 1 for update”后，再执行“select * from t1 where id = 2 for update”；而另一个事务执行“select * from t1 where id = 2 for update”后，再执行“select * from t1 where id = 1 for update”。这种不按固定顺序更新两行记录的情况是比较容易产生死锁的。

使用 show engine innodb status\G 可以检测出死锁，如上面例子产生死锁时可能得到下面的输出：

$ mysql> show engine innodb status\G
 ......
 ------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2019-01-15 23:01:14 0x7000062df000
 *** (1) TRANSACTION:
 TRANSACTION 23064, ACTIVE 55 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
 MySQL thread id 8, OS thread handle 123145405681664, query id 55 localhost root statistics
 select * from t1 where id = 2 for update
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 48 page no 4 n bits 72 index PRIMARY of table `db1`.`t1` trx id 23064 lock_mode X locks rec but not gap waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  0: len 4; hex 80000002; asc     ;;
  1: len 6; hex 000000005a17; asc     Z ;;
  2: len 7; hex 810000010a0110; asc        ;;

 *** (2) TRANSACTION:
 TRANSACTION 23065, ACTIVE 37 sec starting index read
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1136, 2 row lock(s)
 MySQL thread id 9, OS thread handle 123145405984768, query id 78 localhost root statistics
 select * from t1 where id = 1 for update
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 48 page no 4 n bits 72 index PRIMARY of table `db1`.`t1` trx id 23065 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  0: len 4; hex 80000002; asc     ;;
  1: len 6; hex 000000005a17; asc     Z ;;
  2: len 7; hex 810000010a0110; asc        ;;

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 48 page no 4 n bits 72 index PRIMARY of table `db1`.`t1` trx id 23065 lock_mode X locks rec but not gap waiting
 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  0: len 4; hex 80000001; asc     ;;
  1: len 6; hex 000000005a18; asc     Z ;;
  2: len 7; hex 820000010b0110; asc        ;;

 *** WE ROLL BACK TRANSACTION (2)
......

2.4. 一致性非锁定读（不加锁，MVCC）和锁定读（显式加锁）

2.4.1. 一致性非锁定读（不加锁，MVCC）

一致性非锁定读（Consistent Nonlocking Reads）是指对于没有显式加锁的 SELECT 语句，InnoDB 存储引擎通过“行多版本控制”的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个“快照数据”。如图 2 所示。之所以称其为非锁定读，因为不需要等待访问的行上 X 锁的释放。

Figure 2: InnoDB 存储引擎的非锁定读（快照读）

快照数据是指该行的之前版本的数据，该实现是通过 undo 段来完成。而 undo 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

可以看到，非锁定读机制极大地提高了数据库的并发性。在 InnoDB 存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。

通过图 2 可以知道，快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。就图 2 所显示的，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control, MVCC)。

在事务隔离级别 READ COMMITED 和 REPEATABLE READ (InnoDB 存储引擎的默认事务隔离级别）下，INNODB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据（这会导致同一事务中，前后两次相同的 SELECT 可能会读到不同的结果）。而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本（这会导致，同样的 SELECT 操作读到的结果会是一致的）。来看表 5 所示的例子。

Table 5: 不同的隔离级别下，会话 A 的第 3 个 SELECT 语句结果不同
时间	会话 A	会话 B
1	BEGIN;
2	SELECT * FROM t1 WHERE id = 1;
3		BEGIN;
4		UPDATE t1 SET id = 3 WHERE id = 1;
5	SELECT * FROM t1 WHERE id = 1;
6		COMMIT;
7	SELECT * FROM t1 WHERE id = 1;
8	COMMIT;

在 Repeatable Read 隔离级别下，会话 A 的第 3 个 SELECT 语句将返回 id=1 的数据（因为读取事务开始时的行数据版本）。
在 Read Committed 隔离级别下，会话 A 的第 3 个 SELECT 语句将返回空数据（因为总是读取被锁定行的最新一份快照数据）。

注：在 Serializable 隔离级别下，没有显式加锁的 SELECT 语句也会加锁，不存在“快照读”了，参见节 2.7.10 。

2.4.2. 锁定读（显式加锁）

在前一小节中讲到，在默认配置下，即事务的隔离级别为 REPEATABLE READ 模式下，INNODB 存储引擎的 SELECT 操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句。

InnoDB 存储引擎对于 SELECT 语句支持两种锁定读（locking read）操作：

SELECT ... FOR UPDATE    -- 加X锁
SELECT ... FOR SHARE     -- 加S锁

其中，“SELECT ... FOR SHARE”也可以写为“SELECT ... LOCK IN SHARE MODE”。

注：SELECT ... FOR UPDATE, SELECT ... FOR SHARE 都必须在一个事务中，当事务提交了，锁也就释放了。因此在使用上述两句 SELECT 锁定语句时，务必加上 BEGNN 或者 START TRANSACTION 或者 SET AUTOCOMMIT=0。

2.5. 行锁的 3 种算法（记录锁、Gap 锁、Next-Key 锁）

InnoDB 存储引擎有 3 种行锁的算法，分别是：
1、Record Lock：记录锁，单个行记录上的锁；
2、Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
3、Next-Key Lock: 结合了 Gap Lock 和 Record Lock，锁定一个范围，并且锁定记录本身。

如果列 id 是表 t1 的主键，下面是记录锁和间隙锁的实例：

SELECT * FROM t1 WHERE id = 10 FOR UPDATE                 -- 当列id是主键时，且id=10的记录存在时，会加“记录锁”
DELETE FROM t1 WHERE id = 10                              -- 同上
SELECT id FROM t1 WHERE id BETWEEN 10 and 20 FOR UPDATE   -- 当列id为主键时，会加“间隙锁”

注：上面例子中，有一个重要的前提：id 是表 t1 的主键。当这个前提不满足时，结论可能不正确。节 2.7 会介绍在不同场景下语句“DELETE FROM t1 WHERE id = 10”会加不同类型的锁。

2.5.1. Gap 锁实例

Gap 锁的作用是防止其它事务插入记录到 Gap 中，从而达到避免幻读的目的。 MySQL 事务的默认隔离级别是“Repeatable-Read”，尽管 SQL92 标准中该隔离级别下允许出现“幻读”现象的。不过，在 MySQL 中，使用了 Next-Key Locking(Gap Locking)使得在“Repeatable-Read”隔离级别下就可以避免幻读现象。

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

摘自：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks

下面介绍 Gap 锁防止幻读的原理，假设有数据：

CREATE TABLE t1 (id INT PRIMARY KEY, val INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2),(2,3),(3,4),(12,13),(18,19);

在 RR 隔离级别下，进行下面操作：

Session 1	Session 2
begin;	begin;
select * from t1 where id between 4 and 11 for update; -- 会拿到 GAP 锁
	insert into t1 values (6, 7); -- blocking

“Session 1”中的 SELECT 语句会拿到 Gap 锁，以防止其它事务插入记录到 Gap 中（“Session 2 的 INSERT 语句会阻塞住”），这样 Session 1 多次执行“SELECT id FROM t1 WHERE id BETWEEN 4 and 11 FOR UPDATE;”总会得到相同的记录，避免的幻读的产生。

上面例子的加锁情况如下（注：lock_mode 中有 GAP 字样）：

mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 13782:144:3:5 | 13780       | X,GAP     | RECORD    | `test1`.`t1` | PRIMARY    |        144 |         3 |        5 | 12        |
| 13781:144:3:5 | 13779       | X         | RECORD    | `test1`.`t1` | PRIMARY    |        144 |         3 |        5 | 12        |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+

再看 GAP 锁的另外一个例子，假设有数据：

CREATE TABLE t1 (id INT PRIMARY KEY, val INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2),(2,3),(3,4),(12,13),(18,19);

在 RR 隔离级别下，进行下面操作：

Session 1	Session 2
begin;	begin;
select * from t1 where id = 5 for update; -- 会拿到 GAP 锁
	insert into t1 values (6, 7); -- blocking

“Session 1”中由于主键 id=5 的记录并不存在，会拿到 GAP 锁 （如果 id=5 的记录存在，则直接拿记录上的锁），GAP 保护的 id 范围为 4 到 11；这时，“Session 2”中插入 id=6 的数据（在 4 和 11 之间）会阻塞住，直到当前事务超时或者“Session 1”释放锁。

上面例子的加锁情况如下（注：lock_mode 中有 GAP 字样）：

mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 13780:144:3:5 | 13782       | X,GAP     | RECORD    | `test1`.`t1` | PRIMARY    |        144 |         3 |        5 | 12        |
| 13779:144:3:5 | 13781       | X,GAP     | RECORD    | `test1`.`t1` | PRIMARY    |        144 |         3 |        5 | 12        |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+

再看一个 GAP 锁的例子，假设有数据：

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY (b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (20, 30), (50, 60), (45, 90);

这个例子中 b 是普通的索引（不是唯一索引）。在 RR 隔离级别下，进行下面操作：

Session 1	Session 2
begin;	begin;
select * from t1 where b = 60 for update; -- 会拿到 GAP 锁
	insert into t1 values (4, 90); -- blocking

加锁情况如下（注：lock_mode 中有 GAP 字样）：

mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 13860:147:4:4 | 13860       | X,GAP     | RECORD    | `test1`.`t1` | b          |        147 |         4 |        4 | 90, 45    |
| 13859:147:4:4 | 13859       | X,GAP     | RECORD    | `test1`.`t1` | b          |        147 |         4 |        4 | 90, 45    |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+

说明：如果“Session 2”中执行 INSERT INTO t1 VALUES (46, 90); ，则不会阻塞住。这是因为记录 (4, 90) 在 GAP 中，而 (46, 90) 不在 GAP 中：

b    a
60,  50
90,  4      -- In Gap immediately after (60, 50)
90,  45
90,  46     -- Not in Gap

参考：https://stackoverflow.com/questions/50504026/gap-locks-or-not

除前面介绍的三个例子外，节：2.7.8 中也详细介绍了 GAP 锁避免幻读的原理。

2.5.2. Gap 锁是兼容的

假设有数据：

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(12),(18);

在 RR 隔离级别下，进行下面操作：

Session 1	Session 2
begin;	begin;
select * from t1 where id = 5 for update; -- 会拿到 GAP 锁
	select * from t1 where id = 5 for update; -- 也可获得 GAP 锁

由于 GAP 锁兼容，“Session 2”中也可以拿到 GAP 锁，并不会阻塞住。

不过，如果操作不当，可能导致死锁，如：

Session 1	Session 2
begin;	begin;
select * from t1 where id = 5 for update; -- 会拿到 GAP 锁
	select * from t1 where id = 5 for update; -- 也可获得 GAP 锁
insert into t1 values (4); -- 会 block，等待 session 2
	insert into t1 values (4); -- 会死锁！等待 session 1

上面例子中，Session 1/2 插入语句也可以不一样，只要是 3 < id < 12 的插入语句都会导致死锁。

2.5.3. 关闭 Gap 锁

通过把事务隔离级别设置为 RC（默认为 RR 级别），或者开启选项 innodb_locks_unsafe_for_binlog (默认是关闭的），可以避免 Gap 锁。

2.5.4. Insert Intention Locks（属于 GAP 锁）

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入记录前，如果发现记录所在间隙已经有了 GAP 锁，那么 Insert 操作会申请“插入意向锁”。插入意向锁是一种特殊的 GAP 锁，和普通的 GAP 锁不同，插入意向锁之间不是兼容的。

如果想插入记录上没有 GAP 锁，那么 InnoDB 通常对插入操作无需加锁，此时如果有其它事务拿这个记录的 S 锁进行读取或 X 锁进行修改，会造成脏读或者脏写问题。MySQL 是通过隐式锁转换来解决这个问题的。隐式锁转换的实现细节如下：

对于聚簇索引记录，有一个隐藏列 trx_id，记录着最后改动这条记录的事务 id，很自然的，新插入的记录的 trx_id 就是他自己的事务 id，如果其他事务此时想获得该记录的 S 锁或者 X 锁时，会检查 trx_id 代表的事务是否是当前活跃事务，如果是的话，帮助该事务创建一个 X 锁（也就是为当前事务创建⼀个锁结构，is_waiting 属性是 false)，然后自己进入等待状态（也就是为⾃⼰也创建⼀个锁结构，is_waiting 属性是 true)。

对于二级索引，没有 trx_id 属性，但是在⼆级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该⻚⾯做改动的最大的事务 id，如果 PAGE_MAX_TRX_ID 属性值⼩于当前最小的活跃事务 id，那么说明对该⻚⾯做修改的事务都已经提交了，否则就需要在⻚⾯中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复情景聚簇索引的做法。

事务 id 相当于加了⼀个隐式锁。别的事务在对这条记录加 S 锁或者 X 锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后⾃⼰再⽣成⼀个锁结构后进⼊等待状态。

参考：
读 MySQL 源码再看 INSERT 加锁流程
 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-insert-intention-locks

2.6. Two-Phase Locking Protocol

Two-Phase Locking Protocol (2PL) 是指把一个事务中的锁操作分为前后两个阶段：Growing phase（这个阶段只能获得锁，不能释放锁）和 Shrinking phase（这个阶段不能获得锁，只能释放锁）。也就是说一个事务中先拿所有的锁，一旦开始释放锁就不能再拿其它锁了。

为什么要这样设计呢？假设有两个事务 T1 和 T2，功能如下。

事务 T1 从账户 B 向账户 A 转移 50：

T1:
lock-X(B);
read(B);
B := B - 50;
write(B);
unlock(B);
lock-X(A);
read(A);
A := A + 50;
write(A);
unlock(A).

事务 T2 显示账户 A 和 B 的总和：

T2:
lock-S(A);
read(A);
unlock(A);
lock-S(B);
read(B);
unlock(B);
display(A+B).

考虑如图 3 所示的 Schedule。

Figure 3: Schedule 1

假设初始时 A 和 B 分别是 100 和 200，执行事务 T2 后显示 A+B 为 250，出现了数据不一致。我们已经加了锁，为什么数据不一致呢？问题出在事务 T1 过早 unlock(B)。显然， 如果我们遵守 2PL 约束，则可以避免上述不一致的情况发生。

参考：《Database System concepts，6th Edition》chapter 14, 15

2.7. SQL 语句加锁的实例分析

请分析下面 SQL 语句会加哪种类型的锁？

SELECT * FROM t1 WHERE id = 10;             -- SQL1
DELETE FROM t1 WHERE id = 10;               -- SQL2
SELECT * FROM t1 WHERE id = 10 FOR UPDATE;  -- 加锁分析同 DELETE FROM t1 WHERE id = 10;
UPDATE t1 set ... WHERE id = 10;            -- 加锁分析同 DELETE FROM t1 WHERE id = 10;

对于 SQL1，不会加锁，采用的是 MVCC（快照读）。

对于 SQL2，没有一个简单的答案，我们还需要知道一些提前条件，比如：
1、id 列是不是主键？
2、当前系统的隔离级别是什么？
3、id 列如果不是主键，那么 id 列上有索引吗？
4、id 列上如果有二级索引，那么这个索引是唯一索引吗？

下面列举一些常见组合来具体分析 SQL2 的加锁情况。

注：本节内容主要摘自：http://hedengcheng.com/?p=771

2.7.1. 聚簇索引（Clustered Index） VS. 非聚簇索引（Secondary Index）

在介绍加锁情况前，先介绍一下聚簇索引的概念。

每个 InnoDB 表都有一个特别的索引，称为聚簇索引（Clustered Index），可以认为它和主键（Primary Key）是同义词。

聚簇索引和非聚簇索引（Secondary Index）的重要区别：聚簇索引的叶子节点就是数据节点（一张表只允许存在一个聚簇索引）；而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针，还需要找一次才找到数据。

关于聚簇索引，MySQL 有下面行为：
1、如果用户定义了主键，则 InnoDB 直接使用它作为聚簇索引。
2、如果用户没有定义主键，则 InnoDB 尝试寻找第一个有“NOT NULL”属性的 UNIQUE 索引作为聚簇索引。
3、如果上面两个条件都不能满足，则 InnoDB 自己生成一个隐藏的列（新增记录时这列保存行 ID 值）作为聚簇索引。

一般来说，“聚簇索引”和“主键索引”可以混用，它们表达是同一个东西。

参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

2.7.2. 组合一：id 是主键，隔离级别是“Read-Committed”

这种情况比较简单，结论是：“在主键上，id = 10 的记录加上 X 锁”，如图 4 所示。

Figure 4: id 是主键、隔离级别是 RC 时的加锁情况

2.7.3. 组合二：id 是唯一索引，隔离级别是“Read-Committed”

唯一索引和主键的重要不同点在于：唯一索引可以为 null，主键不能为 null。

此组合中，id 是 unique 索引，假设主键是 name 列。此时，加锁的情况由于组合一有所不同。由于 id 是 unique 索引，因此 delete 语句会选择走 id 列的索引进行 where 条件的过滤，在找到 id=10 的记录后，首先会将 unique 索引上的 id=10 索引记录加上 X 锁，同时，会根据读取到的 name 列，回主键索引（聚簇索引），然后将聚簇索引上的 name = 'd' 对应的主键索引项加 X 锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个 SQL，是通过主键索引来更新：update t1 set id = 100 where name = 'd'; 此时，如果 delete 语句没有将主键索引上的记录加锁，那么并发的 update 就会感知不到 delete 语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：若 id 列是 unique 列，其上有 unique 索引。那么 SQL 需要加两个 X 锁，一个对应于 id unique 索引上的 id = 10 的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录。如图 5 所示。

Figure 5: id 是唯一索引，隔离级别是 RC 时的加锁情况

2.7.4. 组合三：id 是非唯一索引，隔离级别是“Read-Committed”

相对于组合一、二，组合三又发生了变化，隔离级别仍旧是 RC 不变，但是 id 列上的约束又降低了，id 列不再唯一，只有一个普通的索引。

首先，id 列索引上，满足 id = 10 查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于：组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。如图 6 所示。

Figure 6: id 是非唯一索引，隔离级别是 RC 时的加锁情况

结论：若 id 列上有非唯一索引，那么对应的所有满足 SQL 查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

2.7.5. 组合四：id 无索引，隔离级别是“Read-Committed”

相对于前面三个组合，这是一个比较特殊的情况。id 列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL 会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加 X 锁；有人说会将聚簇索引上，选择出来的 id = 10;的记录加上 X 锁。那么实际情况呢？请看图 7 。

Figure 7: id 无索引，隔离级别是 RC 时的加锁情况

由于 id 列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条（红色标记），但是，聚簇索引上所有的记录，都被加上了 X 锁。无论记录是否满足条件，全部被加上 X 锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于 MySQL 的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 MySQL Server 层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL 有一些改进，在 MySQL Server 过滤条件，发现不满足后，会调用 unlock_row 方法，把不满足条件的记录放锁（违背了 2PL 的约束）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若 id 列上没有索引，SQL 会走聚簇索引的全扫描进行过滤，由于过滤是由 MySQL Server 层面进行的。因此每条记录，无论是否满足条件，都会被加上 X 锁。但是，为了效率考量，MySQL 做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了 2PL 的约束。

2.7.6. 组合五：id 是主键，隔离级别是“Repeatable-Read”

组合五，id 列是主键列，Repeatable Read 隔离级别，针对 SQL2 的加锁情况与组合一的结论一样：“在主键上，id = 10 的记录加上 X 锁”。

2.7.7. 组合六：id 是唯一索引，隔离级别是“Repeatable-Read”

组合六的加锁，与组合二的结论一样：两个 X 锁，id 唯一索引满足条件的记录上一个 X 锁，对应的聚簇索引上的记录一个 X 锁。

2.7.8. 组合七：id 是非唯一索引，隔离级别是“Repeatable-Read”（介绍了 Gap 锁防止幻读的原理）

组合七，Repeatable Read 隔离级别，id 上有一个非唯一索引，执行 delete from t1 where id = 10; 假设选择 id 列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？答案是如图 8 所示。

Figure 8: id 是非唯一索引，隔离级别是 RR 时的加锁情况

图 8 相对于组合三看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个 GAP 锁，而且 GAP 锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP 锁有何用？

其实这个多出来的 GAP 锁，就是 RR 隔离级别，相对于 RC 隔离级别，不会出现幻读的关键。确实，GAP 锁锁住的位置，也不是记录本身，而是两条记录之间的 GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (如果返回更多记录则称发生了幻读)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP 锁应运而生。

如图 8 中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到 B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入 id=10 的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此， 为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL 选择了用 GAP 锁，将这三个 GAP 给锁起来。

Insert 操作，如 insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个 GAP 是否已经被锁上，如果被锁上，则 Insert 不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X 锁)，与组合三类似。同时还是增加 3 把 GAP 锁，将可能插入满足条件记录的 3 个 GAP 给锁上，保证后续的 Insert 不能插入新的 id=10 的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

有心的朋友看到这儿，可以会问：既然防止幻读，需要靠 GAP 锁的保护，为什么组合五、组合六，也是 RR 隔离级别，却不需要加 GAP 锁呢？因为 GAP 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id 是主键；组合六，id 是 unique 键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了 GAP 锁的使用。

结论：Repeatable Read 隔离级别下，id 列上有一个非唯一索引，对应 SQL：delete from t1 where id = 10; 首先，通过 id 索引定位到第一条满足查询条件的记录，加记录上的 X 锁，加 GAP 上的 GAP 锁，然后加主键聚簇索引上的记录 X 锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录 X 锁，但是仍旧需要加 GAP 锁，最后返回结束。

2.7.9. 组合八：id 无索引，隔离级别是“Repeatable-Read”

如果 id 列上没有索引。此时 SQL2：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如图 9 所示。

Figure 9: id 无索引，隔离级别是 RR 时的加锁情况

这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了 X 锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了 GAP 锁。这个示例表，只有 6 条记录，一共需要 6 个记录锁，7个 GAP 锁。试想，如果表上有 1000 万条记录呢？

在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发 SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合四类似，这个情况下，MySQL 也做了一些优化，就是所谓的 semi-consistent read。semi-consistent read 开启的情况下，对于不满足查询条件的记录，MySQL 会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加 GAP 锁。 semi-consistent read 如何触发：要么是 read committed 隔离级别；要么是 Repeatable Read 隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数。

结论：在 Repeatable Read 隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有 GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发 semi-consistent read，来缓解加锁开销与并发影响，但是 semi-consistent read 本身也会带来其他问题，不建议使用。

2.7.10. 组合九：Serializable

Serializable 隔离级别时，对于 SQL2：delete from t1 where id = 10; 来说，Serializable 隔离级别与 Repeatable Read 隔离级别完全一致，因此不做介绍。

Serializable 隔离级别，影响的是 SQL1：select * from t1 where id = 10; 这条 SQL，在 RC，RR 隔离级别下，都是快照读，不加锁。但是在 Serializable 隔离级别，SQL1 会加读锁，也就是说快照读不复存在，MVCC 并发控制降级为 Lock-Based CC。

结论：在 MySQL/InnoDB 中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable 隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

3. InnoDB 事务

事务有 ACID 四个特性：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

3.1. 事务的实现

前面介绍的“锁”，主要是用于实现事务的隔离性。事务的原子性和持久性主要由 Redo Log 来保证；事务的一致性主要由 Undo Log 来保证。

有的 DBA 或许会认为 undo 是 redo 的逆过程，其实不然。redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的内容不同， redo 通常是物理日志，记录的是页的物理修改操作。undo 是逻辑日志，根据每行记录进行记录。

3.1.1. Redo Log（物理日志，记录对每个页的修改）

为什么需要 Redo Log 呢？事务提交后，必须将事务对数据页的修改写到磁盘上，才能保证事务的 ACID 特性。

但是，事务对数据页的修改是“随机写”，其性能较低，如果每次事务提交都写磁盘，则会极大影响数据库的性能。

这种情况下，一般采用“先写日志文件（Write-Ahead Logging）”的优化策略，日志文件持久化到文件中以后，内存中被修改的数据在后台可以慢慢地刷回磁盘。这种策略将“随机写”优化为了“顺序写”（写日志文件过程是顺序写），性能大大提高。

总结： redo log 是为了保证已提交事务的 ACID 特性，同时能够提高数据库性能的技术。

3.1.1.1. 参数 innodb_flush_log_at_trx_commit 对性能的影响

MySQL 把日志写到 Redo log 的具体过程如下：先写到 MySQL 的 Log Buffer 中，然后写到 OS Cache 中，最后调用 fsync 把 OS Cache 中内容写到文件系统中，如图 10 所示。

Figure 10: MySQL 把日志写到 Redo log 的具体过程

不过，这个行为是可以通过参数 innodb_flush_log_at_trx_commit 来控制的，这个参数有三个可选值（0/1/2），不同设置下的行为区别如图 11 所示。

Figure 11: innodb_flush_log_at_trx_commit 为 0/1/2 时的含义

总结：
设置 innodb_flush_log_at_trx_commit 为 0 时（性能最好），如果 MySQL 突然 Crash，则可能有一秒的数据丢失；
设置 innodb_flush_log_at_trx_commit 为 1 时（性能最差，但最安全，这是默认值），可以保证强一致性；
设置 innodb_flush_log_at_trx_commit 为 2 时（折衷方案），如果操作系统突然 Crash，则可能有一秒的数据丢失；

在《MySQL 技术内幕：InnoDB 存储引擎，姜承尧著，第 2 版》7.2.1 节中有一个关于“不同 innodb_flush_log_at_trx_commit 设置下插入 50 万行记录”的测试，这里仅给出其所执行时间，如表 6 所示，可见 innodb_flush_log_at_trx_commit 设置为 1（默认值）时，性能最差。

Table 6: 不同 innodb_flush_log_at_trx_commit 设置对于插入的速度影响（共 50 万行记录）
innodb_flush_log_at_trx_commit	执行所用时间
0	13.90 秒
1	1 分 53.11 秒
2	23.37 秒

注： 对于支付系统，请保持 innodb_flush_log_at_trx_commit 的默认值 1，因为它最安全；对于其它高并发业务，建议设置 innodb_flush_log_at_trx_commit 为 2。

3.1.1.2. Redo Log 和 binlog

在 MYSQL 数据库中还有一种二进制日志（binlog），其用来进行 POINT-IN-TIME (PIT）的恢复及主从复制（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。

首先，重做日志是在 INNODB 存储引擎层产生，而二进制日志是在 MYSQL 数据库的上层产生的，并且二进制日志不仅仅针对于 INNODB 存储引擎，MYSQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

其次，两种日志记录的内容形式不同。MYSQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句。而 INNODB 存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。

此外，两种日志记录写入磁盘的时间点不同。二进制日志只在事务提交最后提交后进行一次写入。而 INNODB 存储引擎的重做日志在事务进行中不断地被写入。

3.1.2. Undo Log（逻辑日志，根据每行记录进行记录）

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要 undo。因此在对数据库进行修改时，INNODB 存储引擎不但会产生 redo，还会产生一定量的 undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条 ROLLBACK 语句请求回滚，就可以利用这些 undo 信息将数据回滚到修改之前的样子。

除了回滚操作，undo 的另一个作用是 MVCC，即在 INNODB 存储引擎中 MVCC 的实现是通过 undo 来完成（前文有对此介绍）。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。

最后也是最为重要的一点是，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。

3.2. InnoDB 事务隔离级别

3.2.1. InnoDB 的 4 种隔离级别

MySQL/InnoDB 定义的 4 种隔离级别（默认为 Repeatable Read）：

1、Read Uncommited
可以读取未提交记录。一般不会使用此隔离级别。

2、Read Committed (RC)
快照读（如不带 FOR UPDATE 的 SELECT）使用 MVCC 来实现。在同一个事务里，前后两次相同的 SELECT 可能会读到不同的结果（因为在 RC 隔离级别下，每次读取最新的快照）。
针对当前读（如：DELETE 或者带 FOR UPDATE 的 SELECT），RC 隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。

3、Repeatable Read (RR)
快照读（如不带 FOR UPDATE 的 SELECT）使用 MVCC 来实现。在同一个事务里，同样的 SELECT 操作读到的结果会是一致的（因为在 RR 隔离级别下，每次读取事务开始时的快照）。
针对当前读（如：DELETE 或者带 FOR UPDATE 的 SELECT），RR 隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

4、Serializable
从 MVCC 并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S 锁)，写加写锁 (X 锁)。

注 1：在 SQL92 标准中 RR 隔离级别下是允许出现幻读现象的，不过 MySQL 在 RR 隔离级别下就避免了幻读现象。
注 2：在 RR 隔离级别下，SELECT FOR UPDATE 执行成功后（获得了锁），但之后相同 where 条件的 SELECT 语句（只是不带 FOR UPDATE，属于快照读）也可能读取的是旧数据。比如，有测试数据如下：

CREATE TABLE t1 (id INT(11), A INT(11), PRIMARY KEY (id));
insert into t1 value (1, 100);

假设有两个事务 Tx1 和 Tx2，执行下面操作：

-----------------------------------------+------------------------------------------
 begin;   --Tx1                          | begin;     --Tx2
-----------------------------------------+------------------------------------------
select * from t1 where id = 1 for update;|
  +----+------+                          |
  | id | A    |                          |
  +----+------+                          |
  |  1 |  100 |                          |
  +----+------+                          |
-----------------------------------------+------------------------------------------
                                         | select * from t1 where id = 1 for update;
-----------------------------------------+------------------------------------------
 update t1 set A = 100+1 where id =1;    |
-----------------------------------------+------------------------------------------
 commit;                                 |
-----------------------------------------+------------------------------------------
                                         |   +----+------+
                                         |   | id | A    |
                                         |   +----+------+
                                         |   |  1 |  101 |
                                         |   +----+------+
-----------------------------------------+------------------------------------------
                                         | select * from t1 where id = 1;
                                         |   +----+------+
                                         |   | id | A    |
                                         |   +----+------+
                                         |   |  1 |  100 |
                                         |   +----+------+
-----------------------------------------+------------------------------------------
                                         |  commit;
-----------------------------------------+------------------------------------------

可以发现，Tx2 中两个 select 语句的 where 条件一样，但得到的结果是不同的（第 2 个 select 读到了旧数据）。

3.2.2. 查看和设置隔离级别

MySQL 中，可以使用下面命令查看事务的隔离级别：

MariaDB [(none)]> SELECT @@TX_ISOLATION;                    -- 查看当前session的事务隔离级别
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'tx_isolation';       -- 查看当前session的事务隔离级别
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select @@global.tx_isolation;               -- 查看全局的事务隔离级别
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show global variables like '%tx_isolation%'; -- 查看全局的事务隔离级别
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

使用下面命令可以设置事务的隔离级别：

set session transaction isolation level READ UNCOMMITTED;
set session transaction isolation level READ COMMITTED;
set session transaction isolation level REPEATABLE READ;
set session transaction isolation level SERIALIZABLE;
set global transaction isolation level READ UNCOMMITTED;
set global transaction isolation level READ COMMITTED;
set global transaction isolation level REPEATABLE READ;
set global transaction isolation level SERIALIZABLE;

参考：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

4. 参考

《MySQL技术内幕：InnoDB存储引擎，姜承尧著，第2版》第6章，第7章
MySQL加锁处理分析：http://hedengcheng.com/?p=771

MySQL InnoDB Locking and Transaction Model

Table of Contents

1. InnoDB 存储引擎简介

2. InnoDB 锁

2.1. Lock 和 Latch

2.2. 锁的类型

2.2.1. 共享锁、排他锁

2.2.2. 意向锁

2.2.2.1. 为什么要意向锁

2.3. 查看正在运行的事务和锁（3个重要的表）

2.3.1. 正在运行的事务（information_schema.innodb_trx）

2.3.2. 每张表上锁的情况（performance_schema.data_locks）

2.3.3. 事务的相互等待情况（performance_schema.data_lock_waits）

2.3.4. 实例：分析当前事务和锁

2.3.5. 实例：检测死锁

2.4. 一致性非锁定读（不加锁，MVCC）和锁定读（显式加锁）

2.4.1. 一致性非锁定读（不加锁，MVCC）

2.4.2. 锁定读（显式加锁）

2.5. 行锁的 3 种算法（记录锁、Gap 锁、Next-Key 锁）

2.5.1. Gap 锁实例

2.5.2. Gap 锁是兼容的

2.5.3. 关闭 Gap 锁

2.5.4. Insert Intention Locks（属于 GAP 锁）

2.6. Two-Phase Locking Protocol

2.7. SQL 语句加锁的实例分析

2.7.1. 聚簇索引（Clustered Index） VS. 非聚簇索引（Secondary Index）

2.7.2. 组合一：id 是主键，隔离级别是“Read-Committed”

2.7.3. 组合二：id 是唯一索引，隔离级别是“Read-Committed”

2.7.4. 组合三：id 是非唯一索引，隔离级别是“Read-Committed”

2.7.5. 组合四：id 无索引，隔离级别是“Read-Committed”

2.7.6. 组合五：id 是主键，隔离级别是“Repeatable-Read”

2.7.7. 组合六：id 是唯一索引，隔离级别是“Repeatable-Read”

2.7.8. 组合七：id 是非唯一索引，隔离级别是“Repeatable-Read”（介绍了 Gap 锁防止幻读的原理）

2.7.9. 组合八：id 无索引，隔离级别是“Repeatable-Read”

2.7.10. 组合九：Serializable

3. InnoDB 事务

3.1. 事务的实现

3.1.1. Redo Log（物理日志，记录对每个页的修改）

3.1.1.1. 参数 innodb_flush_log_at_trx_commit 对性能的影响

3.1.1.2. Redo Log 和 binlog

3.1.2. Undo Log（逻辑日志，根据每行记录进行记录）

3.2. InnoDB 事务隔离级别

3.2.1. InnoDB 的 4 种隔离级别

3.2.2. 查看和设置隔离级别

4. 参考