MySQL进阶｜MySQL中的事务（二）

原创

Aion

发布于 2023-11-27 20:04:40

2300

发布于 2023-11-27 20:04:40

文章被收录于专栏：开发运维工程师

上一个章节说了什么是事务，在MySQL数据库中如何查询事务，以及哪些存储引擎支持事务。这一章节来说说事务的隔离。

1.1 隔离的设计

事务隔离是数据库处理的基础之一。隔离级别是在多个事务同时进行更改和执行查询时微调性能与结果的可靠性、一致性和可再现性之间的平衡的设置。所以软件在设计之初考虑到大多数使用场景可以通用的情况，就针对这些做了最平衡的处理。

1.2 隔离的级别

从MySQL的官方网站，我们可以查询到MySQL中有四种隔离级别，他们执行的是InnoDB提供 SQL:1992 标准，他们分别是：

READ UNCOMMITTED：读未提交。在该隔离级别下，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
READ COMMITTED：读已提交。在该隔离级别下，一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
REPEATABLE READ（default）：可重复读。MySQL的默认隔离级别，在该隔离级别下，可以避免脏读、不可重复读，但幻读问题仍然存在。
SERIALIZABLE：可序列化。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

隔离级别所能解决的问题如下：

1.3 事务在MySQL语句中使用

「开启事务」

BEGIN 或 START TRANSACTION ；

「提交事务」

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

「回滚事务」

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

「创建保存点」

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

「删除事务保存点」

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

「回滚到标记点」

ROLLBACK TO identifier 把事务回滚到标记点；

「设置事务隔离级别」

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

注意⚠️：具体的事务使用可以参考下官方提供的说明文档：自动提交、提交和回滚。

1.4 如何查询当前服务器的事务

下面在我实验环境下，使用两种方式来查看事务。

「方式一」不区分大小写

show variables like 'transaction_isolation';

「方式二」不区分大小写

SELECT @@transaction_isolation;

当然，我们还可以从可视化工具中查询。

1.5 如何设置和使用事务隔离级别

「设置全局变量」其实就是设置一个全局变量。

-- 方式一
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL '[READ-UNCOMMITTED｜READ-COMMITTED｜REPEATABLE-READ｜SERIALIZABLE]'

-- 方式二
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '[READ-UNCOMMITTED｜READ-COMMITTED｜REPEATABLE-READ｜SERIALIZABLE]'

「开始执行1.3所列步骤」

在默认不修改事务隔离级别的情况，在MySQL服务器使用命令行实验下。

开启事务、回滚事务

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

mysql>
mysql> show tables;
+-----------------------------+
| Tables_in_study             |
+-----------------------------+
| table1                      |
| table2                      |
| tb                          |
| test_01                     |
+-----------------------------+
29 rows in set (0.00 sec)

mysql>
mysql> select * from table1 ;
+----+----------+
| id | col_name |
+----+----------+
|  1 | 张三1    |
|  2 | 张三2    |
|  3 | 张三3    |
|  4 | 张三4    |
|  5 | 张三5    |
|  6 | 李四1    |
|  7 | 李四2    |
|  8 | 李四3    |
|  9 | 李四4    |
| 10 | 李四5    |
+----+----------+
10 rows in set (0.00 sec)

mysql>
mysql> delete from table1 where id = 6;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> select * from table1 ;
+----+----------+
| id | col_name |
+----+----------+
|  1 | 张三1    |
|  2 | 张三2    |
|  3 | 张三3    |
|  4 | 张三4    |
|  5 | 张三5    |
|  6 | 李四1    |
|  7 | 李四2    |
|  8 | 李四3    |
|  9 | 李四4    |
| 10 | 李四5    |
+----+----------+
10 rows in set (0.00 sec)

插入数据未提交

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

mysql>
mysql> select * from table1 ;
+----+----------+
| id | col_name |
+----+----------+
|  1 | 张三1    |
|  2 | 张三2    |
|  3 | 张三3    |
|  4 | 张三4    |
|  5 | 张三5    |
|  6 | 李四1    |
|  7 | 李四2    |
|  8 | 李四3    |
|  9 | 李四4    |
| 10 | 李四5    |
+----+----------+
10 rows in set (0.00 sec)

mysql>
mysql> insert into table1(id, col_name) values(100, "测试100"), (110, "测试110");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from table1 ;
+-----+-----------+
| id  | col_name  |
+-----+-----------+
|   1 | 张三1     |
|   2 | 张三2     |
|   3 | 张三3     |
|   4 | 张三4     |
|   5 | 张三5     |
|   6 | 李四1     |
|   7 | 李四2     |
|   8 | 李四3     |
|   9 | 李四4     |
|  10 | 李四5     |
| 100 | 测试100   |
| 110 | 测试110   |
+-----+-----------+
12 rows in set (0.00 sec)

mysql>

提交后再查询

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

mysql>
mysql> select * from table1 ;
+----+----------+
| id | col_name |
+----+----------+
|  1 | 张三1    |
|  2 | 张三2    |
|  3 | 张三3    |
|  4 | 张三4    |
|  5 | 张三5    |
|  6 | 李四1    |
|  7 | 李四2    |
|  8 | 李四3    |
|  9 | 李四4    |
| 10 | 李四5    |
+----+----------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql> insert into table1(id, col_name) values(100, "测试100"), (110, "测试110");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from table1 ;
+-----+-----------+
| id  | col_name  |
+-----+-----------+
|   1 | 张三1     |
|   2 | 张三2     |
|   3 | 张三3     |
|   4 | 张三4     |
|   5 | 张三5     |
|   6 | 李四1     |
|   7 | 李四2     |
|   8 | 李四3     |
|   9 | 李四4     |
|  10 | 李四5     |
| 100 | 测试100   |
| 110 | 测试110   |
+-----+-----------+
12 rows in set (0.00 sec)

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

mysql>
mysql> select * from table1 ;
+-----+-----------+
| id  | col_name  |
+-----+-----------+
|   1 | 张三1     |
|   2 | 张三2     |
|   3 | 张三3     |
|   4 | 张三4     |
|   5 | 张三5     |
|   6 | 李四1     |
|   7 | 李四2     |
|   8 | 李四3     |
|   9 | 李四4     |
|  10 | 李四5     |
| 100 | 测试100   |
| 110 | 测试110   |
+-----+-----------+
12 rows in set (0.00 sec)

好了，今天的分享就到了这里，下次再见！！！

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

2023腾讯·技术创作特训营第三期