Mysql的事务隔离级别

---

前言

什么是隔离性？

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性，是指不同事务的内部操作是隔离的，并发执行时各个事务之间不互相干扰。
严格的隔离性，对应了事务隔离级别中的Serializable（序列化），但实际应用中出于性能方面的考虑很少使用序列化。

---

一、事务隔离级别

SQL标准中定义了4种隔离级别：读未提交、读已提交、可重复读和序列化。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。
隔离级别与读取问题的关系如下：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	可能	可能	可能
Read Committed	不可能	可能	可能
Repeatable Read	不可能	不可能	可能
Serializable	不可能	不可能	不可能

二、默认隔离级别

在大多数数据库系统中，默认使用的隔离级别是读已提交（如Oracle）或可重复读（如Mysql和InnoDB）。需要注意的是，在SQL标准中，可重复读不能避免幻读问题，但是InnoDB的可重复读避免了幻读的问题（它是怎么做到呢？）

三、使用演示

3.1 读未提交

1.事务1设置事务隔离级别为读未提交，开启事务后查询users表id=1的数据。

>mysql set session transaction isolation level read uncommitted;
>mysql start transaction;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

2.事务2开启事务，修改users表id=1的数据但未提交。

>mysql start transaction;
>mysql update users set cname='刘二' where id=1;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘二  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.事务1再次查询users表id=1的数据，看到未提交的数据即出现“脏读”问题。

>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘二  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.2 读已提交

1.事务1设置事务隔离级别为读已提交，开启事务后查询users表id=1的数据。

>mysql set session transaction isolation level read committed;
>mysql start transaction;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

2.事务2开启事务，修改users表id=1的数据但未提交。

>mysql start transaction;
>mysql update users set cname='刘二' where id=1;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘二  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.事务1再次查询users表id=1的数据，仍看到旧的数据即不再出现脏读。

>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.事务2提交后，事务1再次查询users表id=1的数据，看到已提交的数据即出现“不可重复读”问题。

>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘二  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.3 可重复读

1.事务1设置事务隔离级别为可重复读，开启事务后查询users表id=1的数据。

>mysql set session transaction isolation level repeatable read;
>mysql start transaction;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

2.事务2开启事务，修改users表id=1的数据且提交。

>mysql start transaction;
>mysql update users set cname='刘二' where id=1;
>mysql commit;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘二  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.事务1再次查询users表id=1的数据，看到旧的数据即出现“幻读”问题。
在可重复读设置下，数据库系统直接返回上次查询的结果。幻读：读取到的数据，与数据库真实的数据不一致（被其他修改了）

>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

3.4 序列化

1.事务1设置事务隔离级别为序列化，开启事务后查询users表id=1的数据。

>mysql set session transaction isolation level serializable;
>mysql start transaction;
>mysql select * from users where id=1;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  1 | 刘一  | 666      |
+----+-------+----------+
1 row in set (0.00 sec)

2.事务2开启事务，去修改users表id=1的数据，等待数秒后获取锁超时而修改失败。
–因为事务1对users表id=1的数据加了“行级锁”且未释放，则事务2不能对它修改。

>mysql start transaction;
>mysql update users set cname='刘二' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.事务2去修改users表id=2的数据，修改成功。
–因为事务1只对users表id=1的数据加了“行级锁”而不是全表，所以id=2可以被其他事务修改。

>mysql update users set cname='王wu' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

>mysql select * from users where id=2;
+----+-------+----------+
| id | cname | password |
+----+-------+----------+
|  2 | 王wu  | 111      |
+----+-------+----------+
1 row in set (0.00 sec)

---

总结

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。
尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用乐观锁或悲观锁来控制。(什么是乐观锁和悲观锁，可以下一篇日志继续，哈哈~)