MySQL中的事务隔离级别

 

SQL 标准用三个必须在并行的事务之间避免的现象定义了四个级别的事务隔离。 这些不希望发生的现象是： 

• 脏读（dirty reads）

一个事务读取了另一个未提交的并行事务写的数据。 

• 不可重复读（non-repeatable reads）

一个事务重新读取前面读取过的数据， 发现该数据已经被另一个已提交的事务修改过。 

• 幻读（phantom read）

一个事务重新执行一个查询，返回一套符合查询条件的行， 发现这些行因为其他最近提交的事务而发生了改变。

 

SQL 事务隔离级别 

离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读已提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

 

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询：

查看InnoDB系统级别的事务隔离级别：

    mysql> SELECT @@global.tx_isolation; or  show variables like "%tx%";

结果：

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

查看InnoDB会话级别的事务隔离级别：

  mysql> SELECT @@tx_isolation;

结果：
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

修改事务隔离级别：

    mysql> set global transaction isolation level read committed;

    Query OK, 0 rows affected (0.00 sec)

    mysql> set session transaction isolation level read committed;

    Query OK, 0 rows affected (0.00 sec)

InnoDB的可重复读隔离级别和其他数据库的可重复读是有区别的，不会造成幻象读（phantom read），所谓幻象读，就是同一个事务内，多次select，可以读取到其他session insert并已经commit的数据。下面是一个小的测试，证明InnoDB的可重复读隔离级别不会造成幻象读。测试涉及两个session，分别为session 1和session 2，隔离级别都是repeateable read，关闭autocommit

    mysql> select @@tx_isolation;
  

    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
   

    mysql> set autocommit=off;

    Query OK, 0 rows affected (0.00 sec)

session 1 创建表并插入测试数据

     mysql> create table test(i int) engine=innodb;

    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test values(1);
    Query OK, 1 row affected (0.00 sec)

session 2 查询，没有数据，正常，session1没有提交，不允许脏读
     mysql> select * from test;
     Empty set (0.00 sec)

session 1 提交事务

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

session 2 查询，还是没有数据，没有产生幻象读
                        
    mysql> select * from test;
    Empty set (0.00 sec)
                        
                        
以上试验版本：
                        
    mysql> select version();
    +-------------------------+
    | version()               |
    +-------------------------+
    | 5.0.37-community-nt-log |
    +-------------------------+
    1 row in set (0.00 sec)

 

 

 

作者： 江枫| 【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
地址： http://rdc.taobao.com/blog/dba/html/91_mysql_innodb_transaction_isolation_level.html

--------------------

什么是脏读、不可重复读、幻读  by Oneyearlater的理解

两个事务操作同时一个表table

1. 脏读 ：A  update了row 1， 但是还没有commit，这时候B 读取了row1当中的数据（select * from table where id=row1）；但是A 继续update，update到row 99 发生了错误，A发生rollback，那么B 读到的就是脏数据 -- row1 和数据库当中的不一致。

2. 不可重复读 ： B 读取 row2（select * from table where id=row2）， 然后A update了row2， B再读取 row2， 那么两次B读到数据就不一样了。

3. 幻读 :   B 对table进行计数统计（select count(*) from table），row3 属于符合条件的结果之一，然后A  delete row3，（或者insert  了一条新记录row4，row4也是符合条件的结果）； B对table进行计数统计， 那么两次B读到count就不一样了。

mysql给出的资料当中也指出了，   If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements. The results of a concurrent INSERT may not be visible immediately.   

（from http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html）

话说幻读在大部分生产环境下是可以接受的，要消除只有串行化A和B。。。

下面是别人总结的区别：
不可重复读的重点是修改 : 
同样的条件 ,   你读取过的数据 ,   再次读取出来发现值不一样了 
幻读的重点在于新增或者删除 
同样的条件 ,   第 1 次和第 2 次读出来的记录数不一样 


l          READ_UNCOMMITTED 会出现脏读、不可重复读、幻读 ( 隔离级别最低，并发性能高 )

l          READ_COMMITTED  会出现不可重复读、幻读问题（锁定正在读取的行）

l          REPEATABLE_READ 会出幻读（锁定所读取的所有行）   <== mysql 默认在此，大部分的rmdbd也会把隔离级别设置在此

l          SERIALIZABLE 保证所有的情况不会发生（锁表）