MySQL数据库的事务的隔离级别

无忧摸鱼

发布于 2022-05-31 11:22:43

9850

发布于 2022-05-31 11:22:43

文章被收录于专栏：摸鱼天堂

数据库事务（Database Transaction），是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功、要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

● 原子性（Atomic），事务必须是原子工作单元。对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。 ● 一致性（Consistent），事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B-Tree索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。 ● 隔离性（Insulation），由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。 ● 持久性（Duration），事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些是在事务内和事务间可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。 ● 读未提交（Read Uncommitted），在读未提交级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，读未提交不会比其他的级别好太多，但是缺乏其他级别的很多好处，在实际应用中一般很少使用。 ● 读已提交（Read Committed），大多数数据库系统的默认隔离级别都是读已提交，但MySQL数据库不是。读已提交满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（Non-repeatable Read），因为两次执行同样的查询，可能会得到不一样的结果。 ● 可重复读（Repeatable Read），解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务中又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。可重复读是MySQL的默认事务隔离级别。 ● 可串行化（Serializable），是最高的隔离级别。它通过强制事务串行执行，避免了前面所说的幻读问题。简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。

1、读未提交（Read Uncommitted）

打开一个客户端A，并设置当前事务模式为读未提交（Read Uncommitted），查询表test01的初始值。在客户端A的事务提交之前，打开另一个客户端B，更新表test01。这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据。一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据。

– 打开客户端A，设置读未提交的隔离级别，查询表test01的值
mysql> set session tx_isolation=‘READ-UNCOMMITTED’;
mysql> select @@tx_isolation;
±-----------------+
| @@tx_isolation |
±-----------------+
| READ-UNCOMMITTED |
±-----------------+
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
– 打开客户端B，更新表test01，先不要提交
mysql> set session tx_isolation=‘READ-UNCOMMITTED’;
mysql> select @@tx_isolation;
±-----------------+
| @@tx_isolation |
±-----------------+
| READ-UNCOMMITTED |
±-----------------+
mysql> start transaction;
mysql> update test01 set text = ‘wujing’ where id =1;
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+
– 客户端A查询客户端B更新的数据
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+
– 客户端B回滚事务，那客户端A查询到的数据其实就是脏数据
mysql> rollback;
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+

2、读已提交（Read Committed）

打开一个客户端A，并设置当前事务模式为读已提交（Read Committed），查询表test01的初始值。在客户端A的事务提交之前，打开另一个客户端B，更新表test01。这时，客户端B的事务还没提交，客户端A不能查询到客户端B已经更新的数据，解决了脏读问题，再一次执行与上一步相同的查询，结果产生了与上一步不一致的数据，即产生了不可重复读的问题。

– 打开客户端A，设置读已提交的隔离级别，查询表test01的值
mysql> set session tx_isolation=‘READ-COMMITTED’;
mysql> select @@tx_isolation;
±---------------+
| @@tx_isolation |
±---------------+
| READ-COMMITTED |
±---------------+
mysql> start transaction;
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
– 打开客户端B，更新表test01，先不要提交
mysql> set session tx_isolation=‘READ-COMMITTED’;
mysql> select @@tx_isolation;
±---------------+
| @@tx_isolation |
±---------------+
| READ-COMMITTED |
±---------------+
mysql> start transaction;
mysql> update test01 set text = ‘wujing’ where id =1;
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+
– 客户端A不能查询到客户端B已经更新的数据，解决了脏读问题
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
– 客户端B执行提交操作，并查询更新后的数据
mysql> commit;
– 客户端A可以查询到客户端B已经提交后更新的数据，不能解决不可重复读的问题
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+

3、可重复读（Repeatable Read）

打开一个客户端A，并设置当前事务模式为可重复读（Repeatable Read），查询表test01的初始值。在客户端A的事务提交之前，打开另一个客户端B，更新表test01并提交。在客户端A查询表test01的所有记录，与前一个步骤查询结果一致，没有出现不可重复读的问题。在可重复读的隔离级别下，使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本），而insert、update和delete会更新版本号，是当前读（当前版本）的情况下进行相应的操作。在客户端A继续执行更新（update）操作，更新的值是客户端B提交后的值继续更新而来，数据的一致性并没有被破坏。

– 打开客户端A，设置可重复读的隔离级别，查询表test01的值
mysql> set session tx_isolation=‘REPEATABLE-READ’;
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+
mysql> start transaction;
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
– 打开客户端B，更新表test01并提交
mysql> set session tx_isolation=‘REPEATABLE-READ’;
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+
mysql> start transaction;
mysql> update test01 set id = 12 where id =1;
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 12 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
mysql> commit;
– 客户端A不能查询到客户端B已经提交更新后的数据，解决了不可重复读的问题
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+
– 客户端A继续执行更新操作，并提交，查询更新后的数据，可以发现id值是由客户端B更新后的数据进行操作的，数据的一致性没有被破坏
mysql> update test01 set id = id + 2 where text = ‘zhangsan’;
mysql> commit;
mysql> select * from test01;
±-----±---------+
| id | text |
±-----±---------+
| 14 | zhangsan |
| 2 | lisi |
| 3 | wangmin |
±-----±---------+

4、串行化（Serializable）

打开一个客户端A，并设置当前事务模式为可重复读（Repeatable Read），查询表test01的初始值。在客户端A的事务提交之前，打开另一个客户端B，执行更新表test01操作，可以发现，表被锁住了。

– 打开客户端A，设置可重复读的隔离级别，查询表test01的值
mysql> set session tx_isolation=‘Serializable’;
mysql> select @@tx_isolation;
±---------------+
| @@tx_isolation |
±---------------+
| SERIALIZABLE |
±---------------+
mysql> start transaction;
– 打开客户端B，更新表test01，先不提交
mysql> set session tx_isolation=‘Serializable’;
mysql> select @@tx_isolation;
±---------------+
| @@tx_isolation |
±---------------+
| SERIALIZABLE |
±---------------+
mysql> start transaction;
mysql> update test01 set text = ‘wujing’ where id =1;
– 客户端A执行查询操作，发现表test01被锁住了，过几秒钟出现报错
mysql> select * from test01;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
– 客户端B执行提交操作后，客户端A就可以继续执行查询操作
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+
mysql> commit;
– 客户端B可以执行查询操作
mysql> select * from test01;
±-----±--------+
| id | text |
±-----±--------+
| 1 | wujing |
| 2 | lisi |
| 3 | wangmin |
±-----±--------+

在MySQL数据库中，事务隔离级别为“Serializable”时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。事务隔离级别为读已提交时，写数据只会锁住相应的行；事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁，如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读；事务隔离级别为串行化时，读写数据都会锁住整张表。因此，隔离级别越高，越能保证数据的完整性和一致性，这对并发性能的影响也越大。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/07/18 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库