探究Mysql的“可串行化”隔离级别

一直对最高级别的可串行化这种隔离级别不太理解，今天来做几个实验探究下

隔离级别

首先Mysql的隔离级别分为四种：

1. 未提交读 READ-UNCOMMITTED
2. 提交读 READ-COMMITTED
3. 可重复读 REPEATABLE-READ
4. 可串行化 SERIALIZABLE

其中可串行化是最高的隔离级别，可以避免丢失修改、脏读、不可重复读、幻读。

在实验开始前，我们首先了解下Mysql隔离级别的相关操作。

• 设置当前session会话的隔离级别为未提交读：

set session transaction isolation level READ UNCOMMITTED;

• 设置当前session会话的隔离级别为提交读：

set session transaction isolation level READ-COMMITTED;

• 设置当前session会话的隔离级别为可重复读：

set session transaction isolation level REPEATABLE READ;

• 设置当前session会话的隔离级别为可串行化：

set session transaction isolation level SERIALIZABLE;

• 查看隔离级别：

show variables like '%isolation%';

隔离级别为可重复读的对照实验

首先用可重复读隔离级别做一个对照实验，如下两个事务，左边事务先开始，并执行select语句，随后右边事务再开始，并尝试进行update操作：

在上图中，左边事务先开始并执行了select查询，随后右边的事务开始并执行update语句，该update语句成功执行。

这是因为：在可重复读隔离级别下，普通的select语句使用快照读，不上锁；因此右边事务的update语句执行时可以上排他锁并成功执行。

我们再到可串行化隔离级别做相同实验试试。

隔离级别为可串行化的相同实验

我们改用可串行化隔离级别做相同的实验，如下两个事务，左边事务先开始，并执行select语句，随后右边事务再开始，并尝试进行update操作：

在上图中，左边事务先开始并执行了select查询，随后右边的事务开始并执行update语句，结果update语句一直阻塞，过了一阵子之后报错“等待锁超时”。

显然结果与前面使用可重复读隔离级别时不同，这是因为：在可串行化隔离级别下，即使普通的select查询也会上共享锁，进行当前读，因此右边事务进行update操作时想要获取排它锁需要一直等待，最终超时。

深入探索

为什么select上的是共享锁，我们继续在可串行化隔离级别进行如下实验：先让左边事务执行select查询，随后再让右边事务执行相同的select查询，如果都能正常执行，说明上的是共享锁。

结果如下：

左边的事务先开始执行select，右边的事务后开始查询，而且二者都成功查询到结果，这说明可串行化隔离级别下，普通的select上的是共享锁。

接下来进一步探索可串行化隔离级别下上的锁是否next-key lock（即record lock+gap lock），如下所示，该表的索引为empno列，先让左边事务查询empno小于7698的数据，随后在右边事务试图更新empno=7698、empno>7698、empno<7698的数据，结果如下：

可见empno小于和等于7698的数据查询都发生了“等待锁超时”，显然在可串行化隔离级别中使用范围查询不仅会锁住索引、而且锁住了索引之间的间隙，即使用了next-key lock （ record lock + gap lock）。

总结

1. 在“可串行化”隔离级别下普通的select语句也会上共享锁，进行当前读。
2. 在“可串行化”隔离级别中使用范围查询会上next-key lock。
3. 显然使用当前读和next-key lock可以帮助“可串行化”隔离级别避免幻读现象