Mysql 四种事务隔离级别
一、前提
时过一年重新拾起博文记录，希望后面都能坚持下来。 接着之前MySql的学习，先记录下这篇。
以下都是基于mysql8 innodb存储引擎进行分析的。
二、事务的ACID特性
-
A(Atomicity) 原子性
指整个数据库事务是不可分割的单位，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。只有使事务中所有的数据库操作都执行成功，才算整个事务执行成功。否则事务中任何一个SQL语句执行失败，那么这个事务就是执行失败的， 已执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
-
C(consistency) 一致性
一致性事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
例如：在表中有个字段为姓名为唯一约束，即在表中姓名不能重复。如果一个事务对姓名字段进行了修改，但是在事务提交或事务操作发生回滚后，表中的姓名变得非唯一了，这就破坏了事务的一致性要求，即事务将数据从一种状态变为了一种不一致的状态。
-
I(isolation) 隔离性
事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。通常可以使用锁来实现。
-
D(durability) 持久性
事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。
需要注意的是：只能从事务本身的角度来保证结果的永久性，例如：在事务提交后，所有变化都是永久的。即使当数据因为崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。但如果不是数据库本身发生故障，而是一些外部的原因导致数据库发生问题，则有可能是提交的数据丢失(RAID卡损坏)。
因此持久性保证事务系统的高可靠性，而不是高可用性。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合完成。
三、事务的4种隔离级别
-
Read Uncommitted - 未提交读
在该隔离级别下的事务会读取到其未提交事务的数据，此种现象也称之为脏读。
| 步骤 | 事务1 | 事务2 |
| 1 |
设置隔离级别  mysql> set @@session.transaction_isolation |
|
| 2 |
开启事务1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; Empty set (0.00 sec) |
|
| 3 |
无需管隔离级别，只开启事务2并插入记录
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 1, '1'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
| 4 |
此时能查到事务2中未提交事务中的数据，这就是脏读。 mysql> select * from t; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
注意：设置隔离级别之前，记得查看当前隔离级别的key, 有可能版本不一样对应的key不一样。（另外表可以自己随意选择）
1 mysql> show variables like '%isolation%'; 2 +-----------------------+------------------+ 3 | Variable_name | Value | 4 +-----------------------+------------------+ 5 | transaction_isolation | READ-UNCOMMITTED | 6 +-----------------------+------------------+ 7 1 row in set (0.00 sec)
在实际的业务场景中应该都不允许脏读出现，既然这么评价不好为什么还会出现呢？ 但其实这个隔离级别下的数据库并发性能是最好的。
-
Read Committed - 提交读
一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果。这种现象也被称为不可重复读。举例说明：
| 步骤 | 事务1 | 事务2 |
| 1 | 先设置隔离级别
mysql> set @@session.transaction_isolation |
|
| 2 | 开启事务1，查询t表记录为空
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; Empty set (0.00 sec) |
|
| 3 |
无需管隔离级别，只开启事务2并插入记录 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 2, '2'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
| 4 |
继续查询表t依然显示为空 (没有读取到事务2未提交的数据，所以不存在脏读问题) mysql> select * from t; Empty set (0.00 sec) |
|
| 5 |
提交事务2 mysql> commit; |
|
| 6 |
继续查询表t，此时能查询事务2已提交的数据记录（出现前后查询不一致） mysql> select * from t; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ |
总结：在隔离级别中解决了脏读问题，但存在不可重复读的问题（事务中会读取其他已提交事务中的数据）
-
Repeatable Read - 可重复读
该隔离级别是Innodb默认的隔离级别：在同一个事务里select的结果是事务开始时间时间点的状态，解决了不可重复读问题。
但其中会出现幻读现象：基于可重复读的基础上查询结果是一样的，但是当对某些行进行更新或者插入时却会受到影响操作不了，就形成了幻读。例如：
| 步骤 | 事务1 | 事务2 |
| 1 |
设置隔离级别 mysql> set @@session.transaction_isolation |
|
| 2 |
开启事务1，并查询 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+ |
|
| 3 |
无需管隔离级别，只开启事务2并插入记录 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 4, '4'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
| 4 | 继续查询表t依然只有一条记录id=3
(没有读取到事务2未提交的数据，所以不存在脏读问题) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+ |
|
| 5 |
将第二个窗口中的事务提交。
mysql> commit; |
|
| 6 | 继续查询表t依然只有一条记录id=3
(没有读取到事务2已提交的数据，所以不存在不可重复读问题) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+ |
|
| 7 |
接着插入一条4的记录，但提示插入不了，提示主键冲突问题。
然而查询却没有这条id=4记录。 这就是幻读现象。
mysql> insert into t select 4, '4'; ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' mysql> select * from t; +----+------+ | id | name | +----+------+ | 3 | 3 | +----+------+ 1 row in set (0.00 sec) |
|
| 8 | 另一种幻读现象：接着上面操作，不插入记录只更新记录，将name 更新成 '5'后，发现有两行受影响 | |
mysql> update t set name = '5'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from t; +----+------+ | id | name | +----+------+ | 3 | 5 | | 4 | 5 | +----+------+ |
||
总结：在隔离级别中解决了脏读问题、不可重复读的问题，但会存在幻读问题。
但Innodb引擎提供了间隙锁:innodb-next-key-locks, 解决了幻读问题。基于上面结果演示下：
| 步骤 | 事务1 | 事务2 |
| 9 |
查询时加上间隙锁 mysql> begin; mysql> select * from t where id > 0 for update; +----+------+ | id | name | +----+------+ | 3 | 5 | | 4 | 5 | +----+------+ |
|
| 10 |
插入记录为6的数据就会发现插入这条记录获取锁超时，自动异常 insert into t select 6, '6'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
这样成功的避免了幻读问题，阻止了其他事务可能影响到我当前事务所涉及到的数据范围。
-
Serializable - 可串行化
在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。
四、总结
1、脏读：在一个事务中会读取到其未提交事务的数据，此种现象也称之为脏读
2、不可重复读：一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果。这种现象也被称为不可重复读
3、幻读：基于可重复读的基础上查询结果是一样的，但是当对某些行进行更新或者插入时却会受到影响操作不了，就形成了幻读。
|
隔离级别
|
脏读
|
不可重复读
|
幻读
|
|
读未提交(uncommitted read)
|
可能出现
|
可能出现
|
可能出现
|
|
读提交(committed read)
|
不会出现
|
可能出现
|
可能出现
|
|
可重复读(Repeatable Read)
|
不会出现
|
不会出现
|
可能出现（加上间隙锁就不会）
|
|
可串行化(Serializable)
|
不会出现
|
不会出现
|
不会出现
|
五、参考文献
《MySql 技术内幕（Innodb）第二版》
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html