MySql 事务

最近重新复习了下MySql相关的知识，按苏格拉底提问法整理了下MySql InnoDB引擎下的事务问题。

起点从业务需求出发。以银行系统转账功能为例，1.从A账户扣100。2.再去B账户加100，结束。
CURD是原子性的，但多个语句可能1执行成功，2执行失败，此时业务功能就出问题了。

如何保证执行多条SQL语句时，可以作为整体一起成功或一起失败？

使用事务机制，MySql的InnoDB引擎支持事务。事务需要做到 ACID 即原子性、一致性、隔离性、持久性。其中事务的原子性保证了多条SQL可以作为整体一起成功或一起失败。

InnoDB如何实现的事务原子性？

使用撤销日志 undo log。事务里的每个SQL都会在undo log里添加一条反向语句，如insert时undo log里就加个delete。所以当事务出错或主动回滚时，就会按undo log依次回复到事务开始前状态了。成功则也是所有SQL都执行成功。

如果事务正常提交，但从内存写入到磁盘的中途出错了怎么办？

先介绍背景：为了提高性能，数据的修改会先存在内存的Buffer Pool中，然后由后台线程master thread周期性将其刷到磁盘。但从内存写入磁盘的过程中可能出错，使用重做日志 redo log 解决该问题。

事务过程中会先把修改的SQL信息记录到内存里的redo log buffer里，提交时把redo log buffer保存到磁盘 redo log file 中。之后，如果缓冲池里数据写入磁盘中途出错的话，就可以用磁盘的redo log file恢复了。

事务的原子性解决了，是否就没其他问题了？

undo log + redo log 实现了原子性、持久性、一致性。此外还有个隔离性问题。即多线程下多个事务一起操作数据，也可能出现一些问题，比如：

脏读：线程1事务修改某数据，线程2事务读取了这条修改后的进行后续操作，但最后线程1事务回滚了数据。
幻读：线程1事务在某个位置or某范围查询数据，之后线程2事务新插入了数据，之后线程1事务再同条件一查结果变了。
不可重复读：线程1事务先后两次读取某数据，中途线程2事务修改了数据，造成线程1事务读取的两次数据不一致。

以上这些问题总结就是，线程在事务中，读取的数据如果一直变化不可靠，会造成整体事务结果都错误。希望读取数据是一直正确且一致的。

如何解决隔离性问题？

SQL提供了事务隔离级别设置 mysql1
InnoDB默认第三种，可以避免脏读幻读，但可能有不可重复读情况。

InnoDB事务隔离级别如何实现的？

事务隔离级别只是SQL的标准规范，具体实现在InnoDB引擎里。总体实现方案有两种：

加锁阻塞住，事务1未完成时，其它事务阻塞直到事务1释放。
MVCC多版本控制，不阻塞，事务1未完成时，其它事务读取事务1未修改前的正确数据。

可以直接整个表加锁。简单粗暴但并发性能较差。Serializable 的实现方式就是这个。RU 略过，基本没啥控制，一般也不会用。

脏读：

方式1.事务里读取某数据时加锁，这样其它线程事务中就无法修改了，会阻塞住直到事务1释放。
方式2.锁住并发性能低，使用MVCC实现，记录多个版本的数据，其它事务读取时用自己需要的版本。

不可重复读：

类似脏读，一个是读取了其他线程未提交过程中的不一致数据，一个是读取了其它线程提交前后的不一致数据。所以解决方式也是两种，锁 MVCC。

幻读：

类似前面俩，但前面俩是「数据内容不一致」的情况，幻读是针对「有无数据不一致」的情况。因此解决无法锁数据了，只能锁空间，要用到间隙锁。

MVCC比锁性能更好，无阻塞。

MVCC是什么？

Multi-Version Concurrency Control 多版本并发控制。维护一个数据的多个版本，使得并发事务下可以非阻塞的解决读写冲突问题。

MVCC的原理是什么？

隐藏字段 + undo log + readView

MVCC会在表中新增两个隐藏字段
- DB_TRX_ID：最近修改事务ID，记录了最新插入或修改了记录的事务ID。
- DB_ROLL_PTR：回滚指针，执行这条记录的上一个版本，用于配合undo log指向上一个版本。
undo log 回滚日志，用隐藏字段ROLL_PTR连成链表
ReadView结合以上信息确定快照读读哪个版本数据
- trx_ids：当前未提交的所有事务 id 组成的数组
- up_limit_id（min）：trx_ids 中的最小值
- low_limit_id（max）：当前所有事务版本 id 的最大值+1，也就是未来下一个出现的事务 id
- creator_trx_id：本事务的 id （也查到称为 current_trx_id 的，暂时没求证）

简单来说就是为每笔数据的每个事务内每次提交，新建一个记录。当读取的时候按需取对应记录版本的数据。

mysql2
具体原理+案例分析，如上图。 4个事务操作同一条数据。原始数据id、age、name、TRX_ID、ROLL_PTR = (30, 30, A30, 1, null) 默认事务id1，无指针。
先只看修改的，按时间顺序新建了一个undo log记录链表，每次修改都记录之前的数据信息，也用于回滚事务。

事务2将age改为3。
1. undo log 链表加入一条数据，记录修改之前的信息 0x0001: (30, 30, A30, 1, null)
2. 当前记录更改为 (30, 3, A30, 2, 0x0001）TRX_ID=2记录当前修改的事务id，ROLL_PTR=0x0001指向修改前日志undo log
事务3将name改为A3。
1. undo log 记录旧数据 0x0002:（30, 3, A30, 2, 0x0001)
2. 当前记录更改为 (30, 3, A3, 3, 0x0002)
事务4将age改为10。
1. undo log 记录旧数据 0x0003:（30, 3, A3, 3, 0x0002)
2. 当前记录更改为 (30, 10, A3, 4, 0x0003)

当事务中查询时，会创建一条ReadView，如图中事务5第一次查询位置时：
此时事务2已提交，事务3已经修改了数据但未提交，其它事务无修改。事务5是和他们同时开启的，希望读取的是事务2已提交的数据，而事务3已修改未提交的数据是不想脏读到的。

ReadView可以满足我们的需求吗？下面分析下ReadView是如何具体使用的。

快照读时会先把当前数据带入ReadView规则，不符合的话继续链表下一条，直到找到目标数据。

当前记录 TRX_ID = 4 ，1~4规则都不符合。
链表下一条 0x0003: TRX_ID = 3，1~4规则都不符合。
链表下一条 0x0002: TRX_ID = 2，符合规则2 < min_trx_id(3) ，读取该条数据 ( 30, 3, A30 ) 符合需求。

同理事务5的第二个ReadView按照规则会读取到undo log里的 0x0003：（30， 3， A3）符合需求。由此可见MVCC可以避免脏读问题。

那MVCC可以解决不可重复读问题吗？

可以。以上举例是RC级别下，RR级别里几乎没区别，但ReadView只会在当前事务的第一次查询生成，事务内的后续查询都会使用第一次生成的ReadView，这样就保证了多次查询的结果一致，避免了不可重复读问题。

幻读问题如何解决呢？

MVCC是针对数据的多个版本，幻读是多次查询中途在原先没有数据的地方新增了一条，MVCC就无法解决了，还是得依靠锁。

表锁太重，而行锁是针对数据的也不行，因此引入了新的锁类型，间隙锁。用于把数据和数据直接的空隙锁住（PAGE之间是链表，但PAGE内是数组保存的数据，数组中无数据的部分就是空隙）。