MySQL事务隔离级别

http://www.jianshu.com/p/4e3edbedb9a8

http://www.cnblogs.com/zhoujinyi/p/3437475.html

MySQL四种事务隔离级别

隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
可重复读	不可能	不可能	可能
可串行化	不可能	不可能	不可能

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

• 已提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读（MySQL默认隔离级别为可重复读）

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻

创建测试数据库，并插入数据：

mysql> desc account;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | PRI | NULL    |       |
| amount | float   | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1000 |
|  2 |   1000 |
+----+--------+
2 rows in set

我们打开2个终端模拟2个用户操作。

未提交读(Read Uncommitted)

用户A操作如下：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1000 |
|  2 |   1000 |
+----+--------+
2 rows in set

用户B操作如下（不提交事务）：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> update account set amount=amount+200 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

用户A查询数据：

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1200 |
|  2 |   1000 |
+----+--------+
2 rows in set

我们将事务隔离级别设置为Read Uncommitted的时候，即使事务并没有commit，但是我们仍然能够读到未提交的数据。所以这是所有隔离级别中最低的。

我们在一个事务中可以读取到其他事务未提交的数据，这种我们称之为脏读（Dirty Read）。因为事务并未提交，所以数据库中的数据并未发生变化。

已提交读(Read Committed)

用户B将隔离级别设置为Read Committed：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1000 |
|  2 |   1000 |
+----+--------+
2 rows in set

用户A执行更新操作，先不commit：

mysql> start transaction;
Query OK, 0 rows affected

mysql> update account set amount=amount-200 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

用户B查询数据库：

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1000 |
|  2 |   1000 |
+----+--------+
2 rows in set

用户A执行commit操作：

mysql> commit;
Query OK, 0 rows affected

用户B再次查询数据库：

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
+----+--------+
2 rows in set

我们将事务隔离级别设置为Read Committed的时候，当前事务只能读取到其他事务提交的数据，未提交的数据读取不到。

用户B在同一事务中2次读取的结果不同，我们称之为不可重复读（NonRepeatable Read）。

可重复读(Repeated Read)

用户B将隔离级别设置为Repeated Read：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
+----+--------+
2 rows in set

用户A插入数据：

mysql> insert into account(id, amount) values(3, 1000);
Query OK, 1 row affected

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
|  3 |   1000 |
+----+--------+
3 rows in set

用户B再次查询，但是仍然得到是2条数据：

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
+----+--------+
2 rows in set

当我们执行插入操作时，报错：

mysql> insert into account(id, amount) values(3, 1000);
1062 - Duplicate entry '3' for key 'PRIMARY'
mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
+----+--------+
2 rows in set

我们将事务隔离级别设置为Repeated Read的时候，不论其他事务有没有提交，每次读取的结果集都相同。

数据已经发生变化，但是读取的结果集还是保持一致，出现上面的现象，我们称之为幻读（Phantom Read） 。

串行读(Serializable)

用户B将隔离级别设置为串行读(Serializable)：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+--------+
| id | amount |
+----+--------+
|  1 |    800 |
|  2 |   1000 |
+----+--------+
2 rows in set

用户A插入数据，则会出现等待，直到出现超时报错：

mysql> insert into account(id, amount) values (3, 1000);
1205 - Lock wait timeout exceeded; try restarting transaction

如果在A等待的过程中，B提交了事务，则A会执行成功。

我们将事务隔离级别设置为Serializable的时候，事务将会挂起对表的写操作，这是隔离级别中最严的隔离级别，但是也会对性能造成影响。

在实际的情况中，我们需要根据具体的操作情况，选择隔离级别。