实验 - MySql的事务隔离级别

最新推荐文章于 2024-04-19 13:36:59 发布
最新推荐文章于 2024-04-19 13:36:59 发布
阅读量708 收藏
点赞数
分类专栏： mysql 总结 文章标签： mysql 事务
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HackerSaillen/article/details/73527182
版权
总结 同时被 2 个专栏收录
52 篇文章 0 订阅
订阅专栏
mysql
10 篇文章 0 订阅
订阅专栏

通过百度搜索：“MySql 事务隔离级别”，“InnoDB 事务隔离级别” 发现很多的文章“特点”如下：

  • 重点在于解释：不可重复读和幻读的区别；
  • 大部分结论是：在repeatable read的隔离级别下，解决了不可重复读的问题，但是存在幻读问题。

正确的分析可以参考：Innodb中的事务隔离级别和锁的关系

关于脏读、幻读、不可重复、丢失更新，可做如下实验。强调仅限MySql环境，各类数据库的结论推测到其他数据库上是不太合适的。

实验证明目的是证明：MySql InnoDB的Repeatable Read级别是解决了不可重复读和幻读的问题，并直观感受脏读、幻读、不可重复问题

准备表和数据

CREATE DATABASE Test;
USE Test;

CREATE TABLE test(
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    sex CHAR(1),
    PRIMARY KEY (id),
    KEY (name,sex)
)ENGINE = INNODB;

INSERT test SELECT 1,'saillen','男';
INSERT test SELECT 2,'wenwen','女';
INSERT test SELECT 3,'jocker','男';

实验SQL

在各个级别下的实验SQL都是一样的，不一样的是设置事务级别的语句。

#事务1
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START TRANSACTION;
#更新操作：脏读、不可重复读
UPDATE test SET name = 'sai' WHERE id = 1;
#插入操作：幻读
INSERT test SELECT 4,'hacker','男';
COMMIT;

#事务2
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START TRANSACTION;
#在事务1开始前执行一遍
SELECT * FROM test;
#在事务1update和insert后执行一遍
SELECT * FROM test;
#在事务1 commit后执行一遍，出现不可重复和幻读
SELECT * FROM test;

Read Uncommitted级别实验

脏读发生在Read UnCommitted级别下，表示读取到了其他并发的未提交事务的结果

实验结果

事务1的结果：

mysql> SELECT @@tx_isolation \G
*************************** 1. 行 ***************************
@@tx_isolation: REPEATABLE-READ
1 行于数据集 (0.01 秒)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.02 秒)

mysql> select @@tx_isolation ;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 行于数据集 (0.03 秒)

mysql> start transaction;
Query OK, 0 rows affected (0.02 秒)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.03 秒)

mysql> select * from test where id = 1;
+----+------+------+
| id | name | sex  |
+----+------+------+
| 1  | sai  | 男  |
+----+------+------+
1 行于数据集 (0.02 秒)

mysql> rollback;
Query OK, 0 rows affected (0.02 秒)

mysql> select * from test where id = 1;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 1  | saillen | 男  |
+----+---------+------+
1 行于数据集 (0.03 秒)

事务2的结果：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 行于数据集 (0.03 秒)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 秒)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 行于数据集 (0.02 秒)

mysql> start transaction;
Query OK, 0 rows affected (0.02 秒)

mysql> select * from test where id = 1;
+----+------+------+
| id | name | sex  |
+----+------+------+
| 1  | sai  | 男  |
+----+------+------+
1 行于数据集 (0.02 秒)

mysql> select * from test where id = 1;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 1  | saillen | 男  |
+----+---------+------+
1 行于数据集 (0.04 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

结论

MySql在READ UNCOMMITTED事务隔离级别下会有脏读现象，因为不加任何锁，极端情况下才使用的级别；

Read Committed事务级别

不可重复读是指事务1在事务过程中，事务2修改了事务1读取的行，导致事务1两次读取到的结果不一致，重点区别是事务2 commit后才能感知到这次修改。这种情况某些时候是允许并且欢迎的，比如我们对账户做修改的时候，应该实时感知到余额的变化，但是某些时候是不应该被感知的，比如做某个时间段的销量统计的，库存减少在事务中被感知，一是程序容易计算错误，二是我们计算的是某个时候的，下一时刻的变化不在考虑范围内。

实验结果

事务1的实验结果

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.02 秒)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 行于数据集 (0.03 秒)

mysql> start transaction;
Query OK, 0 rows affected (0.02 秒)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.02 秒)

mysql> insert test select 4,'hacker','男';
Query OK, 1 rows affected (0.01 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

事务2实验结果

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.02 秒)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 行于数据集 (0.03 秒)

mysql> start transaction;
Query OK, 0 rows affected (0.01 秒)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
3 行于数据集 (0.02 秒)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
3 行于数据集 (0.03 秒)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
3 行于数据集 (0.02 秒)

mysql> select * from test;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
| 4  | hacker | 男  |
| 3  | jocker | 男  |
| 1  | sai    | 男  |
| 2  | wenwen | 女  |
+----+--------+------+
4 行于数据集 (0.01 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

结论

MySql在Read Committed事务隔离级别下是解决了脏读问题，但是存在不可重复读和幻读问题。在Read Committed级别下，MySql InnoDB引擎采用一致性非锁定读方案去读数据，读不加S锁，但是写加了X锁，所以读发生了问题，如果使用SELECT * FROM test IN SHARE MODE 主动加S锁可以避免不可重复读；

如果主动加S或者X锁，会发下事务1被阻塞在update的地方，因为update要加X锁，row已经被加S锁，所以要阻塞，事务并发性降低。

如果使用SELECT * FROM test FOR UPDATE 主动加X锁也可以避免，但是无法避免幻读。

Repeatable Read级别

Repeatable read 是MySql的默认事务级别，也是网上部分结论出问题的事务级别，该级别在ISO和一些数据库的实现上是用来解决不可重复读问题的，但是解决不了幻读。但是实际上，MySql的可重复读级别是解决了‘幻读’和‘不可重复读’两个问题的！

实验结果

事务1结果

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.01 秒)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.02 秒)

mysql> insert test select 5,'h','女';
Query OK, 1 rows affected (0.02 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

mysql>

事务2结果

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 行于数据集 (0.02 秒)

mysql> start transaction;
Query OK, 0 rows affected (0.01 秒)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | 男  |
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
4 行于数据集 (0.02 秒)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | 男  |
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
4 行于数据集 (0.05 秒)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | 男  |
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
4 行于数据集 (0.03 秒)

mysql> 
mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | 男  |
| 3  | jocker  | 男  |
| 1  | saillen | 男  |
| 2  | wenwen  | 女  |
+----+---------+------+
4 行于数据集 (0.02 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

mysql> select * from test ;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
| 5  | h      | 女  |
| 4  | hacker | 男  |
| 3  | jocker | 男  |
| 1  | sai    | 男  |
| 2  | wenwen | 女  |
+----+--------+------+
5 行于数据集 (0.03 秒)

结论

MySql的可重复事务级别解决了不可重复和幻读问题，这个和大部分的数据库以及ISO标准是不一样的。解决方案是采用Next-Key Lock实现，通过锁定范围来实现。

以上述例子分析，select * from test; 这里没有加where条件，肯定走全表扫描，所以加锁的范围就是负无穷到正无穷，如果是加where条件为id > 10 and id < 20，那么加锁范围就是[10,20]会锁定id为10，11，……，20的行，这些行可能不存在，但是在锁定范围内是的数据是不会被update和insert的。MySql下解决幻读的方案存在一定小问题，但是并不严重。

串行化

在一般的扁平事务中，MySql的默认事务隔离级别完全够用了，不需要串行化，串行化是MySql为分布式事务准备的。实际使用很少这里不实验。

赛小文
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库实验报告五--事务管理
贾作真时真亦贾的博客
12-06 2198
备份数据库： BACKUP DATABASE stu TO DISK=N'D:\Downloads\stu.bak' BACKUP DATABASE stu TO DISK=N'D:\Downloads\studiff.bak' WITH DIFFERENTIAL BACKUP LOG stu TO DISK=N'D:\Downloads\stulog.bak' 恢复数据库： RESTORE D...
MySQL-事务的隔离级别
幸遇三杯酒好，况逢一朵花新
09-14 215
文章目录前言事务是什么是事务？事务的特性（ACID）并发事务带来的问题：那么幻读和不可重复读有什么区别呢？Map小结 前言   今天就来认识一下MySQL的事务。 事务 是什么是事务？   事务是逻辑上的一组操作，要么都执行，要么都不执行。 事务的特性（ACID） 原子性（atomicity）：事务是最小的执行单位，不允许分割。事物的原子性确保动作要么全部完成，要么完全不起作用。 一致性（c...
MYSQL事务隔离级别详解附加实验
weixin_34358092的博客
02-25 126
参考： https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html http://xm-king.iteye.com/blog/770721 1.事务介绍 1.1 4种事务介绍 Read Uncommitted（读取未提交内容）        在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应...
（九）MySQL基础——事务以及隔离级别
Steriles_的博客
10-27 378
一、事务 1、介绍：事务由单独单元的一个或多个sql语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元格作为一个不可分割的整体，如果单元中某条sql语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态。如果单元中的所有sql语句均执行成功，则事务被顺利执行。 2、事务的（ACID）属性 （1）原子性（Atomicity） 原子性是指...
事务及事务隔离级别
To the beyond and infinity!
07-16 139
什么是事务 事务是访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据库的存取。事务的正确执行使得数据库从一种状态转换为另一种状态。 事务必须服从ISO/IEC所制定的ACID原则。ACID是原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）的缩写，这四种状态的意思是： 1、原子性 即不可分割，事务要么全部被执行，要么全部不执行。如果事务的所有子事务全部提交成功，则所有的数据库操作被提交，数据库状态发生变化；如果有子事务失.
关于mysql的事务的控制实验
oFengFengFeng的专栏
10-10 441
mysql默认的写操作的sql是自动提交的，所以为了使得mysql不再是自动提交，而改成手动提交， 开启一个事务的操作BEGIN; 写操作的语句 INSERT INTO `sys_users` VALUES (null,'zhang9999','ab260cf4152c17c411faefb91ee0d034','53a4a4e5792191662530944475dd2371'
MySQL的事务隔离级别
weixin_65175296的博客
06-21 381
事务的隔离级别
Mysql事务隔离级别之读提交详解
09-09
MySQL 事务隔离级别是数据库管理系统为了确保数据一致性而设置的一系列规则，它们定义了在一个事务中，用户可以读取到其他事务中哪些修改过的数据。本文主要探讨的是“读提交”（READ-COMMITTED）这一隔离级别。 在...
MySQL四种事务隔离级别详解
01-21
本文实验的测试环境：Windows 10+cmd+MySQL5.6.36+InnoDB 一、事务的基本要素（ACID） 　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会...
MySQL事务隔离级别中可重复读与幻读
最新发布
2301_79099460的博客
04-19 998
在说幻读之前,我们要先来了解脏读和不可重复读。
Mysql.事务隔离级别(transaction_isolation)
newlife1441的博客
07-15 3534
MySQL有四种不同的隔离级别read-uncommit、read-commit、repeatable-read和serializable,这四种隔离级别分别解决了不同的数据一致性问题，也存在不同的问题，为了验证这些问题，我做了以下实验。
MySQL事务隔离级别详解：深入理解ACID特性和隔离级别
[MySQL事务隔离级别详解：深入理解ACID特性和隔离级别](https://ask.qcloudimg.com/http-save/yehe-7197959/ti9e3deoyc.png) # 1. MySQL事务基础** 事务是数据库中一系列操作的集合，这些操作要么全部成功，要么...
MySql（八）：事务实践
weixin_40074110的博客
05-26 346
事务： 事务是不可能分隔的逻辑工作单元，需要保证原子性、隔离性、一致性、持久性的一个或者多个数据库操作称为一个事务。 事务具备四个属性(ACID)： 例：小明向小强转账10元。 原子性(Atomicity): 事物是一个完整的操作，各个操作不可分，要么都执行，要么都不执行。所以我们把要么全部做，要么全部不做的规则称之为原子性。 转账是一个不可分割操作，要么成功，要么失败，不可能存在中间状态(小明转出了，小强没有转入)。 隔离性(Isolation): 对数据修改的并非事...
数据库系统原理实验——事务实验
qq_43417003的博客
10-29 2102
针对TPC-H数据库，设计几个典型的事务应用，包括显式事务、事务提交、事务回滚等。 （1）显式事务的编写 ①创建一个事务，当用户购买零件时，插入订单明细和订单记录，修改供应基本表以保持数据一致性。 ②创建一个事务，当用户撤销某个用户购买记录时，删除订单明细（假设只有一项订单明细）和订单记录，然后修改供应基本表以保持数据一致性。 （2）显式事务的编写（带有回滚） 创建一个事务，当用户购买零件时，插入订单明细（假设只购买一项明细）和订单记录，然后修改供应基本表，以保持数据一致性。
事务的四大隔离级别的实验
weixin_43843172的博客
10-25 562
数据库实验之事务的四大级别操作 背景介绍： 一、事务的四大特性（ACID） 了解事务隔离级别之前不得不了解的事务的四大特性。 1、原子性（Atomicity） 事务开始后所有操作，要么全部做完，要么全部不做。事务是一个不可分割的整体。事务在执行过程中出错，会回滚到事务开始之前的状态，以此来保证事务的完整性。类似于原子在物理上的解释：指化学反应不可再分的基本微粒，原子在化学反应中不可分割 。 2、一致性（Consistency） 事务在开始和结束后，能保证数据库完整性约束的正确性即数据的完整性。比如经典的转账
事务隔离级别及Mysql的支持
炎黄的专栏
11-26 1292
MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。 未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。 提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的
真正理解Mysql的四种隔离级别
Ariescat Blog
02-28 1161
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。 事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。
实现JS复制、粘贴，Chrome/Firefox下可用
热门推荐
HackerSaillen的专栏
05-13 6万+
背景：       最近一直在做一个Web Excel项目，其中一项功能一直很纠结，就是数据的复制粘贴功能。功能要求：可以把Web端数据粘贴到Excel中，也可以从Excel中直接粘贴数据到页面编辑器中。支持Button粘贴和Ctrl+C/Ctrl+V快捷键复制粘贴。       本以为这个功能会很简单，但是查了很多资料，用JS操作粘贴板IE下很好用，直接支持一个window.clip
接口和抽象类可以被new嘛？——顺便总结内部类
HackerSaillen的专栏
08-04 2万+
背景：     最近有同事跟我说了他面试时遇到的问题，考官问：“接口和抽象类可以被new嘛？”。这可能不是考官的原话，但是据他表达考官大概就是这个意思了。听到这个问题，我的第一反应是肯定不行啊，直接对接口和抽象类调用new，编译器都过不去。但是他说，考官说可以，用匿名内部类实现。听见这个回到，我感觉那个考官太…………，有点无语。我们可以仔细分析下这个问题。 直接new接口和抽象类  
MySQL事务隔离级别实战演示：不同级别影响数据一致性
MySQL的事务隔离级别是确保数据库并发操作正确性和一致性的重要机制。InnoDB存储引擎作为MySQL的核心部分，支持多种隔离级别，如读未提交(Read Uncommitted)、读已提交(Read Committed)、可重复读(Repeatable Read)...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值