MySQL的事务隔离级别 | 2023腾讯·技术创作特训营 第三期

2023-11-18 09:39:32 浏览数 (1)

不知道你有没有讲过这种场景，比如转账。正常情况下是一定要确保转出账户转出的金额，是正确累加到转入账户，任何一方的异常，都可判定这个交易是异常，这样的系统也是不可靠的。

这个交易的过程，其实就是要保证如下例子的两个语句是要确保都能正确执行：

代码语言：go复制
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance   100 WHERE account_id = 2;

理想情况下，这两个语句应该一起执行。要么两个语句都成功运行，要么两个语句都不能成功运行。

这就是需要事务的目的。

本文我们将来探讨下，什么是事务，事务的隔离级别级异常。

事务

在 MySQL 中只有在 InnoDB 引擎下才支持事务，MyISAM 引擎是不支持事务的。

所谓事务，通俗的说就是处理一系列事情，只许成功，若过程有任何一步失败就会回退到事务之前的状态（回滚）。

就如上面的转账场景，只要有一条语句运行不成功，这个交易应该要被回退。

事务有四个特性，也是是常说的ACID：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)，四个特性缺一不可。本文所说的事务隔离级别，要说的就是隔离性。

下面是四个特性的定义，了解以下：

  • 原子性(Atomicity)：原子性意味着事务必须作为一个整体，要么成功要么失败。事务中的所有步骤都必须成功运行，否则就失败。事务无法部分完成。
  • 一致性(Consistency)：一致性意味着在事务完成后，数据库将保持有效且一致的状态。意味着所有主键和外键以及其他约束都会得到遵守。数据不会损坏或丢失成为此事务的副作用。
  • 隔离性(Isolation)：隔离意味着事务在不影响数据库的其他用户或会话的情况下运行。事务执行的操作不会影响其他事务，其他操作也不会影响此事务。
  • 持久性(Durability)：持久性意味着此事务的结果存储在数据库中，并且在数据库崩溃或失败时不会丢失。这是通过将数据永久存储在数据库中并使用备份来实现的。

什么是事务隔离

事务隔离是数据库管理的基本概念之一。她是上文介绍的 ACID 中的 “I”。隔离级别决定了并发事务的处理方式。

所有生产级应用程序都会生成多个线程来满足并发请求。这些线程反过来又创建多个数据库会话和事务来满足处理要求。运行这些事务的数据可能相同或不同，我们希望我们的应用程序能够以合理的效率可靠地处理请求。这就是理解隔离级别属性至关重要的地方。

设置事务隔离级别可以全局设置，也可以会话级别设置，稍后我们会有实践。

异常

MySQL InnoDB 引擎使用不同的锁定机制提供四个级别的隔离。每个级别在事务之间提供不同程度的隔离，并有其自身的优势和异常。

以下几个异常的概念名词是我们需要搞清楚的，它们是事务隔离级别要实际解决的问题。

脏读

脏读指的是读到了其他事务未提交的数据，未提交的数据意味着有可能会回滚，也就是数据最终不会落地到数据库里。读到可能最终不会存在的数据，这就是脏读。

可重复读

可重复读是指在一个事务内，最开始读到的数据和事务结束之前任意时刻读到的同个数据都是一致的。通常针对的是数据更新(UPDATE)操作。

不可重复读

与可重读读相比，不可重复读指的是同一个事务内，不同的时刻读到的同一批数据可能是不一致的，它可能会受到其他事务的影响，比如其他事务更改了这些数据并提交了。通常也是针对数据更新(UPDATE)操作。

幻读

幻读是针对数据插入(INSERT)操作来说的。假设事务A对某些行的内容做了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并在事务A提交之前先提交了，而这时，在事务A中查询，会发生好像刚刚的更改对于某个数据未起作用，但其实是事务B刚插入进来的，让用户感觉出现幻觉，这就是幻读。

事务隔离级别

现在，我们开始来了解事务的隔离级别。

在任何 RDBMS（关系型数据库） 系统都按以下顺序具有四个基本隔离级别，隔离强度有低到高：

  1. Read Uncommitted 读取未提交
  2. Read Committed 读取未提交
  3. Repeatable Read 可重复读
  4. Serializable 串行化

默认情况下，MySQL的隔离级别是 Repeatable Read，而 Postgresql 默认为 Read Committed 隔离级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

隔离级别

脏读

不可重复读

幻读

读未提交

可能

可能

可能

读提交

不可能

可能

可能

可重复读

不可能

不可能

可能

串行化

不可能

不可能

不可能

隔离级别和异常与示例

下面我们就来演示以下隔离级别以及异常。在开始之前，先常见好数据库和账户表。

登录 MySQL：

代码语言：shell复制
D:laragonwww
λ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

创建数据库以及账户表：

代码语言：shell复制
mysql> use study_cases;
Database changed
mysql> CREATE TABLE `account` (
    ->  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    ->  `balance` DECIMAL(20,6) NULL DEFAULT NULL,
    ->  PRIMARY KEY (`id`) USING BTREE
    -> )
    -> COLLATE='utf8mb4_unicode_ci'
    -> ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected, 1 warning (0.13 sec)

插入一条数据：

代码语言：shell复制
mysql> insert into account (name,balance) values ('Peter', 1000);
Query OK, 1 row affected (0.05 sec)

下来我们就可以来实践事务隔离级别及异常了。

读取未提交(Read Uncommitted)

“读未提交”在并发事务中提供最低或最弱的隔离级别。所有读取都以非锁定方式进行。使用此隔离级别，事务可以从其他事务中读取为提交的数据，从而导致脏读。

以下示例，我们先将事务隔离级别设置为 "READ-UNCOMMITED"。并启动两个单独的 MySQL 的会话 S1 和 S2，以及各自的事务 T1 和 T2：

S1 和 T1：

代码语言：shell复制
mysql> set transaction_isolation = "READ-UNCOMMITTED";
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

S2 和 T2：

代码语言：shell复制
mysql> set transaction_isolation = "READ-UNCOMMITTED";
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

现在，我们来更新T1中的余额，但不提交它。同时，我们在T2中尝试获取余额。

在 S1 中更新T1中的余额：

代码语言：shell复制
mysql> update account set balance = balance   100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在 S2 中在T2中尝试获取余额：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1100.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

T2 将余额读取为 1100 而不是 1000。现在，在这个阶段，如果 T1 由于任何原因决定回滚，并且T2已经在自己的应用程序线程中使用了值1000，则会发生脏读的情况。

T1 回滚：

代码语言：shell复制
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

T2 查询：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

所以 T1 未提交数据，T2 中读到的数据不是最终的数据，T2 发生了脏读。

读已提交（Read Committed）

“读已提交”提供比“读未提交”更高的事务隔离级别，略低于“可重复读”。使用此隔离级别，事务仍将能够访问来自其他事务的更新数据；前提是要有提交的数据。这有助于避免脏读的情况。

下面的例子中，我们将隔离级别设置为"READ-COMMITTED"，并分别启动会话S1，S2 和 事务 T1、T2。

S1：

代码语言：shell复制
mysql> set transaction_isolation = "READ-COMMITTED";
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> set transaction_isolation = "READ-COMMITTED";
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

接下来，我们将在不提交下更新T1中的余额，并尝试获取 T2 中相同的记录的余额。

S1：

代码语言：shell复制
mysql> update account set balance = balance   100 where id = 1;
Query OK, 1 row affected (0.00 sec)                            
Rows matched: 1  Changed: 1  Warnings: 0                       
                                                               
mysql> select * from account where id = 1;                     
 ---- ------- -------------                                    
| id | name  | balance     |                                   
 ---- ------- -------------                                    
|  1 | Peter | 1100.000000 |                                   
 ---- ------- -------------                                    
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1000.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

接下来，我们将在不提交的情况下更新 T1 中的余额，并尝试获取 T2 中相同记录的余额。

S1：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1100.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

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

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

mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1200.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- -------------               
| id | name  | balance     |              
 ---- ------- -------------               
|  1 | Peter | 1000.000000 |              
 ---- ------- -------------               
1 row in set (0.00 sec)                   
                                          
mysql> select * from account where id = 1;
 ---- ------- -------------               
| id | name  | balance     |              
 ---- ------- -------------               
|  1 | Peter | 1000.000000 |              
 ---- ------- -------------               
1 row in set (0.00 sec)                   
                                          
mysql> select * from account where id = 1;
 ---- ------- -------------               
| id | name  | balance     |              
 ---- ------- -------------               
|  1 | Peter | 1200.000000 |              
 ---- ------- -------------               
1 row in set (0.00 sec)

T2 将余额读取为 1000，从而避免了脏读取。但是，在此隔离级别下仍会进行不可重复的读取。我们下面来理一下。

首先，我们将获取事务 T1 和 T2 中的记录，并更新 T1 中的余额。接下来，我们在 T1 中提交更新，并再次在 T2 中获取记录。

因此，在同一事务 T2 中，查询会导致不同的值，从而导致不可重复的读取。发生这种情况是因为在“读已提交”隔离级别下，innodb 会在上次 DML操作（数据操纵语句）后创建并从新快照读取。

可重复读取

可重复读取时 MySQL InnoDB 引擎默认的隔离级别。此级别通过建立和使用在事务开始时创建的快照来解决不可重读的读取问题。因此，同一个食物中的查询将产生相同的值。

以下示例，我们不会更新隔离级别，二十使用默认隔离级别，并分别启动会话S1、S2 和事务 T1 和 T2。

S1：

代码语言：shell复制
mysql> select @@SESSION.transaction_isolation;
 --------------------------------- 
| @@SESSION.transaction_isolation |
 --------------------------------- 
| REPEATABLE-READ                 |
 --------------------------------- 
1 row in set (0.00 sec)

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

S2：

代码语言：shell复制
mysql>  select @@SESSION.transaction_isolation;
 ---------------------------------             
| @@SESSION.transaction_isolation |            
 ---------------------------------             
| REPEATABLE-READ                 |            
 ---------------------------------             
1 row in set (0.00 sec)                        
                                               
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

接下来，我们将更新并提交 T1 中的记录，并尝试在 T2 中获取相同的记录。

S1：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1200.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

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

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

mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1200.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1200.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

mysql> select * from account where id = 1;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1200.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

从上面的代码片段可以看出，避免了不可重复的读取。但是，幻读却无法避免（T2 最后一行产生幻读，因为 T1 已经提交 ）。

让我们看下面的例子来更好地理解这一点。同样，我们将分别启动会话 S1、S2 和事务 T1、T2。此外，我们将在两个事务中获取和显示记录。

S1：

代码语言：shell复制
mysql> start transaction;            
Query OK, 0 rows affected (0.00 sec) 
                                     
mysql> select * from account;        
 ---- ------- -------------          
| id | name  | balance     |         
 ---- ------- -------------          
|  1 | Peter | 1300.000000 |         
 ---- ------- -------------          
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> start transaction;            
Query OK, 0 rows affected (0.00 sec) 
                                     
mysql> select * from account;        
 ---- ------- -------------          
| id | name  | balance     |         
 ---- ------- -------------          
|  1 | Peter | 1300.000000 |         
 ---- ------- -------------          
1 row in set (0.00 sec)

现在，我们将在 T1 中插入一条新记录并提交它。接下来，我们在 T2 中获取记录。由于使用此隔离级别快照，因此使用在事务开始时建立的快照，因此 T2 仍将无法查看新记录。

S1：

代码语言：shell复制
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.03 sec)

mysql> insert into account (name, balance) values ('Tom', 0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
|  2 | Tom   |    0.000000 |
 ---- ------- ------------- 
2 rows in set (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
|  2 | Tom   |    0.000000 |
 ---- ------- ------------- 
2 rows in set (0.00 sec)

S2：

代码语言：shell复制
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

接下来，我们将尝试更新 T2 中新插入的记录。

S2：

代码语言：shell复制
mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
 ---- ------- ------------- 
1 row in set (0.00 sec)

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

mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
|  2 | Tom   |  100.000000 |
 ---- ------- ------------- 
2 rows in set (0.00 sec)

S1：

代码语言：shell复制
mysql> select * from account;
 ---- ------- ------------- 
| id | name  | balance     |
 ---- ------- ------------- 
|  1 | Peter | 1300.000000 |
|  2 | Tom   |    0.000000 |
 ---- ------- ------------- 
2 rows in set (0.00 sec)

因此，从上面的代码片段中可以看出，虽然建立的快照没有任何新插入记录的视图，但 T2 仍然能够更新和读取相同的记录（id=2的那行）。

虽然可重复读取是隔离的默认 MySQL 设置，但商业应用程序会根据其对性能和可靠性的需求设置隔离级别。

串行化（Serializable）

Serializable 在并发事务之间提供最高级别的隔离。如果启用了autocommit标志，它的行为很像“可重复读；否则，所有读取都以锁定方式执行。

让我们看下面的例子来实际理解这一点。我们将隔离级别设置为“SERIALIZABLE”，并分别启动会话 S1、S2 和事务 T1、T2。我们将首先在 T1 中获取具有某些条件的记录，然后尝试在 T2 中更新相同的记录。

S1：

代码语言：shell复制
mysql> start transaction;                  
Query OK, 0 rows affected (0.00 sec)       
                                           
mysql> select * from account where id = 1; 
 ---- ------- -------------                
| id | name  | balance     |               
 ---- ------- -------------                
|  1 | Peter | 1300.000000 |               
 ---- ------- -------------                
1 row in set (0.00 sec)

S2：

代码语言：shell复制
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> update account set balance = balance   100 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上面的代码片段中可以看出，由于所有读取都是以锁定方式执行的，因此 T2 中的更新查询会等待 T1 完成并超时。

此隔离级别是最严格的，可避免上面列出的所有异常情况。

总结

可重复读取是 MySQL 的默认隔离设置，而商业应用程序则根据其对性能和可靠性的需求来设置隔离级别。这在很大程度上取决于应用程序所需的工作流程类型以及性能和可靠性之间的平衡要求。

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

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

0 人点赞