MySQL事务详解

2021/03/05 MySQL

> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349)

# 概述

## 简介

事务由单独单元的**一个或多个SQL语句组成**，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

> 案例：转账要么都成功，要么都不成功

update 表 set 余额=余额-500 where name='A'
    update 表 set 余额=余额+500 where name='B'

## 支持的存储引擎

`SHOW ENGINES` 命令来查看当前 `MySQL` 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务，在 `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.01 sec)
```

## 事务的ACID特性

- 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
- 一致性（Consistency）：事务执行前后，数据从一个 **合法性状态** 变换到另外一个 **合法性状态** 。
- 隔离性（Isolation）：一个事务的执行不受其他事务的干扰
- 持久性（Durability）：一个事务一旦提交，则会永久的改变数据库的数据.

# 事务的使用

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

## 显式事务

执行步骤如下：

1. 开始事务：`START TRANSACTION` 或者 `BEGIN` ，作用是显式开启一个事务。 `START TRANSACTION` 语句相较于 `BEGIN` 特别之处在于，后边能跟随几个修饰符：
 1. `READ ONLY` ：标识当前事务是一个 **只读事务** ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
 2. `READ WRITE` ：标识当前事务是一个 **读写事务** ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
 3. `WITH CONSISTENT SNAPSHOT` ：启动一致性读。
2. 执行语句：一系列事务中的操作（主要是 `DML` ，不含 `DDL` ）
3. 添加保存点（可选）：
 1. `SAVEPOINT xxx` ：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
 2. `RELEASE SAVEPOINT xxx` ：删除某个保存点。
4. 提交事务 或 中止事务（即回滚事务）
 1. `COMMIT` ：提交事务，当提交事务后，对数据库的修改是永久性的。
 2. `ROLLBACK` ：回滚事务，即撤销正在进行的所有没有提交的修改
 3. `ROLLBACK TO [SAVEPOINT]` ：将事务回滚到某个保存点。

## 隐式事务

### 事务的自动提交

`MySQL` 中有一个系统变量 `autocommit` ：

- 默认情况下，`autocommit`自动提交的值为`ON`
- 当开启自动提交时，若不开启事务，则所有修改立即生效，无法回滚。
- 当关闭自动提交时，所有的 `DML` 操作都需要手动提交。
- 如果想关闭 自动提交 的功能，可以使用下边两种方法之一：
    - 显式的的使用 `START TRANSACTION` 或者 `BEGIN` 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
    - 把系统变量 `autocommit` 的值设置为 `OFF` ，就像这样：

修改自动提交

- 开启：`SET autocommit = ON;` 或 `SET autocommit = 1;`
- 关闭：`SET autocommit = OFF;` 或 `SET autocommit = 0;`

### 隐式提交数据的情况

在关闭自动提交的情况下，有一些语句也会自动提交事务

- 数据定义语言（ `Data definition language` ，缩写为： `DDL` ） 数据库对象，指的就是数据库、表、视图、存储过程等结构。当使用 `CREATE` 、 `ALTER` 、 `DROP` 等语句修改数据库对象时，就会隐式的提交之前语句所属的事务。
- 隐式使用或修改 `MySQL` 数据库中的表 使用 `ALTER USER` 、 `CREATE USER` 、 `DROP USER` 、 `GRANT` 、 `RENAME USER` 、 `REVOKE` 、 `SET PASSWORD` 等语句时也会隐式的提交之前语句所属的事务
- 事务控制或关于锁定的语句
 1. 在一个事务还没提交或者回滚时就又使用 `START TRANSACTION` 或者 `BEGIN` 语句开启了另一个事务时，会隐式的提交之前语句所属的事务
 2. 当前的 `autocommit` 系统变量的值为 `OFF` ，手动调为 `ON` 时，也会隐式的提交之前语句所属的事务
 3. 使用 `LOCK TABLES` 、 `UNLOCK TABLES` 等关于锁定的语句也会隐式的提交之前语句所属的事务
- 加载数据的语句 使用 `LOAD DATA` 语句来批量往数据库中导入数据时，也会隐式的提交之前语句所属的事务
- 关于 `MySQL` 复制的一些语句 使用 `START SLAVE` 、 `STOP SLAVE` 、 `RESET SLAVE` 、 `CHANGE MASTER TO` 等语句时会隐式的提交之前语句所属的事务
- 其它的一些语句 使用 `ANALYZE TABLE` 、 `CACHE INDEX` 、 `CHECK TABLE` 、 `FLUSH` 、 `LOAD INDEX INTO CACHE` 、
`OPTIMIZE TABLE` 、 `REPAIR TABLE` 、 `RESET` 等语句也会隐式的提交前边语句所属的事务。

# 隔离级别

## 并发问题

对于同时运行的多个事务，当这些事务访问数据库中**相同的数据时**，如果没有采取必要的隔离机制，就会导致各种**并发问题**：

- **脏读**：对于两个事务`T1`、`T2`，`T1`可以读取到`T2`更新但还没有被提交的字段。之后若`T2`回滚，`T1`读取的内容就是临时且无效的。
- **不可重复读**：对于两个事务`T1`、`T2`，`T1`读取了一个字段，然后`T2`更新了该字段。之后`T1`再次读取同一个字段，值就不同了。
- **幻读**：对于两个事务`T1`、`T2`，`T1`从一个表中读取了数据，然后`T2`在该表中插入了一些新的行。之后如果`T1`操作全表时就会多出几行，且再次读取时行数不同。

## 四种隔离级别

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

隔离级别 | 描述 | 脏读 | 不可 重复读 | 幻读
---|---|---|---|---
READ UNCOMMITTED 读未提交 | 允许事务读取未被其他事物提交的变更 | √ | √ | √
READ COMMITED 读已提交 | 只允许事务读取已经被其它事务提交的变更 | × | √ | √
REPEATABLE READ 可重复读 | 确保事务可以多次从一个字段中读取相同的值。 事务持续期间禁止其他事务对这个字段进行更新 | × | × | √
SERIALIZABLE 串行化 | 确保事务可以从一个表中读取相同的行。 事务持续期间禁止其他事务对该表执行插入、更新和删除操作。 | × | × | ×

> PS：打`√`代表会出现问题，`x`代表不会出现

## 查询与修改隔离级别

MySQL默认隔离级别：`REPEATABLE-READ`

### 查询：

- 详细查询：`SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;`
- 仅查询当前会话：`SELECT @@transaction_isolation;`

### 修改

#### 配置文件

MySQL默认隔离级别为`REPEATABLE READ`，可以通过`my.ini`配置文件进行修改

在`[mysqld]`标签下添加如下内容：

`transaction-isolation = [级别]`

级别如下（注意：使用配置文件时单词之间有`-`）：

- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ
- SERIALIZABLE

修改后重启服务生效

#### 语句

若不重启服务，则可以通过`SQL`进行修改，语法如下：

`SET [范围] TRANSACTION ISOLATION LEVEL [级别]`;

> 范围如下：

- GLOBAL
- SESSION
-

若`[范围]`不填写，则仅对**下一个事务**有效

> 级别如下：

- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE

### 示例

> 当前会话

> 全局会话

- 该操作仅对后面新的会话生效，不对当前会话和已连接的会话生效
- 若服务器重启，则恢复默认的`REPEATABLE READ`

```bash
# 修改前查询
mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)
# 修改
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
# 修改后查询
mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-UNCOMMITTED               | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)
# 重新登录后再次查询
mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED                |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)
# 重启MySQL后登录再次查询
mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)
```

> 下一个事务

略。。。。。。

# 事务的使用

## 步骤

- 步骤1：开启事务：`START TRANSACTION;`
- 步骤2：执行SQL语句：`SELECT | INSERT | UPDATE | DELETE`
- 步骤3：结束事务：
  - 提交事务：`COMMIT;`
  - 回滚事务：`ROLLBACK;`

## 简单演示

> 表结构与数据准备

```sql
CREATE TABLE `account` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `balance` DOUBLE NOT NULL DEFAULT '0' COMMENT '余额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB COMMENT='账户表';

INSERT  INTO `account`(`id`,`balance`) VALUES 
(1,3500),
(2,1500);
```

### 正常示例

```bash
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    2000 |
|  2 |    2000 |
+----+---------+
2 rows in set (0.00 sec)

mysql> UPDATE `account` SET `balance` = `balance` + 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `account` SET `balance` = `balance` - 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    2500 |
|  2 |    1500 |
+----+---------+
2 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    2500 |
|  2 |    1500 |
+----+---------+
2 rows in set (0.00 sec)
```

### 回滚示例

```bash
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    2500 |
|  2 |    1500 |
+----+---------+
2 rows in set (0.00 sec)

mysql> UPDATE `account` SET `balance` = `balance` + 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `account` SET `balance` = `balance` - 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    3000 |
|  2 |    1000 |
+----+---------+
2 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `account`;
+----+---------+
| id | balance |
+----+---------+
|  1 |    2500 |
|  2 |    1500 |
+----+---------+
2 rows in set (0.00 sec)
```

# 不同隔离级别并发问题演示

- 准备两个会话`T1`、`T2`
- `T1`修改隔离级别后开启事务
- `T2`直接开启事务

> 下文的截图中`T1`为黑色框、`T2`为绿色框，表结构和数据仍为上文的表

## 读未提交 READ UNCOMMITTED

![](https://cdn.maxqiu.com/upload/be81dba1ed474cecbcff53a83c908124.jpg)

如图：`T1`在第6步读取到了`T2`在第5补更新但未提交的值，当`T2`在第7步回滚时，`T1`第6步读取的值就是无效的。出现***脏读***

## 读已提交 READ COMMITTED

![](https://cdn.maxqiu.com/upload/6196c088ebad4833bb30bb739b07fc1a.jpg)

如图：`T1`在第6步读取不到`T2`在第5步更新但未提交的值，说明解决了***脏读***；但是当`T2`在第7步提交后，`T1`在第8步读取到了`T2`已提交的数据。出现***不可重复读***

## 可重复读 REPEATABLE READ

![](https://cdn.maxqiu.com/upload/ec3de1058d1b4cf59e7b33cf659fb753.jpg)

如图：`T1`在第7步读取不到`T2`在第6步**更新并提交**的值，说明解决了***不可重复读***；当`T2`在第8步执行了插入后，`T1`在第9步依旧查询不到`T2`插入的值；但是却在第10步更新了三条记录，并且再次查询时读出三条记录，与第9步行数不符。。出现***幻读***

## 串行化 SERIALIZABLE

![](https://cdn.maxqiu.com/upload/75e852c0b0c242368b3972bcf0eabd8d.jpg)

如图：当`T2`在第5步执行插入时，会被暂停，当`T1`在第6步执行提交结束事务后`T2`才可以完成插入（注意观察插入的完成时间）。因为不允许其他事务执行修改操作，解决了***幻读***