MySQL
六、MySQL事务
6.1事务的基础介绍
一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。
MySQL中事务管理的过程:
-- 开启事务
-- 注意！！！begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句
（第一个快照读语句），事务才真正启动。如果你想要马上启动一个事务，可以使用
-- start transaction with consistent snapshot 这个命令。
START TRANSACTION;
或者BEGIN;
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 回滚事务(出现问题)
ROLLBACK;
-- 提交事务(没出现问题)
COMMIT;
-- 查看事务具体状态
select * from information_schema.Innodb_trx\G
事务的提交方式：
- 提交方式
- 自动提交(MySQL默认为自动提交)
- 手动提交
- 修改提交方式
- 查看提交方式
-- 标准语法
SELECT @@AUTOCOMMIT; -- 1代表自动提交 0代表手动提交
-- 或者
show variables like 'transaction_isolation';
- 修改提交方式
-- 标准语法
SET @@AUTOCOMMIT=数字;
-- 修改为手动提交
SET @@AUTOCOMMIT=0;
6.2事务的四大特征(ACID)
事务有四个属性：
- A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
- C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
- I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
- D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
6.3事务的隔离级别
多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。数据库共有四种隔离级别，分别是：
| 1 | 读未提交 | read uncommitted |
|---|---|---|
| 2 | 读已提交 | read committed |
| 3 | 可重复读 | repeatable read |
| 4 | 串行化 | serializable |
不同的隔离级别会产生不同的问题（读写并发）：
| 问题 | 现象 |
|---|---|
| 脏读 | 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致 |
| 不可重复读 | 是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致 |
| 幻读 | select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功 |
有的博客提到了"lost update"的问题，这是“写写并发”的问题，并不属于这里的讨论范围。MySQL里update是read-write，是一种当前读，包括insert和delete也是，具体可参考：当前读和快照读、更新丢失。
补充下lost update，在数据库现有的任何隔离级别下都不会发生丢失更新，因为在更新一个数据时，如果当前事务没有提交，那么另一个事务是无法提交的。但是在业务层面的丢失更新时值得注意的，比如银行转账，假设余额有10000元，两个线程都是先查询余额再进行扣除，线程A转走1000，剩余9000，此时线程B转走1元，更新的时候设置余额为9999元，导致凭空多出了钱。解决办法就是串行化，在第一步查询的时候使用当前读，先加锁。
幻读和不可重复读的区别：
- 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
- 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）
各隔离级别会出现的问题总结：
| 隔离级别 | 读数据一致性 | 出现脏读 | 出现不可重复读 | 出现幻读 | 数据库默认隔离级别 | |
|---|---|---|---|---|---|---|
| 1 | read uncommitted（读未提交） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 | |
| 2 | read committed（读已提交） | 语句级 | 否 | 是 | 是 | Oracle / SQL Server |
| 3 | repeatable read（可重复读） | 事务级 | 否 | 否 | 是 | MySQL |
| 4 | serializable （串行化） | 最高级别，事务级 | 否 | 否 | 否 |
事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差，所以要根据具体的应用来确定合适的事务隔离级别。
在MySQL中操作隔离级别的相关sql
- 查询数据库隔离级别
-- 标准语法
SELECT @@TX_ISOLATION;
-- MySQL 8.0 该命令改为
SELECT @@transaction_isolation;
- 修改数据库隔离级别
-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
-- 例如修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;
更多的关于数据库隔离级别可参考：理解MySQL隔离级别：可重复读、再谈数据库事务隔离性。
6.4MVCC
-
先补充一点相关概念：
-
当前读（current read）
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
- 快照读（snapshot read）
不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，在很多情况下，避免了加锁操作，降低了开销；快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
数据库并发场景有三种，分别为：
读-读：不存在任何问题，也不需要并发控制读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读写-写：有线程安全问题，可能会存在更新丢失问题。
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。用来解决读-写冲突的无锁并发控制，在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的。
6.4.1隐式字段
InnoDB存储引擎在每行数据的后面添加了三个隐藏字段：
1. DB_TRX_ID(6字节)：表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
2. DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息。
3. DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。
6.4.2Read View
class ReadView {
/* ... */
private:
trx_id_t m_low_limit_id; /* 大于这个 ID 的事务均不可见 */
trx_id_t m_up_limit_id; /* 小于这个 ID 的事务均可见 */
trx_id_t m_creator_trx_id; /* 创建该 Read View 的事务ID */
trx_id_t m_low_limit_no; /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */
ids_t m_ids; /* 创建 Read View 时的活跃事务列表 */
m_closed; /* 标记 Read View 是否 close */
}
上面是Read View 结构源码，Read View 的作用是利用 consistent read view 提供某一时刻事务系统的快照, 后续数据的读通过 Read View 来完成对应的事务可见性. Read View 中的m_low_limit_id和m_up_limit_id分别用来判断事务的可见性. m_low_limit_no用来判断是否可以被purge线程删除，m_ids包括当前 Read View 的活跃事务链表, 当事务需要读取一条 Record 时，会通过 Record 中 trx id 和m_ids中的活跃事务链表对比，判断当前 Record 是否对当前事务可见。
事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力，可参考：快照读案例中的例子。
简单介绍下上面的几个概念：
- Purge线程：为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下旧记录的deleted_bit，并不真正将旧记录删除。为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
- m_low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
- m_up_limit_id：活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
- m_ids：Read View创建时其他未提交的活跃事务ID列表，按照大小降序排列。创建Read View时，将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。不包括当前事务自己和已提交的事务。
- m_creator_trx_id：当前创建事务的ID，是一个递增的编号
6.4.3undo log
undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。
大多数对数据的变更操作包括 insert/update/delete，在InnoDB里，undo log分为如下两类：
- insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
- update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。
记录行修改的流程：
- 事务A先对该行加排它锁
- 然后把该行数据拷贝到undo log中，作为旧版本
- 拷贝完毕后，修改原始数据，并且修改DB_TRX_ID为事务A的ID, 回滚指针指向拷贝到undo log的旧版本（事务提交前即修改DB_TRX_ID，此外还会将修改后的最新数据写入redo log）
- 事务提交，释放排他锁
update undolog大致结构：（图源，这个博主整理个很多MySQL相关的思维导图）
insert undo log 结构差不多，但是没有事务相关的部分和旧值部分。
6.4.4可见性比较算法
在innodb中，创建一个新事务后，执行第一个select语句的时候，innodb会创建一个快照（read view），快照中会保存系统当前不应该被本事务看到的其他活跃事务id列表（即m_ids）。当用户在这个事务中要读取某个记录行的时候，innodb会将该记录行的DB_TRX_ID与该Read View中的一些变量进行比较，判断是否满足可见性条件。
假设当前事务要读取某一个记录行，该记录行的DB_TRX_ID（即最新修改该行的事务ID）为trx_id，Read View的活跃事务列表m_ids中最早的事务ID为m_up_limit_id，将在生成这个Read Vew时系统出现过的最大的事务ID+1记为m_low_limit_id（即还未分配的事务ID）。
具体的比较算法如下:
-
如果 trx_id < m_up_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之前就提交了，所以该记录行的值对当前事务是可见的。跳到步骤5。
-
如果 trx_id >= m_low_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤4。
-
如果 m_up_limit_id <= trx_id < m_low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表trx_ids进行查找（源码中是用的二分查找，因为是有序的）：
(1) 如果在活跃事务列表trx_ids中能找到 id 为 trx_id 的事务，表明：在“当前事务”创建快照前，“该记录行的值”被“id为trx_id的事务”修改了，但没有提交；或者在“当前事务”创建快照后，“该记录行的值”被“id为trx_id的事务”修改了（不管有无提交）；这些情况下，这个记录行的值对当前事务都是不可见的，跳到步骤4；
(2)在活跃事务列表中找不到，则表明“id为trx_id的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见，跳到步骤5。
-
在该记录行的 DB_ROLL_PTR 指针所指向的undo log回滚段中，取出最新的的旧事务号DB_TRX_ID, 将它赋给trx_id，然后跳到步骤1重新开始判断。
-
将该可见行的值返回。
上面的流程可参考下面两个示意图加以理解。
6.4.5RR和RC的Read View产生区别
Repeatable Read：只有事务在begin之后，执行第一条select（读操作）时, 才会创建一个快照(read view)，将当前系统中活跃的其他事务记录起来；并且事务之后都是使用的这个快照，不会重新创建，直到事务结束。
Read Committed：事务在begin之后，执行每条select（读操作）语句时，快照会被重置，即会重新创建一个快照(read view)。也就是RC级别是MVCC没有解决不可重复读问题。
这里的读操作指的是快照读，如果是当前读，会对访问数据加锁，读取的是最新的数据。
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）
6.4.6MVCC与幻读
说下结论：
MySQL的RR级别，MVCC能解决快照读的幻读问题，MVCC+next key lock 能解决当前读的幻读问题。
具体可参考：InnoDB幻读问题
这个小结大部分内容参考自：MVCC、innodb事务分析
注：内容是从语雀上的学习笔记迁移过来的，有些参考来源已经无法追溯，侵权私删。
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
