03|事务隔离: 为什么你改了我还看不见

本文作为学习笔记, 侵删, 原内容来自于极客时间MySQL实战45讲
01|基础架构: 一条SQL查询语句是如何执行的
02|日志系统: 一条SQL更新语句是如何执行的
03|事务隔离: 为什么你改了我还看不见
04|深入浅出索引(上)
05|深入浅出索引(下)
06|全局锁和表锁 : 给表加个字段怎么有这么多阻碍
07|行锁功过 : 怎么减少行锁对性能的影响
08|事务到底是隔离的还是不隔离的
实践篇
MySQL基本篇

基础篇(03)

提到事务, 你肯定不陌生, 和数据库打交道的时候, 我们总是会用到事务. 最经典的例子就是转账, 你要给老王转100块, 而此时你的银行卡只有100块.

转账过程具体到程序会有一系列的操作, 比如查询余额、做减法、更新余额等, 这些操作必须保证是一体的, 不然等程序查完之后, 还没做减法之前, 你这100块, 完全可以借着这个时间差再查一次, 然后再给另一个朋友转账. 这就要用到"事务"这个概念了.

简单来说, 事务就是要保证一组数据库操作, 要么全部成功, 要么全部失败. 在MySQL中, 事务支持是在引擎层实现的. 你现在知道, MySQL是一个支持多引擎的系统, 但并不是所有的引擎都支持事务. 比如MySQL原生的MyISAM引擎就不支持事务 这也是MyISAM被InnoDB取代的重要原因之一.

本篇中, 会议InnoDB为例, 剖析MySQL在事务支持方面的特定实现, 并基于原理给出相应地实践建议, 希望这些案例能加你对MySQL事务原理的理解.

隔离性和隔离级别

提到事务, 你肯定会想到ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”.

当数据库上有多个事务同时执行的时候, 就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题, 为了解决这些问题, 就有了"隔离级别"的概念.

在谈隔离级别之前, 先要知道, 隔离得越严实, 效率就会越低. 因此很多时候, 我们都要在二者之前寻找一个平衡点. SQL标准的事务隔离包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）.

• 读末提交是指, 一个事务还没提交时, 它做的变更就能被别的事务看到.
• 读提交是指, 一个事务提交之后, 它做的变更才会被其他事务看到.
• 可重复读是指, 一个事务执行过程中看到的数据, 总是跟着这个事务在启动时看见的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.
• 串行化, 顾名思义是对于同一行记录, “写"会加"写锁”, “读"会加"读锁”. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成, 才能继续执行.

其中"读提交"和"可重复读"比价难理解, 所以我用一个例子来说明这几种隔离级别. 假设数据表T中只有一列, 其中一行的值为1, 下面是按照时间顺序执行两个事务的行为.

mysql>CREATE TABLE T(c int) engine=InnoDB;
INSERT INTO T(c) VALUE(1);

我们来看看在不同隔离级别下, 事务A会有哪些不同的返回结果, 也就是图里面V1、V2、V3的返回值分别是什么.

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上, 数据库里面会创建一个视图, 访问的时候以视图的逻辑结果为准. 在"可重复读" 隔离级别下, 这个视图是在事务启动时创建的, 整个事务存在期间都用这个视图. 在"读提交"隔离级别下, 这个视图是在每个SQL语句开始执行的时候创建的. 这个需要注意的是, “读末提交” 隔离级别下直接返回记录上的最新值, 没有视图概念; 而"串行化"隔离级别下直接用加锁的方式来避免并行访问.

我们可以看到在不同的隔离级别下, 数据库行为是有所不同的.Oracle数据库的默认隔离级别其实就是"读提交", 因此对于一些从Oracle迁移到MySQL的应用, 为保证数据隔离级别的一致, 一定要记得将MySQL的隔离级别设置为"读提交".

配置的方式是, 将启动参数transaction-isolation的值设置成READ-COMMITTD. 你可以用show variables来查看当前的值.

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

总结来说, 存在是有原因的, 每种隔离级别都有自己的使用场景, 你要根据自己的业务情况来定. **什么时候需要“可重复读”的场景呢？**我们来看一个数据校对逻辑的案例.

假设你在管理一个个人银行账户表. 一个表存了账户余额, 一个表存了账单明细. 到了月底你要做数据校对, 也就是判断上个月的余额和当前余额的差额, 是否与本月的账单明细一致. 你一定希望在校对过程中, 即使有用户发生了一笔新的交易, 也不影响你的校对结果.

这个时候使用"可重复读"隔离级别就很方便. 事务启动时的视图可以认为是静态的, 不受其他事务更新的影响.

事务隔离的实现

理解了事务的隔离级别, 再来看看事务隔离具体是怎么实现的. 这里我们展开说明"可读重复".

在MySQL中, 实际上每条记录在更新的时候都会同时记录一条回滚记录. 记录上的最新值, 通过回滚操作, 都可以得到前一个状态的值.

假设一个值从1按顺序改成了2、3、4, 在回滚日志里面就会有类似下面的记录.

当前值是4, 但是在查询这条记录的时候, 不同时刻启动的事务会有不同的read-view. 如图中看到的, 在视图A、B、C里面, 这一记录的值分别是1、2、4, 同一条记录在系统中可以存在多个版本, 就是数据库的多版本并发控制(MVCC). 对于read-view A, 要得到1, 就必须将当前值依次执行图中所有的回滚操作得到.

同时你会发现, 即使现在有另外一个事务正在将4改成5, 这个事务跟read-view A、B、C对应的事务是不会冲突的.

你一定会问, 回滚日志总不能一直保存吧, 什么时候删除呢? 答案是, 在不需要的时候才删除. 也就是说, 系统会判断, 当没有事务再需要用到这些回滚日志时, 回滚日志会被删除.(类似于JVM的GC).

什么时候才不需要了呢? 就是当系统里没有比这个回滚日志更早的read-view的时候.

基于上面的说明, 我们来讨论一下为什么建议尽量不要使用长事务.

长事务意味着系统里面会存在很老的事务视图. 由于这些事务随时可能访问数据库里面的任何数据, 所以这个事务提交之前, 数据库里面它可能用到的回滚记录都必须保留, 这就会导致大量占用存储空间.

在MySQL 5.5及之前的版本, 回滚日志是跟数据字典一起放在ibdata文件里的, 即使长事务最终提交, 回滚段被清理, 文件也不会变小. 见过数据只有20GB, 而回滚段有200GB的库吗? 最终只好为了清理回滚段, 重建整个库.

除了对回滚段的影响, 长事务还占用锁资源, 也可能拖垮整个库, 这个在讲锁的时候展开说明.

事务的启动方式

如前面所述, 长事务有这些潜在风险, 建议尽量避免. MySQL的事务启动方式有以下几种:

1. 显示启动事务语句

   mysql>BEGIN;
   
   mysql>start transaction; //启动业务的另一种写法
   
   mysql>ROLLBACK;
   
   mysql>COMMIT;  
   

2. set autocommit=0, 这个命令会将这个线程的自动提交关掉. 意味着如果你只执行一个select语句, 这个事务就启动了, 而且并不会自动提交. 这个事务持续存在直到你主动执行commit或rollback语句, 或者断开连接.

有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令. 这就导致接下来的查询都在事务中, 如果是长连接, 就导致了意外的长事务.

因此, 建议使用set autocommit=1, 通过显示语句的方式来启动事务.

但是有的开发会纠结"多一次交互"的问题. 对于一个需要频繁使用事务的业务, 第二种方式每个事务在开始时都不需要主动执行一次"begin", 减少了语句的交互次数. 如果你有这个顾虑, 建议你使用commit work and chain语法.

在autocommit为1的情况下, 用begin显示启动的事务, 如果执行commit则提交事务. 如果执行commit work and chain, 则是提交事务并自动启动下一个事务, 这样也省去了再次执行begin语句的开销. 同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中.

你可以在information_schema库的innodb_trx这个表中查询长事务, 比如下面这个语句, 用于查找持续时间超过60s的事务.

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

小结

本篇介绍了MySQL的事务隔离级别的现象和实现, 根据实现原理分析了长篇事务存在的风险, 以及如何正确的方式避免长事务.

思考题: 现在知道了系统应该避免长事务, 如果你是业务开发负责人同时也是数据库负责人, 你会有什么方案来避免出现或处理这种情况?

这个问题分两部分来看. 分别是应用开发端和数据库端来看.

首先, 从应用开发端来看:

1. 确认是否使用了 set autocommit=0. 这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认. 一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1.
2. 确认是否有不必要的只读事务. 有些框架会习惯不管什么语句先用 begin/commit 框起来. 我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中. 这种只读事务可以去掉.
3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间. （为什么会意外？在后续的文章中会提到这类案例）

其次, 从数据端来看:

1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2. Percona 的 pt-kill 这个工具不错，推荐使用；
3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）. 如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便.