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

2024-03-19 09:44

本文主要是介绍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

相关文章

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们