Innodb事务隔离级别

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

一、事务隔离级别

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 UPDATESELECT ... LOCK IN SHARE MODEUPDATE, 和 DELETE ,这些以唯一条件搜索唯一索引的,只锁定所找到的索引记录,而不锁定该索引之前的间隙。 否则这些操作将使用 next-key 锁定,以 next-key 和 gap locks 锁定找到的索引范围,并阻塞其它用户的新建插入。在 consistent reads 中,与前一个隔离级相比这是一个重要的差别: 在这一级中,同一事务中所有的 consistent reads 均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的SELECTs ,这些 SELECTs 的相互关系是一致的。
  • SERIALIZABLE 这一级与上一级相似,只是无格式(plain)的 SELECTs 被隐含地转换为 SELECT ... LOCK IN SHARE MODE
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQLORACLE不同,后者是通过在数据块中对相应数据行加锁来实现的。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的多版本一致性读将下一篇再来记录。


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页