mysql8的隔离级别_mysql8学习笔记8--隔离级别

最新推荐文章于 2022-11-18 10:24:12 发布

叫我Zed

最新推荐文章于 2022-11-18 10:24:12 发布

阅读量256

点赞数

文章标签： mysql8的隔离级别

本文链接：https://blog.csdn.net/weixin_42393127/article/details/113685277

版权

学习了这篇文章：https://www.cnblogs.com/jian-gao/p/10795407.html

事务的四个特征(ACID)

事务具有四个特征：原子性( Atomicity )、一致性( Consistency )、隔离性( Isolation )和持续性( Durability )。这四个特性简称为 ACID 特性。

1 、原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

关于四种隔离mysql参考文档是这么描述的：

15.7.2.1事务隔离级别

事务隔离是数据库处理的基础之一。隔离是缩写ACID中的I;隔离级别是一种设置，用于在多个事务同时进行更改和执行查询时微调性能与结果的可靠性，一致性和可重复性之间的平衡。

用户可以使用该SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请--transaction-isolation在命令行或选项文件中使用该选项。有关隔离级别和级别设置语法的详细信息，请参见第13.3.7节“ SET TRANSACTION语句”。

InnoDB支持使用不同的锁定策略在此描述的每个事务隔离级别。您可以在默认数据REPEATABLE READ级别上实现高度一致性，以便在重要的数据上执行ACID合规性。或者，在批量报告之类的情况下，精确的一致性和可重复的结果与最小化锁定开销相比不那么重要，您READ COMMITTED甚至可以放宽一致性规则，甚至可以放宽一致性规则READ UNCOMMITTED。SERIALIZABLE实施比甚至更严格的规则REPEATABLE READ，并且主要用于特殊情况下(例如XA)事务以及用于解决并发和死锁问题。

下表描述了MySQL如何支持不同的事务级别。列表从最常用的级别到最不常用的级别。

感兴趣的可以去看：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Mysql的四种隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted(读取未提交内容)

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读(Dirty Read)。

Read Committed(读取提交内容)

这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read)，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read(可重读)

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 (Phantom Read)。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题。

Serializable(可串行化)

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

出现问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

接下来我按照文章中方法在本地测试环境测试下Mysql的隔离级别

Read Uncommitted(读取未提交内容)

下面，将利用MySQL的客户端程序，我们分别来测试一下这几种隔离级别。

测试数据库为company，表为customers4；表结构：

mysql>desc customers4;+--------------+-----------+------+-----+---------+-------+

+--------------+-----------+------+-----+---------+-------+

| cust_id | int(11) | NO | | 0 | |

+--------------+-----------+------+-----+---------+-------+

9 rows in set (0.00sec)

mysql>

(一)、将A的隔离级别设置为read uncommitted(未提交读)

set session transaction isolation level read uncommitted;

查看隔离级别是否设置成功

select @@transaction_isolation (mysql版本 8.0 以后)

select @@tx_isolation (mysql版本 8.0 之前)

查看mysql版本

> status

A：启动事务，此时数据为初始状态

start transaction;

B：启动事务，更新数据，但不提交

start transaction;

A：再次读取数据，发现数据已经被修改了，这就是所谓的“脏读”

A客户端：

mysql>mysql>use company;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| REPEATABLE-READ |

+-------------------------+

1 row in set (0.00sec)

mysql>set session transaction isolation level read uncommitted;

Query OK,0 rows affected (0.00sec)

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| READ-UNCOMMITTED |

+-------------------------+

1 row in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql>

B客户端：

mysql>use company;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql>show create table customers4;+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| customers4 |CREATE TABLE `customers4` (

`cust_id`int(11) NOT NULL DEFAULT '0',

`cust_name`char(50) CHARACTER SET utf8 NOT NULL,

`cust_address`char(50) CHARACTER SET utf8 DEFAULT NULL,

`cust_city`char(50) CHARACTER SET utf8 DEFAULT NULL,

`cust_state`char(5) CHARACTER SET utf8 DEFAULT NULL,

`cust_zip`char(10) CHARACTER SET utf8 DEFAULT NULL,

`cust_country`char(50) CHARACTER SET utf8 DEFAULT NULL,

`cust_contact`char(50) CHARACTER SET utf8 DEFAULT NULL,

`cust_email`char(255) CHARACTER SET utf8 DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

1 row in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql> update customers4 set cust_name='woman' where cust_id=2;

Query OK,1 row affected (0.04sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>

这时B客户端rollback：

mysql>rollback;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql>

A客户端查看结果也变了，那么之前读取的数据就是 ‘脏读’

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql>

经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

(二)、将客户端A的事务隔离级别设置为read committed(已提交读)

set session transaction isolation level read committed;

A：启动事务，此时数据为初始状态

B：启动事务，更新数据，但不提交

A：再次读数据，发现数据未被修改

B：提交事务

A：再次读取数据，发现数据已发生变化，说明B提交的修改被事务中的A读到了，这就是所谓的“不可重复读”

A客户端：

mysql>set session transaction isolation level read committed;

Query OK,0 rows affected (0.00sec)

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| READ-COMMITTED |

+-------------------------+

1 row in set (0.00sec)

mysql>commit;

Query OK,0 rows affected (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.01sec)

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| READ-COMMITTED |

+-------------------------+

1 row in set (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+------------+

2 rows in set (0.00sec)

mysql>

B客户端，这时update后还未commit，A客户端在事务中查到的还是旧数据

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> update customers4 set cust_email='AAA@szkingdom.com' where cust_name='spider_man';

Query OK,1 row affected (0.07sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> commit；

Query OK, 0 rows affected (0.09 sec)

B客户端commit后，再在A客户端查看：

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>

经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。

(三)、将A的隔离级别设置为repeatable read(可重复读)

A：启动事务，此时数据为初始状态

B：启动事务，更新数据，但不提交

A：再次读取数据，发现数据未被修改

B：提交事务

A：再次读取数据，发现数据依然未发生变化，这说明这次可以重复读了

B：插入一条新的数据，并提交

A：再次读取数据，发现数据依然未发生变化，虽然可以重复读了，但是却发现读的不是最新数据，这就是所谓的“幻读”

A：提交本次事务，再次读取数据，发现读取正常了

A客户端：

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| READ-COMMITTED |

+-------------------------+

1 row in set (0.00sec)

mysql>set session transaction isolation level repeatable read;

Query OK,0 rows affected (0.00sec)

mysql> select@@transaction_isolation;+-------------------------+

| @@transaction_isolation |

+-------------------------+

| REPEATABLE-READ |

+-------------------------+

1 row in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>

B客户端：

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> update customers4 set cust_contact='13545467358' where cust_id=1;

Query OK,1 row affected (0.05sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>

B客户端提交后：

mysql> update customers4 set cust_contact='13545467358' where cust_id=1;

Query OK,1 row affected (0.05sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>commit;

Query OK,0 rows affected (0.09sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>

A客户端查询还是没变，退出事务后才变：

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>commit;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00 sec)

由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题

(四)、将A的隔离级别设置为可串行化(Serializable)

A：启动事务，此时数据为初始状态

B：发现B此时进入了等待状态，原因是因为A的事务尚未提交，只能等待(此时，B可能会发生等待超时)

A：提交事务

B：发现插入成功

A客户端：

mysql>set session transaction isolation level serializable;

Query OK,0 rows affected (0.28sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>

B客户端：

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select *from customers4;+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

+---------+------------+-----------------------+-----------+------------+----------+--------------+--------------+-------------------+

2 rows in set (0.00sec)

mysql>

A客户端，update未提交：

mysql> update customers4 set cust_country='China' where cust_id=1;

Query OK,1 row affected (0.05sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>

B客户端，先进入等待，最后超时：

mysql> update customers4 set cust_country='China' where cust_id=1;

Query OK,1 row affected (0.05sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>