MySQL数据库|事务的隔离级别_老汪软件技巧-棋牌游戏开发

作者：老汪软件技巧
发表时间：2024-12-30 17:03
浏览量：

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

1). 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低.

不带 GLOBAL 或 SESSION 关键字表示设置下一个事务的隔离级别；

使用 GLOBAL 关键字表示对全局设置事务隔离级别，设置后的事务隔离级别对所有新的数据库连接生效；

使用 SESSION 关键字表示对当前的数据库连接设置事务隔离级别，只对当前连接生效；

MySQL 事务隔离级别实践

在 MySQL 中创建一个 test 数据库，在 test 数据库中创建一个 account 账户数据表作为测试使用：

CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '账户id',
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `balance` int DEFAULT '0' COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

在 account 表中插入测试数据：

INSERT INTO test.account (name, balance) 
VALUES
('熊大',300),
('熊二',400),
('光头强',500)

此时熊大、熊二、光头强的账户余额分别为 300 元、400 元、500元。

读未提交（read uncommitted）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为读未提交 read uncommitted：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+
1 row in set (0.00 sec)

在终端 A 开启事务并查询熊二的账户余额：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 熊二 |     400 |
+----+--------+---------+
1 row in set (0.00 sec)

此时熊二的账户余额为 400 元。

在终端 B 开启事务并向熊二的账户余额添加 50 元：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> update account set balance = balance + 50 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时在终端 A 查询熊二的账户余额：

mysql> select * from account where id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 熊二 |     450 |
+----+--------+---------+
1 row in set (0.00 sec)

可以发现，终端 A 事务读取到了终端 B 事务还未提交的数据，这个问题就是脏读。终端 A 事务两次读取的数据不一致，这个问题就是不可重复读。

如果此时终端 B 事务回滚，而终端 A 事务对熊二账户余额进行减 50 元，结果会是什么样？

终端 B 事务回滚并查询到余额为 400 元：

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from account where id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 熊二 |     400 |
+----+--------+---------+
1 row in set (0.00 sec)

终端 A 事务修改并提交，查询到余额为 350 元：

mysql> update account set balance = balance - 50 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from account where id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 熊二 |     350 |
+----+--------+---------+
1 row in set (0.00 sec)

以下是整个事件的时间线：

那么脏读有什么影响呢？

在应用程序中，如果一个事务读到脏数据，并作为其他业务逻辑的依据或者进行其他处理，但其并不知道其他会话回滚了事务，那么后续的整个逻辑处理都可能存在问题。

读已提交（read committed）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为读已提交 read committed：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-COMMITTED                  |
+---------------------------------+
1 row in set (0.00 sec)

在终端 A 开启事务并查询光头强的账户余额：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     500 |
+----+-----------+---------+
1 row in set (0.00 sec)

此时光头强的账户余额为 500 元。

在终端 B 开启事务，为光头强的账户余额增加 100 元：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> update account set balance = balance + 100 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 500 元，说明不存在脏读问题：

mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     500 |
+----+-----------+---------+
1 row in set (0.00 sec)

在终端 B 提交事务：

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

此时终端 A 事务再次查询光头强的账户余额，查询到余额为 600 元：

mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     600 |
+----+-----------+---------+
1 row in set (0.00 sec)

_数据库事务隔离级别由低到高_mysql数据库事物隔离级别

可以发现终端 A 事务相同条件下两次查询的结果不一致，这个问题就是不可重读读。

以下是整个事件的时间线：

可重复读（repeatable read）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为可重复读 repeatable read：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select * from account;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | 熊大    |     300 |
|  2 | 熊二    |     350 |
|  3 | 光头强 |     600 |
+----+-----------+---------+
3 rows in set (0.00 sec)

此时查询熊大、熊二、光头强的账户余额分别为 300 元、350 元、600 元。

在终端 A 开启事务并查询光头强的账户余额：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     600 |
+----+-----------+---------+
1 row in set (0.00 sec)

此时光头强的账户余额为 600 元。

在终端 B 开启事务，为光头强的账户余额增加 100 元：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update account set balance = balance + 100 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 600 元，说明不存在脏读问题：

mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     600 |
+----+-----------+---------+
1 row in set (0.00 sec)

在终端 B 提交事务：

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

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 600 元，说明不存在不可重复读问题：


```sql
mysql> select * from account where id = 3;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     600 |
+----+-----------+---------+
1 row in set (0.00 sec)

以下是整个事件的时间线：

但在可重复读的事务隔离级别下，仍然存在幻读问题。下面我们来复现一下。

在终端 A 开启事务并查询 id 大于 2 的账户信息：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from account where id > 2;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     700 |
+----+-----------+---------+
1 row in set (0.00 sec)

可以看到查询得到的结果是 1 条数据。

在终端 B 开启事务，插入一条数据并提交：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into account (name,balance) values('吉吉国王',400);
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

此时终端 A 再次查询 id 大于 2 的账户信息：

mysql> select * from account where id > 2;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  3 | 光头强 |     700 |
+----+-----------+---------+
1 row in set (0.00 sec)

得到的结果仍然是相同的 1 条数据。为什么？说好的幻读呢？其实这是 MySQL 为了提高性能，使用了基于乐观锁的 MVCC（多版本并发控制）机制来避免了幻读问题，但幻读问题仍然存在。

如果终端 A 事务执行把 id 大于 2 的账户余额都修改为 800：

mysql> update account set balance = 800 where id > 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0
 
mysql> select * from account where id > 2;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  3 | 光头强    |     800 |
|  4 | 吉吉国王 |     800 |
+----+--------------+---------+
2 rows in set (0.00 sec)

此时可以看到查询得到的结果是 2 条数据。

以下是整个事件的时间线：

幻读无法通过行级锁来解决，需要使用串行化的事务隔离级别，但这种事务隔离级别会极大的降低数据库的并发能力。

串行化（serializable）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为串行化 serializable：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| SERIALIZABLE                    |
+---------------------------------+
1 row in set (0.00 sec)

在终端 A 开启事务并查询 id 大于 2 的账户信息：

Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id > 2;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  3 | 光头强    |     800 |
|  4 | 吉吉国王 |     800 |
+----+--------------+---------+
2 rows in set (0.00 sec)

可以看到查询得到的结果是 2 条数据。

在终端 B 开启事务，插入一条数据：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into account (name,balance) values('蹦蹦',600);
1205 - Lock wait timeout exceeded; try restarting transaction

可以看到，在终端 B 事务执行新增操作时，会发生阻塞，锁超时后会抛出 1205 - Lock wait timeout exceeded; try restarting transaction 错误，避免了幻读。可以通过 select * from performance_schema.data_locks; 查看事务的锁信息，从 supremum pseudo-record 获知，通过添加间隙锁解决幻读问题。

上一条查看详情 +【唯一随机数】如何用JavaScript的Set生成唯一的随机数？

下一条 查看详情 +没有了