MySQL的四种隔离级别

作者: Darren 分类: mysql 发布时间: 2019-10-20 23:29

声明:首先这边博文,是针对网上看到一篇博文的完善与补充,因为那篇博文有问题,在第三个实验中有错误,让人看不明白,有错误,所以我这边完善与补充那篇博文,如果以下正确的地方,我就复制了。

通过本次的学习,终于搞明白了事务的隔离级别以及脏读幻读等出现的原因,原文地址:https://www.cnblogs.com/zyzblogs/p/11375381.html

  • 首先要明白什么是事务?
    • 事务是程序中一系列严密的操作,所有的操作必须完成,否则在所有的操作中所做的所有的更改都会被撤销。也就是事务的原子性,一个事务中的一系列的操作要么全部成功,要么全部不做
    • 事务的结束有两种,当事务中所有的步骤全部成功执行的时候,事务提交。如果其中一个步骤失败,将会发生回滚操作,撤销到事务开始之前的所有的操作。
  • 事务的ACID
    • 事务具有四个特征
      • 原子性
        • 事务是数据库的逻辑工作单位,事务中包含多个操作,要么都走,要么都不做
      • 隔离性
        • 事务彼此之间是不能互相干扰的,即一个事务的操作对该数据库的其他事务操作是隔离的,并发执行的各个事务时间互补干扰
      • 持续性
        • 事务一旦提交,其变更是永久性的,
      • 一致性
        • 事务执行的结果必须满足从一个状态变到另一个状态,因此当数据库只包含成功事务提交的结果时,就说数据库处于一致性的状态。如果数据库系统在运行时发生系统故障,有些未完成的事务被迫中止,而有一部分修改已经写入数据库,这个时候数据库就处于一种不正确的状态。其实以上三个条件最终都是为了保持数据库数据的一致性服务的
  • MySQL的四种隔离级别
    • SQL标准定义了四种隔离级别,用来限定事务内外的哪些改变是可见的,哪些是不可见的
      • 读取未提交的数据【Read Uncommitted】
        • 在改隔离级别,所有的事务都可以看到其他事务没有提交的执行结果。很少用于实际应用。性能也不必其他的级别好多少
      • 取提交的内容【Read  Committed】
        • 该隔离级别是大多数数据库的默认的隔离级别(不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别也支持不可重复读,即同一个select可能得到不同的结果
      • 可重读【Repeatable Read】
        • 这是MySQL默认的隔离级别,它确保同一个事务在并发读取数据时,会看到同样的数据行。不过理论上会导致另外一个问题,幻读。幻读:相同的条件查询一些数据,然后其他事务新增或者是删除了该条件的数据,然后导致读取的结果不一样多。InnoDB和Falcon存储引擎通过多版本控制(MVCC)机制解决了该问题
      • 可串行化【serializable】
        • 这是事务的最高隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决了幻读的问题。它在每个读的数据行上面加上共享锁,。但是可能会导致超时和锁竞争
      • 这四种隔离级别采用不同的锁类型来实现
        • 脏读
          • 读取了前一个事务未提交的或者是回滚的数据
        • 不可重复度
          • 同样的select查询,但是结果不同,过程中有事务更新了原有的数据
        • 幻读
          • 两次查询的结果数量不一样,过程中有事务新增或者是删除数据
隔离级别
脏读
不可重复度
幻读
读取未提交的
读取提交的
不会
可重读
不会
不会
可串行化
不会
不会
不会

 

创建sql语句:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 1);
INSERT INTO `test` VALUES (2, 2);
INSERT INTO `test` VALUES (3, 3);
INSERT INTO `test` VALUES (4, 4);

SET FOREIGN_KEY_CHECKS = 1;
查看数据库的隔离级别
show variables like '%isolation%'
设置数据库的隔离级别
set session transaction isolation level Read Uncommitted;
设置数据库的隔离级别为:Read Uncommitted

实验一:读取未提交-Read Uncommitted

前置条件:将数据库的隔离级别设置为read uncomitted;
set session transaction isolation level Read Uncommitted;
1199220-20190819093410222-326416064
1199220-20190819093354119-732534092

 

第一步:A开启事务:start tracsaction;

 1199220-20190819093429934-574860130

第二步:A查询数据:select * from test;

1199220-20190819093436836-1418559415

 

第三步:B开启事务:start transaction;

1199220-20190819093443264-518413686

第四步:B查询数据:select  * from test;

1199220-20190819093450867-1162857273

第五步:B更新数据:update test set num=10 where id=1;B没有提交事务

 1199220-20190819093456841-1602675685

第六步:A读取数据—-A读取到了B未提交的数据(当前数据库的隔离级别是:Read Uncommitted)

 1199220-20190819093504594-493083298

第七步:B回滚数据:rollback;

 1199220-20190819093510321-662696069

第八步:B查询数据:select *from test;

1199220-20190819093513983-683303284

第九步:A查询数:select * from test;

1199220-20190819093522350-309951080

结论:事务B更新了数据,但是没有提交,事务A读取到的是B未提交的记录。因为造成脏读。Read Uncommitted是最低的隔离级别。读取未提交的 A这边提交了,但是还没提交事务,但是B在另外一边却读取到了数据是A提交但是还没提交的事务.

实验二:读取已提交-Read Committed

前置条件:将数据库的隔离级别设置为:Read Committed;
set session transaction isolaction level Read Committed;

1199220-20190819093551808-1650208403

1199220-20190819093556989-802979012

第一步:A开始事务:start transaction;

 1199220-20190819093602224-46506610

第二步:A查询数据:select *from test;

 1199220-20190819093606555-138700621

第三步:B开启事务:start transaction;

1199220-20190819093612630-747112929

第四步:B查询数据:select * from test;

 1199220-20190819093617101-338737044

第五步:B更新数据:update test set num=10 where id=1;

 1199220-20190819093622193-568327506

第六步:A查询数据:select * from test;

1199220-20190819093631544-712505994

第七步:B提交数据:commit;

 1199220-20190819093642831-1020273682

第八步:A查询数据:select * from test;

 1199220-20190819093652298-1983733066

结论:Read Committed 读已提交的隔离级别解决了脏读的问题,但是出现了不可重复读的问题,即事务A在两次查询的结果不一致,因为在两次查询之间事务B更新了一条数据。
读已提交的只允许读取已经提交的记录 ,但是不要求可重复读。
读取提交的 A这边这边提交了数据,并且提交事务了,B这边取得了A提交的数据并且与提交了事务的诗句

实验三:可重读度-Repeatable Read

前置条件:将数据库的级别设置为可重复度
set session transaction isolation level repeatable read;

第一、二步:A开始事务:start transaction;并且查询A的数据:select * from test;

%title插图%num

第三步:B开启事务:start transaction;

%title插图%num

第四步:B查询数据:select * from test;

%title插图%num

第五步:B更新数据:update test set num=10 where id=1;

%title插图%num

第六步:B查询数据:select * from test;

%title插图%num

第七步:提交事务。

%title插图%num

第八步:查询的A的数据。

%title插图%num

第九步:在B窗口中在此插入一条数据,并且查询。

%title插图%num

第十步:在此查询A的数据。

%title插图%num

最后A也提交事务,此时发现A查询的数据已经和B查询的结果一致了;
结论:Repeatable Read隔离级别只允许读取已经提交的事务的记录。
可重复度 A这边不管你提交什么数据或者插入的数据,在B这边取得的数据都是不变了,都是跟以前的开启事务之前那一次的数据一样的.

实验四:客串行化-Serializable

​前置条件:将数据库的隔离级别设置为可串行化

1199220-20190819093907846-1711975657

第一步:A开始事务并查询数据

 1199220-20190819093912923-1418819383

第二步:B开启事务并insert数据,发现只能等待,并不能执行下去

 1199220-20190819093919786-433336792

第三步:A提交事务

 1199220-20190819093924683-559048411

第四步:B插入数据
 1199220-20190819093929570-803744580
结论:serializable完全锁定字段,若一个事务来操作同一份数据,那么就必须等待,直到前一个事务完成并解除锁为止。是完整的隔离级别,会锁住对应的数据表,因为会导致效率问题。
可串行化,就是一个个按照队列来,然后相当于开启了事务后,就直接上锁了,将表锁起来了,其他都都不能读与写,这种是最安全的,但是效率也是最低的.

发表评论

您的电子邮箱地址不会被公开。