【MySQL】Innodb事务隔离级别(转)

http://orzdba.wordpress.com/2011/03/03/transaction_isolation_levels/

一、事务隔离级别

ANSI/ISO SQL标准定义了4中事务隔离级别：未提交读（read uncommitted），提交读（read committed），重复读（repeatable read），串行读（serializable）。

对于不同的事务，采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在：

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、非重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，4种事务隔离级别分别表现的现象如下表：

隔离级别	脏读	非重复读	幻像读
read uncommitted	允许	允许	允许
read committed		允许	允许
repeatable read			允许
serializable

 

InnoDB 中的隔离级详细描述：

• READ UNCOMMITTED 这通常称为 'dirty read'：non-locking SELECTs 的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离度下是非 'consistent' reads；另外，这级隔离的运作如同 READ COMMITTED。
• READ COMMITTED 有些类似 Oracle 的隔离级。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句只锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。以一个唯一地搜索条件使用一个唯一索引(unique index)的 UPDATE 和 DELETE，仅仅只锁定所找到的索引记录，而不锁定该索引之前的间隙。但是在范围型的 UPDATE and DELETE中，InnoDB 必须设置 next-key 或 gap locks 来阻塞其它用户对范围内的空隙插入。 自从为了 MySQL 进行复制(replication)与恢复(recovery)工作'phantom rows'必须被阻塞以来，这就是必须的了。Consistent reads 运作方式与 Oracle 有点类似： 每一个 consistent read，甚至是同一个事务中的，均设置并作用它自己的最新快照。
• REPEATABLE READ 这是 InnoDB 默认的事务隔离级。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。 否则这些操作将使用 next-key 锁定，以 next-key 和 gap locks 锁定找到的索引范围，并阻塞其它用户的新建插入。在 consistent reads 中，与前一个隔离级相比这是一个重要的差别： 在这一级中，同一事务中所有的 consistent reads 均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的SELECTs ，这些 SELECTs 的相互关系是一致的。
• SERIALIZABLE 这一级与上一级相似，只是无格式(plain)的 SELECTs 被隐含地转换为 SELECT ... LOCK IN SHARE MODE。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与ORACLE不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1).Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供给查询一个数据库在一个时间点的快照。

如果以默认的 REPEATABLE READ 隔离级，那么所有在同一事务中的 consistent reads 只读取同一个在事务中第一次读所确定的快照。 你可以通过提交当前事务并发出一个新的查询以获得新的数据快照。 Consistent read 对其所访问的表不加任何锁定。

(英文原文：

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED andREPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.)

 

 

 

2).Locking reads : (select ...for update与 select ...lock in share mode) 

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html 

 

 

Share mode 是共享锁(写锁)，允许事务读取最新数据，如果他被其它事务使用(edit,update)而没有提交，读取锁将被阻塞知道那个事务结束.

For update 是排他锁(读写锁)，阻止其他事务读取或者写入行数据.

 

SHARE MODE可用来实现参照完整性. 比如有 Parent 和 Child 表， 从 Parent 查出来的信息要插到子表，万一在插的时候主表数据被删了怎么办？可加 SHARE MODE:

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;
	

在共享模式下执行读取的意思就是读取最新的现有资料，并在所读取的行上设置一个共享模式的锁定。如果最新的数据属于其它用户仍未提交的事务，那将不得不等到这个事务被 提交 。共享模式的可以防止其它用户更新或删除我们当前所读取的行。当查询获得 'Jones'后，就可以安全地向子表 CHILD 中加入子行，然后提交事务。 

 

 

 

 

二、数据库中的默认事务隔离级别

在Oracle中默认的事务隔离级别是提交读（read committed）。

对于MySQL的Innodb的默认事务隔离级别是重复读（repeatable read）。可以通过下面的命令查看：

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

+———————–+—————–+

| @@GLOBAL.tx_isolation | @@tx_isolation  |

+———————–+—————–+

| REPEATABLE-READ | REPEATABLE-READ |

+———————–+—————–+

1 row in set (0.00 sec)

用户也可以通过下面的 SQL 语句为单个连接或所有新建的连接改变隔离级：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                           | REPEATABLE READ | SERIALIZABLE}

 

 

下面进行一下测试：

Time	Session 1	Session 2
T1	set autocommit=0;	set autocommit=0;
T2	mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec)
T3		mysql> update tmp_test set version=2 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec)
T4	mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec) 【说明】 Session 2未提交，看到数据不变，无脏读。
T5		commit;
T6	mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec) 【说明】 Session 2已经提交，还是看到数据不变，即可以重复读。
T7	commit;
T8	mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec) 【说明】 提交事务，看到最新数据。
T9		mysql> insert into tmp_test values(2,1); Query OK, 1 row affected (0.00 sec) mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | |    2 |       1 | +——+———+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
T10	mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec) 【说明】 Session 2的insert事务已经提交，看到的数据和T8的时候一样，即未发生幻象读。
T11	mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | |    2 |       1 | +——+———+ 2 rows in set (0.00 sec) 【说明】 事务提交，看到最新数据。

上面的结果可以看到Innodb的重复读（repeatable read）不允许脏读，不允许非重复读（即可以重复读，Innodb使用多版本一致性读来实现）和不允许幻象读（这点和ANSI/ISO SQL标准定义的有所区别）。

另外，同样的测试：

1、当session 2进行truncate表的时候，这个时候session 1再次查询就看不到数据。

2、当session 2进行alter表的时候，这个时候session 1再次查询就看不到数据。

MySQL官方文档中的多版本一致性读中说明了原因：Consistent read does not work over certain DDL statements。

关于Innodb的多版本一致性读将下一篇再来记录。