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;

结果：

+-----------------------+
| @@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)