MySQL45讲笔记

01、基础架构：一条SQL语句是如何执行的

02、日志系统：一条SQL更新语句是如何执行的？

03、事务隔离：为什么你改了我还看不见？

事务的四个特性

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

为什么会有隔离级别的概念?

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

并发事务处理的问题

脏读	一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态，这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些 “脏” 数据，并据此做进一步的处理， 就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。	读到别人未提交的数据
不可重复读	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性	读到别人已经提交的数据
幻读	一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。	读到别人新插入的数据

注意：幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

SQL 标准的事务隔离级别

读未提交（read uncommitted）	一个事务还没提交时，它做的变更就能被别的事务看到。
读提交（read committed）	一个事务提交之后，它做的变更才会被其他事务看到。
可重复读（repeatable read）	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化（serializable ）	“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

抛砖引玉

事务隔离是如何实现的？

前置知识

视图

虚拟表（view）	它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
一致性读视图(consistent read view)	InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。 在事务隔离的时候，事务T启动的时候会创建一个视图read-view,之后事务T执行期间，即使有其他事务修改了数据，事务T还是看到和启动时一样的。

事务的起点

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。

如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。一般默认autocommit=1。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。标准SQL事务隔离级别的实现是依赖锁的。

读未提交（read uncommitted）	直接返回记录上的最新值，没有视图概念			事务对当前被读取的数据不加锁; 事务在更新某数据的瞬间(就是发生更新的瞬间) ,必须先对其加行级共享锁，直到事务结束才释放。
读提交（read committed）	视图是在每个 SQL 语句开始执行的时候创建的。		MySQL的InnoDB引擎在提交读级别通过MVCC解决了不可重复读的问题	事务对当前被读取的数据加行级共享锁(当读到时才加锁)**， 一旦读完该行，立即释放该行级共享锁; 事务在更新某数据的瞬间(就是发生更新的瞬间) ,必须先对其加行级排他锁**，直到事务结束才释放。
可重复读（repeatable read）	视图是在事务启动时创建的，整个事务存在期间都用这个视图。		在可重复读级别通过间隙锁解决了幻读问题	事务在读取某数据的瞬间(就是开始读取的瞬间) , 必须先对其加行级共享锁，直到事务结束才释放; 事务在更新某数据的瞬间(就是发生更新的瞬间)， 必须先对其加行级排他锁，直到事务结束才释放。
串行化（serializable ）	直接用加锁的方式来避免并行访问。			事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放; 事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。

Undo日志实现事务

undo日志是为了保障数据库的原子性和一致性，是一种逻辑日志。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

Undo日志作用

• 用于事务回滚
• MVCC

inser undo log	update undo log
是insert操作中产生的undo log，因为只对本事务可见，该类undo log在事务提交后就可以删除，不需要进行purge操作。	update undo log是delete和update操作产生的undo log。此类undo log是MVCC的基础，在本事务提交后不能简单的删除，需要放入purge队列purge_sys->purge_queue等待purge线程进行最后的删除。

在Innodb中使用表空间，回滚段，页等多级概念结构实现undo功能，并随版本多次改进。

快照

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

实际上并不是说要把整个数据库都拷贝一遍。而是主要记录事务的id。
在InnoDB中里面的每一个事务都有一个唯一id 叫做transaction id。
事务开始的时候像InnoDB申请的。并且有严格的递增顺序。

因为每行数据会有多个版本，每一次事务更新数据的时候都会生成一个新的数据版本，并且吧自己的事务id transaction id 赋值给这个数据版本你的事务ID。

这个属性记录为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

在可重复读中，以事务自己的启动时刻为准，如果一个数据版本是在我启动之前生成的，就可以看见，承认。如果是我启动以后才生成的，我就不认，我必须找到它的上一个版本。

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

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

图中的虚线其实是undo log。

怎么实现？

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：
如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

具体例子
现在有事务ABC
1.在事务A开始前，系统里面只有一个活跃事务ID是99；
2.在事务A、B、C的ID为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事务去查看的时候，就会在历史版本中一个个看，第一个看到101的，不在可见视图中，就是大于100，不看，第二个102，大于100，不看。再来看到90，比低水位小，可看。
这样事务A不论什么时候查询，看到的行数据的结果都是一致的，所以叫做一致性读

再看更新逻辑
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

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

如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。