本文为《MySQL归纳学习》专栏的第十五篇文章，同时也是关于《MySQL事务》知识点的第三篇文章。
相关文章：
MySQL探秘：解码事务、分布式事务与长事务的奥秘
MySQL并发事务问题与事务隔离级别揭秘
欢迎阅读《MySQL深度解析MVCC的"快照"工作机制》。在数据库世界里，MVCC是我们高效处理并发读写的强大工具，但你是否真正理解其背后的"快照"是如何运作的呢？本篇文章将深度探讨MVCC中的快照机制，并详细对比快照读和当前读的差异，帮助你更好地理解和应用这个关键概念。
“快照”在 MVCC 里是怎么工作的？
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。
如果一个库有 100G，那么我启动一个事务，MySQL 就要拷贝 100G 的数据出来，这个过程得多慢啊。可是，我平时的事务执行起来很快啊。实际上并不需要拷贝出这 100G 的数据。
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。注意，只读事务是不会分配事务ID的，执行 select trx_id from information_schema.innodb_trx;语句查询到的很大的数字并不是事务 ID，这个数字是每次查询的时候由系统临时计算出来的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。
如下一个更新事务中：
begin;
update tt set k=1 where id=1;
commit;
begin;
update tt set k=10 where id=1;
commit;
begin;
update tt set k=k+1 where id=1;
commit;
begin;
update tt set k=k*2 where id=1;
commit;
我们用下面这张图来演示一个记录被多个事务连续更新后的状态。
图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。
语句更新会产生 undo log（回滚日志）， 用来保证事务的原子性，那么 undo log 在上图中处于哪个位置呢？
实际上，上图中的三个虚线箭头，即 U1、U2、U3，U1 记录的应该是 set k=1;U2 记录的为 set k=k-1;U3 记录内容为 set k=k/2;就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。
明白了多版本和 row trx_id 的概念后，我们再来想一下，InnoDB 是怎么定义那个“100G”的快照的。
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。何谓上一个版本呢？数据库实际上存储的是最新版本的数据，如果想要获取上一个版本的数据，则需要回溯计算，比如说当前最新版本为 V4，那么通过 U3 可以得到 V3 版本。
当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间（关于事务的启动方式，在上文有所介绍），当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面所有已创建但未提交的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。 注意，有些事务执行时间比较长，有些短事务很快就执行完了，并不是小于高水位大于低水位的事务就都没有提交。所以row trx_id 在这个范围内却不在数组中就是已经提交了的可见。
而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。这个视图数组把所有的 row trx_id 分成了几种不同的情况。
这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：
关于上述4种可能，比较有争议的一点是最后一种可能，即 trx_id 在高低水位之间，但是已经提交事务了，这是为什么呢？在黄色这个范围里面并不意味这这个范围里有这个值，比如[1,2,3,5,6]，低水位为1，高水位为6，4在这个数组1-5的范围里，却没在这个数组里面。为什么4没在这个数组内呢？假设当前启动的事务有1，2，3，但是都没有提交，而4直接提交结束了，那么轮到下一个新事务5时，对应的一致性视图就是[1,2,3,5,6]。
我们还是举例来说明，下面是一个只有两行的表的初始化语句。
mysql> CREATE TABLE t (
id int(11) NOT NULL,
k int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
按照下图顺序执行对应的 SQL
SELECT trx_id FROM information_schema.INNODB_TRXG
--事务A
start transaction with consistent snapshot;
select k from t where id=2 for update;
select k from t where id=1;
commit;
-- 事务B
start transaction with consistent snapshot;
update t set k=k+1 where id=1;
select k from t where id=1;
commit;
--事务C
start transaction with consistent snapshot;
update t set k=k+1 where id=1;
commit;
按照上图的执行流程，结果为：事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1。
这里，我们不妨做如下假设：
这样，事务 A 的视图数组就是[99,100], 此时低水位为99，高水位为101；事务 C 的视图数组是[99,100,101]，此时低水位为99，高水位为102；事务 B 的视图数组是[99,100,102], 此时低水位为99，高水位为103。为什么事务B的视图数组没有 101呢？根据下图可知，事务B 已提交，不属于活跃的事务了。
实操截图如下所示：
按照我们假设的事务ID来分析这三个事务，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 101，而 90 这个版本已经成为了历史版本。
第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 102，而 101 又成为了历史版本。我们按照高低水位法进行分析，为什么事务C的修改对事务B可见？(1,2)这条记录的 row trx_id 是 101，因为事务B的视图数组为[99,100,102]，然后它落在了黄色区域，又因为 101不在该数组中，所以对于事务B可见。
你可能注意到了，在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。
读数据都是读最新的，然后根据版本进行回溯。所以，事务 A 查询语句的读数据流程是这样的：
- 找到 (1,3) 的时候，判断出 row trx_id=102，比高水位101大，处于红色区域，不可见；
- 接着，找到上一个历史版本，一看 row trx_id=101，等于高水位101，处于红色区域，不可见；
- 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位99小，处于绿色区域，可见。
虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
可重复读的更新逻辑
在上文中，为了演示事务ID的分配，我在事务A中初次调用查询语句时，额外加上了 for update，这与另一条查询语句相比有什么差异呢？
把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，即为当前读。
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
所以，除了 update 语句外，select 语句如果加锁，也是当前读。
所以，如果把事务 A commit 之前执行当前读，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
select k from t where id=1 lock in share mode;
select k from t where id=1 for update;
如果事务 C 不是马上提交的，而是在事务 B 修改操作后提交（此时事务B未提交），那么事务 B 的更新语句会如何处理呢？
经过测试发现，事务C没有 commit，则事务B的 update 语句会被阻塞，这是为什么呢？
原因在于事务 C 没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C释放这个锁，才能继续它的当前读。
事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
那么，我们再看一下，在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别应该是多少呢？
这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。
还是下面这些SQL，同样的执行顺序，不同的隔离级别，将会发生什么变化呢？
-- 当前会话更改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT trx_id FROM information_schema.INNODB_TRXG
--事务A
start transaction with consistent snapshot;
select k from t where id=2 for update;
select k from t where id=1;
commit;
-- 事务B
start transaction with consistent snapshot;
update t set k=k+1 where id=1;
select k from t where id=1;
commit;
--事务C
start transaction with consistent snapshot;
update t set k=k+1 where id=1;
commit;
测试结果如下：
由图可知，事务A的查询结果变为了2，说明事务C的执行结果对事务A可见。
扩展
为什么RR能实现可重复读而RC不能？
(1)快照读的情况下，RR 不能更新事务内的 up_limit_id，而 RC 每次会把 up_limit_id 更新为快照读之前最新已提交事务的 transaction id，则 RC 不能可重复读。 (2)当前读的情况下，RR 是利用 record lock+gap lock 来实现的,而 RC 没有 gap，所以 RC 不能可重复读。
如何解决如下业务问题？
SQL 代码如下：
CREATE TABLE like (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
liker_id int(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_user_id_liker_id (user_id,liker_id)
) ENGINE=InnoDB;
CREATE TABLE friend (
id int(11) NOT NULL AUTO_INCREMENT,
friend_1_id int(11) NOT NULL,
friend_2_id int(11) NOT NULL,
UNIQUE KEY uk_friend (friend_1_id,friend_2_id),
PRIMARY KEY (id)
) ENGINE=InnoDB;
并发场景下，两个人同时关注对方，会出现不会成为好友的情况，如下图所示：
解决方案
首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值 1、2、3。
值是 1 的时候，表示 user_id 关注 liker_id; 值是 2 的时候，表示 liker_id 关注 user_id; 值是 3 的时候，表示互相关注。
让“like”表里的数据保证 user_id < liker_id，这样不论是 A 关注 B，还是 B 关注 A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突。
然后，当 A 关注 B 的时候，来判断 B 是否关注A，逻辑改成如下所示的样子：
应用代码里面，比较 A 和 B 的大小，如果 A begin; /*启动事务*/
insert into like(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from like where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship，
如果是1，事务结束，执行 commit
如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;
如果 A>B，则执行下面的逻辑
mysql> begin; /*启动事务*/
insert into like(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from like where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship，
如果是2，事务结束，执行 commit
如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;
然后，insert … on duplicate 语句，确保了在事务内部，执行了这个 SQL 语句后，就强行占住了这个行锁，之后的 select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。
操作符 “|” 是按位或，连同最后一句 insert 语句里的 ignore，是为了保证重复调用时的幂等性。
这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是 like 表里面有一条关于 A 和 B 的记录，而且 relation_ship 的值是 3， 并且 friend 表里面也有了 A 和 B 的这条记录。
虽然我们强调尽量不要使用唯一索引，但这是基于“业务开发保证不会插入重复记录”的情况下，着重要解决性能问题的时候，才建议尽量使用普通索引。如果业务上一定需要插入重复记录，则正常使用唯一索引。
具体验证过程如下：
更新操作失效
问题：
mysql> CREATE TABLE t (
id int(11) NOT NULL primary key auto_increment,
a int(11) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1,2);
这时候，表 t 里有唯一的一行数据 (1,2)。假设，我现在要执行：
mysql> update t set a=2 where id=1;
结果显示，匹配 (rows matched) 了一行，修改 (Changed) 了 0 行。
仅从现象上看，MySQL 内部在处理这个命令的时候，可以有以下三种选择：
你觉得实际情况会是以上哪种呢？你可否用构造实验的方式，来证明你的结论？进一步地，可以思考一下，MySQL 为什么要选择这种策略呢？
选择1：
步骤3的更新语句被阻塞了，如果时间足够的话（InnoDB行锁默认等待时间是50秒），还会报锁等待超时的错误。加锁这个动作是 InnoDB 才能做的，所以排除选项 1。
总结而言：问题中的截图其实就是我们的步骤2，如果一个更新语句不 commit，会使得当前读失效。
选择2：
session A 的第二个 select 语句是一致性读（快照读)，它是不能看见 session B 的更新的。
现在它返回的是 (1,3)，表示它看见了某个新的版本，这个版本只能是 session A 自己的 update 语句做更新的时候生成。session A 中执行了修改语句获取到了 a 的最新值，由于 a 值一样，所以 changed 为 0，但是确实做了修改操作，不然 session A 的第二个 select 语句显示结果不可能为 3。
选择3：
如果修改重复值，MySQL在更新操作前是会判断一下的，如下图所示：
与选择２的流程图相比，where 条件添加 a=3，可以判断出不需要更新 WHERE 里面有a和 SET 的 a 相同，UPDATE 执行时发现了这点，于是直接返回，故没有新增任何修改记录。
我们再多看一个案例，如果修改值是个新值，则会执行修改操作。
补充：
上面我们的验证结果都是在 binlog_format=statement 格式下进行的。
如果是 binlog_format=row 并且 binlog_row_image=FULL 的时候，由于 MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来了。
根据上面说的规则，“既然读了数据，就会判断”， 因此在这时候，select * from t where id=1，结果就是“返回 (1,2)”。
同理，如果是 binlog_row_image=NOBLOB, 会读出除 blob 外的所有字段，在我们这个例子里，结果还是“返回 (1,2)”。
对应的代码如图 15 所示。这是 MySQL 5.6 版本引入的，如下图所示：
如果表中有 timestamp 字段而且设置了自动更新的话，那么更新“别的字段”的时候，MySQL 会读入所有涉及的字段，这样通过判断，就会发现不需要修改。
小结
InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
- 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
- 对于读提交，查询只承认在语句启动前就已经提交完成的数据；
而当前读，总是读取已经提交完成的最新版本。
一致性读针对不加锁的查询语句，更新语句绝对是当前读，加锁的查询语句也是当前读。
你也可以想一下，为什么表结构不支持“可重复读”？这是因为表结构没有对应的行数据，也没有 row trx_id，因此只能遵循当前读的逻辑。
当然，MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可重复读。
