MySQL数据库事务的隔离级别介绍


在大学学习数据库的时候就接触到了事务,但在现在的工作中也没有使用过,因为平台不支持,在需要保证数据一致性的时候就要自己做,比如有两条delete语句,当第一条执行成功第二条失败的时候,就要把第一条删除的记录再添加上,非常麻烦。也致使自己对事务没有真正理解和掌握。

使用事务是保证数据的正确性,N条sql语句组成一个事务,这N条语句都成功则成功,若有一条失败则失败,需要回滚到执行前的状态。

事务的ACDI特性:

A:Automicity,原子性

C:Consistency,一致性

I:Isolation,隔离性

D:Durability,持续性

MySQL数据库的不同存储引擎对事务的支持不同,MyISAM存储引擎不支持事务,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种事务隔离级别分别表现的现象如下表:

image

 

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

在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)

下面进行一下测试:

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

阅读更多
换一批

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