事务的隔离级别以及解决的问题

一、 4个隔离级别

（1）读未提交：read uncommitted （2）读已提交：read committed （3）可重复读：repeatable read （4）串行化：serializable

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

二、如何设置事务的隔离级别：

SELECT @@global.tx_isolation, @@tx_isolation;

set session transaction isolation level repeatable read;

SET  transaction isolation level read uncommitted;
SET  transaction isolation level read committed;
set  transaction isolation level repeatable read;
SET  transaction isolation level serializable;

SET GLOBAL transaction isolation level read uncommitted;
SET GLOBAL transaction isolation level read committed;
set GLOBAL transaction isolation level repeatable read;
SET GLOBAL transaction isolation level serializable;

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

三、隔离级别说明

3.1 读未提交

• 事物A和事物B，事物A未提交的数据，事物B可以读取到
• 这里读取到的数据叫做“脏数据”，叫脏读
• 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

简而言之第一个事务没提交，别的事物就能读，这种数据不一定是正确的因为人家可能回滚呀！

案例：

楠哥发工资了，老婆让楠哥把工资打到他老婆的账号上，但是该事务并未提交，就让老婆去查看，老婆一看真的打了钱了，高高兴兴关了网页，此时楠哥急中生智进行回滚，钱瞬间回来，一次蒙混了一个月工资。所以楠哥老婆看到的数据我们称之为“脏数据”。

必须开两个事务

use test;
SET  transaction isolation level read uncommitted;

1-楠哥，转账

start transaction;
UPDATE user set balance = balance - 10000 where id = 1;
UPDATE user set balance = balance + 10000 where id = 2;

2-楠哥老婆，查账，不错，钱已到账

start transaction;
select * from user where id = 2;
commit;

3-楠哥,回马枪，回滚

rollback;

4-楠哥老婆某天查账，哎，怎么少了一万

start transaction;
select * from user where id = 2;
commit;

出现上述情况，即我们所说的脏读 ，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

3.2、读已提交

能读到别的事物已经提交的数据。

问题：A事务在本次事务中，对自己操作过的数据，进行了多次读取发现数据不一致，不可重复读。

简单点说就是不能让我好好的重复读，一个事务里读出来的数据都不一样，让不让人干活了。

针对的语句update和delete，会导致不可重复读

楠哥拿着工资卡去消费，系统读取到卡里确实有10200元，而此时她的老婆也正好在网上转账，把楠哥工资卡的2000元转到另一账户，并在 楠哥之前提交了事务，当楠哥扣款时，系统检查到楠哥的工资卡和上次读取的不一样了，楠哥十分纳闷，明明卡里有钱，为何......

SET  transaction isolation level read committed;

1-楠哥去消费了，显示有余额，贼高兴

start transaction;
select * from user where id = 1;

2-老婆转账

start transaction;
UPDATE user set balance = balance + 500 where id = 2;
UPDATE user set balance = balance - 500 where id = 1;
commit;

3-楠哥查账，同一个事务里，发现钱少了。

select * from user where id = 1;

当隔离级别设置为Read committed 时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。

3.3、可重复读

解决不可重复读的问题：A事务在本次事务中，对自己操作过的数据，进行了多次读取数据一致

问题：幻读：A事务在本次事务中对未操作的数据进行多次查询，发现第一次没有，第二次出现了就像幻觉一样。或者第一次有而第二次没有。针对delete和insert。

幻读案例：

楠哥的老婆在银行部门工作，她时常通过银行内部系统查看楠哥的账户信息。有一天，她正在查询到楠哥账户信息时发现楠哥只有一个账户，心想这家伙应该没有私房钱。此时楠哥在另外一家分行右开了一个账户，准备存私房钱。一次同时楠哥老婆点击了打印，结果打印出的楠哥账户居然多了一个，真实奇怪。

set  transaction isolation level repeatable read;

1-楠哥开启事务

start transaction;

2-老婆查账户

start transaction;
select * from user where name = '楠哥';

3-楠哥趁机开户

insert into user values(3,'楠哥',10000);
commit;

4-老婆再查询并打印，应该发现楠哥多了一个账户，但是没有。

select * from user where name = '楠哥';

MySQL 通过多版本并发控制（MVCC）（快照读/一致性读）其实解决了幻读问题。

原理：事务开启后，将历史数据存一份快照，其他事务增加与删除的数据，对于当前事务来说是不可见的。

当然还能这样测一下

set  transaction isolation level repeatable read;

1-楠哥开启事务

start transaction;

2-老婆查账户，给楠哥开了个账户

start transaction;
select * from user where name = '楠哥';
insert into user values(3,'楠哥',10000);

3-楠哥不知道老婆给他开了账户，自己也开一个，看见自己没有这个3号账户，居然不能插入，很奇幻。

select * from user where name = '楠哥';
insert into user values(3,'楠哥',10000);

3.4、 串行化

• 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
• 这种隔离级别很少使用，吞吐量太低，用户体验差
• 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。
• 别的地方一用这个数据就不能修改删除，直到别的地方提交

SET  transaction isolation level serializable;

1-楠哥

begin;
select * from user;

2-老婆

begin;
select * from user;

3-楠哥操作发现卡住了

delete from user where id = 9;

4-老婆这边一提交，那边就能操作了

commit;

四、额外说明

PS：

脏读：（同时操作都没提交的读取）

脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。

例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的数据就是脏的。

解决办法：把数据库的事务隔离级别调整到READ_COMMITTED

 

不可重复读：（同时操作，事务一分别读取事务二操作时和提交后的数据，读取的记录内容不一致）

不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。

例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

解决办法：把数据库的事务隔离级别调整到REPEATABLE_READ

 

幻读：（和可重复读类似，但是事务二的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）

例如：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入（注意时插入或者删除，不是修改））了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。这就叫幻读。

解决办法：把数据库的事务隔离级别调整到SERIALIZABLE_READ

 

摘自楠哥笔记：https://www.ydlclass.com/doc21xnv/database/transaction/#_4-%E4%BA%8B%E5%8A%A1%E7%89%B9%E6%80%A7-%E9%9A%94%E7%A6%BB%E6%80%A7