30、MySQL 数据库事务

数据库事务 (`Database Transaction`) 是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行

事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源

举个例子，A 向 B 转账 100 元，其实整个转账过程就是一个事务，要么转账成功了，A 的账户扣了 100 元，B 的账户增加了 100 元，要么转账失败，A 还是那么多钱，B 还是没钱，如果出现 A 扣了 100 元，B 的账户却没增加 100 元，那是要出问题的，是不？

事务，就是用来做这件事的，用来保证要么转账成功，要么转账失败

### **MySQL 事务**

------------

MySQL 支持事务，但是，但是只有使用 `Innodb` 数据库引擎的数据库或表才支持事务

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

一般来说，事务是必须满足4个条件 ( ACID )

Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(持久性)

**1、 原子性；**

一组事务，要么成功；要么失败回滚当作什么事都没发生

**2、 一致性；**

有非法数据 (外键约束之类)，事务撤回

**3、 隔离性；**

事务独立运行。一个事务处理后的结果，影响了其它事务，那么其它事务会撤回
事务的100%隔离，需要牺牲速度

**4、 持久性；**

软、硬件崩溃后，InnoDB 数据表驱动会利用日志文件重构修改
可靠性和高速度不可兼得 innodb_flush_log_at_trx_commit 选项决定什么时候吧事务保存到日志里

MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 `COMMIT` 操作

因此要显式地开启一个事务务须使用命令 `BEGIN` 或 `START TRANSACTION`，或者执行命令 `SET AUTOCOMMIT=0`，用来禁止使用当前会话的自动提交

### **MySQL 事物控制语句**

------------

| 语句 | 说明 |
| --- | --- |
BEGIN 或 START TRANSACTION	|显式地开启一个事务
COMMIT	|提交事务，并使已对数据库进行的所有修改成为永久性的
ROLLBACK	|回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
SAVEPOINT identifier	|SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT
RELEASE SAVEPOINT identifier	|删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
ROLLBACK TO identifier	|把事务回滚到标记点
SET TRANSACTION	|用来设置事务的隔离级别，InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ，SERIALIZABLE

### **MYSQL 事务处理主要有两种方法**

1、 用 `BEGIN`,`ROLLBACK`,`COMMIT` 来实现；

| 命令 | 说明 |
| --- | --- |
| BEGIN | 开始一个事务 |
| ROLLBACK | 事务回滚 |
| COMMIT | 事务确认 |

2、 直接用 `SET` 来改变MySQL的自动提交模式；
```python
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

```

### **MySQL 事务测试**

1、 测试数据准备；

运行下面的 SQL 语句在 souyunku 数据库中准备测试数据

```python
DROP TABLE IF EXISTS tbl_language;
    CREATE TABLE IF NOT EXISTS tbl_language(
       id INT UNSIGNED AUTO_INCREMENT,
       name VARCHAR(64) NOT NULL,
       url VARCHAR(128) NOT NULL,
       founded_at DATE,
       PRIMARY KEY ( id )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    INSERT INTO tbl_language VALUES
        (1,'Python','https://baidu.com','1991-2-20'),
        (2,'PHP','http://www.php.net','1994-1-1'),
        (3,'Ruby','https://www.ruby-lang.org/','1996-12-25'),
        (4,'Kotlin','http://kotlinlang.org/','2016-02-17');
```

2、 打开两个终端(命令行提示符)，我们称之为a和b，然后分别运行下面的语句切换到souyunku数据库；

```python
mysql -u root -p
use souyunku;
```

3、 分别在a和b终端中运行下列语句查看表中数据情况；

```python
SELECT * FROM tbl_language;
```

```python
a 终端
MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
+----+--------+----------------------------+------------+
4 rows in set (0.01 sec)

b 终端

MariaDB [souyunku]> SELECT * FROM tbl_language;
 +----+--------+----------------------------+------------+
 | id | name   | url                        | founded_at |
 +----+--------+----------------------------+------------+
 |  1 | Python | https://baidu.com          | 1991-02-20 |
 |  2 | PHP    | http://www.php.net         | 1994-01-01 |
 |  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
 |  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
 +----+--------+----------------------------+------------+
 4 rows in set (0.01 sec)
 
 # 两个终端中的数据一模一样
```

4、 在a终端中执行以下SQL语句开启事务；

```python
begin;

# 运行结果如下

MariaDB [souyunku]> begin;
Query OK, 0 rows affected (0.00 sec)
```

5、 在a终端中执行以下语句插入两条数据；

```python
 INSERT INTO tbl_language (name,url) VALUES
        ('Perl','http://www.perl.org/'),
        ('Scala','http://www.scala-lang.org/');
		
# 提示成功插入 2 两条数据，然后继续在 a 终端中使用下面的语句查看数据情况
SELECT * FROM tbl_language;

# 运行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+
6 rows in set (0.00 sec)

# 说明刚刚真的是插入成功了
```

6、 在b终端中输入以下SQL命令查看数据情况；
```python
SELECT * FROM tbl_language;
# 运行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
+----+--------+----------------------------+------------+
4 rows in set (0.00 sec)

# 只有 4 条，a 终端中的插入操作没有影响到 b 终端，这就是事务的原理和独特性

```

7、 在a终端中执行以下语句提交事务；

```python
commit;

# 执行结果如下

MariaDB [souyunku]> commit;
 Query OK, 0 rows affected (0.00 sec)
 
# 然后继续在 a 终端中使用下面的语句查看数据情况

SELECT * FROM tbl_language;

# 运行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
 +----+--------+----------------------------+------------+
 | id | name   | url                        | founded_at |
 +----+--------+----------------------------+------------+
 |  1 | Python | https://baidu.com          | 1991-02-20 |
 |  2 | PHP    | http://www.php.net         | 1994-01-01 |
 |  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
 |  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
 |  5 | Perl   | http://www.perl.org/       | NULL       |
 |  6 | Scala  | http://www.scala-lang.org/ | NULL       |
 +----+--------+----------------------------+------------+
 6 rows in set (0.00 sec)
 
# 有 6 条记录，说明插入和提交事务时成功的
```

8、 在b终端中输入以下SQL命令查看数据情况；

```python
SELECT * FROM tbl_language;

# 运行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
 +----+--------+----------------------------+------------+
 | id | name   | url                        | founded_at |
 +----+--------+----------------------------+------------+
 |  1 | Python | https://baidu.com          | 1991-02-20 |
 |  2 | PHP    | http://www.php.net         | 1994-01-01 |
 |  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
 |  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
 |  5 | Perl   | http://www.perl.org/       | NULL       |
 |  6 | Scala  | http://www.scala-lang.org/ | NULL       |
 +----+--------+----------------------------+------------+
 6 rows in set (0.00 sec)
 
 
# 记录变成 6 条了，说明事务执行成功了

```

### **事务回滚演示**

1、 继续上面的操作，我们先运行以下SQL语句查看数据情况；

```python
SELECT * FROM tbl_language;

# 运行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+
6 rows in set (0.00 sec)
```

2、 在a终端中执行以下语句开启事务；

```python
begin;

# 执行结果如下

MariaDB [souyunku]> begin;
Query OK, 0 rows affected (0.00 sec)

# 继续在 a 终端中执行以下一句删除 id 大于等于 5 的记录

DELETE FROM tbl_language where id >= 5;

# 执行结果如下

MariaDB [souyunku]> DELETE FROM tbl_language where id >= 5;
Query OK, 2 rows affected (0.02 sec)

# 然后执行以下语句查看数据情况

SELECT * FROM tbl_language;

# 执行结果

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
+----+--------+----------------------------+------------+
4 rows in set (0.00 sec)

# 可以看到只剩下 4 条数据了
```
3、 然后在b终端中输入以下命令查看数据情况；

```python
SELECT * FROM tbl_language;

# 执行结果

+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+

# b终端显示还剩下 6 条
```
4、 赶紧在a终端中执行以下命令回滚操作；
```python
rollback;

# 执行结果如下

MariaDB [souyunku]> rollback;
Query OK, 0 rows affected (0.01 sec)

# 然后执行以下语句查看数据情况

SELECT * FROM tbl_language;

# 执行结果

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+
6 rows in set (0.00 sec)

# 数据又回来了
```

5、 然后在b终端中输入以下命令查看数据情况；

```python
SELECT * FROM tbl_language;

# 执行结果

+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+

# 原来数据真的没删除
```

### **PHP 中使用事务实例**

------------

PHP中的 `PHP_PDO` 提供了以下三个方法用户进行事务操作

我们这里只做简单的演示

**测试数据准备**

运行下面的 SQL 语句在 `souyunku` 数据库中准备测试数据

```python
DROP TABLE IF EXISTS tbl_language;
CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tbl_language VALUES
    (1,'Python','https://baidu.com','1991-2-20'),
    (2,'PHP','http://www.php.net','1994-1-1'),
    (3,'Ruby','https://www.ruby-lang.org/','1996-12-25'),
    (4,'Kotlin','http://kotlinlang.org/','2016-02-17');
```

运行下面的 SQL 语句查看数据情况
```python
SELECT * FROM tbl_language;
```

执行结果
```python
MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://baidu.com          | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
+----+--------+----------------------------+------------+
4 rows in set (0.00 sec)
```

然后创建一个文件 `main.php` 复制以下代码
```php
<?php 
$dbh = new PDO('mysql:host=127.0.0.1;dbname=souyunku', 'root', '');    
try {
    // 开启事务
    $dbh->beginTransaction();
    $sql=<<<END
INSERT INTO tbl_language (name,url) VALUES
    ('Perl','http://www.perl.org/'),
    ('Scala','http://www.scala-lang.org/');
END;
    // 添加两条记录
    $dbh->exec($sql);
    // 删除 id = 1 的记录
    $dbh->exec("DELETE FROM tbl_language WHERE id = 1");
    // 将 id= 3 记录的 name 改成 php7.2
    $dbh->exec("UPDATE tbl_language SET name = 'php7.2' WHERE id = 2");
    // 提交事务
    $dbh->commit();
    echo "执行事务成功\n";
} catch (Exception $e) {
  // 如果发生错误，就回滚
  $dbh->rollBack();
  echo "执行事务失败：" , $e->getMessage(),"\n";
```

运行PHP 脚本输出结果如下

```php
$ php main.php
执行事务成功
```

在mysql> 终端中输入以下命令查看情况
```php
SELECT * FROM tbl_language;

# 执行结果如下

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  2 | php7.2 | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
|  5 | Perl   | http://www.perl.org/       | NULL       |
|  6 | Scala  | http://www.scala-lang.org/ | NULL       |
+----+--------+----------------------------+------------+
5 rows in set (0.01 sec)
```

可以看到，所有我们想做的修改，都成功了