MySQL事务和锁

记录一下 MySQL 中事务的实现机制和锁，以及不同隔离级别下的测试笔记。

事务隔离级别

数据准备

-- 创建表
CREATE TABLE t(
  a INT PRIMARY KEY,
  b INT,
  c INT
);
-- 插入数据
INSERT INTO t VALUES (1,1,1);
INSERT INTO t VALUES (2,2,2);
INSERT INTO t VALUES (3,3,3);
INSERT INTO t VALUES (4,4,4);
INSERT INTO t VALUES (5,5,5);
INSERT INTO t VALUES (6,6,6);

READ UNCOMMITTED（读未提交）

-- 查询当前事务隔离级别 默认：REPEATABLE-READ
SELECT @@tx_isolation;

-- 设置 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 事务A
begin;
update t set c = 99 where a = 6;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select * from t where a = 6;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 6 |    6 |   99 |
+---+------+------+

-- 事务B可以查询事务A未提交的数据
-- 一旦事务A回滚，事务B读到的就是 脏数据

隔离级别最弱的，会出现“脏读”，一般不会使用。违反了 ACID 原则。

READ COMMITTED（读已提交）

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
update t set c = 88 where a = 6;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
select * from t where a = 6;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 6 |    6 |   99 |
+---+------+------+

-- 事务A
commit;

-- 事务B
select * from t where a = 6;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 6 |    6 |   88 |
+---+------+------+

不同的事务之间，可以读取其他事务已经提交的数据。

但是会有一个问题：事务 B 多次读取，可能读取的数据不一致。称作：不可重复读。

REPEATABLE READ（可重复读）

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;

-- 事务A
update t set c = 77 where a = 6;
commit;

-- 事务B
select * from t where a = 6;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 6 |    6 |   88 |
+---+------+------+
-- 即使事务A已提交，事务B查询的还是旧数据，解决了“**不可重复读**”的问题
-- 事务B结束后，才能查看事务A提交的数据。

REPEATABLE READ 解决了 READ COMMITTED 中“不可重复读”的问题。

在对事务隔离级别的定义中，REPEATABLE READ 会有“幻行”的问题。InnoDB 实现了 MVCC（多半版本并发控制），解决了“幻行”的问题。

注意

事实上，事务 A 和事务 B 并非完全隔离，InnoDB 通过 ReadView 和事务 ID 来判断哪些数据对于当前事务是可见的，哪些是不可见的。

例如：

事务 A 开启，但是没有查询(没创建 ReadView)。
事务 B 开启，修改数据，提交事务。
事务 A 可以查询到事务 B 修改的数据。

事务 A 查询、创建 ReadView 时，事务 B 已经提交（不活跃），事务 A 会将事务 B 修改的数据也读出来。具体的判断依据会在后面记录。

SERIALIZABLE（可串行化）

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;

-- 事务A
update t set c = 66 where a = 6;

-- 事务B
select * from t where a = 6;
-- SELECT操作会被阻塞，事务A会给查询出来的数据行加 排它锁。
-- 事务B不可读不可写，直到事务A结束，释放行锁。

在 SERIALIZABLE 隔离级别下，事务会给查询到的数据行加共享锁，其他事务可读不可写。给写入的数据加排它锁，其他事务不可读，不可写。

SERIALIZABLE 是最强的隔离级别，也是并发性能最差的隔离级别，一般也很少使用。InnoDB 默认的隔离级别（REPEATABLE READ）通过实现 MVCC 来最大程度的给减少给数据行加锁，在遵循 ACID 的同时最大程度的来支持高并发。

保存点

对于一个事务，要么全部执行，要么全部回滚。

如果事务涉及到很多操作，但是在最后一步操作失误，如果全部回滚，意味着所有的操作都要重来一遍。

保存点(SAVEPOINT) 提供了一个“存档”的功能，可以让事务回滚到指定的位置。通过“版本链”的方式实现，后面会讲到。

-- 事务A
begin;
UPDATE t SET c = 999 WHERE a = 6;

-- 设置保存点
SAVEPOINT s;

UPDATE t SET c = 999 WHERE a = 5;

-- 回滚到保存点
ROLLBACK TO s;

SELECT * FROM t;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    2 |
| 3 |    3 |    3 |
| 4 |    4 |    4 |
| 5 |    5 |    5 |
| 6 |    6 |  999 |
+---+------+------+

-- 删除保存点
RELEASE SAVEPOINT s1;

类似于“游戏存档”的功能。

MVCC 实现机制

在 InnoDB 中，数据以“行”的格式保存在磁盘中，MySQL 支持多种行格式。具体的“行格式”可以查看笔者以前的博客。

InnoDB 会在数据行中保存两个隐藏列：

TRX_ID：事务 ID，每次数据被修改都会保存相应事务的 ID。
ROLL_PTR：回滚指针，数据行被修改时，会生成一个版本链，通过回滚指针连接，用于事务回滚时恢复数据。

版本链

数据行被修改时，会生成一条“版本链”。

不同的数据行版本之间，通过“回滚指针”连接，事务回滚时，通过回滚指针来恢复数据。

ReadView

InnoDB 中，通过 ReadView 来判断：不同的数据行版本中，哪些版本对于当前事务是可见的，哪些是不可见的。

在 READ UNCOMMITTED 隔离级别下，事务读取的总是最新的版本，所以不需要 ReadView。在 SERIALIZABLE 隔离级别下，通过对数据行加锁来限制读写，也不需要 ReadView。

只有 READ COMMITTED 和 REPEATABLE READ 需要 ReadView，二者创建的时机不同。

READ COMMITTED 可以访问其他事务已提交的数据，所以每次查询都会创建新的 ReadView。
REPEATABLE READ 第一次查询就创建 ReadView，之后的查询都会直接使用已有的 ReadView，可以保证即使其他事务已提交，当前事务仍然认为它是活跃的。

核心内容

ReadView 中主要包含的核心内容：

m_ids：创建 ReadView 时活跃的事务 ID 集合。
min_trx_id：创建 ReadView 时，活跃的最小事务 ID。
max_trx_id：创建 ReadView 时，下一个应该分配的事务 ID(递增)。
creator_trx_id：创建 ReadView 的当前事务 ID。

max_trx_id 不代表 m_ids 中的最大值，因为有的事务可能在创建 ReadView 之前就已经提交，也就不会出现在 m_ids 中。

判断依据

访问的数据行版本 TRX_ID 等于 creator_trx_id，代表当前事务在访问自己修改的数据，可以访问。
访问的数据行版本 TRX_ID 小于 min_trx_id，说明当前事务开启前，该版本的事务已经提交，可以访问。
访问的数据行版本 TRX_ID 大于 max_trx_id，说明该版本的事务在 ReadView 创建之后开启，不可访问。
访问的数据行版本 TRX_ID 在 min_trx_id 和 max_trx_id 之间，则要判断是否在 m_ids 中，如果在：说明事务活跃，不可访问。如果不在：说明事务已经提交，可以访问。

1、看不到 ReadView 创建以后启动的事务 2、看不到 ReadView 创建时活跃的事务

INNODB_TRX

活跃的事务信息保存在：INFORMATION_SCHEMA.INNODB_TRX 表中，采用 MEMORY 存储引擎，速度非常快。

事务 ID 是递增的，且读和写的事务 ID 是分开记录的。

查看活跃的事务 ID

BEGIN;
SELECT * FROM t;
SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
COMMIT;
-- 读 281479607707384

BEGIN;
UPDATE t SET c = 99 WHERE a = 6;
SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
COMMIT;
-- 写 8206301

锁

锁的类型

读锁：共享锁、S 锁
写锁：排他锁，X 锁

只有“读读”锁不会冲突，对于一个资源，可以加多个读锁，其他锁加多个均会冲突、阻塞。

SELECT 操作不会加任何锁，InnoDB 利用 MVCC 来支持高并发，读都可以读，但不能同时写。

手动加锁

读锁

-- 事务A 加读锁
begin;
select * from t where a = 1 lock in share mode;

-- 事务B
-- 可以查询
select * from t where a = 1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
-- 可以再加读锁
select * from t where a = 1 lock in share mode;
-- 再加写锁 会阻塞
select * from t where a = 1 for update;

写锁

-- 事务A 加写锁
begin;
select * from t where a = 1 lock in share mode;

-- 事务B
-- 再加读锁/写锁 均会阻塞
-- SELECT查询是可以，查询不会加任何锁

锁的范围

表锁

-- 读锁
lock tables t read;
-- 写锁
lock tables t write;

行锁

LOCK_REC_NOT_GAP 行记录锁，只锁住数据行。
LOCK_GAP 间隙锁，锁定一个范围，但不包括记录本身。
LOCK_ORDINARY 间隙锁，锁定一个范围，且包括记录本身。

对于不同的事务隔离级别，InnoDB 锁住的数据范围不同。

READ COMMITTED 锁范围

使用主键只对主键对应的数据行加锁。
使用唯一索引只对唯一索引和对应数据行加锁。
使用普通索引对满足条件的索引和对应数据行加锁。
不使用索引对满足条件的索引和对应数据行加锁。

REPEATABLE READ 锁范围

使用主键只对主键对应的数据行加锁。
使用唯一索引只对唯一索引和对应数据行加锁。
使用普通索引对符合条件的索引、数据行以及间隙加锁。
没有使用索引对表中所有的记录和间隙加锁。

意向锁

IS 锁：意向共享锁。
IX 锁，意向排他锁。

当事务在数据行中加锁时，会先在表中加对应的意向锁。

目的：为了快速判断表中的数据行是否有加锁，没有意向锁标记，需要遍历每一行去判断。

死锁

多个事务在互相占用对方锁住的资源，都在等待对方释放锁，造成死循环。

-- 事务A
begin;
select * from t where a = 1 for update;

-- 事务B
begin;
select * from t where a = 2 for update;

-- 事务A
select * from t where a = 2 for update;-- 阻塞

-- 事务B
select * from t where a = 1 for update;-- 死锁
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction