MySQL事务隔离级别(二)

时间:2023-01-25 09:33:46

搞清楚MySQL事务隔离级别

首先创建一个表 account。创建表的过程略过(由于 InnoDB 存储引擎支持事务,所以将表的存储引擎设置为 InnoDB)。表的结构如下:

MySQL事务隔离级别(二)

MySQL事务隔离级别(二)

为了说明问题,我们打开两个控制台分别进行登录来模拟两个用户(暂且成为用户 A 和用户 B 吧),并设置当前 MySQL 会话的事务隔离级别。

一. read uncommitted(读取未提交数据)

具体用户 A 的操作如下:

set session transaction isolation level read uncommitted;
start transaction;
select * from account;

MySQL事务隔离级别(二)

用户 B 的操作如下:

1
2
3
4
5
6
7
8
9
10
set session transaction isolation level read uncommitted;
start transaction;
update account
set
 account=account+200
where
 id =1
;

随后我们在 A 用户中查询数据,结果如下:

MySQL事务隔离级别(二)

结论一

我们将事务隔离级别设置为 read uncommitted,即便是事务没有 commit,但是我们仍然能读到未提交的数据,这是所有隔离级别中最低的一种

那么这么做有什么问题吗?

那就是我们在一个事务中可以随随便便读取到其他事务未提交的数据,这还是比较麻烦的,我们叫脏读。我不知道这个名字是怎么起的,为了增强大家的印象,可以这么想,这个事务好轻浮啊,饥渴到连别人没提交的东西都等不及,真脏,呸!

实际上我们的数据改变了吗?

答案是否定的,数据库里面的数据并没有改变,因为只有事务 commit 后才会更新到数据库。

二.  read committed(可以读取其他事务提交的数据)--- 大多数数据库默认的隔离级别

同样的办法,我们将用户 B 所在的会话当前事务隔离级别设置为 read commited

 
在用户 A 所在的会话中我们执行下面操作:
update account set account=account-200 where id=1;

MySQL事务隔离级别(二)

我们将 id=1 的用户 account 减 200。然后查询,发现 id=1 的用户 account 变为 800。

在 B 用户所在的会话中查询:

select * from account;

结果如下:

MySQL事务隔离级别(二)

我们会发现数据并没有变,还是 1000。

接着在会话 A 中我们将事务提交:commit;

在会话 B 中查询结果如下:

MySQL事务隔离级别(二)

结论二:

当我们将当前会话的隔离级别设置为 read committed 的时候,当前会话只能读取到其他事务提交的数据,未提交的数据读不到。

那么这么做有什么问题吗?

那就是我们在会话 B 同一个事务中,读取到两次不同的结果。这就造成了不可重复读,就是两次读取的结果不同。这种现象叫不可重复读。

三. repeatable read(可重读)---MySQL 默认的隔离级别

现在有个需求,就是老板说在同一个事务中查询结果必须保持一致,如果你是数据库,你会怎么做?数据库是这么做的。

在会话 B 中我们当前事务隔离级别为 repeatable read。具体操作如下:

1
2
set session transaction isolation level repeatable read;
start transaction;

接着在会话 B 中查询数据:

MySQL事务隔离级别(二)

我们在 A 用户所在会话中为表 account 添加一条数据:

1
2
3
4
5
6
7
8
insert
into
 account(id,account) value(3,1000);
commit;

然后我们查询看数据插入是否成功:

MySQL事务隔离级别(二)

回到 B 用户所在的会话,我们查询结果:

MySQL事务隔离级别(二)

用户 B 在他所在的会话中想插入一条新数据 id=3,value=1000。来我们操作下:

MySQL事务隔离级别(二)

什么?竟然插不进去,说我数据重复?

用户 B 当然不服啊,因为查询到数据只有两条啊,为什么插入 id=3 说我数据重复了呢?

我再看一遍,莫非我眼花了?

MySQL事务隔离级别(二)

试想一下,在实际中用户 A 和用户 B 肯定是相互隔离的,彼此不知道操作什么。用户 B 碰到这种现象,肯定会炸毛的啊,明明不存在的数据,插入却说主键 id=3 数据重复了。

结论三:

当我们将当前会话的隔离级别设置为 repeatable read 的时候,当前会话可以重复读,就是每次读取的结果集都相同,而不管其他事务有没有提交。

有什么问题吗?

管他呢,老板的要求满足了。要一个事务中读取的数据一致(可重复读)。我只能这么做啊,打肿脸装胖子。数据已经发生改变,但是我还是要保持一致。但是,出现了用户 B 面对的问题,这种现象叫幻读(记得当时就在这个地方纠结好久,到底什么是幻读啊)。

四. serializable(串行化)

同样,我们将用户 B 所在的会话的事务隔离级别设置为 serializable 并开启事务。

1
2
3
set
 session transaction isolation level serializable;
start transaction;

在用户 B 所在的会话中我们执行下面操作:

select * from account;

结果如下:

MySQL事务隔离级别(二)

MySQL事务隔离级别(二)

如果在等待期间我们用户 B 所在的会话事务提交,那么用户 A 所在的事务的写操作将提示操作成功。

结论四:

当我们将当前会话的隔离级别设置为 serializable 的时候,其他会话对该表的写操作将被挂起。可以看到,这是隔离级别中最严格的,但是这样做势必对性能造成影响。所以在实际的选用上,我们要根据当前具体的情况选用合适的。

==========================================================================================================================================================================================================================================================================

MySQL事务主要用于处理一个包含操作量比较大、复杂的业务。比如说,删除一个学生,我们除了要删除该学生的基本信息,同时也要删除考试记录、违规记录等。诸多的操作组成一个事务。事务是用来管理insertupdatedelete 基本指令的。当MySQL使用innodb引擎的前提下才支持事务操作。

事务的基本特点

原子性

一个事务的执行所有的操作,结果只有两种:要么全部执行、要么全部不执行。事务在执行的过程中,当在某一个节点执行发生错误的时候,事务会被执行rollback操作,将数据恢复到执行该事务之前的状态。A去银行转账,要么转账成功、要么转账失败。

一致性

从事务开始执行到执行完成后,数据库的完整性约束完全没有收到破坏。A转账给B,不可能发生这种情况:A转账成功、B没有收到款。

隔离性

在同一时间点,数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交( read uncommitted )、读提交( read committed )、可重复读( repeatable read|默认方式 )和串行化( serializable )。

持久性

事务成功执行后,事务的所有操作对数据库的更新是永久的,不能回滚。

隔离性的类别

read uncommitted | 读未提交

read committed | 读已提交

repeatable read | 可重复读

serializable | 串行化

MySQL数据库中,引擎默认使用 repeatable read 可重复读

# SELECT @@tx_isolation 或者 SELECT @@transaction_isolation
# MySQL 8.x
# transaction_isolation在MySQL 5.7.20中添加了作为别名 tx_isolation,现已弃用,并在MySQL 8.0中删除。
# 应调整应用程序transaction_isolation以优先使用 tx_isolation。
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.01 sec)

事务的并发问题

脏读

事务A读取了事务B更新的数据,然后事务B在某些因素下执行了回滚,那么事务A读取的数据就是不合理的,即脏数据。

 ## (1)事务A的操作
## 设置为隔离方式为[读未提交 | read uncommitted]
## 开启事务并查询id为1的score的值
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec) mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 80 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) ## (2)事务B的操作
## 开启事务并将id为1的score修改成 75
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> update score set score=75 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 75 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) ## (3)事务A的操作
## 再次读取id为1的score值 75
mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 75 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) ## (4) 事务B的操作
## 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

上述四个步骤中,事务A事务B前读取的score的值为80,在事务B执行修改后读取score的值为75事务B再进行回滚操作,那么事务A在两次读取的score的值是不一致的,那么就是脏读。

不可重复读

事务A需要重复多次读取某组数据,事务A事务B对该组数据修改提交前后进行读取,很显然、两次读取的数据是不一致的,即不可重复读。侧重于元数据的修改。

 ## 使用[读已提交]的模式实践
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec) ## (1) 事务A查询id为1的score 80
mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 80 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) ## (2) 事务B修改id为1的score并提交事务 75
mysql> update score set score=75 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit;
Query OK, 0 rows affected (0.00 sec) ## (3) 事务A再次查询id为1的score的值 75
mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 80 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 75 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec)

从上述的三个步骤中显而易见可以看出,事务A在事务B修改并提交的前后读取同一条数据的值得不一样的,具有不可重复读问题。

幻读

事务A在修改每一条元数据的时候,事务B在此时添加了一条新记录,事务A在处理的过程中突然多了一条数据,即幻读。侧重于数据的删除与修改。

 ## 将事务隔离的模式设置为[可重复读]
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec) ## (1)事务A读取scor数据表
mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 75 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec) ## (2)事务B新增删除一条数据并提交
mysql> delete from score where id=1;
Query OK, 1 row affected (0.01 sec) mysql> commit;
Query OK, 0 rows affected (0.01 sec) ## (3)事务A再次读取score数据表
mysql> select * from score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | alicfeng | 75 |
| 2 | feng | 100 |
| 3 | alic | 90 |
+----+----------+-------+
3 rows in set (0.00 sec)

可见,事务A在事务B删除并提交前后读取的数据一样,出现了幻读。

事务隔离级别的影响

MySQL事务隔离级别(二)

事务隔离性说明

隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。

事务隔离级别为读提交时,写数据只会锁住相应的行(当在写时锁住相应的行,其他事务也就读不到了,知道写完commit之后释放锁,其他事务才可以读到)

事务隔离级别为串行化时,读写数据都会锁住整张表(将读上锁,写也上锁)

MySQL事务隔离级别(二)的更多相关文章

  1. MySQL事务隔离级别测试实例

    https://www.cnblogs.com/huanongying/p/7021555.html MySQL事务隔离级别 事务隔离级别 脏读 不可重复读 幻读 读未提交(read-uncommit ...

  2. MySQL事务隔离级别(一)

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做 ...

  3. [51CTO]新说MySQL事务隔离级别!

    新说MySQL事务隔离级别! 事务隔离级别这个问题,无论是校招还是社招,面试官都爱问!然而目前网上很多文章,说句实在话啊,我看了后我都怀疑作者弄懂没!本文所讲大部分内容,皆有官网作为佐证,因此对本文内 ...

  4. 查询mysql事务隔离级别

    查询mysql事务隔离级别 查询mysql事务隔离级别 分类: DB2011-11-26 13:12 2517人阅读 评论(0) 收藏 举报 mysqlsessionjava   1.查看当前会话隔离 ...

  5. Mysql事务-隔离级别

    MYSQL事务-隔离级别 事务是什么? 事务简言之就是一组SQL执行要么全部成功,要么全部失败.MYSQL的事务在存储引擎层实现. 事务都有ACID特性: 原子性(Atomicity):一个事务必须被 ...

  6. MySQL事务隔离级别 解决并发问题

    MySQL事务隔离级别 1. 脏读: 骗钱的手段, 两个窗口或线程分别调用数据库转账表,转账后未提交,对方查看到账后,rollback,实际钱没转. 演示方法: mysql默认的事务隔离级别为repe ...

  7. mysql事务隔离级别、脏读、幻读

    Mysql事务隔离级别本身很重要,再加上可能是因为各大公司面试必问的缘故,在博客中出现的概率非常高,但不幸的是,中国的技术博客要么是转载,要么是照抄,质量参差不齐,好多结论都是错的,对于心怀好奇之心想 ...

  8. mysql事务隔离级别与设置

    mysql数据库,当且仅当引擎是InnoDB,才支持事务: 1.隔离级别 事务的隔离级别分为:未提交读(read uncommitted).已提交读(read committed).可重复读(repe ...

  9. MySQL事务隔离级别详解

    原文地址:http://xm-king.iteye.com/blog/770721 SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的.低级别的隔离级 ...

随机推荐

  1. android优秀Github源码整理

    1.https://github.com/sd6352051/NiftyNotification 2.https://github.com/sd6352051/NiftyDialogEffects 3 ...

  2. Oracle存储过程function语法及案例

    create or replace function F01_SX03_SUM(statdate varchar2, code varchar2, para varchar2) RETURN numb ...

  3. Java中对List去重, Stream去重

    问题 当下互联网技术成熟,越来越多的趋向去中心化.分布式.流计算,使得很多以前在数据库侧做的事情放到了Java端.今天有人问道,如果数据库字段没有索引,那么应该如何根据该字段去重?大家都一致认为用Ja ...

  4. django - 总结 - cnblog 知识点

    1.图像预览 点击头像------>点击input img和input重合; img在label,input-->display:none $("#avatar").c ...

  5. C++ 用三元组表示法存储稀疏矩阵

    若有一个矩阵(m*n),其中非0元素个数远少于数值为0的元素个数,若开辟一个m*n大空间,来存储这样一个很多元素值为0的矩阵,浪费空间,于是我们只存储这些非0的元素的下标及数值 用一个结构体——三元组 ...

  6. appium自动化测试之元素定位

    方法一 使用SDK中附带的uiautomatorviewer来定位 在SDK安装目录下的tools下有个uiautomatorviewer.bat批处理文件点击运行 运行后(注意appium desk ...

  7. C语言学习一个月后感想

    C语言学习一个月后感想 感谢李晓东老板及计算机工程师联盟的学长学姐和某神秘同级同学的辛勤指导,感谢宋雨田的督促和陪伴. 初识C的1..体会 我本以为凭借瓜皮思维和花里胡哨操作可以让我熟练地学习语言,现 ...

  8. STM32一键下载电路设计原理

    先放原理图(补充:图中的BOOT0通过10K的电阻接到地),再解释为什么这么设计: STM32启动方式:BOOT0和 BOOT1用于设置 STM32的启动方式 ,见下表: BOOT0=1,BOOT1= ...

  9. (字符串的处理4.7.22)POJ 3337 Expression Evaluator(解析C风格的字符串)

    /* * POJ_3337.cpp * * Created on: 2013年10月29日 * Author: Administrator */ #include <iostream> # ...

  10. uva 210 - Concurrency Simulator &lpar;并行程序模拟&rpar;

    from CSDN: https://blog.csdn.net/su_cicada/article/details/87898579 例题6-1 并行程序模拟( Concurrency Simula ...