MySQL - 事务MVCC & 锁

# MySQL - 事务MVCC & 锁

# 事务隔离性 MVCC

# MVCC 介绍

前面在事务原子性之UndoLog 这篇文章中多次提到过MVCC这个东西了，我们说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录直接从页面中删除，而是将记录头的delete_mask设置为1，做标记删除，这主要就是为MVCC服务的。

然后在事务基础这篇文章中介绍了并发事务会有 脏写、脏读、不可重复读、幻读 四个问题，脏写可以通过乐观锁或悲观锁的方式来解决，脏读、不可重复读、幻读 三个问题需要数据库提供一定的事务隔离机制来解决，也就是事务的隔离性。

SQL标准的事务隔离级别有四个，分别能解决并发事务的一些问题：

脏读、不可重复读、幻读 说的都是并发读取的问题，最简单的方式就是给记录加一把锁，不管是更新、读取记录都需要竞争到这把锁之后才能操作。但这种方式的并发性能可想而知会有多么低。

于是 InnoDB 就设计了MVCC来解决并发读取的问题，MVCC 就是多版本并发控制（Multi-Version Concurrency Control）。在 RC、RR 这两种隔离级别下执行SELECT查询时，通过访问记录的版本链，而不需要加锁，这样使得不同事务的读-写操作可以并发执行，从而提升数据库的性能。

# undo log 版本链

前面说 MVCC 是读取记录的版本链实现的，这个版本链其实就是由undo log形成的一个版本链条。

以事务原子性之UndoLog 文章中的这幅图为例，很直观的可以看到，增删改产生的 undo log 通过old roll_pointer连成一个单向链表，记录中的隐藏列roll_pointer则指向最新的一个undo log，就是undo版本链的头结点。

记录中始终都是最新更新的值，可能更新这条记录的事务还未提交：

对于使用 RU 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。
对于使用 SERIALIZABLE 隔离级别的事务来说，InnoDB 使用加锁的方式来访问记录。这个后面再说。
对于使用 RC、RR 隔离级别的事务来说，都必须保证读到已提交事务修改过的记录，如果另一个事务修改的记录还未提交，是不能直接读取记录的最新版本的，此时就可以沿着undo版本链查找当前事务可见的版本。

# ReadView

上一节说到在RC、RR隔离级别下，要保证读到已提交事务修改过的记录，就要在undo版本链上找到当前事务可见的版本。那如何判断版本链上的哪个版本是当前事务可见的呢？

InnoDB 设计了一个 ReadView，在执行一个事务的时候就会创建一个ReadView。

ReadView 有四个关键属性：

m_ids：在生成 ReadView 时当前系统中活跃的事务的事务ID列表。
min_trx_id：生成 ReadView 时当前系统中活跃的事务中最小的事务ID，也就是m_ids中的最小值。
max_trx_id：生成 ReadView 时系统中分配给下一个事务的ID值，就是全局事务ID（Max Trx Id），注意并不是m_ids中的最大值。
creator_trx_id：生成该 ReadView 的事务的事务ID。事务中只有在执行了增删改操作时才会分配一个事务ID，如果是一个只读事务，那 creator_trx_id 默认就为0。

# MVCC 原理

# undo版本链+ReadView机制

有了ReadView后，在事务中查询的时候，就可以沿着 undo 版本链查找当前事务可见的版本。这时 undo log 中的隐藏列 trx_id 就派上用场了，它表示产生这条 undo log 时的事务的事务ID。判断此版本是否可访问的依据就是用 undo log 中的 trx_id 属性值与 ReadView 中的各个属性做比较。

通过如下步骤来判断版本是否可被访问：

① 如果 trx_id 等于 creator_trx_id，说明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
② 如果 trx_id 小于 min_trx_id，说明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
③ 如果 trx_id 大于或等于max_trx_id，说明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
④ 如果 trx_id 在 min_trx_id 和 max_trx_id 之间，此时再判断一下 trx_id 是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

大致的流程图如下：

# RC 和 RR 隔离级别

READ COMMITTED 和 REPEATABLE READ 隔离级别的区别就是它们生成ReadView的时机不同。

READ COMMITTED 是每次查询前都会生成一个独立的 ReadView。
REPEATABLE READ 则只在第一次查询前生成一个 ReadView，之后的查询都重复使用这个 ReadView。
READ UNCOMMITTED 则不需要生成 ReadView，直接读取行记录的数据。

还是以之前的 account 表为例，下面按照操作发生的时间顺序来进行说明。

1、T1

现在 account 表中的初始状态如下，最后更新这条数据的事务ID为100，card 的值为 AA。

系统下一个要分配的事务ID为150，然后系统有两个事务正在运行，事务ID分别为130、135。

2、T2

此时新开一个事务A，查询ID=1的数据，就会生成一个 ReadView 如下图所示：

此时会先判断记录中的 trx_id(100) 与 creator_trx_id(0) 是否相等，这个条件不满足；

接着判断 trx_id(100) < min_trx_id(130)，这个条件满足，那就直接读取这行数据。

此时在事务A中查询 balance=0 的数据，也只会返回1条数据。

3、T3

接着另一个事务B（trx_id=150）更新这条数据，将 AA 更新为 BB，事务还未提交。

接着在事务A中再次查询ID=1的这条数据。

在 RC 隔离级别下，会生成一个新的 ReadView：

先判断行记录，发现 trx_id(150) 在 min_trx_id(130) 和 max_trx_id(160) 之间，同时在 m_ids(130,135,150) 列表中，所以记录行上的数据对本事务不可见；然后继续对比之前的版本，发现 AA 这条版本的 trx_id(100) < min_trx_id(130)，所以就返回 AA 这个版本。所以在 RC 隔离级别下多次读取，看不到别的事务未提交的更新，可避免脏读的问题。

在 RR 隔离级别下，ReadView 不变：

先判断行记录，发现 trx_id(150) 等于 max_trx_id(150)，所以记录行上的数据对本事务不可见；然后继续对比之前的版本，发现 AA 这条版本的 trx_id(100) < min_trx_id(130)，所以就返回 AA 这个版本。所以在 RR 隔离级别下多次读取，看不到别的事务未提交的更新，可避免脏读的问题。

4、T4

接着事务B提交了更新。

接着在事务A中再次查询ID=1的这条数据。

在 RC 隔离级别下，会生成一个新的 ReadView：

先判断行记录，发现 trx_id(150) 在 min_trx_id(130) 和 max_trx_id(165) 之间，同时不在 m_ids(130,135) 列表中，所以记录行上的数据对本事务可见，返回 BB 这个版本。所以在 RC 隔离级别下多次读取，是可以看到别的事务已提交的更新，会有不可重复读的问题。

在 RR 隔离级别下，ReadView 不变：

此时的判断跟上一步中的判断是一样的，最后也是返回 AA 个版本。所以在 RR 隔离级别下多次读取，看不到别的事务已提交的更新，避免了不可重复读的问题。

5、T5

接着一个新的事务（trx_id=175）更新ID=1这条数据，将 BB 更新为 CC，同时还插入了ID=2这条数据，且事务已提交。

这时在事务A中查询 balance=0 的数据。

在 RC 隔离级别下，会生成一个新的 ReadView：

查询ID=1这行记录时，先判断行版本，由于 trx_id(175) 在 min_trx_id(170) 和 max_trx_id(200) 之间，且不在 m_ids(170,180) 列表中，所以返回 CC 这个版本。查询ID=2这行记录时，同样的判断过程，会返回 MM 这个版本。最终查询返回2条数据，而最开始查询只返回1条数据，所以在 RC 隔离级别下多次读取，会有幻读的问题。

在 RR 隔离级别下，ReadView 不变：

查询ID=1这行记录时，最终会沿着版本链找到 AA 这个版本。查询ID=2这行记录时，trx_id(175) > max_trx_id(150)，所以ID=2这行记录不匹配。最终查询只返回1条数据，所以在 RR 隔离级别下多次读取，不会有幻读的问题。

6、T6

接着一个新的事务（trx_id=205）删除了ID=1这条数据，但删除的时候并不是真正的删除，只是将delete_mask标记为 1。

接着在事务A中再次查询ID=1的这条数据。

由于行记录中 delete_mask 标记为 1 了，是不能被查询的，所以只能沿着版本链查询之前的版本。之后的匹配过程跟前面的描述是类似的，就不在赘述了。在 RC 隔离级别下，会返回 trx_id=175，值为 CC 这个版本。在 RR 隔离级别下，会返回 trx_id=100，值为 AA 这个版本。

# MVCC 总结

从上面示例的演示过程就可以看出，MVCC 就是通过 undo log 版本链 + ReadView 实现的一套并发读取的机制。

在 READ COMMITTD 隔离级别下，每次查询都生成一个新的 ReadView，不能读到别的事务未提交的修改，因此解决了 脏读 的问题。但是能读取到别的事务已提交的修改，会有 不可重复读、幻读 的问题。

在 REPEATABLE READ 隔离级别下，只在第一次查询时生成一个 ReadView，之后的查询都重复使用这个 ReadView。别的事务未提交、已提交、新插入的修改都读取不到，因此解决了 脏读、不可重复读、幻读 的问题。

前面介绍 undo log 的文章说过，执行 DELETE 语句或者更新主键的 UPDATE 语句并不会立即把对应的记录完全从页面中删除，而是将 delete_mask 设置为 1，做标记删除。这时就清楚是为什么了，这主要就是为MVCC服务的，因为可能有其它并发运行的事务，要通过版本链读取当前事务可见的版本。

# 快照读和当前读

有一点需要注意的是，前面的示例中的查询都是简单的SELECT查询，这种就是读取undo版本链上的一个快照版本，可以称为快照读或一致性非锁定读。由于是读取的快照，因此在RR隔离级别下可以避免幻读的发生。

但如果是INSERT、DELETE、UPDATE语句，例如下面的SQL，这个 UPDATE 语句会更新 balance=0 的记录，这种方式就称为当前读，读取的是最新的数据。当前读能读取到别的事务已提交的修改，就可能会产生幻读的问题。

UPDATE account SET balance=100 WHERE balance = 0;

1
2

例如，在默认RR隔离级别下，按如下顺序执行SQL，Session B 两次普通查询的结果都是一样的，没有幻读的问题。这是因为 Session B 第二次查询读取的是快照版本，即快照读，不会读取到别的事务提交的修改。

Timeline	Session A	Session B	Session C
t1	TUNCATE TABLE account; INSERT INTO account(card) VALUES ('AA');
t2		BEGIN;	BEGIN;
t3		SELECT * FROM account WHERE balance=0; （返回AA这条记录）
t4			INSERT INTO account(card) VALUES ('BB');
t5			COMMIT;
t6		SELECT * FROM account WHERE balance=0; （返回AA这条记录）
u7		COMMIT;

再按照如下顺序执行SQL，Session B 第一次查询 balance=0 的数据只有AA这一条，然后更新 balance=0 的数据，按理来说只更新一条才对，会发现更新了两条数据，而且再次查询返回了AA、BB这两条数据，此时就产生了幻读的问题。这是因为中间那次更新是当前读，更新时的查询可以读到其它事务提交的更新，此时MVCC是无法解决这个问题的。

Timeline	Session A	Session B	Session C
t1	TUNCATE TABLE account; INSERT INTO account(card) VALUES ('AA');
t2		BEGIN;	BEGIN;
t3		SELECT * FROM account WHERE balance=0; （返回AA这条记录）
t4			INSERT INTO account(card) VALUES('BB')
t5			COMMIT;
t6		UPDATE account SET balance=100 WHERE balance=0; （会看到更新了两行数据）
t7		SELECT * FROM account WHERE balance=100; （返回AA、BB这两条记录）

那当前读这种问题如何解决呢？这就要用到下篇文章中介绍的锁了。

# 事务隔离性之锁

事务隔离性之MVCC 中介绍了 MVCC 是如何保证一致性读的，即一个事务中的修改不会影响另一个事务中的读取，MVCC 在REPEATABLE READ隔离级别下可以避免 脏读、不可重复读、幻读 的问题，保证了事务之间并发读的隔离性。

上篇文章末尾说到 MVCC 其实是 快照读，对普通的 SELECT 查询可以保证事务的隔离性，但 当前读 还是能读到别的事务已提交的修改。除此之外，多个事务并发更新同一条数据，还需要保证并发写的隔离性，避免脏写的问题。这些情况下就要用到锁的机制了，锁机制就是为了支持对共享资源的并发访问，保证数据的完整性和一致性。

# 锁的类型

MySQL 有多种存储引擎，MyISAM、MEMORY、MERGE 这些存储引擎只支持表级锁，而且这些引擎不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话。

InnoDB 存储引擎既支持表级锁，也支持行级锁。行级锁就是针对行记录加锁，行级锁粒度更细，并发性能比表级锁更高。

InnoDB 有如下两种类型的行级锁：

共享锁（S Lock）：简称 S锁，后面就用SLock来表示。SLock 允许事务读一行数据。
排它锁（X Lock）：简称 X锁（XLock），后面就用XLock来表示。XLock 允许事务删除或更新一行数据。

如果一个事务T1获取了一行记录的 SLock，接着另一个事务T2可以立即获取到这行记录的 SLock，因为读取并没有改变这行记录。如果另一个事务T3想要获取 XLock，就会被阻塞，必须等待 T1、T2 都释放了 SLock 才能获取到 XLock。

如果事务T1一开始就获取了 XLock，那其它事务就无法获取 SLock 和 XLock 了，必须等待T1释放了 XLock 后才能获得锁。

它们的兼容性如下表所示：

# 一致性非锁定读

前面说过基于 MVCC 的读取是快照读，也可以称为一致性读或一致性非锁定读，所谓的非锁定读就是指读操作不会对表中的记录做任何加锁操作，其他事务可以对表中的记录做修改。

因为基于 MVCC 的读取是读的undo版本链上的快照版本，所以其它事务可以对同样的记录加 SLock 或 XLock，一致性非锁定读不会去等待行上锁的释放，避免了频繁的加锁操作，大大提高了读操作的性能。

可想而知，非锁定读机制可以极大地提高数据库的并发性。InnoDB 默认就是一致性非锁定读的读取方式，即读取不会占用和等待表上的锁。但需要注意的是，InnoDB 只在 READ COMMITTED、REPEATABLE READ 这两个隔离级别下采用一致性非锁定读，也就是基于 MVCC 的读取。

# 一致性锁定读

前面说 InnoDB 默认是一致性非锁定读，但有些场景下，我们可能想要显示的对读取操作加锁来保证数据逻辑的一致性，这种就是一致性锁定读。也可以称为当前读，因为通过加锁操作来保证读取的是最新的数据，获得锁之后，别的事务就不能更新加锁的记录了。

InnoDB 中的当前读：

SELECT ... LOCK IN SHARE MODE：获取到 SLock，其它事务可以获取到 SLock，但不能获取 XLock。
SELECT ... FOR UPDATE：获取到 XLock，可能要对数据做更新，其它事务会阻塞等待。本质上和 UPDATE 语句的语意是一致的。
UPDATE：更新数据都是先读后写的，而这个读，只能读当前的值，就是当前读。

需要注意的是，LOCK IN SHARE MODE或FOR UPDATE必须在一个事务中使用，事务结束后，锁就自动释放了。

比如在查询账户余额来更新的时候，查询时先对记录显示加 SLock，而不是默认的快照读，这时其它的事务就只能读取这条记录，而无法更新。但这可能会导致死锁，比如事务T1先获取了 SLock，事务T2也获取了同一条记录的 SLock，然后事务T1要更新这条记录，就会一直阻塞住，因为更新要获取记录的 XLock，XLock 和 SLock 是不兼容的。

BEGIN;

SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE;

UPDATE account SET balance=100 WHERE id = 1;

1
2
3
4
5
6

如果一开始查询就加 XLock，这样别的事务就无法再加 SLock 或者 XLock 了，这样就能保证只有一个事务更新记录。

BEGIN;

SELECT * FROM account WHERE id = 1 FOR UPDATE;

UPDATE account SET balance=100 WHERE id = 1;

1
2
3
4
5
6

一般来说，通过SQL加锁来实现一致性是不太好的方式，这样会导致将复杂的业务锁机制隐藏到数据库层面去，在业务代码层面就非常不好维护。一般在分布式系统的场景中，更推荐基于 redis、zookeeper 的分布式锁来实现复杂业务下的锁机制。

# 表级锁

InnoDB 支持对表加锁，表级锁也分为共享锁（SLock）和排他锁（XLock）。

在执行增删改查的SQL语句时，InnoDB 并不会为这个表添加表级别的 SLock 或者 XLock。对表执行DDL操作时（如 ALTER TABLE、DROP TABLE），也不会用到 InnoDB 表级锁，而是用的 MySQL Server 层面提供的一种元数据锁（Metadata Lock，简称MDL）来实现的。

不过我们可以通过如下语句手动获取表级别的 SLock 和 XLock：

LOCK TABLES t READ：InnoDB 会对表加表级别的 SLock。
LOCK TABLES t WRITE：InnoDB 会对表加表级别的 XLock。

表级锁和行级锁是互斥的，它们的兼容性如下表所示。

那如何实现表级别的锁和行级别的锁互斥呢？例如一个事务中在更新某些记录，对这些记录加了行级 XLock，另一个并发事务要用 LOCK TABLES 对这个表加 SLock 或者 XLock，这时肯定就会阻塞。

InnoDB 提供了另一种表级锁，称为意向锁，也分为共享锁和排它锁：

意向共享锁：简称IS锁，后面就称ISLock。当事务准备在某条记录上加 SLock 时，需要先在表级别加一个 ISLock。
意向排他锁：简称IX锁，后面就称IXLock。当事务准备在某条记录上加 XLock 时，需要先在表级别加一个 IXLock。

ISLock和IXLock仅仅为了在之后加表级别的SLock和XLock时可以快速判断表中的记录是否被加锁，所以 ISLock 和 IXLock 是兼容的，IXLock 和 IXLock 也是兼容的，ISLock、IXLock和表级别的 SLock、XLock 是有一定互斥性的。表级锁和意向锁的兼容性如下表所示。

所以在一个事物中更新记录加行级XLock时，首先会在表上加IXLock，如果此时表已经加了 XLock 或 SLock，就会阻塞。如果没有加表级锁，IXLock 就会加锁成功，此时另外的事务想要加表级锁就会阻塞。

表级锁的粒度比较粗，一般我们也不会用到 LOCK TABLES 来手动加表级锁，所以 InnoDB 的表级锁和意向锁是比较鸡肋的。

# 锁结构

无论是表级锁还是行级锁，锁其实就是内存中的一个数据结构，对一条记录加锁的本质，其实就是在内存中创建一个锁结构与之关联。

# 锁的内存结构

# 行锁结构

首先看一下行锁的结构，主要包含的一些信息如下图所示：

① 锁所在的事务信息：记录事务ID（trx_id）等信息。

② 索引信息：行锁是通过索引实现的，索引信息就是记录加锁的记录是属于哪个索引的。

③ 行锁信息：

表空间ID：记录所在表空间ID。
页号：记录所在页号。
n_bits：行锁末尾放了一堆比特位，n_bits 表示使用了多少个比特位。

④ type_mode：这是一个32位的数，存储了四个部分的信息。

lock_mode：锁的模式，占用低4位。可选的值有：
- LOCK_IS：意向共享锁
- LOCK_IX：意向排它锁
- LOCK_S：共享锁
- LOCK_X：排它锁
- LOCK_AUTO_INC：主键自增锁
lock_type：锁的类型，占用第5～8位。可选的值有：
- LOCK_TABLE：表级锁
- LOCK_REC：行级锁
is_waiting：锁的状态，占用第9位。可选的值有：
- 1：当这个比特位为1时，表示当前事务未获取到锁，处在等待状态
- 0：当这个比特位为0时，表示当前事务获取锁成功
rec_lock_type：行锁的具体类型，使用其余的位来表示。只有在lock_type的值为LOCK_REC时，也就是只有在该锁为行级锁时，才会被细分为更多的类型。可选的值有：
- LOCK_ORDINARY：Next-Key Lock
- LOCK_GAP：Gap Lock
- LOCK_REC_NOT_GAP：Record Lock
- LOCK_INSERT_INTENTION：插入意向锁

⑤ 比特位：一个页中有很多记录，末尾的这一堆比特位就是用来表示哪条记录被加锁了。页中的每条记录的记录头中都有一个 heap_no 属性表示记录的排序号，每个比特位就映射着一个 heap_no。

# 表锁结构

相比行锁结构，表锁没有末尾的比特位，以及只存储了表锁相关的一些表信息。

# 查看锁信息

在 information_schema 数据库下，我们可以通过 INNODB_TRX 查询系统当前正在运行的事务信息，还可以通过 INNODB_LOCKS 查询事务持有的锁信息。

INNODB_LOCKS 表中有如下字段：

lock_id：锁的ID
lock_trx_id：事务ID
lock_mode：锁的模式，主要的类型有：共享锁（S）、排它锁（X）、意向共享锁（IS）、意向排它锁（IX）。
lock_type：锁的类型，RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：要加锁的表
lock_index：锁住的索引
lock_space：表空间ID
lock_page：事务锁定页的数量。若是表锁，则该值为NULL
lock_rec：事务锁定行的数量，若是表锁，则该值为NULL
lock_data：事务锁定记录的主键值，若是表锁，则该值为NULL

# 加锁方式

InnoDB 在任何隔离级别下都不会发生脏写的问题，多个并发事务对同一条记录做修改时，只能排队一个一个修改，这个排队就是通过锁来实现的。在对某条记录做 UPDATE、DELETE 操作时，都会先获取这条记录的 XLock 后再操作，获取不到就会阻塞等待。下面就来看下 InnoDB 是如何通过锁避免脏写的问题的。

后面还是以 account 这张表为例，来做一些测试：注意 card 列是唯一索引，name 列是普通索引。

CREATE TABLE `account` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `card` varchar(60) NOT NULL COMMENT '卡号',
  `name` varchar(60) DEFAULT NULL COMMENT '姓名',
  `balance` int(11) NOT NULL DEFAULT '0' COMMENT '余额',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_u1` (`card`) USING BTREE,
  KEY `account_n1` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户表';

1
2
3
4
5
6
7
8
9
10

例如有两个事务T1、T2按如下顺序更新 id=1 这条记录：

事务T1更新 id=1 这条记录时，首先会看内存中有没有与这条记录关联的锁结构，没有的话就会生成一个锁结构与之关联，并且 is_waiting=false，表示获取锁成功（加锁成功），然后事务T1就可以执行更新操作了。

接着事务T2查询id=1这行数据，此时是基于MVCC的快照读，不会对记录加任何锁。

接着事务T2更新id=1这行数据，这时就会发现已经有一个锁与这条记录关联了，然后事务T2也生成一个锁结构与这条记录关联，但是 is_waiting=true，表示获取锁失败（加锁失败），需要等待。

这时查看 INNODB_LOCKS 表，可以看到有两把锁，我们可以得到如下信息：

lock_trx_id：对应的事务ID分别是 560677、560676。
lock_mode：锁的模式是排它锁（XLock）。
lock_type：RECORD 表示加的是行锁。
lock_index：是针对主键索引加的锁。

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 |
+------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 560677:13971:3:2 | 560677      | X         | RECORD    | `test`.`account` | PRIMARY    |      13971 |         3 |        2 | 1         |
| 560676:13971:3:2 | 560676      | X         | RECORD    | `test`.`account` | PRIMARY    |      13971 |         3 |        2 | 1         |
+------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+

1
2
3
4
5
6
7
8

接着可以通过 INNODB_TRX 表查询当前事务信息，可以看到，560676 这个事务的运行状态为 RUNNING， 560677 这个事务的运行状态为 LOCK WAIT，也就是等待锁。从这也可以知道，T1事务的事务ID=560676，T2事务的事务ID=560677。

mysql> SELECT * FROM information_schema.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 560677 | LOCK WAIT | 2021-05-31 17:41:03 | 560677:13971:3:2      | 2021-05-31 17:41:03 |          2 |                   4 | UPDATE account SET balance=200 WHERE id = 1 | starting index read |                 1 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 560676 | RUNNING   | 2021-05-31 17:40:58 | NULL                  | NULL                |          3 |                   3 | NULL                                        | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

1
2
3
4
5
6
7
8

之后事务T1提交事务，就会把该事务生成的锁结构释放掉，然后看还有没有别的事务在等待获取锁，发现事务T2还在等待获取锁，所以把事务T2对应的锁结构的 is_waiting 属性设置为 false，然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就获取到锁了。

# 行级锁

参考：极客时间《丁奇-MySQL实战45讲》

# 行锁类型

InnoDB 既实现了行锁，也实现了表锁。行锁是通过索引实现的，当SQL命中索引时，就会锁住条件内的索引节点。如果没有命中索引，那么锁的就是整个索引树，其实就是升级为表锁了。

InnoDB 有3种行级锁的算法实现，锁结构中rec_lock_type属性就表示行锁类型。

Record Lock：记录锁，专门对索引项加锁，就是锁单条记录。
Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包含记录本身，锁住的是一个区间。
Next-Key Lock：临键锁，Gap Lock + Record Lock 的组合，对索引项之间的间隙加锁，包含记录本身，是一个左开右闭的区间。

Record Lock 总是会锁住索引记录，如果在建表的时候没有设置任何一个索引，那么就会使用隐式的主键(row_id)来进行锁定。

Gap Lock 和 Next-key Lock 锁住的是一段区间，即这个区间被锁住后，不允许插入新的值，所以间隙锁主要用于解决幻读的问题。

还有一种锁是 插入意向锁（Insert Intention），插入意向锁类似于 Gap Lock，不过插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。插入意向锁的主要目的是，如果别的事务在这个间隙加了 Gap Lock，那么要在这个间隙插入数据的这个事务就要生成一个插入意向锁的结构，然后等待。所以插入意向锁和 Gap Lock 是不兼容的。

关于间隙锁，例如一个索引有 10、15、20 这三个值：

那么该索引可能被 Next-Key Lock 锁住的区间为：
```
(-∞, 10]
(10, 15]
(15, 20]
(20, +Supremum]
```
1
2
3
4
5

能被 Gap Lock 锁住的区间为：

(-∞, 10)
(10, 15)
(15, 20)
(20, +Supremum)

1
2
3
4
5

注意在 (20, +∞) 区间实际上的区间是 (20, +Supremum] 这个区间。前面介绍页的结构时说过，InnoDB 每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的记录，Supremum 记录是比改页中何主键值都要大的记录。(20, +Supremum] 这里的 Supremum 就是 20 这条记录所在页的最大记录。

# 加锁规则

什么时候加 Record Lock，什么时候又加 Gap Lock 或者 Next-Key Lock，总结起来加锁规则有如下几条。

1、加锁时会根据索引从左往右查找，加锁的基本单位是 Next-Key Lock，就是说在对某条记录加锁时，默认用 Next-Key Lock 去锁住一个左开右闭的区间。
2、查找过程中只有访问到的对象才会加锁，比如查询只用到了辅助索引，就不会对聚簇索引加锁。
3、唯一索引上的等值查询，给唯一索引加锁的时候，Next-Key Lock 退化为 Record Lock。
4、普通索引上的等值查询，会一直向右遍历，最后一个值不满足等值条件的时候，Next-Key Lock 退化为 Gap Lock。（就是说某个普通索引上可能有多个相同的值，因此就会有多个 Next-Key Lock 锁住的左开右闭的区间，但最后一个区间会退化为 Gap Lock）。
5、唯一索引上的范围查询会访问到不满足条件的第一个值为止（包含这条记录）。

注意只有在 REPEATABLE READ 或以上隔离级别下的特定操作才会取得 Gap Lock 或 Next-key Lock，在 SELECT 、UPDATE 和 DELETE 时，除了基于唯一索引的查询之外，其他索引查询时都会获取 Gap Lock 或 Next-key Lock，即锁住其扫描的范围。

我们往 account 表中初始化几条数据，下面来做些测试验证上面的规则。

TRUNCATE TABLE account;

INSERT INTO account ( id, card, NAME, balance )
VALUES
	( 1, 'A', 'A', 0 ),
	( 5, 'D', 'D', 5 ),
	( 10, 'H', 'H', 10 ),
	( 15, 'M', 'M', 15 ),
	( 20, 'R', 'R', 20 )

1
2
3
4
5
6
7
8
9
10

下面是这张表的初始数据，为了测试，后续我所有的测试SQL都会默认使用 ROLLBACK 回滚掉，也就是每次测试执行SQL前的初始数据都是一样的。

mysql> SELECT * FROM account;
+----+------+------+---------+
| id | card | name | balance |
+----+------+------+---------+
|  1 | A    | A    |       0 |
|  5 | D    | D    |       5 |
| 10 | H    | H    |      10 |
| 15 | M    | M    |      15 |
| 20 | R    | R    |      20 |
+----+------+------+---------+

1
2
3
4
5
6
7
8
9
10
11

这几条数据在主键索引上就会产生如下几个间隙：

(-∞, 1]
(1, 5]
(5, 10]
(10, 15]
(15, 20]
(20, +Supremum]

1
2
3
4
5
6
7

name 这个普通索引上会产生如下几个间隙：

(-∞, A]
(A, D]
(D, H]
(H, M]
(M, R]
(R, +Supremum]

1
2
3
4
5
6
7

# 唯一索引等值查询 - 行锁

1、两个事务按如下顺序执行SQL语句：

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE id=1 LOCK IN SHARE MODE;
	UPDATE account SET balance=200 WHERE id=1; (blocked)
COMMIT;
	ROLLBACK;

两个事务都是使用的主键ID来查询数据，T2事务执行时会阻塞住。

用加锁规则分析下：

根据规则1，加 Next-Key Lock 锁住 (-∞, 1] 这个区间。
根据规则3，由于是唯一索引等值查询，且记录存在，因此 Next-Key Lock 退化为 Record Lock，因此只锁住 id=1 这条记录。

因为T1锁住了 id=1 这条记录，T2 同样也是更新 id=1 这条记录，因此需要等待。

查看 INNODB_LOCKS 表，其中 lock_type 都为 RECORD，表示加的是行级锁；lock_index 列显示锁住的是聚簇索引（主键），lock_data 显示锁定的主键为 1。

mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id     | lock_mode | lock_type | lock_table       | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566301          | X         | RECORD    | `test`.`account` | PRIMARY    | 1         |
| 282768778791576 | S         | RECORD    | `test`.`account` | PRIMARY    | 1         |
+-----------------+-----------+-----------+------------------+------------+-----------+

1
2
3
4
5
6
7
8

两个事务的 locke_mode 是不同的，一个是 X（XLock），一个是 S（SLock）。从这可以看出，Record Lock 有 SLock 和 XLock 之分，互斥规则和前面说的是一样的。

2、接着按如下的顺序执行：

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE card='A' LOCK IN SHARE MODE;
	UPDATE account SET balance=200 WHERE card='A' (blocked)

这次T1、T2是根据唯一索引列 card 来查询，且 card=A 存在，所以在唯一索引card上对card=A这条记录加 Record Lock。从 INNODB_LOCKS 查看 lock_index、lock_data 可以得到证实。

mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id     | lock_mode | lock_type | lock_table       | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566334          | X         | RECORD    | `test`.`account` | account_u1 | 'A'       |
| 282768778791576 | S         | RECORD    | `test`.`account` | account_u1 | 'A'       |
+-----------------+-----------+-----------+------------------+------------+-----------+

1
2
3
4
5
6
7
8

3、接着按如下顺序执行：

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE card='A' LOCK IN SHARE MODE;
	UPDATE account SET balance=200 WHERE id=1; （blocked）

T1 事务根据 card 列查询，T2 事务根据 id 列查询，推测 T1 事务应该是锁住唯一索引 card=A，T2 锁住聚簇索引 id=1。但查看 INNODB_LOCKS，会发现它们都是锁住的聚簇索引 id=1 这条记录。其实也很好理解，T2 要锁住 id=1 这条记录，T1 是想锁定读 card=A 这条记录，T1 需要回表查询聚簇索引上的数据，因此就直接锁住聚簇索引 id=1 这条记录了，阻塞 T2 事务更新这条记录。

mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id     | lock_mode | lock_type | lock_table       | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566336          | X         | RECORD    | `test`.`account` | PRIMARY    | 1         |
| 282768778791576 | S         | RECORD    | `test`.`account` | PRIMARY    | 1         |
+-----------------+-----------+-----------+------------------+------------+-----------+

1
2
3
4
5
6
7
8

4、接着按如下顺序执行：

T1	T2
BEGIN;	BEGIN;
SELECT id FROM account WHERE card = 'A' LOCK IN SHARE MODE;
	UPDATE account SET balance=balance+200 WHERE id=1; （Affected rows: 1）

T1 事务根据 card 查询 id 字段，不需要回表，所以它只需要锁住唯一索引 card=A 即可，T2 事务根据 id 列查询更新，这次会发现 T2 事务可以更新成功，不会阻塞住了。根据加锁规则的第2条可知，只有访问到的对象才会加锁，因此 T1 事务不会对聚簇索引 id=1 加锁，因此T2可以加锁成功。

5、最后按照如下顺序执行：

T1	T2
BEGIN;	BEGIN;
SELECT id FROM account WHERE card = 'A' FOR UPDATE;
	UPDATE account SET balance=200 WHERE id=1; （blocked）

与上一次的执行相比，区别在于 T1 事务的锁定读是用的 FOR UPDATE，这次 T2 事务会阻塞住。查看 INNODB_LOCKS 会发现两个事务都是对聚簇索引 id=1 加 Record Lock(XLock)。

mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_data |
+-------------+-----------+-----------+------------------+------------+-----------+
| 566341      | X         | RECORD    | `test`.`account` | PRIMARY    | 1         |
| 566339      | X         | RECORD    | `test`.`account` | PRIMARY    | 1         |
+-------------+-----------+-----------+------------------+------------+-----------+

1
2
3
4
5
6
7
8

这可以验证锁定读语句 LOCK IN SHARE MODE 和 FOR UPDATE 在语义上的不同，FOR UPDATE 表示想要更新这条记录，LOCK IN SHARE MODE 可能只是想读取这条记录，防止别的事务更新。因此 FOR UPDATE 的语义和 UPDATE 是类似的，认为你接下来要去更新数据，因此会顺便给聚簇索引上满足条件的行加上行锁，防止其它事务并发更新。

# 唯一索引等值查询 - 间隙锁

在上一小节中根据唯一索引查询的记录在数据库中都是存在的，所以都是加的行锁（Record Lock），那如果记录不存在又会加什么锁呢？

按如下顺序执行：

T1	T2	T3
BEGIN;	BEGIN;	BEGIN;
SELECT * FROM account WHERE id=3 LOCK IN SHARE MODE; （Query 0）
	INSERT INTO account VALUES (2, 'B', 'B', 0); （blocked）
		UPDATE account SET balance=balance+100 WHERE id = 5; （Affected rows: 1）

T1 事务查询 id=3 这条记录不存在，接着事务 T2 要插入 id=2 这条记录，被阻塞住了，接着 T3 更新了 id=5 这条记录，可以更新成功。

用加锁规则分析下 T1 事务的加锁：

根据规则1，加锁 Next-Key Lock，由于 id=3 这条记录不存在，所以锁住的是 (1, 5] 这个区间。
根据规则4，这是一个唯一索引等值查询（id=3），所以 id=5 这条记录不满足条件，Next-Key Lock 退化为 Gap Lock，因此锁住的范围是 (1, 5)。

事务 T1 锁住了 (1, 5) 这个间隙，因此 T2 事务想在这个间隙插入一个 id=2 的记录会被阻塞住，而 T3 事务就可以成功更新 id=5 这条记录。

执行 T1、T2，查看 INNODB_LOCKS 表，lock_mode 显示加的是 Gap Lock，很明显 T1 事务加的是 S Gap Lock，T2 事务加的是 X Gap Lock，说明 Gap Lock 也分 S、X 类型。lock_index 显示锁住的是主键索引，lock_data 表示在 id=5 这条记录上加的 Gap Lock。

mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_data |
+-------------+-----------+-----------+------------------+------------+-----------+
| 566789      | X,GAP     | RECORD    | `test`.`account` | PRIMARY    | 5         |
| 566788      | S,GAP     | RECORD    | `test`.`account` | PRIMARY    | 5         |
+-------------+-----------+-----------+------------------+------------+-----------+

1
2
3
4
5
6
7
8

接着按如下顺序执行

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE id=3 LOCK IN SHARE MODE; （Query 0）
	SELECT * FROM account WHERE id=4 LOCK IN SHARE MODE; （Query 0）

在上一步中，T3 事务是对 id=5 这条记录加 Record Lock，可以执行成功。这次执行事务 T2 同样是对 (1,5) 这个区间加 Gap Lock，可以执行成功。

从这可以说明一个问题：虽然 Gap Lock 有 S、X 之分，但是它们起到的作用都是相同的。如果对一条记录加了 Gap Lock，并不会限制其他事务对继续这条记录加 Record Lock 或者 Gap Lock。

Gap Lock 只是用于保护这个间隙，防止插入新的记录，Gap Lock 其实仅仅是为了防止插入幻影记录而提出的。

# 普通索引等值查询

按如下顺序执行SQL

T1	T2	T3	T4
BEGIN;	BEGIN;	BEGIN;	BEGIN;
SELECT id FROM account WHERE name = 'D' LOCK IN SHARE MODE;
	INSERT INTO account VALUES (4, 'C', 'C', 0); （blocked）
		INSERT INTO account VALUES (6, 'E', 'E', 0); （blocked）
			UPDATE account SET balance=balance+100 WHERE name = 'H'; （Affected rows: 1）

事务T1查询 name='D'，name 列是普通索引，用加锁规则来分析下：

根据规则1，遍历索引name列，锁住的间隙是 (A, D]，由于是普通索引，还会向右继续遍历，因此还会锁住 (D, H] 这个区间。
根据规则4，索引上的等值查询，向右遍历到最后一个不满足等值条件的时候，Next-Key Lock 锁住的 (D, H] 会退化为 Gap Lock，只锁住 (D, H)。
根据规则2，只有访问到的对象才会加锁，这里的查询只用到了普通索引name，所以不会对聚簇索引加锁（但注意如果是 FOR UPDATE 查询，就会对聚簇索引加锁）。

也就是说事务T1对name列普通索引加锁，锁住的间隙是 (A, D]、(D, H)。

因此可以看到事务T2想要往 (A, D] 这个间隙插入一个 'C' 会被阻塞住，事务T3想往 (D, H) 间插入一个 'E' 也会被阻塞。而事务T4更新的是 'H'，不在T1锁住的间隙内，因此可以更新成功。

# Limit 语句加锁

还是上面的例子，但这次T1中的查询加了 LIMIT 1，因为满足 name='D' 的数据也只有一条。

T1	T2	T3
BEGIN;	BEGIN;	BEGIN;
SELECT id FROM account WHERE name = 'D' LIMIT 1 LOCK IN SHARE MODE;
	INSERT INTO account VALUES (4, 'C', 'C', 0); （blocked）
		INSERT INTO account VALUES (6, 'E', 'E', 0); （Affected rows: 1）

但这次的结果却不一样了，事务T3可以向 (D, H) 这个间隙插入一个 'E' 了。

有了 LIMIT 语句之后，结果虽然一样，但加锁的效果是不一样的。因为加了 LIMIT 1 之后，在遍历到 name='D' 之后，已经有一条满足条件的数据了，就不会再往后遍历了，因此锁住的区间就只有 (A, D] 了，所以T3事务可以执行成功。

我们这里虽然是用的 SELECT ... LOCK IN SHARE MODE(或 FOR UPDATE)，但它和 DELETE、UPDATE 的加锁逻辑是类似的。如果我们在根据普通索引来 DELETE/UPDATE，且知道记录数时，那我们就可以在执行 DELETE/UPDATE 时加上 LIMIT，这样不仅可以控制删除/更新数据的条数，让操作更安全，还可以减小加锁的范围，提高数据库并发性能。

例如我将T1事务的SELECT换成UPDATE后，可以看到效果还是一样的。但如果去掉 LIMIT 1，事务T3还是会阻塞住的。

T1	T2	T3
BEGIN;	BEGIN;	BEGIN;
UPDATE account SET balance=balance+100 WHERE name = 'D' LIMIT 1;
	INSERT INTO account VALUES (4, 'C', 'C', 0); （blocked）
		INSERT INTO account VALUES (6, 'E', 'E', 0); （Affected rows: 1）

# 主键索引范围查询

按如下顺序执行SQL

T1	T2	T3
BEGIN;	BEGIN;	BEGIN;
SELECT id FROM account WHERE id >= 5 AND id < 6 LOCK IN SHARE MODE;
	INSERT INTO account VALUES (8, 'F', 'F', 0); （blocked）
		UPDATE account SET balance=balance+100 WHERE id = 10; （blocked）

这次事务T1是在主键列上进行的范围查询，注意这里的条件是 id >= 5 AND id < 6，它和直接查询 id=5 是不同的，虽然查询结果相同，但加锁规则是不同的。查询 id=5 很容易理解，加的是 Record Lock。使用加锁规则来分析下 id >= 5 AND id < 6 是加的什么锁：

查询 id >= 5，要对 id=5 这条记录加锁，根据规则1，默认加 Next-Key Lock，锁住 (1, 5] 这个区间。
根据规则3，id=5 是唯一索引等值查询，所以 Next-Key Lock 退化为 Record Lock，只锁住 id=5 这一条记录。
接着进行范围查询，根据规则5，唯一索引上的范围查询会访问到不满足条件的第一个值为止，会访问到 id=10 这条记录，因此加 Next-Key Lock 锁住 (5, 10] 这个间隙。

因此T1事务锁住的是 id=5 这条记录以及 (5, 10] 这个区间。

可以看到事务T2想要往 (5,10) 这个间隙插入 id=8 被阻塞住了，符合预期。但 T3 事务更新 id=10 这条记录也被阻塞住了，因此需要注意，规则5所说的访问到不满足条件的第一个值为止，会包含不满足条件的这条记录，也就是 (5,10]。

接着按如下顺序执行SQL

T1	T2	T3	T4
BEGIN;	BEGIN;	BEGIN;	BEGIN;
SELECT * FROM account WHERE id > 5 AND id <= 15 LOCK IN SHARE MODE;
	INSERT INTO account VALUES (14, 'L', 'L', 0); （blocked）
		INSERT INTO account VALUES (16, 'N', 'N', 0); （blocked）
			UPDATE account SET balance=balance+100 WHERE id = 20; （blocked）

这次T1事务的查询条件是 id > 5 AND id <= 15，按我们的理解，应该是锁住 (5, 10]、(10, 15] 这个区间，事务T2想往(10, 15]这个区间插入id=14，确实被阻塞住了。但事务T3想插入id=16，以及事务T4想更新id=20这条记录也都被阻塞住了。

所以，这里要注意理解原则5，唯一索引上的范围查询会向右访问到不满足条件的第一个值为止，范围查询 id <= 15 时，id=15 这条记录是满足条件的，所以会接着访问，访问到 id=20 这条记录时，才不满足了。所以T1事务锁住的范围是 (5, 10], (10, 15]，(15, 20] 这三个区间。

不过，它这里看起来更像是一个BUG，按照规则3，唯一索引上的等值查询，即查询 id<=15 在匹配到id=15这条记录时就会停止，Next-Key Lock 会退化为 Record Lock，加锁的区间应该是 (5, 10], (10, 15) 以及id=15这条记录，所以不会锁住(15, 20]这个区间才是，而且这明显也是锁得多余了。

# 普通索引范围查询

按如下顺序执行SQL

T1	T2	T3	T4
BEGIN;	BEGIN;	BEGIN;	BEGIN;
SELECT id FROM account WHERE name >= 'D' AND name < 'E' LOCK IN SHARE MODE;
	INSERT INTO account VALUES (4, 'C', 'C', 100); （blocked）
		INSERT INTO account VALUES (8, 'F', 'F', 100); （blocked）
			UPDATE account SET balance=100 WHERE name = 'H'; （blocked）

这次T1事务是对普通索引 name 进行条件查询，查询条件是 name >= 'D' AND name < 'E'，用加锁规则来分析下：

name>='D' 会先找到 name='D' 这条记录，根据规则1，加 Next-Key Lock，锁住 (A, D] 这个区间，由于是普通索引，继续往右遍历，因此还会加 Next-Key Lock 锁住 (D, H]。
由于 name 列不是唯一索引，不满足规则3，因此 Next-Key Lock 锁住的 (A, D] 不会退化为 Record Lock。
由于是范围查询，不是等值查询，因此不满足规则4，Next-Key Lock 锁住的 (D, H] 不会退化为 Gap Lock。这是和 普通索引等值查询 的区别。

因此事务T1锁住的区间是 (A, D]、(D, H] 两个区间。

事务T2想往 (A, D] 区间插入一个 'C'，事务T3想往 (D, H] 区间插入一个 'F'，而事务T4想更新 name='H' 这条记录，T1事务锁住了 (A, D]、(D, H] 两个区间，因此 T2、T3、T4 都需要加锁等待，被阻塞住。注意和普通索引等值查询 的区别，普通索引等值查询 name='D' 时，可以更新 name='H' 这条记录，而范围查询 name>='D' 时不行。

# 无索引查询

按如下顺序执行：

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE balance = 0 FOR UPDATE;
	INSERT INTO account(card, name, balance) VALUES ('X', 'X', 100); （blocked）

这次事务T1查询 balance=0，balance 列上没有任何索引，这时会锁住全表。可以看到事务T2想插入一个 balance=100 会被阻塞住，可以验证这个结论。

# 幻读

幻读是指在同一事务下，连续执行两次同样的SQL语句看到不同的结果，第二次的SQL语句可能会返回之前不存在的行，幻读是专指读到新插入的行。

# 幻读的问题

也许我们会觉得既然是当前读，能读到别的事务新插入的行也是正常的，但幻读有两个问题。

首先从语义上来说，幻读的问题在于，事务T1要锁定的是一行数据，如果事务T2新插入了一行，然后事务T1再次锁定读时发现了两行，这从语义上来说是有问题的。

其次，幻读最大的问题在于可能导致数据和binlog日志在逻辑上的不一致性。binlog 默认是 Statement 模式，记录的是更新的SQL语句。

例如按下表执行，事务T1根据 name='D' 更新 balance=300，如果允许幻读，事务T2就可以再插入一行 name='D'，balance=0 的数据，事务T1最后才提交，这时数据库中新插入的那条 name='D' 的数据 balance=0。binlog是在事务提交后才写入的，因此binlog中的顺序则是T2事务中的插入语句，接着才是事务T1中的更新语句。如果将这个binlog应用到主从复制上，从库中所有 name='D' 的数据 balance=300，这就出现了不一致。

T1	T2
BEGIN;
UPDATE account SET balance=300 WHERE name='D';
	INSERT INTO account VALUES (6, 'D', 'D', 0);
COMMIT;

# 解决幻读

前面的内容已经多次提到过幻读这个问题了，在上一篇文章的末尾，我们提到在RR隔离级别下，基于MVCC的快照读是不会有幻读的问题的，只在当前读的情况下才会出现幻读。因此一般在读取数据时，使用快照读就可以了。

在前面的多项测试中，很容易发现，Next-Key Lock 或者 Gap Lock 就是用于解决幻读问题的，如果只使用 Record Lock 锁住已存在的行记录，那么行之间的间隙是没办法控制的，因此其它事务就可能往这些间隙插入数据，进而导致幻读的问题。

间隙锁是在 可重复读(REPEATABLE READ) 隔离级别下才生效的，因此要避免幻读的问题，需要设置数据库隔离级别为可重复读。

但是间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实对数据库的并发性能会有一定影响。如果确定业务不需要可重复读的保证，可以将数据库隔离级别设置到读已提交(READ COMMITTED)，但这可能会导致数据和 binlog 日志不一致，这时需要把 binlog 模式设置为 row。ROW 模式下，binlog 记录的是每行数据的修改，就不会有 Statement 模式下的那个问题了，但 ROW 模式会产生大量日志内容。

# 死锁

# 死锁问题

前面我们测试了那么多锁相关的内容，但这些测试中多个事务之间只是一个事务加锁，然后阻塞其它事务，只要这个事务执行完释放锁，另一个事务就能继续执行。

死锁就不一样了，死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

例如有下面这样一个场景，根据 name 查询，不存在则插入数据：

T1	T2
BEGIN;	BEGIN;
SELECT * FROM account WHERE name = 'E' FOR UPDATE;
	SELECT * FROM account WHERE name = 'F' FOR UPDATE;
	INSERT INTO account(card, name) VALUES ('F', 'F'); （blocked）
INSERT INTO account(card, name) VALUES ('E', 'E'); （Deadlock found when trying to get lock; try restarting transaction）
	（Affected rows: 1）

事务T1先锁定读 name='E'，加的锁是 Gap Lock，锁住的是 (D, H) 这个间隙，事务T2也是一样的，但间隙锁之间是不会互相阻塞的。事务T2锁住了 (D, H)，但在插入数据时却阻塞住了，它是被T1事务加的 Gap Lock 给阻塞住的。接着事务T1又来插入数据，这时数据库就检测到死锁了，直接抛出死锁异常并重新开始了事务。然后事务T2就得以继续执行事务。

在上面这个示例中，两个事务都持有 (D, H) 这个间隙的 Gap Lock，但接下来的插入操作都要获取这个间隙的插入间隙锁，插入间隙锁和 Gap Lock 是冲突的，因此都要等待对方事务的 Gap Lock 释放，于是就造成了循环等待，导致死锁。

# 解决死锁

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在InnoDB存储引擎中，可以用参数innodb_lock_wait_timeout来设置超时的时间。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此，除了超时机制，当前数据库还都普遍采用 wait-for graph（等待图）的方式来进行死锁检测，当检测到死锁后会选择一个最小（锁定资源最少得事务）的事务进行回滚。较之超时的解决方案，这是一种更为主动的死锁检测方式。可以通过参数 innodb_deadlock_detect=on 开启死锁检测，默认开启。

不过，解决死锁的最佳方式就是预防死锁的发生，我们平时编程中，可以通过一些手段来预防死锁的发生。

在编程中尽量按照固定的顺序来处理数据库记录，比如有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，就有可能导致死锁；
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
更新表时，尽量使用主键更新；使用普通索引更新时，可能会锁住很多间隙。如果不同时事务使用不同索引来更新，也可能导致死锁。
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置锁等待超时参数，通过 innodb_lock_wait_timeout 设置合理的等待超时阈值。在一些高并发的业务中，可以将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

# 文章来源

作者：bojiangzhou 链接：https://juejin.cn/post/6978632592140533796 与 https://juejin.cn/post/6979793212281995271 来源：稀土掘金著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

#MySQL #MVCC #锁

上次更新: 2024-08-19

← MySQL - 事务Redo&Undo Log MySQL - 索引→