MySQL事务杂谈

龙应台，1952年2月13日生于中国台湾高雄大寮乡眷村，现代作家、曾担任“台湾文化部部长”。1974年毕业于台湾国立成功大学外文系后，赴美国求学，后获堪萨斯州立大学英美文学博士。1988年迁居德国，在法兰克福大学任教授。1994年，出版《人在欧洲》。1998年，她的三部书《啊，上海男人》、《这个动荡的世界》、《故乡异乡》在上海相继发行。1999年，龙应台出任台北市首任文化局局长。2008年在香港大学教授任上获评为孔梁巧玲杰出人文学者。2010年11月15日，龙应台以260万人民币的版税收入，荣登作家富豪榜第16名，引发广泛关注。2014年12月5日，正式卸任台湾“文化部长”。

一、事务基础

一、定义

        事务是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询；如果其中任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

1. 启动方式
   • 方式一：begin OR start transaction，一致性视图是在执行第一个快照读语句时创建
   • 方式二：start transaction with consistent snapshot，一致性视图是事务开启后创建

二、事务的特征

1. 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
4. 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

三、事务隔离级别

1. 读未提交(read uncommitted): 即使没有提交当前事务中的修改，对其他事务也是可见的。事务可以读取未提交的数据，被称为脏读(Dirty Read),读取到的数据叫做“脏数据”。
2. 读提交(read committed):当前事务从开始到提交之前，所做的任何修改对其他事务都是不可见的，此级别有时候也被称为不可重复读(nonrepeatable read)。
3. 可重复读(repeatable read):MySQL 默认的事务隔离级别，多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读(Phantom Read)。InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。
4. 串行化(Serializable): 最高的隔离级别，强制事务串行执行，将全部的查询语句加上共享锁，可能导致大量的超时和锁争用的问题。

四、死锁

        死锁是指两个事务或者多个事务在同一资源的相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。

1. 锁等待超时 innodb_lock_wait_timeout
2. 主动死锁检测 innodb_deadlock_detect

五、两阶段提交

1. 写一条更新语句：update student set name = '张三' where id = 2;
2. 执行流程
   • 第一步，执行器先找引擎取id=2这一行。其中id是主键，引擎直接用树搜索找到这一行。如果id=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
   • 第二步，执行器拿到引擎给的行数据，把name值更新成张三得到新的一行数据，再调用引擎接口写入这行新数据。
   • 第三步，引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
   • 第四步，执行器生成这个操作的bin log，并把bin log写入磁盘。
   • 第五步，执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
3. 其中，redo log的写入拆成了两个步骤（三、五）：prepare和commit，这就是”两阶段提交”。

单独写一个update语句的时候就默认提交，两阶段是发生在“提交阶段”的。如果是有begin…commit; 的语句序列，那提交阶段是在执行“commit”这个语句的时候发生的。

六、bin log和redo log

1. bin log是逻辑日志，记录的是每个语句的原始逻辑；redo log是物理日志，记录的是“在某个数据页上做了什么修改”。
2. redo log是InnoDB引擎特有的；bin log是MySQL的Server层实现的，所有引擎都可以使用。
3. redo log是循环写的，空间固定会使用完；bin log是可以追加写的。

七、GTID

1. GTID的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。
2. 它由两部分组成，格式是：

GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

八、WAL

1. WAL(Write-Ahead Logging)，译为预写日志系统，数据库中一种高效的日志算法。对于非内存数据库而言，磁盘I/O操作是数据库效率的一大瓶颈。在相同的数据量下，采用WAL日志的数据库系统在事务提交时，磁盘写操作只有传统的回滚日志的一半左右，大大提高了数据库磁盘I/O操作的效率，从而提高了数据库的性能。

   • 核心思想：在数据写入到数据库之前，先写入到日志，再将日志记录变更到存储器中

2. 日志先行技术有两个问题需要工程上解决

   • 日志刷盘问题。由于所有对数据的修改都需要写日志，当并发量很大的时候，必然会导致日志的写入量也很大，为了性能考虑，往往需要先写到一个日志缓冲区，然后在按照一定规则刷入磁盘，此外日志缓冲区大小有限，用户会源源不断的生产日志，数据库还需要不断的把缓存区中的日志刷入磁盘，缓存区才可以复用，因此，这里就构成了一个典型的生产者和消费者模型。现代数据库必须直面这个问题，在高并发的情况下，这一定是个性能瓶颈，也一定是个锁冲突的热点。
   • 数据刷盘问题。在用户收到操作成功的时候，用户的数据不一定已经被持久化了，很有可能修改还没有落盘，这就需要数据库有一套刷数据的机制，专业术语叫做刷脏页算法。脏页(内存中被修改的但是还没落盘的数据页)在源源不断的产生，然后要持续的刷入磁盘，这里又凑成一个生产者消费者模型，影响数据库的性能。如果在脏页没被刷入磁盘，但是数据库异常crash了，这个就需要做奔溃恢复，具体的流程是，在接受用户请求之前，从checkpoint点(这个点之前的日志对应的数据页一定已经持久化到磁盘了)开始扫描日志，然后应用日志，从而把在内存中丢失的更新找回来，最后重新刷入磁盘。这里有一个很重要的点：在数据库正常启动的期间，checkpoint怎么确定，如果checkpoint做的慢了，就会导致奔溃恢复时间过长，从而影响数据库可用性，如果做的快了，会导致刷脏压力过大，甚至数据丢失

3. 比如当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到redo log里面并更新内存，这个时候更新就算完成了。同时InnoDB引擎会在适当的时候将这个操作记录更新到磁盘里面。

4. write 和 fsync 的时机，是由参数 sync_binlog 控制的：

   • sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
   • sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
   • sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

5. 为了控制redo log的写入策略，InnoDB 提供了innodb_flush_log_at_trx_commit参数，它有三种可能取值：

   • 设置为 0 的时候，表示每次事务提交时都只是把redo log留在redo logbuffer中
   • 设置为 1 的时候，表示每次事务提交时都将redo log直接持久化到磁盘
   • 设置为 2 的时候，表示每次事务提交时都只是把redo log写到page cache

---

二、事务隔离级别

一、事务隔离级别

1. READ UNCOMMITTED(读未提交)：解决更新丢失问题。如果一个事务已经开始写操作，那么其他事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现，即事务需要对某些数据进行修改必须对这些数据加X锁，读数据不需要加S锁。

   • 当前事务中的修改即使没有提交，对其他事务也是可见的，会产生脏读现象

2. READ COMMITTED(读已提交)：：解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现， 即事务需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成后立刻释放 S 锁，不用等到事务结束。

   • 一个事务开始时，只能“看见”已经提交的事务所做的修改，也称不可重复读
   • 不可重复读：在当前事务内多次读取同一个数据的同时，另一个事务对同一个数据进行了修改并提交，那么当前事务两次读取的数据可能不一样

3. REPEATABLE READ(可重复读)：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现，即事务需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成并不立刻释放 S 锁，而是等到事务结束后再释放。

   • 解决了脏读，保证在同一事务中多次读取同样记录结果是一样的，会产生幻读
   • 幻读：当前事务读取某个范围内记录的同时，另一个事务又在该范围内插入了新的记录，当前事务再次读取该范围的记录时会产生幻读
   • InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题

4. SERIALIZABLE(可串行化)：解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

   • 最高的隔离级别，通过强制事务串行执行，避免了幻读
   • 读取每一行的数据上都加锁，可能导致大量的超时和锁竞争，实际很少用到
   • 在要求确保数据的强一致性且可以接受没有并发的情形下才采用

各个隔离级别是通过视图来实现的，访问的时候以视图的逻辑结果为准。

• “可重复读”隔离级别，视图是在事务启动时创建的，整个事务存在期间都用这个视图
• “读提交”隔离级别，视图是在每个SQL语句开始执行的时候创建的
• “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念
• “串行化”隔离级别直接用加锁的方式来避免并行访问

demo

select * from student;
+----+---------+--------------+--------+
| id | name    | hobby        | score  |
+----+---------+--------------+--------+
|  1 | 尼格1   | 测试测试     |   100.00 |

读未提交

• 开两个客户端A和B，分别设置set transaction isolation level read uncommitted;

• 客户端A

  begin；
  select score from student where id = 1; 
  
  +--------+
  | score  |
  +--------+
  | 100.00 |

• 客户端B

  begin；
  update student set score = 10 where id = 1;
  此事务未提交，去客户端A

• 客户端A

  select score from student where id = 1;
  
  +-------+
  | score |
  +-------+
  |  10.00 |
  +-------+

读已提交

• 开两个客户端A和B，分别设置set transaction isolation level read committed;

• 客户端A

  begin；
  select score from student where id = 1; 
  
  +--------+
  | score  |
  +--------+
  | 100.00 |

• 客户端B

  begin；
  update student set score = 10 where id = 1;
  
  // 此事务未提交，去客户端A

• 客户端A

  select score from student where id = 1;
  
  +-------+
  | score |
  +-------+
  | 100.00|
  +-------+

• 客户端B

  • commit;// 提交事务

• 客户端A

  select score from student where id = 1;
  
  +-------+
  | score |
  +-------+
  | 10.00 |
  +-------+

可重复读

• 开两个客户端A和B，分别设置set transaction isolation level repeatable read;

• 客户端A

  begin；
  select score from student where id = 1; 
  
  +--------+
  | score  |
  +--------+
  | 100.00 |

• 客户端B

  begin；
  update student set score = 10 where id = 1;
  
  // 此事务未提交，去客户端A

• 客户端A

  select score from student where id = 1;
  
  +-------+
  | score |
  +-------+
  | 100.00|
  +-------+

• 客户端B

  • commit;// 提交事务

• 客户端A

  select score from student where id = 1;
  
  +-------+
  | score |
  +-------+
  | 100.00|
  +-------+

串行化

• 开两个客户端A和B，分别设置set transaction isolation level serializable;

• 客户端A

  begin；
  select score from student where id = 1; 
  
  +--------+
  | score  |
  +--------+
  | 100.00 |
  
  update student set score = 88 where id = 1;
  
  select score from student where id = 1; 
  
  +--------+
  | score  |
  +--------+
  | 88.00 |    

• 客户端B

  begin；
  select score from student where id = 1; 
   
  +--------+
  | score  |
  +--------+
  | 100.00 |
  
  
  update student set score = 10 where id = 1;
  
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

二、幻读

1. 幻读，指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

   • 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此幻读是在“当前读”下才会出现
   • 幻读仅仅指“新插入的行”

   

   • Q1 只返回 id=5 这一行；
   • 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
   • 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。

2. 产生幻读的原因是行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入了间隙锁 (Gap Lock)。

InnoDB的默认事务隔离级别是RR，基于多版本的控制技术MVCC实现。它不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。如何做到保证数据是一致的同时读取的数据是最新的数据？InnoDB提供了一个间隙锁的技术，也就是结合grap锁与行锁达到最终目的。当使用索引进行插入的时候，InnoDB会将当前的节点和上一个节点加锁。这样当进行select的时候，就不允许加x锁。那么在进行该事务的时候，读取的就是最新的数据。

3. 快照读(snapshot read)：简单的select操作

4. 当前读(current read)

   • select … lock in share mode
   • select … for update
   • insert
   • update
   • delete

5. 总结

   在mysql中提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。这个在使用不同的语句的时候可以动态选择。不加lock in share mode等之类的就使用mvcc，否则使用next-key。mvcc的优势是不加锁，并发性高，缺点是不是实时数据。next-key的优势是获取实时数据，但是需要加锁。同时需要注意几点：

   • 事务的快照时间点是以第一个select来确认的，所以即便事务先开始，但是select在后面的事务的update之类的语句后进行，那么它是可以获取后面的事务的对应的数据。
   • mysql中数据的存放还是会通过版本记录一系列的历史数据，这样可以根据版本查找数据。

读提交隔离级别下复现幻读

• 修改事务隔离级别为读已提交 set global transaction isolation level read committed

• 初始化表和数据

  create table table_phontom(
      id int(11) unsigned not null primary key auto_increment,
      name varchar(60) not null,
      phone varchar(11) unsigned not null key
  );
  
  alter table table_phontom add index(phone);
  
  insert into table_phontom(name,phone) values('tom','15555555555'),('jack','15666666666');

• 查询

  • T1时刻session A开启一个事务，查询phone=’15555555555’的记录，得到1条记录，即id=1，name=tom

  • T2时刻session B更新id=2的记录(默认提交事务)

  • T3时刻查询phone=’15555555555’的记录，得到2条记录，即id=1,2，name=tom,jack

  • T4时刻session C插入一条记录(默认提交事务)

  • T5时刻查询phone=’15555555555’的记录，得到3条记录，即id=1,2,3，name=tom,jack,jerry

  • T6时刻提交事务

    T5时刻，session A查询得到id=3的这条记录称为幻读

    时间\会话	session A	session B	session C
    T1	begin; select * from table_phontom where phone=’15555555555’;
    T2		update table_phontom set phone=’15555555555’ where id=2;
    T3	select * from table_phontom where phone=’15555555555’;
    T4			insert into table_phontom(name,phone) values(‘jerry’,’15555555555’)
    T5	select * from table_phontom where phone=’15555555555’;
    T6	commit;

可重复读下复现幻读(可重复读隔离级别已修复读数据时的幻读，写数据未修复)

• 修改事务隔离级别为可重复读set global transaction isolation level repeatable read

• 初始化表和数据

  create table dept(
      id int(11) unsigned not null primary key auto_increment,
      name varchar(100) not null
  );
  
  insert into dept(name) values('后勤部');

• 查询开始

  • T1时刻session A开启一个事务，查询dept所有记录(只有id=1，name=后勤部 一条记录)

  • T2时刻session B插入一条记录（id=2,name=测试部）

  • T3时刻，session A更新所有记录

  • T4时刻查询所有记录，得到id=1,2，name=研发部,研发部，此时出现了【写】幻读

    时间\会话	session A	session B
    T1	begin; select * from dept;
    T2		insert into dept(name) values(‘测试部’);
    T3	update dept set name=’研发部’;
    T4	select * from dept;
    T5	commit;

三、不可重复读

1. MySQL默认隔离级别可重复读，已通过多版本并发控制MVCC解决了不可重复读

2. 修改为读提交方可复现set global transaction isolation level read committed

3. 查询

   • T1时刻session A开启一个事务，查询id=1的记录，即id=1，name=tom，phone=’15555555555’
   • T2时刻session B根据主键ID更新了记录
   • T3时刻根据ID查询记录，得到id=1，name=tom，phone=’13333333333’，出现不可重复读

   时间\会话	session A	session B
   T1	begin; select * from table_phontom where id=1;
   T2		update table_phontom set phone=’13333333333’ where id=1;
   T3	select * from table_phontom where id=1;
   T4	commit;

幻读针对于插入操作，不可重复读针对于更新操作

四、一致性读

CREATE TABLE `transaction_test` (
  `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);

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot这个命令。

InnoDB 里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id为25的事务更新的，因此它的row trx_id也是 25。

图中的三个虚线箭头，就是undo log，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

这个视图数组把所有的 row trx_id 分成了几种不同的情况。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况
   • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

比如，对于图 2 中的数据来说，如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11。

接下来，我们继续看一下图 1 中的三个事务，分析下事务 A 的语句返回的结果，为什么是 k=1。这里，我们不妨做如下假设：

1. 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
2. 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
3. 三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。

这样，事务A的视图数组就是 [99,100], 事务B的视图数组是 [99,100,101], 事务C的视图数组是 [99,100,101,102]。

从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了。

好，现在事务 A 要来读数据了，它的视图数组是 [99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

• 找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；
• 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
• 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

总结：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。

现在，我们用这个规则来判断图 4 中的查询结果，事务 A 的查询语句的视图数组是在事务 A 启动的时候生成的，这时候：

• (1,3) 还没提交，属于情况 1，不可见；
• (1,2) 虽然提交了，但是是在视图数组创建之后提交的，属于情况 2，不可见；
• (1,1) 是在视图数组创建之前提交的，可见。

五、当前读

按照上面一致性读的分析逻辑，事务 B 的 update 语句，结果好像不对？事务 B 的视图数组是先生成的，之后事务 C 才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来？是的，如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。但是当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。

所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”(current read)。

因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101。在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。

其实，除了 update 语句外，select 语句如果加锁，也是当前读，如：

select k from t where id=1 lock in share mode;//读锁（S 锁，共享锁）

select k from t where id=1 for update;//写锁（X 锁，排他锁）

再往前一步，假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢？

事务 C’的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？

这时候“两阶段锁协议”就要上场了。事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

那么，我们再看一下，在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别应该是多少呢？

这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。

下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化，就是图中的 read view 框。（注意：这里，我们用的还是事务 C 的逻辑直接提交，而不是事务 C’）

这时，事务 A 的查询语句的视图数组是在执行这个语句的时候创建的，时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

• (1,3) 还没提交，属于情况 1，不可见；
• (1,2) 提交了，属于情况 3，可见。

所以，这时候事务 A 查询语句返回的是 k=2，事务 B 查询结果 k=3。

六、总结

1. 对于可重复读，查询只承认在事务启动前就已经提交完成的数据
2. 对于读提交，查询只承认在语句启动前就已经提交完成的数据
3. 当前读，总是读取已经提交完成的最新版本
4. 当前读的规则，就是要能读到所有已经提交的记录的最新值

---

三、MVCC

一、幻读

1. 幻读：多次读取一个范围内的记录(包括直接查询所有记录结果或者做聚合统计)，发现结果不一致(标准档案一般指记录增多，记录的减少应该也算是幻读)。
   • 对于幻读，MySQL的InnoDB引擎默认的RR级别已经通过MVCC自动帮我们解决了，所以在该级别下不好模拟出幻读的情况。

二、模拟幻读

1. 参考别人

   select * from student;
   +----+---------+--------------+--------+
   | id | name    | hobby        | score  |
   +----+---------+--------------+--------+
   |  1 | 尼格1   | 测试测试     |   100.00 |

   • 开两个客户端A和B，分别设置set transaction isolation level repeatable read;
   • 客户端B

   begin;
   select * from student;
   +----+---------+--------------+--------+
   | id | name    | hobby        | score  |
   +----+---------+--------------+--------+
   |  1 | 尼格1   | 测试测试     |   100.00 |

   • 客户端A

   insert into student(id,name,hobby,score) value(2,'张三','吹牛','120');
   select * from student;
   +----+---------+--------------+--------+
   | id | name    | hobby        | score  |
   +----+---------+--------------+--------+
   |  1 | 尼格1   | 测试测试     | 100.00 |
   |  2 | 张三    | 吹牛         | 120.00 |
   +----+---------+--------------+--------+

   • 客户端B

   select * from student;
   +----+---------+--------------+--------+
   | id | name    | hobby        | score  |
   +----+---------+--------------+--------+
   |  1 | 尼格1   | 测试测试     | 100.00   |
   +----+---------+--------------+--------+
   
   insert into student(id,name,hobby,score) value(2,'张三','吹牛','120');
   
   ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' //select时没有id为2的记录，执行插入时却提示主键重复出现幻读

三、MVCC

1. MVCC：Multi-Version Concurrency Control，多版本并发控制，即同一条记录在系统中可以存在多个版本。具体来说其实就是在每一行记录的后面增加了两个隐藏列，记录创建版本号和删除版本号。而每一个事务开启的时候，都会有唯一的递增版本号，被操作的数据会生成一条新的数据行(临时)，但是在提交前对其他事务是不可见的，对于数据的更新(包括增删改)操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中。这样保证了每个事务操作的数据，都是互不影响，也不存在锁的问题。

   • MVCC的实现是通过保存数据在某个时间点的快照来实现的，不管需要执行多长时间
   • 只要事务开始时间相同，每个事务看到的数据都是一致的
   • 事务开始的时间不同时，每个事务对同一张表，同一时刻看到的数据可能是不一样的
   • 不同的存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制
   • 每开始一个新的事务，系统版本号都会严格的自动递增
   • 事务开始时刻的系统版本号作为事务的版本号，用来和查询到的每行记录的版本号进行比较

2. InnoDB的内部实现中为每一行数据增加了三个隐藏列用于实现MVCC。

   • DB_TRX_ID：插入或更新行的最后一个事务的事务标识符。(删除视为更新，将其标记为已删除)
   • DB_ROLL_PTR：写入回滚段的撤消日志记录(若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息)
   • DB_ROW_ID：行标识(隐藏单调自增id)

3. MVCC的工作过程

   • SELECT：InnoDB会根据两个条件来检查每行记录

     • InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行，这样可以确保数据行要么是在开始之前已经存在了，要么是事务自身插入或修改过的
     • 行的删除版本号(DB_ROLL_PTR)要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

   • INSERT

     • InnoDB为新插入的每一行保存当前系统版本号作为行版本号

   • DELETE

     • InnoDB为删除的每一行保存当前的系统版本号作为行删除标识

   • UPDATE

     • InnoDB会插入一行记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识