MySQL 事务隔离性

        我们知道 MySQL 是一个 客户端/服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。而事务有一个称之为 隔离性 的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，我们即想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，鱼和熊掌不可兼得，舍一部分隔离性而取性能者也。

一、事务并发存在的问题

        事务并发执行会存在什么问题呢？换句话说，就是一个事务是怎么干扰到其他事务的呢？

        为了故事的顺利发展，我们得先创建一张表：        

CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(100) DEFAULT NULL COMMENT '客户名称',
  `balance` int DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

表中有数据：

id	name	balance
1	刘备	100
2	关羽	100
3	张飞	100

（1）脏读

        如果一个事务读到了另一个未提交的事务修改过的数据，那就意味着发生了脏读。

        假设现在有两个事务A、B：

        * 会话A和会话B各开始一个事务A和事务B

        * 事务B先执行将（id=2，name=关羽，balance=100）这条记录的余额扣减20，并未提交事务

        * 事务A这时候去查询（id=2）的记录，读到balance列的值为80。

        * 而事务B回滚了事务，那么事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据，这就是脏读。

时间编号	事务A	事务B
1	begin;
2		begin;
3		update account set balance = balance - 20 where name = '关羽';
4	select balance from account where name = '关羽'; （此时读到balance列的值为80，意味着发生了脏读）
5	commit;
6		rollback;

（2）不可重复读

        如果一个事务可以读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读。

        假设现在有两个事务A、B：

        * 会话A和会话B各开始一个事务A和事务B

        * 事务A先查询（id=1）的记录，读到balance列的值为100

        * 事务B对（id=1，name=刘备，balance=100）这条记录的余额进行了扣减20，并提交事务

        * 事务A再次查询（id=1）的记录，读到balance列的值为80

        * 事务B对（id=1）这条记录的余额再次进行了扣减10，并提交事务

        * 事务A再次查询（id=1）的记录，读到balance列的列为70，事务A被事务B干扰到了，在事务A范围内，三次相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。   

时间编号	事务A	事务B
1	begin;
2	select * from account where id = 1; （此时读取balance列的值为100）
3		update account set balance = balance - 20 where id = 1;
4	select * from account where id = 1; （此时读取balance列的值为80）
5		update account set balance = balance - 10 where id = 1;
6	select * from account where id = 1; （此时读取balance列的值为70）

         如上图，我们在事务B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了（id=1）记录的balance的值，每次事务提交后，如果事务A中的事务都可以查询到最新的值，这种现象被称为 不可重复读。

（3）幻读

        如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读。

        假设现在有两个事务A、B：

        * 会话A和会话B各开始一个事务A和事务B

        * 事务A先查询（id>2）的记录，得到了（id=3）的一条记录。

        * 事务B开启，插入一条（id=4）的记录，并且事务提交了。

        * 事务A再去执行相同的查询（id>2）,却得到了（id=3）和（id=4）两条记录。事务A被事务B干扰了，在事务A中相同的条件查询，由于事务B插入了新的数据，导致事务A查询的结果集和前一次查询的结果集不一致，这就是幻读。

时间编号	事务A	事务B
1	begin;
2	select * from account where id > 2; (此时结果集里面只有id=3的记录)
3		begin;
4		insert into account(name, balance) values('诸葛亮', 100);
5		commit;
6	select * from account where id > 2; (此时结果集里面只有id=3和id=4的记录)

        如上图，事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄的提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

二、SQL标准中的四种隔离级别

        我们上边介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题也有轻重缓急之分，我们给这些问题按照严重性来排一下序：

        脏读 > 不可重复读 > 幻读

        我们上面所说的舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。

        下面我们就重点对SQL的4个隔离级别进行详解：

        （1）READ UNCOMMITTED : 未提交可读

        （2）READ COMMITTED：已提交可读   

        （3）REPEATABLE READ：可重复读

        （4）SERIALIZABLE：可串行化

        SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

             下面我们通过修改MySQL的隔离级别针对每个情况进行验证：

        首先，我们了解下如何设定MySQL的事务隔离级别，我们可以在my.cnf中通过在[mysqld]节点下设定 transaction-isolation 属性来实现。

        （1）READ UNCOMMITTED（未提交可读） 隔离级别下，可能发生 脏读、不可重复读和幻读的问题。

        设定当前MySQL隔离级别为 READ UNCOMMITTED（未提交可读）

mysql> show variables like 'transaction_isolation';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

        发生“脏读”

        通过上述验证，我们可以看出，由于事务A第二次可以读取到事务B修改而未提交的数据，而后事务B进行了回滚事务，则事务A读取了错误数据，这就发生了脏读。

         发生“不可重复读”

 

         通过上述验证，我们可以看出，每次事务B对（name=关羽）记录的balance值进行修改后，事务A中都能读取到最新的balance的值，这就发生了不可重复读。

        发生“幻读”

 

        通过上述验证，我们可以看出，在事务A中相同的条件查询，由于事务B插入了新的数据，导致事务A查询的结果集和前一次查询的结果集不一致，这就是发生了幻读。 

         （2）READ COMMITTED（已提交可读） 隔离级别下，可能发生不可重复读和幻读问题，但是不可能发生脏读问题。

        设定当前MySQL隔离级别为 READ COMMITTED（已提交可读）

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

         不可能发生“脏读”

         通过上述验证，我们可以看出，当事务A第一次查询出记录后，事务B对（name=关羽）记录的balance值进行修改，但事务B未提交，事务A第二次查询记录仍然和第一次查询保持一致，即 当MySQL隔离级别为 READ COMMITTED（已提交可读），不会发生脏读。

        发生“不可重复读”

         通过上述验证，我们可以看出，当事务A第一次查询出记录（name=关羽）的balance值为70后，事务B更新了（name=关羽）balance的值为100并提交事务，事务A再次查询（name=关羽）的balance值，发现和第一次结果不一样了，就发生了不可重复读。

即 当MySQL隔离级别为 READ COMMITTED（已提交可读），会发生不可重复读。

         发生“幻读”

        通过上述验证，我们可以看出，当事务A第一次查询出（id>2）范围的结果集只有（id=3）一条记录，事务B插入一条记录，并且满足条件（id>2），后提交事务，事务A再次查询（id>2）范围的结果集会有（id=3）和（id=5）两条记录，就发生了幻读。

 即 当MySQL隔离级别为 READ COMMITTED（已提交可读），会发生幻读。

 （3）REPEATABLE READ（可重复读） 隔离级别下，可能发生幻读问题，但是不可能发生脏读和不可重复读的问题。

        设定当前MySQL的隔离级别为 REPEATABLE READ（可重复读）

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

        不可能发生“脏读”

 

         通过上述验证，我们可以看出，当事务A第一次查询出记录后，事务B对（name=关羽）记录的balance值进行修改，但事务B未提交，事务A第二次查询记录仍然和第一次查询保持一致，即 当MySQL隔离级别为 REPEATABLE READ（可重复读），不会发生脏读。

         不可能发生“不可重复读”

         通过上述验证，我们可以看出，当事务A首次查询（name=关羽）的记录balance的值为100，而后事务B更新了（name=关羽）记录的balance的值为80，并且提交了事务，事务A再次查询（name=关羽）的balance值，发现balance的值仍为100，没有变化，

即 当MySQL隔离级别为 REPEATABLE READ（可重复读），不会发生不可重复读。

         可能发生“幻读”

         通过上述验证，我们可以看出，当事务A首次查询（id>2）范围的结果集只有（id=3）一条记录，事务B插入一条记录，并且提交后，在事务B通过范围（id>2）查询结果集，得到结果集有（id=3）和（id=9）两条数据，而事务A再次查询（id>2）的范围的结果集仍然只有（id=3）的一条记录，明明没有发生“幻读”，怎么说可能发生”幻读“呢？我们继续来看。

 

         事务A当我们继第二次查询后，update表中的数据，再次第三次查询出满足范围（id>2）的结果集出现（id=3）和（id=9）两条记录，又发生了幻读怎么回事呢？       

        结论：

        MySQL在RR（可重复读）级别下的确一定程度上解决了”幻读“的问题，但是没有彻底解决，当事务A执行update操作后，事务A会申请得到一个trx_id，由于修改了记录（id=9）,因此记录（id=9）的trx_id就变成了事务A的事务id，之后事务A中的select查询就可以看到这条记录（id=9）,出现了幻读。

即 当MySQL隔离级别为 REPEATABLE READ（可重复读），可能发生幻读。

（4）SERIALIZABLE（可串行化） 隔离级别下，各种情况都不可能发生，这里就不具体实践了。

        但是SERIALIZABLE隔离级别下，读写冲突，因此并发度急剧下降，在MySQL下不建议使用。

由于本人水平有限，本博客可能存在不足和错误在所难免，还请大家多多指正。