MySQL事物的隔离级别

事物

原子性、一致性、隔离性(未提交读、已提交读、可重复读、可串行化)、持久性

隔离级别

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）

1) 未提交读(READ UNCOMMITTED)
    另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）(隔离级别最低，并发性能高)

2) 提交读(READ COMMITTED)
    本事务读取到的是最新的数据(其他事务提交后的)。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果(不重复读)
    会出现不可重复读、幻读问题(锁定正在读取的行)

3) 可重复读(REPEATABLE READ)
    在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的
    但是，会有幻读现象(稍后解释)。会出幻读(锁定所读取的所有行)

4) 串行化(SERIALIZABLE)
    读操作会隐式获取共享锁，可以保证不同事务间的互斥(锁表)

四个级别逐渐增强，每个级别解决一个问题。
1) 脏读。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据
2) 不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，那么本事务先后两次读到的数据结果会不一致
3) 幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务看不到另一个事物提交的数据。

MyISAM: 不支持事物，表级锁
InnoDB: 事物级存储引擎，支持行锁，事物ACID特性

隔离级别案例

查看数据库隔离级别

查看数据库隔离级别： select @@tx_isolation;
设置隔离级别：

set session transaction isolation level READ UNCOMMITTED

set session transaction isolation level READ COMMITTED

set session transaction isolation level REPEATABLE READ

set session transaction isolation level SERIALIZABLE;

1 未提交读(READ UNCOMMITTED)
1.1 开启两个事物，分别为T1、T2并设置事物隔离级别为READ UNCOMMITTED

mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

1.2 在事物T1中查询所有用户信息

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 18   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

1.3 在事物T2中将 name 为 'zhangsan' 的 age 修改为20，执行update语句后不提交事物

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

mysql> update user set age = 20 where name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 20   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

1.4 在事物T1中查看所有用户信息

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 20   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

从结果中我们可以看到姓名为 zhangsan 的 age 已经变成20了(此时T2中的事物并没有提交)

2 提交读(READ COMMITTED)
2.1 开启两个事物，分别为T1、T2并设置事物隔离级别为READ COMMITTED

mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

2.2 在事物T1中查询所有用户信息

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 20   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

2.3 在事物T2中将 name 为'zhangsan'的 age 修改为16，执行update语句后提交事物

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

mysql> update user set age = 16 where name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 16   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

2.4 在事物T1中查看所有用户信息

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 16   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

从2.1中的结果和2.4中的结果可以看出，在T1事物中的两次select所得的结果不一致

 

3 可重复读(REPEATABLEREAD)
3.1  开启两个事物，分别为T1、T2并设置事物隔离级别为REPEATABLE READ

mysql> set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

3.2 在事物T1中查询所有用户信息

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 20   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

3.3 在事物T2中将 name 为 'zhangsan' 的年龄修改为18，执行update语句后提交事物

mysql> update user set age = 18 where name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 18   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

3.4 在T1中查看所用用户信息

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 20   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

从上面我们可以发现 zhangsan 的年龄仍然为20，commit T1事物再查看所有用户信息

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 18   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

4 串行化(SERIALIZABLE)
4.1 开启两个事物，分别为T1、T2并设置事物隔离级别为SERIALIZABLE

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

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

4.2 在事物T1中查询所有用户信息

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 16   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

4.3 在事物T2中将 name 为 'zhangsan' 的年龄修改为18

mysql> update user set age = 18 where name = 'zhangsan';

由于T1还在事物中，所以此时的update不能执行4.4 提交T1中的事物

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

mysql> select * from user;
+----+----------+-------+------+
| id | name     | money | age  |
+----+----------+-------+------+
|  1 | zhangsan |  1000 | 16   |
|  2 | lisi     |  1000 | 21   |
+----+----------+-------+------+
2 rows in set (0.00 sec)

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

4.5 此时可以看到T2中的update语句才会执行

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

mysql> update user set age = 18 where name = 'zhangsan';
Query OK, 1 row affected (3.77 sec)
Rows matched: 1  Changed: 1  Warnings: 0