MySQL数据库不同隔离级别下所产生的问题

一、创建数据库表

学生表： Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

课程表： Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

教师表： Teacher(t_id,t_name) –教师编号,教师姓名

成绩表： Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

创建学生表

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

创建课程表

CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

创建教师表

CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

创建成绩表

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

二、事务并发操作时产生的四个问题

1、脏写（Dirty Write）

脏写：T1事务和T2事务同时读写同一个数据，就有点像多线程不上锁导致的读写同一块内存区域的数据。

2、脏读（Dirty Read）

脏读：T1事务和T2事务同时读同一个数据，然后T1事务对数据进行了修改但没提交，T2事务进行了数据读取，得到了t时刻得数据，然后T1又修改数据并提交了，那么此时数据应该时最新值，但是T2在t时刻读的却是T1第一次修改后的数据，导致了脏读的问题。

3、不可重复读（Nonrepeatable Read）

不可重复读：可以这么简单的理解，如果两次读到的数据是一样的，那就叫可重复读，那如果两次读的数据不一样的话，那就是不能重复读，叫不可重复读。T2事务在t时刻读取数据，然后T1事务修改了该数据，然后T2再在t2时刻去读这个数据，T2两次读取的结果不一样。

4、幻读（Phantom Read）

如何理解幻读，幻读跟不可重复读相似，只不过两次重复读取数据之后，幻读得到的新数据是多出新数据的情况，称之为幻读。T2事务在t时刻读取数据，然后T1事务添加了新数据，然后T2再在t2时刻去读这个数据，T2两次读取的结果不一样。

三、MySQL的四种隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

1、读未提交（Read Uncommitted）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

2、读已提交（Read Committed）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

3、可重复读（Repeatable Read）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4、可串行化（Serializable）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

四种隔离级别的隔离性：

读未提交(read uncommitted) < 读已提交(read committed) <可重复读(repeatable read) < 可串行化(serializable)

四、不同隔离级别下所产生问题的现象分析

1、查看当前的事务的隔离等级

//查看当前事务的隔离等级(默认是第三级，也就是说，只剩一个幻读的操作可做)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 row in set (0.00 sec)

2、设置当前事务等级为第一级的“读未提交”(read uncommitted)

mysql> set session transaction isolation level read uncommitted;//两个窗口都要设置
Query OK, 0 rows affected (0.00 sec)

开始演示“读未提交”等级所能产生的问题：（左边是T1事务，右边是T2事务）

（1）脏写（可避免）

在第4步想在T2事务中对数据进行修改的时候会返回error，也就是说，最低级的读未提交不允许T2事务修改T1事务修改过的数据，不产生脏写问题。

 

（2）脏读（不可避免）

从第4步可以看出，T2事务读取的是T1的修改后的数据，可是此时T1还没有提交，产生了脏读问题

  

 （3）不可重复读（不可避免）

第4步和第8步读的两次读取的数据是不一样的，所以产生了不可重读读的问题。注意和（2）的步骤区别

 

（4）幻读（不可避免）

此时只开启了T1事务，没有开启T2事务，右边窗口查询2次，由于2次查询中T1事务在表格中新增了数据，所以右边窗口第二次查询的时候就产生了幻读问题。 

 

3、设置当前事务等级为第二级的“读已提交”(read committed)

mysql> set session transaction isolation level read committed;//两个窗口都要设置
Query OK, 0 rows affected (0.00 sec)

开始演示“读未提交”等级所能产生的问题：（左边是T1事务，右边是T2事务）

（1）脏写（可避免）详见   四 2（1）

（2）脏读（可避免）

看不到未提交的数据，避免了脏读。

 （3）不可重复读（不可避免）

产生了重复读数据却不一样的问题。

 （4）幻读（不可避免）

 

4、设置当前事务等级为第三级的“可重复读”(Repeatable Read)

mysql> set session transaction isolation level repeatable read;//两个窗口都要设置
Query OK, 0 rows affected (0.00 sec)

开始演示“读未提交”等级所能产生的问题：（左边是T1事务，右边是T2事务）

（1）脏写（可避免）详见   四 2（1）

（2）脏读（可避免）详见   四 3（2）

（3）不可重复读（可避免）

避免不可重复读，也就是说，可重复读 。T2提交commit之后再去查，才能查到最新的表。

（4）幻读（可避免）

理论上是可以产生幻读的，但是实际上并没有产生幻读。

为什么呢？

悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

MySQL、ORACLE、PostgreSQL等都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免不可重复读和幻读,MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。

MVCC(多版本并发控制)

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：

• SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
• INSERT时，保存当前事务版本号为行的创建版本号
• DELETE时，保存当前事务版本号为行的删除版本号
• UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都要额外的存储空间来记录version，需要更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多读操作都不用加锁,读取数据操作简单，性能好。

 

4、设置当前事务等级为第三级的“可串行化”(Serializable)

mysql> set session transaction isolation level serializable;//两个窗口都要设置
Query OK, 0 rows affected (0.00 sec)

开始演示“读未提交”等级所能产生的问题：（左边是T1事务，右边是T2事务）

（1）脏写（可避免）详见   四 2（1）

（2）脏读（可避免）详见   四 3（2）

（3）不可重复读（可避免）四 4 （3）

（4）幻读（可避免）四 4 （4）

五、总结：

 

参考文献：

mysql 四种隔离级别 - gao_jian - 博客园 (cnblogs.com)

事务隔离级别中的可重复读能防幻读吗? - 腾讯云开发者社区-腾讯云 (tencent.com)