Mysql终极数据库隔离级别讲解

Mysql终极数据库隔离级别讲解

··记住自己的事物的隔离级别是什么就可能引发什么问题，和别人的隔离级别没关系。

mysql中在并发访问数据库的时候可能出现的问题:脏读、不可重复读、幻读，相应的隔离级别从低到高依次有读未提交(readuncommitted)、读已提交(read committed)、可重复读(repeatableread)、顺序化(serializable)。而oracle中隔离级别只有读已提交(read committed)、顺序化(serializable)。

1、脏读：在自己的事物中读取到了别人的事物中还未提交的数据。这肯定是不对的

2、不可重复读：在自己的事物中读取到了别人的事物中已提交的数据，但是这两次读取到的数据前后不一致。这个在某些情况下是正确的，在有些情况下就是不正确的。要看字几对数据的处理目的。

3、幻读：也是在自己的事物中读取到了别人已提交的数据，但是第二次读取到了别人添加了的数据。这个也是在某些情况下是正确的在某些情况下就是错误的。

 

三种现象的区别：

1、脏读肯定是不正确的，读取到别人还未提交的数据本身就是一个错误。

2、不可重复读和幻读的相同点是在自己的事物中都读取到了别人提交了的事物，不同点是不可重复读是自己两次读取到的数据结果的数值不一样，也就是没有记录多少的变化，只有记录数据值得变化，比如：自己第一次查看班级成绩的记录，看到的是班内总共有50个人，其中张三的成绩的100但是第二次再读发现张三的成绩变成了60,，但是班内还是50个人，数量并没有变化。而幻读是自己两次读取到的数据在统计数量上不一致，比如：自己第一次查看班级成绩的记录，看到的是总共50个人的记录，但是自己第二次查看发现班内的成绩是51个人的记录也就是多了一个人的记录。

 

针对这三种现象看一下事物的隔离级别：

1、针对脏读，直接隔离成read committed就可以了，这能保证只能读取到别人提交的数据。

2、针对不可重复读，将事物隔离级别提升为repeatable read，这能保证前后两次读取到的记录的数值是一致的。其实原理是：如果自己读取过某一条记录，就把这一条记录上一把锁不允许别人修改，但是别人可以读。注意是给已读取的一行上一把不允许别人修改的锁，但是对于这个表并没有上锁，所以别人如果添加了一行的话，自己并不能阻止，所以就出现了幻读。(值得注意的是：有的时候自己事物是repeatable read也能避免幻读，避免幻读本应该是Serializable隔离级别才有的功能，注意是有的时候，这个要看运气是不可靠的。)

3、针对幻读，将事物的隔离级别提升为Serializable，这能保证前后两次读取的数据完全一致，即不增也不减，数值也不变化。这个的原理是在自己读取到的表上上了一把排它锁，即不允许别人访问，这样的话就只有自己在操作这张表了，当然不会有任何问题了。

 

 

Oracle中的事物分级思想：

看一下oracle中对事物的隔离级别的分级马上就会发先，oracle中只有Read committed、Serializable两种隔离级别，因为脏读本来就是错误的所以不必要有Read uncommitted，而Repeatable read这种隔离级别能避免重复读但是却不能避免幻读是含糊的，也是没有必要的，因为不可重复读、幻读在一般情况下本来就是正确的应该发生的事情，只不过自己的目的不同而造成了这种现象“不正确”罢了，所以要吗都能避免不可重复读和幻读，要吗都不能避免不可重复读和幻读。

由此来看oracle的隔离分级是清楚的、简单的。所以我们建议就按照oracle这种分级来管理自己的数据库，如果只要求做查询的话，而且能查询到数据的实时变化，就设置成Read committed，这样能看到数据的动态变化，也符合要求。如果要做数据拥挤分析的话，就设置成Serializable，这能保证自己在做统计的时候，别人不能访问自己正在统计分析的表，能保证自己的统计分析是在一个绝对自己控制没有别人干扰的条件下进行。

 

 

 

下面这张表格是四中分级和三种现象的列表：

 

 

 

 

数据库支持

隔离级别

脏读

不可重复读

幻读

Mysql

Read uncommitted

存在

存在

存在

Mysql、oracle

Read committed

无

存在

存在

Mysql

Repeatable read

无

无

可能有也可能没有

Mysql、oracle

Serializable

无

无

无

 

 

 下面是关于各种分级的演示实验：

1.当把事务的隔离级别设置为read uncommitted时，会引发脏读、不可重复读和虚读

 

 

A窗口

settransaction isolation level read uncommitted;

starttransaction;

select* from account;

-----发现a帐户是1000元，转到b窗口

select* from account

-----发现a多了100元，这时候a读到了b未提交的数据（脏读）

 

 

B窗口

starttransaction;

updateaccount set money=money+100 where name='aaa';

-----不要提交，转到a窗口查询

 

 

2.当把事务的隔离级别设置为read committed时，会引发不可重复读和虚读，但避免了脏读

 

A窗口

settransactionisolation level  readcommitted;

starttransaction;

select*from account;

-----发现a帐户是1000元，转到b窗口

select*from account;

-----发现a帐户多了100,这时候，a读到了别的事务提交的数据，两次读取a帐户读到的是不同的结果（不可重复读）

 

 

B窗口

starttransaction;

updateaccountset money=money+100 where name='aaa';

commit;

-----转到a窗口

 

 

3.当把事务的隔离级别设置为repeatable read(mysql默认级别)时，会引发虚读(有的时候能读到有的时候读不到，但是要知道可能会引发幻读)，但避免了脏读、不可重复读

 

A窗口

settransactionisolation level repeatable read;

starttransaction;

select*from account;

----发现表有4个记录，转到b窗口

select*from account;

----可能发现表有5条记录，这时候发生了a读取到另外一个事务插入的数据（虚读）

 

 

B窗口

starttransaction;

insertintoaccount(name,money) values('ggg',1000);

commit;

-----转到 a窗口

 

4.当把事务的隔离级别设置为Serializable时，会避免所有问题

A窗口

settransaction isolation level Serializable;

starttransaction;

select* from account;

-----转到b窗口

 

B窗口

starttransaction;

insertinto account(name,money) values('ggg',1000);

-----发现不能插入，只能等待a结束事务才能插入

 

 

 

另外附上在命令行窗口中设置和查看事物隔离级别的语法：

set transaction isolation level readuncommitted;          //设置隔离级别是read uncommitted

select @@tx_isolation;                                                          //查看当前的隔离级别

 

set transaction isolation level readcommitted;               //设置隔离级别是readcommitted

 

set transaction isolation level repeatableread;              //设置隔离级别是repeatableread

 

set transaction isolation levelserializable;                       //设置隔离级别是serializable

 

set @@autocommit=0;                                                          //关闭sql语句的事物自动提交