【MySQL】事务管理

        MySQL服务器作为一个网络服务器，一定会被多个客户端进行同时访问；多个客户端对同一份数据进行CURD操作，不可避免的会出现并发问题。

        MySQL中有一个表，是银行存储用户余额的记录；当应用层有一个需要：常某向刘某转账100元。则一定会有以下的SQL语句组。

1. 找到与常某相关的一条记录，将其中的余额字段： - 100元
2. 找到与刘某相关的一条记录，将其中的余额字段： + 100元

-- 如果第一条SQL语句运行完毕了，准备运行第二条SQL语句又或者第二条语句运行期间出错了，
-- 是不是该保证数据操作前的一致性？以方便下次重新操作？

一、事务概念

1. 什么是事务

        事务就是一组SQL语句；这些SQL语句运行的时候，这一组DML语句要么全部成功，要么全部失败，是一个整体。事务具有以下四种属性（ACID）：

• 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性（Consistency）：MySQL内部的一致性、应用层业务逻辑的一致性，需同时满足。
• 隔离性（Isolation，又称独立性）：数据库运行多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2. 为什么要有事务

        为了简化应用层的编程模型，让应用层不再考虑各种潜在问题或并发问题；MySQL并不是生来就具有的，是应用层需要这种需求，因此事务本质是为了应用层服务的。

3. 事务的版本支持

        查看数据库引擎：

-- 并不是所有的存储引擎都支持事务，如MyISAM就不支持事务
-- show engines; -- 显示格式不同

show engines \G;

*************************** 1. row ***************************
      Engine: InnoDB  -- 存储引擎名称
     Support: DEFAULT -- 是否支持该存储引擎,InnoDB是MySQL默认的
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES     -- 支持事务  
          XA: YES
  Savepoints: YES     -- 设置回滚点  
*************************** 2. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO      -- 不支持事务  
          XA: NO
  Savepoints: NO

4. 事务的提交方式：

        事务的提交方式有两种：

• 自动提交：每一条SQL语句都是独立事务。
• 手动提交：需要手动commit了，才能保证修改持久化。

-- 查看事务提交方式
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.05 sec)

-- 设置事务提交方式
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

5. 事务的操作

        a. 启动显示事务

-- 启动显示事务 start transaction ，也可以是begin
begin;

-- 有相关关系的SQL语句组

commit;

        b. 手动提交事务

-- 需要保证autocommit的值为OFF;
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

-- 有相关关系的SQL语句组

commit;

        c. 自动提交事务

-- 也间接证明了单条SQL语句与事务的关系
-- 需要保证autocommit的值为ON;
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

-- 每一条SQL语句，都会自动完成事务的提交

        d. 设置回滚点 / 回滚到事务启动前

-- 设置回滚点
savepoint X[保存点标识符]
-- 回滚到X
rollback X
-- 回滚到事务开始前
rollback X

-- 注意：事务一旦完成提交，则无法回滚。 

        总结：

• 对于InnoDB的每一条SQL语言都默认封装称事务，自动提交。
• 事务可以回滚，是原子性的体现。
• 事务一旦提交，则无法回滚，是持久性的体现。

二、事务隔离级别

1. 隔离性的理解

        MySQL服务器中，一定会运行大量的事务，不可避免的会出现多个事务访问同一张表，甚至同一条记录（MySQL中，表中的一行信息，成为记录）；如果规定它们仅以先后顺序进行运行，那MySQL的访问效率就太低下了。而隔离性出现的目的，首先为了MySQL支持事务的并发运行、其次解决事务并发运行出现的问题，如多个事务相互影响而导致的脏读、幻读、不可重复读等问题。其中并发场景有以下三种：

• 读读：不存在任何问题，也不需要并发控制。
• 写读：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读。
• 写写：有线程安全问题，可能会存在更新丢失问题。  

2. 隔离级别相关操作

        a. 查看隔离级别

-- 全局隔离级别，数据来自MySQL配置文件
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

-- 会话隔离级别，数据来自全局隔离级别
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| SERIALIZABLE                    |
+---------------------------------+
1 row in set (0.01 sec)

-- 会话隔离级别，数据来自全局隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)

        b. 设置隔离级别

-- 设置当前会话 or 全局隔离级别语法
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE

3. 读未提交

        read uncommited，当写事务对表进行增删改，并发运行的读事务，会看到写事务的中间状态。建议阅读者，可以自己进行试验验证。

        脏读：读到写事务的中间状态。

        幻读：相同的select语句，读到数据行数发生变化。

        不可重复读：相同的select语句，某些行中的某些字段可能发生了变化。

4. 读提交

        read committed，当写事务对表进行增删查改，并发运行的读事务，不会看到写事务的中间状态，当写事务事务提交之后，读事务才可看到写事务的增删改的结果。

        幻读：相同的select语句，读到数据行数发生变化。

        不可重复读：相同的select语句，某些行中的某些字段可能发生了变化。

5. 不可重复读

        repeatable read，当写事务对表进行增删查改，并发运行的读事务，不会看到写事务的中间状态；当写事务事务提交之后且读事务也完成了事务提交，读事务所在的MySQL客户端在启动下一次事务时，才可看到写事务的增删改的结果。

        帮助理解：两个小孩在浑浊的池子游泳（正在运行各自的事务），其中一个小孩上岸了（读事务完成事务提交），才能看到另一个小孩是否上岸。

6. 串行化

        serializable，串行化，任何时间，只允许一个事务运行。     

三、一致性

        一致性，它是一个比较抽象的概念，我曾一致的以为，一致性不就是原子性吗？数据从一个确定的状态，到达另一个确定的状态，这不就是原子性吗？它至少离不开原子性的支持，我是不是可以理解为，只要保证了事务的原子性，就能保证事务的一致性？但是这里的一致性，不光MySQL内部的一致性，也包含的业务逻辑的一致性。

        MySQL内部需保证一致性：如表数据每个字段都应该合法，符合约束。符合MySQL的定义的规则。而它是由原子性来保障。

        业务逻辑的一致性：需要应用层配合，如：写正确的SQL语句。

-- 银行的转账逻辑
-- 常某向刘某转账100元。则一定会有以下的SQL语句组。
-- 业务逻辑的一致性，就是要保证常某和刘某的余额总和是不变的。而这需要应用层输入正确的SQL语句，这点仅依赖于MySQL是不足的。

四、深入理解读写并发

        多版本并发控制（MVCC）是一种用来解决读写冲突的无锁并发控制

• 为读写事务提供了并发支持，不会互相堵塞的现象，提高了数据库并发读写的性能。
• 为读提交、可重复读等隔离级别提供了技术支持。
• 同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。
• 下面我们来理解MVCC

1. 对事务的建模

        MySQL服务器中，一定存在大量的事务，有的事务刚启动，有的事务正在运行，有的时候即将提交。所以MySQL需要对事务进行管理，会为每一个事务生成一个对象（管理者事务的诸多属性），其中需要注意的是，每一个事务都会被分配一个线性增长事务ID，顾名思义越新的事务，其ID值一定越大。

2. 4个记录隐藏字段

        在我们创建表的时候，其实会存在4个默认字段：

• DB_TRX_ID：最近修改事务的ID，记录创建这条记录/最后依次修改该记录的事务ID。
• DB_ROLL_PTR：回滚指针，指向这条记录的上一本版本（这些数据在undo log中）。
• DB_ROW_ID：隐含的自增ID（隐藏主键），如果数据表没用主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。
• flag：标识该行记录是否已被删除。当我们学过索引，了解索引的底层原理后，就会知道MySQL中的表在内存中是以B树的形式组织的，每一页存储着以数组组织的表数据（目录页除外），为删除记录的时候，不需要数据的挪动，提高效率，就需要flag字段的支持。

3. 三个日志

• undo log：用于存储数据修改前的历史备份，用于事务回滚和多版本的历史数据。
• redo log：用于修改后的记录数据，用于崩溃恢复。
• binlog log：简单理解成数据备份。

4. 历史版本链

        每个事务修改表数据时，都会先备份一份，再进行修改；通过undo log与DB_TRX_ID、DB_ROLL_PTR，就可以形成一个基于链表记录的历史版本链。一个个版本，我们可以称之一个个快照。所谓回滚，无非就是用历史数据，覆盖当前数据。

• 如果是`delete`呢？一样的，别忘了，删数据不是清空，而是设置flag为删除即可。也可以形成版本。
• 如果是`insert`呢？因为`insert`是插入，也就是之前没有数据，那么`insert`也就没有历史版本。但是一般为了回滚操作，insert的数据也是要被放入undo log中，如果当前事务commit了，那么这个undo log 的历史insert记录就可以被清空了。

        那么问题来了，select读取，是读取最新的版本呢，还是读取历史版本？

• 当前读：取最新的记录，就是当前读。增删改，都叫做当前读，select也有可能当前读，比如：select lock in share mode(共享锁), select for update。
• 快照读：读取历史版本(一般而言)，就叫做快照读。

        那么，是什么决定了，select是当前读，还是快照读呢？隔离级别!

• 隔离性，让不同的事务看到不同的内容。
• 隔离级别，决定了事务能看到的版本。

4. Read View

        当事务进行快照都操作的时候生产的读视图（Read view），在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)。

        Read View 在 MySQL 源码中,就是一个类，本质是用来进行可见性判断的。 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件,用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个版本的数据。其中最重要的类属性是以下四个：

• m_ids;        用来维护Read View生成时刻，系统正活跃的事务ID；
• up_limit_id;         记录m_ids列表中事务ID最小的ID；
• low_limit_id;        Read View生成时刻系统尚未分配的下一个事务ID，页就是目前已出现过的事务ID的最大值+1；
• creator_trx_wd;        创建该Read View的事务ID

class ReadView 
{
// 省略...
private:
/** 高水位，大于等于这个ID的事务均不可见*/
trx_id_t m_low_limit_id

/** 低水位：小于这个ID的事务均可见 */
trx_id_t m_up_limit_id;

/** 创建该 Read View 的事务ID*/
trx_id_t m_creator_trx_id;

/** 创建视图时的活跃事务id列表*/
ids_t m_ids;

/** 配合purge，标识该视图不需要小于m_low_limit_no的UNDO LOG，
* 如果其他视图也不需要，则可以删除小于m_low_limit_no的UNDO LOG*/
trx_id_t m_low_limit_no;

/** 标记视图是否被关闭*/
bool m_closed;

// 省略...
};

5. RR与RC的本质区别

• RC：每一次快照读都会生成一个当前的Read View；
• RR：只会在第一次快照读生成Read View；