MySQL事务

博主： BeiJiXS
发布时间：2021 年 02 月 27 日
259 次浏览
暂无评论
4937字数
分类： CentOS 7

![](https://blog.beijixs.cn/usr/uploads/2021/02/1667128502.png)

事务：一组原子性的SQL查询，或者说一个独立工作单元。一个支持事务的存储引擎或关系型数据库，必然是能够满足`ACID`测试的。其中MySQL或MariaDB中有一个`事务日志`，就是用于崩溃后回滚。
</div>

## ACID测试

* A：atomicity，原子性；整个事务中所有操作要么全部成功执行，要么全部失败后回滚；
* C：consistency，一致性；数据库总是从一个一致性状态转换为另一个一致性状态;
* I：Isolation，隔离性；一个事务所作出的操作在提交之前，是不能为其他所见；隔离有多种隔离级别；
* D：durability，持久性；一旦事务提交，其所做的修改悔永久保存于数据库中；

隔离性中，隔离界越高，数据安全性越高；但是并发能力越低。
</div>

## 事务

```
 启动事务：START TRANSACTION
     ...
     ...
 结束事务:
     1. COMMIT: 提交 -> 持久固化
     2. ROLLBACK: 回滚 => 回到事务开始之前的状态
```

只有事务型存储引擎方能支持此类操作;
</div>

```
 # 有一个全局参数 autocommit ，意为自动提交
 MariaDB [test]> show global variables like '%auto%';
 +-----------------------------+-------+
 | Variable_name               | Value |
 +-----------------------------+-------+
 | auto_increment_increment    | 1     |
 | auto_increment_offset       | 1     |
 | autocommit                  | ON    |
 | automatic_sp_privileges     | ON    |
 | innodb_autoextend_increment | 8     |
 | innodb_autoinc_lock_mode    | 1     |
 | innodb_stats_auto_update    | 1     |
 | sql_auto_is_null            | OFF   |
 +-----------------------------+-------+
 8 rows in set (0.00 sec)
 # 建议：显式请求和提交事务，而不要使用“自动提交”功能，因为每次提交都会产生磁盘IO
 # 关闭自动提交
 MariaDB [test]> set global autocommit=OFF
 #　永久关闭，在配置文件新增
 # autocommit = OFF 或 autocommit = 0
 
 #　发现一个问题，就是查询也需要commit才能查询到最新数据
```

## savepoint（保存点）

```
 MariaDB [(none)]> HELP SAVEPOINT
 Name: 'SAVEPOINT'
 Description:
 Syntax:
 SAVEPOINT identifier    # 保存点
 ROLLBACK [WORK] TO [SAVEPOINT] identifier   # 回滚到某个点
 RELEASE SAVEPOINT identifier    # 删除点
```

**实例**

```
 # 开始事务
 MariaDB [test]> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 # 插入第一条数据
 MariaDB [test]> INSERT students (Name, Age, Gender) VALUES ('小红', 16, 0);
 Query OK, 1 row affected (0.00 sec)
 # 保存点
 MariaDB [test]> SAVEPOINT S1;
 Query OK, 0 rows affected (0.00 sec)
 # 插入第二条数据
 MariaDB [test]> INSERT students (Name, Age, Gender) VALUES ('张三', 20, 1);
 Query OK, 1 row affected (0.00 sec)
 # 发现第二条数据是不想要的，于是回滚到第二条数据插入操作之前
 MariaDB [test]> ROLLBACK TO S1;
 Query OK, 0 rows affected (0.00 sec)
 # 提交
 MariaDB [test]> COMMIT;
 Query OK, 0 rows affected (0.00 sec)
 # 查看表 可见第二条数据没有被保存下来
 MariaDB [test]> SELECT * FROM students;
 +----+--------+------+--------+
 | ID | Name   | Age  | Gender |
 +----+--------+------+--------+
 |  3 | 小红   |   16 |      0 |
 +----+--------+------+--------+
 1 row in set (0.00 sec)
 
 MariaDB [test]> 
 
```

## 事务隔离级别

1. READ UNCOMMITTED（读未提交，存在下面的前三种问题）
2. READ COMMITTED（读提交，存在不可重复读、幻读问题）
3. REPEATED READ（可重读， 存在幻读问题）
4. SERIALIZABLIE（可串行化， 存在加锁读问题）

可能存在的问题：

* 脏读(读到别人没有提交的数据，然后对方又回滚了)
* 不可重复读
* 幻读
* 加锁读

</div>

**查看全局事务隔离级别**

```
 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%isola%';
 +---------------+-----------------+
 | Variable_name | Value           |
 +---------------+-----------------+
 | tx_isolation  | REPEATABLE-READ |
 +---------------+-----------------+
 1 row in set (0.00 sec)
 # tx_isolation 服务器变量
 # 默认是 REPEATABLE-READ 可重读的
 # 可以在SESSION级别修改
```

**脏读演示**

```
 # 在会话一和会话二中设置隔离级别为'READ-UNCOMMITTED'
 MariaDB [(test)]> SET SESSION tx_isolation='READ-UNCOMMITTED'
```

```mysql
 # 开始事务
 MariaDB [(test)]> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 # 查询全部数据
 MariaDB [test]> SELECT * FROM students;
 +----+--------+------+--------+
 | ID | Name   | Age  | Gender |
 +----+--------+------+--------+
 |  3 | 小红   |   16 |      0 |
 +----+--------+------+--------+
 1 row in set (0.00 sec)
 # 更新小红ID为4
 MariaDB [test]> UPDATE students SET ID=4 WHERE ID=3;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 # 可以查看到ID已经更新了，这个时候我们到会话二中查询一下
 MariaDB [test]> SELECT * FROM students;
 +----+--------+------+--------+
 | ID | Name   | Age  | Gender |
 +----+--------+------+--------+
 |  4 | 小红   |   16 |      0 |
 +----+--------+------+--------+
 1 row in set (0.00 sec)
 # 回滚
 MariaDB [test]> ROLLBACK;
 Query OK, 0 rows affected (0.00 sec)
```

</div><div role="tabpanel" id='tabs-698d8a15ae757b2c5590a215b536589f171' class="tab-pane fade  ">

```
 MariaDB [(none)]> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 # 此时我们查询到ID确实变为了4
 MariaDB [(none)]> SELECT * FROM test.students;
 +----+--------+------+--------+
 | ID | Name   | Age  | Gender |
 +----+--------+------+--------+
 |  4 | 小红   |   16 |      0 |
 +----+--------+------+--------+
 1 row in set (0.00 sec)
 # 当会话一回滚操作后，ID又变为了3；这就是脏读
 MariaDB [(none)]> SELECT * FROM test.students;
 +----+--------+------+--------+
 | ID | Name   | Age  | Gender |
 +----+--------+------+--------+
 |  3 | 小红   |   16 |      0 |
 +----+--------+------+--------+
 1 row in set (0.00 sec)
```

</div>
</div>
</div>

最后修改：2021 年 02 月 28 日