MySQL 事物隔离级别与锁（上）

MySQL 通过设置不同的事物隔离级别，数据库可以在并发性能与并发问题之间进行权衡，这种权衡离不开 MySQL 提供的多样化锁支持。MySQL 锁可以分为 表级锁、行级锁、意向锁、共享锁、排他锁，在 MySQL InnoDB 引擎下的锁会有更多细致的分类，这篇文章我们主要介绍 MySQL InnoDB 下的锁机制。

MySQL InnoDB 锁

MySQL InnoDB 支持的锁种类繁多，用途、作用范围各不相同，可以根据不同应用需求调整锁的使用，以下表格描述了 MySQL InnoDB 支持的几种主要锁类型及其用途：

锁类型	作用	范围	加锁方式
共享锁（S）	允许多个事务并发读取数据，但不允许修改	单行， 多行， 全表	自动 或显式
排他锁（X）	只允许持有该锁的事务读写数据，阻止其他事务的任何操作	单行， 多行， 全表	自动 或显式
意向共享锁（IS）	表明事务打算在某些行上加 共享锁（S），确保表级锁和行级锁的一致性，提高加锁效率	全表	自动加锁
意向排他锁（IX）	表明事务打算在某些行上加 排他锁（X），确保表级锁和行级锁的一致性，提高加锁效率	全表	自动加锁
间隙锁（Gap Lock）	锁定索引记录之间的空隙，防止其他事务在该间隙中插入和修改新记录	索引记录之间的间隙	自动或显式
临键锁（Next-Key Lock）	锁定索引记录及其前后的间隙，防止其他事务在该间隙中插入和修改新记录	索引记录及其前后的间隙	自动或显式
记录锁（Record Lock）	锁定单个索引记录，防止其他事务修改或删除该记录	单行索引记录	自动或显式
插入意向锁（Insert Intention Lock）	允许多个事务在不同位置插入记录，但防止在相同间隙位置插入	插入位置之间的空隙	自动加锁
自增长锁（AUTO-INC Lock）	确保 AUTO_INCREMENT 列的唯一性和有序性	全表	自动加锁

显示加锁语句

大部分的锁都是系统自动添加的，但我们可以根据应用需要，通过以下语句显示控制加锁类型：

显示加锁语句	锁类型
SELECT * FROM table LOCK IN SHARE MODE;	S 锁
SELECT * FROM table FOR UPDATE;	X 锁
LOCK TABLES table WRITE;	表级 X 锁
LOCK TABLES table READ;	表级 S 锁

锁兼容性

在 MySQL InnoDB 中，不同类型的锁在并发事务间存在兼容性问题。下面展示了意向锁（IS、IX）、共享锁（S）、排他锁（X）之间的兼容关系：

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

隔离级别与锁

MySQL InnoDB 锁对于不同事物隔离级别的支持是有差异的，加锁类型在不同隔离级别下也不尽相同。接下来我们会从执行以下 SQL 语句开始，分析不同事物隔离级别下的加锁情况：

SQL 语句
SELECT * FROM table WHERE column = ?;
SELECT * FROM table WHERE column < ?;
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE;
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE;
SELECT * FROM table WHERE column = ? FOR UPDATE;
SELECT * FROM table WHERE column < ? FOR UPDATE;

下文事例都将基于表 employees 中的数据：

id（主键）	name（非索引）	employee_number （唯一索引）	age（普通索引）
1	Alice	1001	30
5	Bob	1020	25
13	Charlie	1010	35
14	David	1035	25
25	Eve	1040	32

Read UnCommitted

SQL 语句	锁类型
SELECT * FROM table WHERE column = ?;	不加锁
SELECT * FROM table WHERE column < ?;	不加锁
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE;	不加锁
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE;	不加锁
SELECT * FROM table WHERE column = ? FOR UPDATE;	意向排他锁（IX） 和 行级排他锁（X）
SELECT * FROM table WHERE column < ? FOR UPDATE;	意向排他锁（IX） 和 行级排他锁（X）

可以看到在 Read UnCommitted 事物隔离级别下，即使查询语句中显示指定了 LOCK IN SHARE 的查询，也无法申请到行级共享锁（S），这也符合 Read UnCommitted 允许脏读的设定，每次读取到的数据都是最新记录 （当前读）。在没有锁的保护下，会读取到其他事物未提交的数据，产生脏读。接下来我们看看 FOR UPDATE 行级排他锁 X 在不同 column 下的加锁区别。

主键 id（聚簇索引）

时间线	事务 A	事务 B
T1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T2	START TRANSACTION;
T3	SELECT * FROM employees WHERE id = 1 FOR UPDATE; (加锁)
T4		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T5		START TRANSACTION;
T6		UPDATE employees SET name = 'Robert' WHERE id = 1; (被阻塞)
T7	COMMIT; (事务 A 提交，释放锁)
T8		COMMIT; (事务 B 提交修改)

事物 A 在 T3 时刻的 WHERE 条件中使用了主键的等值查询， id = 1 这条记录的聚簇索引上会被添加 X 锁，事物 A 在添加 X 锁之前需要先添加意向排他锁 IX，这里可以通过意向排他锁 IX，快速判断是否可以添加 X 锁（若不存在 IX，则说明当前不存在其事物的排他锁 X），所以这里会添加表级意向排他锁 IX 和行级排他锁 X，X 会添加在 id = 1 的聚簇索引上。事物 B 在 T6 尝试更新此记录，此时 UPDATE 语句会自动添加行级排他锁 X ，申请的同样是 id = 1 这条记录的排他锁 X，这导致锁冲突，操作被阻塞。

时间线	事务 A	事务 B
T1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T2	START TRANSACTION;
T3	SELECT * FROM employees WHERE id >= 13 FOR UPDATE; (加锁)
T4		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T5		START TRANSACTION;
T6		UPDATE employees SET name = 'Robert' WHERE id = 14; (被阻塞)
T7	COMMIT; (事务 A 提交，释放锁)
T8		COMMIT; (事务 B 提交修改)

事物 A 在 T3 时刻的 WHERE 条件中使用了主键的范围查询，符合条件的行记录聚簇索引上都会被添加 X 锁，所以这里会添加表级意向排他锁 IX 和行级排他锁 X， X 会分别添加在聚簇索引 id = 13，id = 14，id = 25 上，事物 B 尝试更新这其中的记录时导致锁冲突，操作被阻塞。

唯一索引

基本上与聚簇索引 id（主键） 的加锁情况一致（这也容易理解，因为当表中未设置主键 id 的时候，聚集索引可能是第一个不允许为 null 的唯一索引），使用唯一索引 employee_number 时，系统不仅会在该索引上加排他锁 X，还会在相应的聚簇索引 id 上加上排他锁 X。

普通索引

时间线	事务 A	事务 B
T1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T2	START TRANSACTION;
T3	SELECT * FROM employees WHERE age = 25 FOR UPDATE; (加锁)
T4		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T5		START TRANSACTION;
T6		UPDATE employees SET name = 'Robert' WHERE age = 25; (被阻塞)
T7	COMMIT; (事务 A 提交，释放锁)
T8		COMMIT; (事务 B 提交修改)

事物 A 在 T3 时刻的 WHERE 条件中使用了普通索引的等值查询，首先普通索引 age = 25 上会被添加行级排他锁 X，对应的 id = 5，id = 14 的聚簇索引上也会被添加排他锁 X，所以这里会添加表级意向排他锁 IX 和行级排他锁 X， X 会被添加在 age = 25 的普通索引上和 id = 5，id = 14 的聚簇索引上，事物 B 尝试更新这其中的记录时都会导致锁冲突，操作被阻塞。

时间线	事务 A	事务 B
T1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T2	START TRANSACTION;
T3	SELECT * FROM employees WHERE age > 30 FOR UPDATE; (加锁)
T4		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T5		START TRANSACTION;
T6		UPDATE employees SET name = 'Robert' WHERE age = 35; (被阻塞)
T7	COMMIT; (事务 A 提交，释放锁)
T8		COMMIT; (事务 B 提交修改)

事物 A 在 T3 时刻的 WHERE 条件中使用了普通索引的范围查询，首先符合条件的普通索引 age = 32，age = 35 上会被添加行级排他锁 X，对应的 id = 25，id = 13 的聚簇索引上也会被添加排他锁 X，所以这里会添加表级意向排他锁 IX 和行级排他锁 X， X 会被添加在 age = 32，age = 35 的普通索引上和 id = 25，id = 13 的聚簇索引上，事物 B 尝试更新这其中的记录时都会导致锁冲突，操作被阻塞。

非索引

基本上与聚簇索引 id（主键） 的加锁情况一致，主要的区别在于，非索引列的查询会进行全表扫描，全表扫描使用聚簇索引进行，扫描的每一行会先添加排他锁锁 X，如果不符合查询条件的值，锁会被立即释放，最终加锁情况与聚簇索引一致。

Read Committed

SQL 语句	锁类型
SELECT * FROM table WHERE column = ?;	不加锁
SELECT * FROM table WHERE column < ?;	不加锁
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE;	意向共享锁（IS） 和 行级共享锁（S）
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE;	意向共享锁（IS） 和 行级共享锁（S）
SELECT * FROM table WHERE column = ? FOR UPDATE;	意向排他锁（IX） 和 行级排他锁（X）
SELECT * FROM table WHERE column < ? FOR UPDATE;	意向排他锁（IX） 和 行级排他锁（X）

不同于 Read UnCommitted 隔离级别，LOCK IN SHARE MODE 在 Read Committed 下会添加表级意向共享锁 IS 和 行级共享锁 S，FOR UPDATE 的加锁情况与 Read UnCommitted 一致， LOCK IN SHARE MODE 在不同列类型的查询条件下，锁定的记录与 FOR UPDATE 一致，只是锁的类型变成了 IS 和 S。

时间线	事务 A	事务 B
T1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
T2	START TRANSACTION;
T3	SELECT * FROM employees WHERE id = 1 LOCK IN SHARE MODE; (锁定 id = 1，防止其他事务修改)
T4		SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
T5		START TRANSACTION;
T6		UPDATE employees SET name = 'Robert' WHERE id = 1; (被阻塞，因为事务 A 锁定了 id = 1)
T7	SELECT * FROM employees WHERE id = 1; (读取到的值和之前一致，不会发生不可重复读)
T8	COMMIT;
T9		COMMIT;

在 Read Committed 隔离级别下，使用 LOCK IN SHARE MODE 可以使快照读变为当前读，再加上行级排他锁 X 的应用，这似乎可以解决一部分在 Read Committed 隔离级别下产生的不可重复读问题。