事务中的四种隔离级别

事务的四种隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。我们的数据库锁，也是为了构建这些隔离级别存在的。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

1.查看InnoDB存储引擎 会话级的隔离级别 和 系统级的隔离级别 

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

按照SQL:1992 事务隔离级别，InnoDB默认是可重复读的（REPEATABLE-READ）

如果选择global，意思是此语句将应用于之后的所有session，而当前已经存在的session不受影响。

如果选择session，意思是此语句将应用于当前session内之后的所有事务。

如果什么都不写，意思是此语句将应用于当前session内的下一个还未开始的事务。

2.

① 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

 

a.更改隔离级别

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

mysql> set global tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation,@@global.tx_isolation;
+------------------+-----------------------+
| @@tx_isolation   | @@global.tx_isolation |
+------------------+-----------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED      |
+------------------+-----------------------+
1 row in set (0.00 sec)

b.演示脏读

开启一个事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from config;
+----+--------------------+--------+
| id | name               | value  |
+----+--------------------+--------+
|  1 | 支付时间           | 30     |
|  2 | 最少提现           | 50     |
|  3 | 佣金               | 0      |
|  4 | 合伙人佣金         | 0.0075 |
|  5 | 抽到商品次数       | 6      |
|  6 | 拼团超时           | 1      |
|  7 | 上市时间           | 9      |
|  8 | 下市时间           | 23     |
|  9 | 上市时间           | 9      |
| 10 | 下市时间           | 23     |
| 11 | 测试               | 666    |
+----+--------------------+--------+
11 rows in set (0.00 sec)

开启另一个事务，写一个插入语句，但未提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into config values (15,'测试1','777');
Query OK, 1 row affected (0.00 sec)

在第一个事务中再次查询，查到了第二个事务尚未提交的数据

mysql> select * from config;
+----+--------------------+--------+
| id | name               | value  |
+----+--------------------+--------+
|  1 | 支付时间           | 30     |
|  2 | 最少提现           | 50     |
|  3 | 佣金               | 0      |
|  4 | 合伙人佣金         | 0.0075 |
|  5 | 抽到商品次数       | 6      |
|  6 | 拼团超时           | 1      |
|  7 | 上市时间           | 9      |
|  8 | 下市时间           | 23     |
|  9 | 上市时间           | 9      |
| 10 | 下市时间           | 23     |
| 11 | 测试               | 666    |
| 15 | 测试1              | 777    |
+----+--------------------+--------+
12 rows in set (0.00 sec)

第二个事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

第一个事务再次查询，数据又变回来了

mysql> select * from config;
+----+--------------------+--------+
| id | name               | value  |
+----+--------------------+--------+
|  1 | 支付时间           | 30     |
|  2 | 最少提现           | 50     |
|  3 | 佣金               | 0      |
|  4 | 合伙人佣金         | 0.0075 |
|  5 | 抽到商品次数       | 6      |
|  6 | 拼团超时           | 1      |
|  7 | 上市时间           | 9      |
|  8 | 下市时间           | 23     |
|  9 | 上市时间           | 9      |
| 10 | 下市时间           | 23     |
| 11 | 测试               | 666    |
+----+--------------------+--------+
11 rows in set (0.00 sec)

② 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

a.演示不可重复读

上面的演示中其实已经发生了不可重复读，即在第二个事务尚未回滚的时候，第一个事务的两次查询结果不相同。为不可重复读。

③ 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

a.演示幻读

更改隔离级别为可重复读

mysql> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

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

开启一个事务，查询

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from config;
+----+--------------------+--------+
| id | name               | value  |
+----+--------------------+--------+
|  1 | 支付时间           | 30     |
|  2 | 最少提现           | 50     |
|  3 | 佣金               | 0      |
|  4 | 合伙人佣金         | 0.0075 |
|  5 | 抽到商品次数       | 6      |
|  6 | 拼团超时           | 1      |
|  7 | 上市时间           | 9      |
|  8 | 下市时间           | 23     |
|  9 | 上市时间           | 9      |
| 10 | 下市时间           | 23     |
| 11 | 测试               | 666    |
+----+--------------------+--------+
11 rows in set (0.00 sec)

开启第二个事务，插入语句，并提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into config values (15,'测试1','777');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

第一个事务，再次查询，并且插入一条id为15的语句

mysql> select * from config;
+----+--------------------+--------+
| id | name               | value  |
+----+--------------------+--------+
|  1 | 支付时间           | 30     |
|  2 | 最少提现           | 50     |
|  3 | 佣金               | 0      |
|  4 | 合伙人佣金         | 0.0075 |
|  5 | 抽到商品次数       | 6      |
|  6 | 拼团超时           | 1      |
|  7 | 上市时间           | 9      |
|  8 | 下市时间           | 23     |
|  9 | 上市时间           | 9      |
| 10 | 下市时间           | 23     |
| 11 | 测试               | 666    |
+----+--------------------+--------+
11 rows in set (0.00 sec)

mysql> insert into config value (15,'测试2',888);
ERROR 1062 (23000): Duplicate entry '15' for key 'PRIMARY'

可见报出了id重复的错误，但查询结果中没有id为15的数据，就像出现了幻觉一样

注：在测试中要注意隔离级别的更改，第一次更改为 未提交读（Read uncommitted），所以同时出现了脏读和不可重复读，当然也可以出现幻读的现象。第二次更改为 可重复读（Repeatable read），是为了去掉脏读和不可重复读的影响。

本文主要参考了http://blog.youkuaiyun.com/taylor_tao/article/details/7063639