mysql 隔离级别

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

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

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

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

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

         这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

         脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

         不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

         幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

         在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

下面，将利用MySQL的客户端程序，分别测试几种隔离级别。测试数据库为test，表为tx；表结构：

id                              int

num

                              int

两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。

（一）、将A的隔离级别设置为read uncommitted(未提交读)

 在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

        经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

（二）、将客户端A的事务隔离级别设置为read committed(已提交读)

 在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

       经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。

(三)、将A的隔离级别设置为repeatable read(可重复读)

 在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

B插入数据：

客户端B：

客户端A：

       由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题。

(四)、将A的隔离级别设置为 可串行化 (Serializable)

A端打开事务，B端插入一条记录

事务A端：

事务B端：

因为此时事务A的隔离级别设置为serializable，开始事务后，并没有提交，所以事务B只能等待。

事务A提交事务：

事务A端

事务B端

      

         serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止 。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

版权声明：本文为博主原创文章，未经博主允许不得转载。

目录(?)[+]

1. MVCC简介

1.1 什么是MVCC

MVCC是一种多版本并发控制机制。

1.2 MVCC是为了解决什么问题?

• 大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用.
• 大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.

1.3 MVCC实现

MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制.

2.MVCC 具体实现分析

下面,我们通过InnoDB的MVCC实现来分析MVCC使怎样进行并发控制的. 
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.下面看一下在REPEATABLE READ隔离级别下,MVCC具体是如何操作的.

2.1简单的小例子

create table yang( 
id int primary key auto_increment, 
name varchar(20));

假设系统的版本号从1开始.

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为版本号. 
第一个事务ID为1；

start transaction;insert into yang values(NULL,'yang') ;insert into yang values(NULL,'long');insert into yang values(NULL,'fei');commit;
1
2
3
4
5
1
2
3
4
5

对应在数据中的表如下(后面两列是隐藏列,我们通过查询语句并看不到)

idname创建时间(事务ID)删除时间(事务ID)1yang1undefined2long1undefined3fei1undefined

SELECT

InnoDB会根据以下两个条件检查每行记录: 
a.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的. 
b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除. 
只有a,b同时满足的记录，才能返回作为查询结果.

DELETE

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识. 
看下面的具体例子分析: 
第二个事务,ID为2;

start transaction;select * from yang;  //(1)select * from yang;  //(2)commit; 
1
2
3
4
1
2
3
4

假设1

假设在执行这个事务ID为2的过程中,刚执行到(1),这时,有另一个事务ID为3往这个表里插入了一条数据; 
第三个事务ID为3;

start transaction;insert into yang values(NULL,'tian');commit;
1
2
3
1
2
3

这时表中的数据如下:

idname创建时间(事务ID)删除时间(事务ID)1yang1undefined2long1undefined3fei1undefined4tian3undefined

然后接着执行事务2中的(2),由于id=4的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以id=4的数据行并不会在执行事务2中的(2)被检索出来,在事务2中的两条select 语句检索出来的数据都只会下表:

idname创建时间(事务ID)删除时间(事务ID)1yang1undefined2long1undefined3fei1undefined

假设2

假设在执行这个事务ID为2的过程中,刚执行到(1),假设事务执行完事务3后，接着又执行了事务4; 
第四个事务:

start   transaction;  delete from yang where id=1;commit;  
1
2
3
1
2
3

此时数据库中的表如下:

idname创建时间(事务ID)删除时间(事务ID)1yang142long1undefined3fei1undefined4tian3undefined

接着执行事务ID为2的事务(2),根据SELECT 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,而id=4的行上面已经说过,而id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务2的(2)select * from yang也会把id=1的数据检索出来.所以,事务2中的两条select 语句检索出来的数据都如下:

idname创建时间(事务ID)删除时间(事务ID)1yang142long1undefined3fei1undefined

UPDATE

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间.

假设3

假设在执行完事务2的(1)后又执行,其它用户执行了事务3,4,这时，又有一个用户对这张表执行了UPDATE操作: 
第5个事务:

start  transaction;update yang set name='Long' where id=2;commit;
1
2
3
1
2
3

根据update的更新原则:会生成新的一行,并在原来要修改的列的删除时间列上添加本事务ID,得到表如下:

idname创建时间(事务ID)删除时间(事务ID)1yang142long153fei1undefined4tian3undefined2Long5undefined

继续执行事务2的(2),根据select 语句的检索条件,得到下表:

idname创建时间(事务ID)删除时间(事务ID)1yang142long153fei1undefined

还是和事务2中(1)select 得到相同的结果.