MYSQL事务隔离

MySQL主要有4种类型的事务隔离级别，分别为：

read uncommitted 【读取未提交内容】

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

read committed  【读取提交内容】

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

repeatable read  【可重读】

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

serializable  【可串行化】

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read):

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):

在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

接下来我们使用MySQL客户端来测试MySQL事务的不同隔离级别：

1、read uncommitted【读取未提交内容】

首先设置客户端1、客户端2当前session的隔离模式都为read uncommitted、注意有2个客户端都要设置,使用2个客户端的目的主要是为了模拟并发的情况

客户端1：

mysql1> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql1> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

客户端2：

mysql2> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql2> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

接着在客户端1查询下表数据，并且开始事务，更新数据：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
+----+------+------+
2 rows in set (0.01 sec)

开始事务：

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

更新数据：

mysql1> update student set name = 'tom' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

客户端1事务开始操作完，并没有提交，这时候我们用客户端2模拟2个事务并发的情况。

客户端2首先开始事务，然后查询数据

开始事务：

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

查询数据：

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | tom  |   18 |
+----+------+------+
2 rows in set (0.00 sec)

这时候我们看到，虽然客户端1的事务并没有commit提交，但是客户端2的事务已经查到了客户端1事务待提交的数据，但是如果此时客户端1rollback，

客户端1，事务回滚

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

客户端2，再查询数据：

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
+----+------+------+
2 rows in set (0.00 sec)

此时，客户端2里的事务就出现了所谓的脏读。

现在我们在客户端2里面，更新数据，更新成功

mysql2> update student set name = 'bob' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

客户端1，也更新数据，发现更新超时，也就是说，在一个事务A中更新数据，在未提交或者未回滚的这段时间里，这条数据将被锁定，这时候其他连接的更

新操作会被锁定，一直等待到超时或者等待事务A被提交或者被回滚才会进行更新操作。

mysql1> update student set name = 'test' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 2.read committed 【读取提交内容】

首先设置客户端1、客户端2的事务隔离级别

客户端1：

mysql1> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql1> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

客户端2：

mysql2> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql2> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

接着我们先查询下客户端1的数据

客户端1：查询数据

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
+----+------+------+
2 rows in set (0.00 sec)

客户端1：开始事务：

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

客户端1：插入一条数据

mysql1> insert into student values(3, 'wmq', 20);
Query OK, 1 row affected (0.00 sec)

 数据插入成功，这时候我们转到客户端2:

开始事务，查询数据

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
+----+------+------+
2 rows in set (0.00 sec)

这时候我们发现，客户端2并没有查询到客户端1中事务插入的数据

我们再转到客户端1:

执行commit提交

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

客户端2再进行查询：

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

数据出来了，这就是所谓的读取提交的内容，也就是在事务A内必须要在事务B提交之后才能出现。

3. repeatable read  【可重读】

先设置客户端1，客户端2的事务隔离级别：

mysql1> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql1> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

客户端2：

mysql2> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql2> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

客户端1、客户端2查询数据，并且开始事务：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

接着我们在客户端2

更新数据：

mysql2> update student set name = 'mary' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

然后再客户端1

查询：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

这时候我们发现，并没有引起脏读的问题。

接着我们在客户端2

插入数据：

mysql2> insert into student values(4,'lucy',18);
Query OK, 1 row affected (0.00 sec)

然后到客户端1继续查询：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

发现也没有出现脏读。

把客户端2的事务提交：

mysql2> commit;
Query OK, 0 rows affected (0.01 sec)

接着再在客户端1查询：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | wmq  |   20 |
+----+------+------+
3 rows in set (0.00 sec)

依然没有数据，由此我们得出结论，在repeatable read的这个隔离级别中，同一个事务，不会出现脏读，和不可重复读的情况。

接着我们rollback客户端2再查询

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

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | mary |   20 |
|  4 | lucy |   18 |
+----+------+------+
4 rows in set (0.00 sec)

出来了，在repeatable read级别中，事务之间是相互独立的。

4. serializable  【可串行化】

 首先也是设置客户端1、客户端2的隔离级别

客户端1：

mysql1> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql1> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

 客户端2：

mysql2> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql2> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

首先我们在客户端2开始事务，并且执行更新操作

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

mysql2> update student set name = 'lucy' where id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

然后客户端1也开始事务，执行查询

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

mysql1> select * from student;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这时候发现客户端1的事务查询失败，说明在客户端2事务在开始更新的时候，记录已经被锁定，这导致客户端1里的事务1查询失败，一直需要等待解锁。

接着我们把客户端2的事务提交，查询数据

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

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

mysql2> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | mary |   20 |
|  4 | lucy |   18 |
+----+------+------+
4 rows in set (0.00 sec)

然后在客户端1的事务进行查询：

mysql1> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kate |   18 |
|  2 | jim  |   18 |
|  3 | mary |   20 |
|  4 | lucy |   18 |
+----+------+------+
4 rows in set (0.00 sec)

发现不影响，说明在读取数据相互都不影响。

接着我们继续在客户端2进行数据更新：

mysql2>  update student set name = 'wmq' where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

发现更新失败，说明客户端1的事务已经对数据加了读锁，导致客户端2更新失败。