IT虾米网

MySQL的事务隔离级别详解

admin 2018年06月08日 数据库 1066 0

一、数据库的隔离级别概述

隔离级别

脏读(Dirty Read

不可重复读(NonRepeatable Read

幻读(Phantom Read

未提交读(Read uncommitted--(脏读)

可能

可能

可能

已提交读(Read committed--(不可重复读)

不可能

可能

可能

可重复读(Repeatable read

不可能

不可能

可能

可串行化(Serializable

不可能

不可能

不可能


二、未提交读(Read uncommited) -- (脏读)

会出现脏读,也就是可能读取到其他会话中未提交事务修改的数据数据库一般都不会用,而且任何操作都不会加锁


分别在A、B两个客户端执行: 
 
A: 
root@(none) 10:54>SET GLOBAL tx_isolation='READ-UNCOMMITTED'; 
Query OK, 0 rows affected (0.00 sec) 
 
root@(none) 10:54>SELECT @@tx_isolation; 
+------------------+ 
| @@tx_isolation | 
+------------------+ 
| READ-UNCOMMITTED | 
+------------------+ 
1 row in set (0.00 sec) 
//开启事务 
[email protected] 10:55>begin; 
Query OK, 0 rows affected (0.00 sec) 
 
[email protected] 10:55>select * from test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
+------+ 
4 rows in set (0.00 sec) 
 
B上: 
[email protected] 10:58>select @@tx_isolation; 
+------------------+ 
| @@tx_isolation | 
+------------------+ 
| READ-UNCOMMITTED | 
+------------------+ 
1 row in set (0.00 sec) 
 
[email protected] 10:58> 
[email protected] 10:58>begin; 
Query OK, 0 rows affected (0.00 sec) 
 
[email protected] 10:58>insert into test.test1 values (999); 
Query OK, 1 row affected (0.00 sec) 
 
[email protected] 10:58>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 999 | 
+------+ 
5 rows in set (0.00 sec) 
此处B客户端并未commit; 
 
再查看A客户端: 
[email protected] 10:58>select * from test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 999 | 
+------+ 
5 rows in set (0.00 sec) 
 
此处A可以看到新的记录了。

可见,客户端B中新增了记录,未commit,此时客户端A却读出了新增的数据,如果此时客户端B取消掉当前的记录,那么数据库中就不存在该记录,然而客户端A却拥有了该数据,这就是 脏读!


三、已提交读(Read commited) -- (不可重复读)

这是大多数数据库的默认隔离级别(除了MySQL),简单的理解就是,只能读取到最新的数据


在A客户端: 
root@(none) 11:10>SET GLOBAL tx_isolation='READ-COMMITTED'; 
Query OK, 0 rows affected (0.00 sec) 
 
root@(none) 11:10>SELECT @@tx_isolation; 
+----------------+ 
| @@tx_isolation | 
+----------------+ 
| READ-COMMITTED | 
+----------------+ 
1 row in set (0.00 sec) 
 
root@(none) 11:10> 
root@(none) 11:10>begin; 
Query OK, 0 rows affected (0.00 sec) 
 
root@(none) 11:10>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
+------+ 
 
在B客户端执行: 
[email protected] 11:11>begin; 
Query OK, 0 rows affected (0.00 sec) 
 
[email protected] 11:11>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
+------+ 
4 rows in set (0.00 sec) 
 
[email protected] 11:11> 
[email protected] 11:11>delete from test.test1 where a=1; 
Query OK, 1 row affected (0.00 sec) 
 
[email protected] 11:12>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 2 | 
| 3 | 
| 4 | 
+------+ 
 
此时查询A客户端: 
 
root@(none) 11:12>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
+------+ 
此处看出A客户端无变化,在B客户端执行commit后再查看A客户端: 
 
root@(none) 11:13>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 2 | 
| 3 | 
| 4 | 
+------+ 
 
可以看到A客户端的数据已经变了。已提交读只允许读取已提交的记录,但不要求可重复读。 
用MVCC来说就是读取当前行的最新版本。

可以看到,已提交读,是读到只能读取到最新的数据;

当客户端B删除了记录时未commit时,此时数据库中最新的数据还是原来的数据,所以客户端A读取到的还是原来的数据;

当客户单B commit之后,此时数据库中最新的数据已经是删除后的数据了,所以客户端A读取到的是删除后的数据;


四、可重复读(Repeatable read )

这是MySQL Innodb的默认隔离级别在同一个事务内的查询都是事务开始时刻一致的。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读,然而,在MySQL中通过InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

在A客户端上: 
root@(none) 11:17>SET GLOBAL tx_isolation='REPEATABLE-READ'; 
Query OK, 0 rows affected (0.00 sec) 
 
root@(none) 11:17> 
root@(none) 11:17> 
root@(none) 11:17>SELECT @@tx_isolation; 
+-----------------+ 
| @@tx_isolation | 
+-----------------+ 
| REPEATABLE-READ | 
+-----------------+ 
1 row in set (0.00 sec) 
 
root@(none) 11:17>BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
 
在B客户端上: 
[email protected] 11:20>select @@tx_isolation; 
+-----------------+ 
| @@tx_isolation | 
+-----------------+ 
| REPEATABLE-READ | 
+-----------------+ 
1 row in set (0.00 sec) 
 
[email protected] 11:20>insert into test.test1 values (555); 
Query OK, 1 row affected (0.00 sec) 
 
[email protected] 11:20>commit; 
Query OK, 0 rows affected (0.00 sec) 
 
[email protected] 11:21> 
[email protected] 11:21>select * from test.test1; 
+------+ 
| a | 
+------+ 
| 2 | 
| 3 | 
| 4 | 
| 555 | 
+------+ 
4 rows in set (0.00 sec) 
此处在B客户端上已经commit. 
 
然后查看A客户端: 
 
root@(none) 11:22>SELECT * FROM test.test1; 
+------+ 
| a | 
+------+ 
| 2 | 
| 3 | 
| 4 | 
+------+ 
3 rows in set (0.00 sec) 
 
root@(none) 11:22>commit; 
Query OK, 0 rows affected (0.00 sec) 
 
 
root@(none) 11:22>SELECT * FROM test.test1; 
+------+ 
| a | 
+------+ 
| 2 | 
| 3 | 
| 4 | 
| 555 | 
+------+ 
4 rows in set (0.00 sec) 
 
在A客户端上提交后可以看到新数据。 
也就是说在可重复读隔离级别只能读取已经提交的数据,并且在一个事务内,读取的数据就是事务开始时的数据。

可以看到,事务A一开始读的数据,接着事务B对数据进行修改,但是并没有影响到事务A的读的数据,也就是说,在Repeatable read的隔离级别上,在一个事务内,


五、Serializable(可串行化)  不使用

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

该类型在A客户端操作test.test1表时会锁定该数据,如果B客户端想要操作test.test1就需要等待A客户端释放。


六、什么是幻读?

幻读只要为insert造成的,当事务不是独立执行时,发生的现象。

例如,事务A修改员工表的所有员工工资为1000,修改行数为10,然后事务B增加一个1000的员工,事务A读取员工表时,发现,工资1000的员工数时11,造成了幻读


七、已提交读和可重复读的原理

通过锁机制来实现?

如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。需要Serializable隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥(当数据处于被读状态,不能被写;或者处于被写状态,不能被读),这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。

MySQL、Oracle、PostgreSQL等成熟的数据库,出于性能考虑,都是使用了以乐观锁为理论基础的MVCC(多版本并发控制)来避免这两种问题。


八、多版本并发控制  MVCC

mvcc的实现,是通过保存数据在某个时间点的快照来实现的。(快照读)

也就是说

1. 无论事务执行多长时间,每一个事务看到的数据都是一样的

2. 根据事务开始的时间不同,不同的事务对同一张表,同一时刻看到的数据可能不一样


九、MVCC在MySQL InnoDB的使用(通过乐观锁)

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

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

通过MVCC,虽然每行记录都需要额外的存储空间,更多的行检查工作以及一些额外的维护工作,但可以减少锁的使用,大多数读操作都不用加锁,读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行,也只锁住必要行。

我们不管从数据库方面的教课书中学到,还是从网络上看到,大都是上文中事务的四种隔离级别这一模块列出的意思,RR级别是可重复读的,但无法解决幻读,而只有在Serializable级别才能解决幻读。于是我就加了一个事务C来展示效果。在事务C中添加了一条teacher_id=1的数据commit,RR级别中应该会有幻读现象,事务A在查询teacher_id=1的数据时会读到事务C新加的数据。但是测试后发现,在MySQL中是不存在这种情况的,在事务C提交后,事务A还是不会读到这条数据。可见在MySQL的RR级别中,是解决了幻读的读问题的。参见下图

innodb_lock_1

读问题解决了,根据MVCC的定义,并发提交数据时会出现冲突,那么冲突时如何解决呢?我们再来看看InnoDB中RR级别对于写数据的处理。

####“读”与“读”的区别
可能有读者会疑惑,事务的隔离级别其实都是对于读数据的定义,但到了这里,就被拆成了读和写两个模块来讲解。这主要是因为MySQL中的读,和事务隔离级别中的读,是不一样的。

我们且看,在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,是不及时的数据,不是数据库当前的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题。

对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在MVCC中:

  • 快照读:就是select
    • select * from table ....;
  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert;
    • update ;
    • delete;

事务的隔离级别实际上都是定义了当前读的级别,MySQL为了减少锁处理(包括等待其它锁)的时间,提升并发能力,引入了快照读的概念,使得select不用加锁。而update、insert这些“当前读”,就需要另外的模块来解决了。

因为更新数据、插入数据是针对当前数据的,所以不能以快照的历史数据为参考,此处就是这个意思。

###写("当前读")
事务的隔离级别中虽然只定义了读数据的要求,实际上这也可以说是写数据的要求。上文的“读”,实际是讲的快照读;而这里说的“写”就是当前读了。
为了解决当前读中的幻读问题,MySQL事务使用了Next-Key锁。

####Next-Key锁
Next-Key锁是行锁和GAP(间隙锁)的合并,行锁上文已经介绍了,接下来说下GAP间隙锁。

行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。但如何避免别的事务插入数据就成了问题。我们可以看看RR级别和RC级别的对比

RC级别:

事务A 事务B
begin;

begin;

select id,class_name,teacher_id from class_teacher where teacher_id=30;

id class_name teacher_id
2 初三二班 30


 
update class_teacher set class_name='初三四班' where teacher_id=30;

insert into class_teacher values (null,'初三二班',30);

commit;

select id,class_name,teacher_id from class_teacher where teacher_id=30;

id class_name teacher_id
2 初三四班 30
10 初三二班 30


 

RR级别:

事务A 事务B
begin;

begin;

select id,class_name,teacher_id from class_teacher where teacher_id=30;

id class_name teacher_id
2 初三二班 30
 
update class_teacher set class_name='初三四班' where teacher_id=30;

insert into class_teacher values (null,'初三二班',30);

waiting....

select id,class_name,teacher_id from class_teacher where teacher_id=30;

id class_name teacher_id
2 初三四班 30
 
commit; 事务Acommit后,事务B的insert执行。

通过对比我们可以发现,在RC级别中,事务A修改了所有teacher_id=30的数据,但是当事务Binsert进新数据后,事务A发现莫名其妙多了一行teacher_id=30的数据,而且没有被之前的update语句所修改,这就是“当前读”的幻读。

RR级别中,事务A在update后加锁,事务B无法插入新数据,这样事务A在update前后读的数据保持一致,避免了幻读。这个锁,就是Gap锁。

MySQL是这么实现的:

在class_teacher这张表中,teacher_id是个索引,那么它就会维护一套B+树的数据关系,为了简化,我们用链表结构来表达(实际上是个树形结构,但原理相同)

innodb_lock_2

如图所示,InnoDB使用的是聚集索引,teacher_id身为二级索引,就要维护一个索引字段和主键id的树状结构(这里用链表形式表现),并保持顺序排列。

Innodb将这段数据分成几个个区间

  • (negative infinity, 5],
  • (5,30],
  • (30,positive infinity);

update class_teacher set class_name='初三四班' where teacher_id=30;不仅用行锁,锁住了相应的数据行;同时也在两边的区间,(5,30]和(30,positive infinity),都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。

受限于这种实现方式,Innodb很多时候会锁住不需要锁的区间。如下所示:

事务A 事务B 事务C
begin; begin; begin;

select id,class_name,teacher_id from class_teacher;

id class_name teacher_id
1 初三一班

5

2 初三二班 30
   
update class_teacher set class_name='初一一班' where teacher_id=20;    
 

insert into class_teacher values (null,'初三五班',10);

waiting .....

insert into class_teacher values (null,'初三五班',40);
commit; 事务A commit之后,这条语句才插入成功 commit;
  commit;  

update的teacher_id=20是在(5,30]区间,即使没有修改任何数据,Innodb也会在这个区间加gap锁,而其它区间不会影响,事务C正常插入。

如果使用的是没有索引的字段,比如update class_teacher set teacher_id=7 where class_name='初三八班(即使没有匹配到任何数据)',那么会给全表加入gap锁。同时,它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁,因为没有索引,则这些字段也就没有排序,也就没有区间。除非该事务提交,否则其它事务无法插入任何数据。

行锁防止别的事务修改或删除,GAP锁防止别的事务新增,行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。


参考:http://blog.csdn.net/matt8/article/details/53096405

InnoDB加锁分析

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Spring 整合 MyBatis,Mapper注入失败详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。