MySQL 高级篇 -- 事务

一、概述

事务时数据库区别于文件系统的重要特性之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据的修改不会因为系统崩溃而丢失。

1.1 存储引擎支持情况

MySQL 中，只有InnoDB 是支持事务的。

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.07 sec)

mysql> 

1.2 基本概念

• 事务 一组逻辑操作单元，使数据从一种状态变换到另一种状态。

• 事务处理的原则 保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。

1.3 事务的 ACID 特性

• 原子性（atomicity） 原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

• 一致性（consistency） 一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态。

1. 这种状态是 语义上 的而不是语法上的，跟具体的业务有关。
2. 那什么是合法的数据状态呢？满足 预定的约束 （如主键存在且唯一、外键约束、列完整性等）的状态就叫做合法的状态。
3. 通俗一点，这状态是由你自己来定义的。

• 隔离型（isolation） 事务的隔离性是指一个事务的执行 不能被其他事务干扰。

1. 即一个事务内部的操作及使用的数据对 并发 的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
2. 如果无法保证隔离性会怎么样？假设A账户有200元，B账户0元。A账户往B账户转账两次，每次金额为50元，分别在两个事务中执行。如果无法保证隔离性，会出现下面的情形：

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

• 持久性（durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

1. 持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志。
2. 当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。
3. 这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执
   行，从而使事务具有持久性。

1.4 事务的状态

我们现在知道事务是一个抽象的概念，它其实对应着一个或多个数据库操作，MySQL根据这些操作所执行的不同阶段把事务大致划分成几个状态。

• 活动的（active） 事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

• 部分提交的（partially committed） 当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘时，我们就说该事务处在部分提交的状态。

• 失败的（failed） 当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统
  错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

• 中止的（aborted） 如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

• 提交的（committed） 当一个处在部分提交的状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了提交的状态。

二、使用事务

使用事务有两种方式，分别为 显式事务 和 隐式事务 。

2.1 显式事务

• 开启显式事务 START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个修饰符。

BEGIN;
#或者
START TRANSACTION;

start transaction read only; #开启一个只读事务
start transaction read only , with consistent snapshot; #开启只读事务和一致性读
start transaction read write , with consistent snapshot; #开启读写事务和一致性读

1. READ ONLY ：标识当前事务是一个 只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
2. READ WRITE ：标识当前事务是一个 读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
3. WITH CONSISTENT SNAPSHOT ：启动一致性读。

• 事务中的操作 主要是DML，不含DDL。

• 提交事务 或 中止事务（即回滚事务）

BEGIN;

# 在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点
savepoint 保存点名称;

# 删除某个保存点
release savepoint 保存点名称

# 提交事务 当提交事务后，对数据库的修改是永久性的
COMMIT;

# 或 回滚事务，即撤销正在进行的所有没有提交的修改
ROLLBACK;

# 或 将事务回滚到某个保存点
ROLLBACK TO [SAVEPOINT]

2.2 隐式事务

• 指示是否开启自动提交

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.04 sec)

mysql> 

• 参数说明

1. 当我们设置 autocommit=0 时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务，都需要用 COMMIT 进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。
2. 当我们设置 autocommit=1 时，每条 SQL 语句都会自动进行提交。 不过这时，如果你采用 START TRANSACTION 或者 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚。

• 关闭的方法

1. 显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务，这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
2. 把系统变量 autocommit 的值设置为 OFF

SET autocommit = OFF;
#或
SET autocommit = 0;

2.3 隐式提交的场景

• 数据库定义语言（DDL）

1. 数据库对象，指的就是数据库、表、视图、存储过程等结构。
2. 当我们使用create、alter、drop等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。即：

BEGIN;

select ...
update ... # 事务中的语句

create table ....  # 此语句会隐式的提交前边语句所属于的事务 

• 隐式使用或修改mysql 数据库中的表：当我们使用 alter user、create user、drop user、grant、rename user、remove、set、password等语句时也会隐式的提交前边语句所属于的事务。

• 事务控制或关于锁定的语句

1. 当我们在一个事务 还没提交或者回滚时 就又使用 start transaction 或者 begin 语句开启了另一个事务时，会 隐式的提交 上一个事务。即

BEGIN;

select ...
update ... #事务中的语句

BEGIN;  # 此语句会隐式的提交前面语句所属于的事务

2. 当前autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。
3. 使用lock tables、unlock tables等关于锁定的语句也会隐式的提交前边语句所属的事务

• 加载数据的语句：使用load data语句来批量往数据库中导入数据时，也会隐式的提交前面语句所属的事务。

• 关于Mysql复制的一些语句：使用start slave、stop slave、reset slave、change master to等语句会隐式提交前面语句的事务

2.4 completion_type 参数

• completion_type

1. completion = 0，默认情况，当我们执行commit的时候会提交事务，在执行下一个事务时，还需要使用start transaction或者begin来开启
2. completion = 1，这种情况下，当我们提交事务后，相当于执行了 commit and chain ，也就是开启一个链式事务，即当我们提交事务之后会开启一个 相同隔离级别 的事务。
3. completion = 2，这种情况下 commit = commit and release，也就是当我们提交后，会 自动与服务器断开连接。

三、隔离级别

• 问题引出

1. MySQL是一个 客户端／服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称为一个会话（Session）。
2. 每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。
3. 事务有 隔离性 的特性，理论上在某个事务 对某个数据进行访问时，其他事务应该进行 排队 ，当该事务提交之后，其他事务才可以继续访问这个数据。
4. 但是这样对 性能影响太大 ，我们既想保持事务的 隔离性，又想让服务器在处理访问同一数据的多个事务时 性能尽量高些 ，那就看 二者如何权衡取舍 了。

3.1 数据并发问题

• 脏写（ Dirty Write ） 对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过的数据，那就意味着发生了脏写。
  

• 脏读（ Dirty Read ） 对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新但还没有被提交 的字段。之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。

• 不可重复读（ Non-Repeatable Read ） 对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读取 同一个字段，值就不同了。那就意味着发生了不可重复读。

• 幻读（ Phantom ） 对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。

3.2 四种隔离级别

数据并发问题按照严重性排序：脏写 > 脏读 > 不可重复读 > 幻读

• 设立一些隔离级别，隔离级别越低，并发问题发生的就越多。

• 设置 MySQL 事务的隔离级别

MySQL 默认的隔离级别为 REPEATABLE-READ，MySQL 在该隔离级别上已经解决了幻读问题。

mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

mysql> 

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式：
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

3.3 隔离级别举例

• 读未提交之脏读

1. 允许脏读
   
2. 不允许脏写
   

• 读已提交

• 可重复读

• 幻读