Mysql 事务

1. 概述

事务就是要保证一组数据库操作，要么全部成功，要么全部失败
在 MySQL 中，事务支持是在引擎层实现的：MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务，比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一

1.1 事务的启动方式

1. set autocommit=1，会开启自动提交，这个时候也可以使用显示启动事务语句，begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接
3. commit work and chain，在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中
4. start transaction with consistent snapshot， begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

1.2 长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

有时候并不是有意使用长事务，而是由于误用导致的
有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务

如何避免
使用set autocommit=0，再选择显示手动开启事务

2. 隔离性和隔离级别

2.1 为什么需要隔离级别

当数据库上有多个事务同时执行的时候，就可能出现
脏读（dirty read）：

• 一个事务读到另一个事务未提交的数据

不可重复读（non-repeatable read）：

• 一个事务两次读取同一行的数据但两次读取到的数据不一样（这里的不同指的是某一条或多条数据的前后内容不一致，但是数据条目相同），这是由于在查询间隔，该事务需要使用的数据被另一事务修改了；
• 脏读和不可重复读的区别是：脏读是某一事务读取了另一事务未提交的脏数据，而不可重复读则是读取了其他事务提交了的数据；有时候不可重复读并不是问题

幻读（phantom read）：

• 一个事务执行了两次查询，但是第二次查询比第一次查询多出了一些数据行（注意不可重复读和幻读的区别）；
• 例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
• 幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同)，所不同的是不可重复读可能发生在update,delete操作中，而幻读发生在insert操作中

为了解决这些问题，就有了隔离级别的概念

2.2 有哪些隔离级别

串行化 > 可重复读 > 读提交 > 读未提交
隔离界别越高，效率越低 

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

2.3 如何理解隔离级别

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2

2.4 默认设置和参数

• Oracle 数据库的默认隔离级别是“读提交”
• Mysql 数据库的默认隔离级别是“可重复读”
  因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交

配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

3. 数据库理论中使用锁来实现事务的隔离级别

3.1 排他锁，共享锁

排它锁（Exclusive），又称为X 锁，写锁。
共享锁（Shared），又称为S 锁，读锁。
读写锁之间有以下的关系：

• 一个事务对数据对象O加了 S 锁，可以对 O进行读取操作，但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁，但是不能加 X 锁。
• 一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

即读写锁之间的关系可以概括为：多读单写

读未提交(Read Uncommitted)：

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

读已提交(Read Committed)：

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

可重复读取(Repeatable Read)：

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

串行化(Serializable)：

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

4. mysql出于性能考虑使用MVCC实现事务的隔离级别

一文讲透 MySQL 的 MVCC 机制

4.1 什么是MVCC

MVCC：多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别的实现

4.2 PostgreSQL 的实现方式

在《高性能 MySQL》这本书中对PostgreSQL 引擎的MVCC实现做了概述，基本原理如下：

系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。

在MySQL中，会在表中每一条数据后面添加两个字段：

• 创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
• 删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

可重复读的实现原理如下
SELECT
select时读取数据的规则为：创建版本号<=当前事务版本号&（删除版本号为空||>当前事务版本号）
创建版本号<=当前事务版本号保证取出的数据不会有后启动的事务中创建的数据
删除版本号为空或>当前事务版本号保证了至少在该事务开启之前数据没有被删除，是应该被查出来的数据。

INSERT
insert时将当前的系统版本号赋值给创建版本号字段。

UPDATE
插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行，实际上这里的更新是通过delete和insert实现的。

DELETE
删除时将当前的系统版本号赋值给删除版本号字段，标识该行数据在那一个事务中会被删除，即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据

4.3 InnoDB 的实现方式

InnoDB引擎对于MVCC的具体的实现是，在数据库的每一行中，添加额外的三个字段：

DB_TRX_ID -- 记录插入或更新该行的最后一个事务的事务 ID
DB_ROLL_PTR -- 指向改行对应的 undolog 的指针
DB_ROW_ID -- 单调递增的行 ID，他就是 AUTO_INCREMENT 的主键 ID

innodb 拥有一个自增的全局事务 ID，每当一个事务开启，在事务中都会记录当前事务的唯一 id，而全局事务 ID 会随着新事务的创建而增长

同时，新事务创建时，事务系统会将当前未提交的所有事务 ID 组成的数组传递给这个新事务

4.3.1 快照读

每当一个事务更新一条数据时，都会在写入对应 undo log 后将这行记录的隐藏字段 DB_TRX_ID 更新为当前事务的事务 ID，用来表明最新更新该数据的事务是该事务

当另一个事务去 select 数据时，读到该行数据的 DB_TRX_ID 不为空并且 DB_TRX_ID 与当前事务的事务 ID 是不同的，这就说明这一行数据是另一个事务修改并提交的

那么，这行数据究竟是在当前事务开启前提交的还是在当前事务开启后提交的呢？

p_limit_id 记录了当前事务开启时所有未提交事务 ID 的最小值，他就是低水位线，而 low_limit_id 则记录了当前事务开启时全局事务 ID + 1，他就是高水位线

如果某一行数据的 DB_TRX_ID 在 TRX_ID 集合中或大于等于 low_limit_id，那么就说明这行数据是在当前事务开启后提交的，如果某一行数据的 DB_TRX_ID 小于 up_limit_id 就说明该事务是当前事务开启前提交的

对于当前事务开启后提交的数据，当前事务需要通过隐藏的 DB_ROLL_PTR 字段找到 undo log，然后进行逻辑上的回溯才能拿到事务开启时的原数据

这个通过 undo log + 数据行获取到事务开启时的原始数据的过程就是“快照读”

4.3.2 当前读

很多时候，我们在读取数据库时，需要读取的是行的当前数据，而不需要通过 undo log 回溯到事务开启前的数据状态，主要包含以下操作：

insert
update
select ... lock in share mode
select ... for update

4.3.3 可重复读解决不可重复读与幻读问题的原理

对于正常的 select 查询 innodb 实际上进行的是快照读，即通过判断读取到的行的 DB_TRX_ID 与 DB_ROLL_PTR 字段指向的 undo log 回溯到事务开启前或当前事务最后一次更新的数据版本，从而在这样的场景下避免了可重复读与幻读的问题

针对已存在的数据，insert 和 update 操作虽然是进行当前读，但 insert 与 update 操作后，该行的最新修改事务 ID 为当前事务 ID，因此读到的值仍然是当前事务所修改的数据，不会产生不可重复读的问题

但如果当前事务更新到了其他事务新插入并提交了的数据，这就会造成该行数据的 DB_TRX_ID 被更新为当前事务 ID，此后即便进行快照读，依然会查出该行数据，产生幻读（其他事务插入或删除但未提交该行数据的情况下会锁定该行，造成当前事务对该行的更新操作被阻塞，所以这种情况不会产生幻读问题）

4.4 PostgreSQL 的实现方式 vs InnoDB

PostgreSQL 的 MVCC 实现与 innodb 的 MVCC 实现相比，最大的优点在于其查询无需解析 undo log 进行回溯

对于数据回滚，只需要删除所有 xmin 为当前事务 ID 的记录，清除所有 xmax 为当前事务 ID 的 xmax 字段即可

但其缺点也很明显，那就是随着更新操作，数据库中会产生大量的额外数据，这些数据同时也对数据库其他的操作例如索引的建立等都带来了额外的性能消耗