mysql笔记：21. 演示脏读、不可重复读和幻读现象

2024-03-19 09:44
文章标签 mysql 21 笔记 重复 现象 演示 不可 database 脏读 幻读

本文主要是介绍mysql笔记：21. 演示脏读、不可重复读和幻读现象，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 脏读
    • 1. 设置窗口B中事务的隔离级别
    • 2. 演示脏读
    • 3. 重新设置窗口B中事务的隔离级别
    • 4. 再次验证脏读
  • 不可重复读
    • 1. 演示不可重复读
    • 2. 设置窗口B中事务的隔离级别
    • 3. 再次验证不可重复读
  • 幻读
    • 1. 设置窗口B中事务的隔离级别
    • 2. 演示幻读
    • 3. 重新设置窗口B中事务的隔离级别
    • 4. 再次验证幻读
  • 串行化插入问题
    • 1. 设置窗口B中事务的隔离级别
    • 2. 演示串行化插入问题

在MySQL中，事务有4种隔离级别，分别为READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读）和SERIALIZABLE（串行化）。

脏读

READ UNCOMMITTED是事务隔离级别中最低的级别，该级别下的事务可以读取其他事务中未提交的数据，这种读取方式也被称为脏读。
下面以示例数据库employees.departments表为例演示脏读。首先，开启两个终端窗口，分别称为窗口A和窗口B。两个窗口都登录到MySQL数据库并切换到employees库。

1. 设置窗口B中事务的隔离级别

MySQL默认隔离级别是REPEATABLE READ，该级别可以避免脏读。为演示需求，将其隔离级别设置为READ UNCOMMITTED。

mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)# 设置隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

2. 演示脏读

在窗口A中开启事务，并更新Sales部门名称为Sales2。

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales     |
+---------+-----------+
1 row in set (0.01 sec)mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> UPDATE departments SET dept_name='Sales2' WHERE dept_no='d007';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales2    |
+---------+-----------+
1 row in set (0.00 sec)

此时不要提交事务，否则无法演示脏读现象。
切换到窗口B，查询该部门信息。

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales2    |
+---------+-----------+
1 row in set (0.01 sec)

从结果看，窗口B中可以看到修改后的部门名称，这是由于窗口B的事务隔离级别比较低，因此读取了窗口A中还没有提交的内容，出现了脏读情况。
脏读演示完毕，可以在窗口A中执行ROLLBACK命令回滚事务，让数据恢复初始状态。

3. 重新设置窗口B中事务的隔离级别

为防止脏读的发生，将B中事务的隔离级别设置为READ COMMITTED，该隔离级别可以避免脏读。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

4. 再次验证脏读

在窗口B中查询部门d007的信息。

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales     |
+---------+-----------+
1 row in set (0.00 sec)

然后在窗口A中开启事务并修改部门d007的信息。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> UPDATE departments SET dept_name='Sales3' WHERE dept_no='d007';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales3    |
+---------+-----------+
1 row in set (0.00 sec)

修改数据后再次查询d007的部门信息已更新，但数据未提交。此时在窗口B中查询。

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales     |
+---------+-----------+
1 row in set (0.00 sec)

结果显示窗口B中并没有查询到窗口A中未提交的内容，说明READ COMMITTED隔离级别可以避免脏读。
验证演示完毕，可以在窗口A中执行ROLLBACK命令回滚事务，让数据恢复初始状态。

不可重复读

在MySQL的READ COMMITTED级别下，事务只能读取其他事务已经提交的内容，可以避免脏读现象，但是会出现不可重复读和幻读的情况。
不可重复读是指在事务内重复读取数据，由于多次查询期间，其他事务更新了相关数据，因此出现多次读取结果不一致的现象。

1. 演示不可重复读

在窗口B中设置事务的隔离级别为READ COMMITTED，然后开启事务查询d007的部门信息。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales     |
+---------+-----------+
1 row in set (0.00 sec)

此时，在窗口A中使用UPDATE语句更新d007的部门信息。

mysql> UPDATE departments SET dept_name='Sales4' WHERE dept_no='d007';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales4    |
+---------+-----------+
1 row in set (0.00 sec)

修改成功后，再次回到窗口B中查询d007的部门信息。

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales4    |
+---------+-----------+
1 row in set (0.00 sec)

结果显示在一个事务中，相同的查询语句两次查询结果不一致。其实不可重复读并不算错误，但在有些情况下却不符合实际需求。
不可重复读演示完毕，可以在窗口B中执行COMMIT命令提交事务。

2. 设置窗口B中事务的隔离级别

为了防止不可重复读的情况出现，修改窗口B中事务的隔离级别为REPEATABLE READ。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

3. 再次验证不可重复读

在窗口B中开启事务，并查询d007的部门信息。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales4    |
+---------+-----------+
1 row in set (0.00 sec)

在窗口A中使用UPDATE语句更新d007的部门信息：

mysql> UPDATE departments SET dept_name='Sales5' WHERE dept_no='d007';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales5    |
+---------+-----------+
1 row in set (0.00 sec)

然后在窗口B中查询d007的部门信息：

mysql> SELECT * FROM departments WHERE dept_no='d007';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d007    | Sales4    |
+---------+-----------+
1 row in set (0.00 sec)

对比窗口B的两次查询结果，发现窗口B中事务的隔离级别修改为REPEATABLE READ后，查询结果是一致的，并没有出现不同的数据，说明此该级别可以避免不可重复读的情况。
验证演示完毕，可以在窗口B中执行COMMIT命令提交事务。

幻读

幻读又被称为虚读，是指在一个事务内两次查询中的数据条数不一致。幻读和不可重复读类似，都是在两次查询过程中。区别是，幻读是由于其他事务插入记录导致记录数变化而引起的。
理论上，REPEATABLE READ级别可以避免脏读、不可重复读，但会出现幻读。不过MySQL的存储引擎通过多版本并发控制机制解决了该问题，所以当事务隔离级别为REPEATABLE READ时可以避免幻读。

1. 设置窗口B中事务的隔离级别

REPEATABLE READ级别可以避免幻读，所以将事务的隔离级别设置得更低。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

2. 演示幻读

在窗口B中开启事务，并查询部门名称以字母D开头的信息：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM departments WHERE dept_name LIKE 'D%';
+---------+-------------+
| dept_no | dept_name   |
+---------+-------------+
| d005    | Development |
+---------+-------------+
1 row in set (0.00 sec)

在窗口A中插入新成立的部门Design：

mysql> INSERT INTO departments value('d010', 'Design');
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM departments WHERE dept_no='d010';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d010    | Design    |
+---------+-----------+
1 row in set (0.01 sec)

切换到窗口B中，再次查询部门名称以字母D开头的信息：

mysql> SELECT * FROM departments WHERE dept_name LIKE 'D%';
+---------+-------------+
| dept_no | dept_name   |
+---------+-------------+
| d010    | Design      |
| d005    | Development |
+---------+-------------+
2 rows in set (0.00 sec)

删除数据和新增数据类似，也会出现幻读现象。

幻读演示完毕，可以在窗口B中执行COMMIT命令提交事务。

3. 重新设置窗口B中事务的隔离级别

为避免幻读现象，将窗口B中事务的隔离级别设置为REPEATABLE READ。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

4. 再次验证幻读

在窗口B中开启事务，并查询部门名称以字母D开头的信息：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM departments WHERE dept_name LIKE 'D%';
+---------+-------------+
| dept_no | dept_name   |
+---------+-------------+
| d010    | Design      |
| d005    | Development |
+---------+-------------+
2 rows in set (0.00 sec)

在窗口A中插入新成立的部门Design2：

mysql> INSERT INTO departments value('d011', 'Design2');
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM departments WHERE dept_no='d011';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d011    | Design2   |
+---------+-----------+
1 row in set (0.00 sec)

切换到窗口B中，再次查询部门名称以字母D开头的信息：

mysql> SELECT * FROM departments WHERE dept_name LIKE 'D%';
+---------+-------------+
| dept_no | dept_name   |
+---------+-------------+
| d010    | Design      |
| d005    | Development |
+---------+-------------+
2 rows in set (0.00 sec)

对比窗口B中的两次查询结果，发现窗口B设置事务的隔离级别为REPEATABLE READ后，同一个事务的两次查询结果是一致的，并没有读取其他事务新插入的记录。说明REPEATABLE READ可以避免幻读。
验证演示完毕，可以在窗口B中执行COMMIT命令提交事务。

串行化插入问题

事务的隔离级别设置为SERIALIZABLE可以避免脏读、不可重复读和幻读，但也会导致大量的超时和锁竞争，数据库性能极差。

1. 设置窗口B中事务的隔离级别

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set (0.01 sec)

2. 演示串行化插入问题

在窗口B中开启事务，并查询表中数据。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT COUNT(1) FROM departments;
+----------+
| COUNT(1) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

切换到窗口A，并插入新成立的部门Design3：

mysql> INSERT INTO departments value('d012', 'Design3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

窗口A中执行插入语句后，不会立即执行成功，而是光标闪烁，一直等待。此时提交窗口B中的事务，窗口A中的插入操作会立即执行；否则A中的操作一直等待，直到最后超时提示信息。
默认超时时间是50秒。

这篇关于mysql笔记：21. 演示脏读、不可重复读和幻读现象的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/825569。

相关文章

Tolua使用笔记（上）

Tolua使用笔记（上）

目录   1.准备工作 2.运行例子 01.HelloWorld：在C#中，创建和销毁Lua虚拟机 和 简单调用。 02.ScriptsFromFile：在C#中，对一个lua文件的执行调用 03.CallLuaFunction：在C#中，对lua函数的操作 04.AccessingLuaVariables：在C#中，对lua变量的操作 05.LuaCoroutine：在Lua中，
阅读更多...
AssetBundle学习笔记

AssetBundle学习笔记

AssetBundle是unity自定义的资源格式，通过调用引擎的资源打包接口对资源进行打包成.assetbundle格式的资源包。本文介绍了AssetBundle的生成，使用，加载，卸载以及Unity资源更新的一个基本步骤。 目录 1.定义： 2.AssetBundle的生成： 1）设置AssetBundle包的属性——通过编辑器界面 补充：分组策略 2）调用引擎接口API
阅读更多...
mysql索引四（组合索引）

mysql索引四（组合索引）

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。 因为有事，下面内容全部转自：https://www.cnblogs.com/farmer-cabbage/p/5793589.html 为了形象地对比单列索引和组合索引，为表添加多个字段： 　　 CREATE TABLE mytable( ID INT NOT NULL, use
阅读更多...
mysql索引三（全文索引）

mysql索引三（全文索引）

前面分别介绍了mysql索引一（普通索引）、mysql索引二（唯一索引）。 本文学习mysql全文索引。 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 在MySql中，创建全文索引相对比较简单。例如：我们有一个文章表（article），其中有主键ID（
阅读更多...
mysql索引二（唯一索引）

mysql索引二（唯一索引）

前文中介绍了MySQL中普通索引用法，和没有索引的区别。mysql索引一（普通索引） 下面学习一下唯一索引。 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE，把它定义为一个唯一索引。 添加数据库唯一索引的几种
阅读更多...
mysql索引一（普通索引）

mysql索引一（普通索引）

mysql的索引分为两大类，聚簇索引、非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同。聚簇索引能够提高多行检索的速度、非聚簇索引则对单行检索的速度很快。         在这两大类的索引类型下，还可以降索引分为4个小类型：         1，普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。         2，唯一索引：与普通索引
阅读更多...
《offer来了》第二章学习笔记

《offer来了》第二章学习笔记

1.集合 Java四种集合：List、Queue、Set和Map 1.1.List：可重复 有序的Collection ArrayList： 基于数组实现，增删慢，查询快，线程不安全 Vector： 基于数组实现，增删慢，查询快，线程安全 LinkedList： 基于双向链实现，增删快，查询慢，线程不安全 1.2.Queue：队列 ArrayBlockingQueue：
阅读更多...
21.手绘Spring IOC运行时序图

21.手绘Spring IOC运行时序图

1.再谈IOC与 DI IOC(lnversion of Control)控制反转：所谓控制反转，就是把原先我们代码里面需要实现的对象创 建、依赖的代码，反转给容器来帮忙实现。那么必然的我们需要创建一个容器，同时需要一种描述来让 容器知道需要创建的对象与对象的关系。这个描述最具体表现就是我们所看到的配置文件。 DI(Dependency Injection)依赖注入:就是指对象是被动接受依赖类
阅读更多...
【服务器运维】MySQL数据存储至数据盘

【服务器运维】MySQL数据存储至数据盘

查看磁盘及分区 [root@MySQL tmp]# fdisk -lDisk /dev/sda: 21.5 GB, 21474836480 bytes255 heads, 63 sectors/track, 2610 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesSector size (logical/physical)
阅读更多...
操作系统实训复习笔记（1）

操作系统实训复习笔记（1）

目录 Linux vi/vim编辑器（简单） （1）vi/vim基本用法。 （2）vi/vim基础操作。 进程基础操作（简单） （1）fork()函数。 写文件系统函数（中等） ​编辑 （1）C语言读取文件。 （2）C语言写入文件。 1、write()函数。  读文件系统函数（简单） （1）read()函数。 作者本人的操作系统实训复习笔记 Linux
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2