MySQL隔离级别

原文地址：http://blog.csdn.net/taylor_tao/article/details/7063639



事务具有ACID四种特性。

但是Isolation并发可能引起如下问题：

1.脏读

允许读取到未提交的脏数据。

2.不可重复读

如果你在时间点T1读取了一些记录，在T2时再想重新读取一次同样的这些记录时，这些记录可能已经被改变、或者消失不见。

3.幻读

解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

由ANSI/ISO定义的SQL-92标准定义的四种隔离级别

1.Read Uncommitted

2.Read Committed

3.Repeatable Read

4.Serializable


隔离解别 脏读 不可重复读 幻读
Read Uncommitted Y Y Y
Read Committed N Y Y
Repeatable(default) N N Y
Serializable N N N
下面用Mysql数据库做一些小实验

Mysql 版本号

[sql] view plaincopy
mysql> select version(); 
+------------+ 
| version()  | 
+------------+ 
| 5.1.52-log | 
+------------+ 
1 row in set (0.00 sec) 
查看InnoDB存储引擎 系统级的隔离级别 和 会话级的隔离级别

[sql] view plaincopy
mysql> select @@global.tx_isolation,@@tx_isolation; 
+-----------------------+-----------------+ 
| @@global.tx_isolation | @@tx_isolation  | 
+-----------------------+-----------------+ 
| REPEATABLE-READ       | REPEATABLE-READ | 
+-----------------------+-----------------+ 
1 row in set (0.00 sec) 

更改会话级的隔离级别
[sql] view plaincopy
Session 1: 
mysql> set session tx_isolation='read-uncommitted'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> select @@global.tx_isolation,@@tx_isolation; 
+-----------------------+------------------+ 
| @@global.tx_isolation | @@tx_isolation   | 
+-----------------------+------------------+ 
| REPEATABLE-READ       | READ-UNCOMMITTED | 
+-----------------------+------------------+ 
1 row in set (0.00 sec) 
 
 
Session 2: 
mysql> select @@global.tx_isolation, @@tx_isolation; 
+-----------------------+-----------------+ 
| @@global.tx_isolation | @@tx_isolation  | 
+-----------------------+-----------------+ 
| REPEATABLE-READ       | REPEATABLE-READ | 
+-----------------------+-----------------+ 
1 row in set (0.00 sec) 

更改系统级的隔离级别
[sql] view plaincopy
Session 1: 
mysql> set global tx_isolation='read-uncommitted'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> select @@global.tx_isolation,@@tx_isolation; 
+-----------------------+------------------+ 
| @@global.tx_isolation | @@tx_isolation   | 
+-----------------------+------------------+ 
| READ-UNCOMMITTED      | READ-UNCOMMITTED | 
+-----------------------+------------------+ 
1 row in set (0.00 sec) 
 
Session 2: 
mysql> select @@global.tx_isolation, @@tx_isolation; 
+-----------------------+-----------------+ 
| @@global.tx_isolation | @@tx_isolation  | 
+-----------------------+-----------------+ 
| READ-UNCOMMITTED      | REPEATABLE-READ | 
+-----------------------+-----------------+ 
1 row in set (0.00 sec) 

关闭SQL语句的自动提交

[sql] view plaincopy
mysql> set autocommit=off; 
Query OK, 0 rows affected (0.00 sec) 

查看SQL语句自动提交是否关闭

[plain] view plaincopy
mysql> show variables like 'autocommit'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| autocommit    | OFF   | 
+---------------+-------+ 
1 row in set (0.00 sec) 

建立实验表
[sql] view plaincopy
mysql> create table tao (col1 tinyint unsigned, col2 varchar(20), primary key(col1)); 
Query OK, 0 rows affected (0.08 sec) 
 
mysql> show create table tao \G; 
*************************** 1. row *************************** 
       Table: tao 
Create Table: CREATE TABLE `tao` ( 
  `col1` tinyint(3) unsigned NOT NULL DEFAULT '0', 
  `col2` varchar(20) DEFAULT NULL, 
  PRIMARY KEY (`col1`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

演示脏读


更改隔离级别为Read Committed后，不存在脏读的问题。

[sql] view plaincopy
mysql> set global tx_isolation='read-committed'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> set session tx_isolation='read-committed'; 
Query OK, 0 rows affected (0.00 sec) 

演示不可重复读


更改隔离级别为Repeatable Read后，不存在不可重复读的问题。

[sql] view plaincopy
mysql> set global tx_isolation='repeatable-read'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> set session tx_isolation='repeatable-read'; 
Query OK, 0 rows affected (0.00 sec) 

幻读


更改隔离级别为完全串行化 Serializable 后，不存在幻读的问题。

[sql] view plaincopy
mysql> set global tx_isolation='serializable'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> set session tx_isolation='serializable'; 
Query OK, 0 rows affected (0.00 sec) 

在这种情况下，只允许一个事务在执行，其它事务必须等待这个事务执行完后才能执行。没有并发，只是单纯的串行。