MySQL学习-2|事务隔离：为什么你改了我还看不到？

事务简述

环境： MySQL 5.7.24, for linux-glibc2.12 (x86_64)

• 简单来说，事务就是要保证 一组 数据库操作，要么全部成功，要么全部失败。
• ACID(Atomicity 原子性、Consistency 一致性、Isolation 隔离性、Durability 持久性)
• 事务支持是在存储引擎层¹实现的。 InnoDB 引擎支持事务，而MyISAM引擎不支持事务。

default-storage-engine 这个参数可设置存储引擎，MySQL默认 InnoDB 存储引擎。

-- 查看使用的引擎信息
show engines;

隔离级别

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

隔离得越严格，效率就会越低。
SQL标准的事务隔离级别：读未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)、串行化(Serializable)。

隔离级别	特点
读未提交	一个事务还没提交时，它所做的变更就已经能被其他事务看到
读提交	一个事务提交之后，它所做的变更才能被其他事务看到
可重复读	一个事务执行过程中看到的数据，总是跟它在启动时所看到的数据是一致的。未提交的变更，对其他事务不可见
串行化	对于同一行记录，“写"会加"写锁”，“读"会加"读锁”。出现读写锁冲突时，后访问的事务必须等待前一个事务执行完成，才能继续执行

• Oracle数据库默认隔离级别读提交，因此对于从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，一定要记得将MySQL隔离级别(参数transaction-isolation = READ-COMMITTED)设置为读提交。

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

示例分析

-- CREATE
mysql> create table T(c int) engine=InnoDB;
-- INSERT
insert into T(c) values(1);

• 启动2个事务：A、B，按照以下步骤操作，分别看在不同的事务隔离级别下，事务A中返回结果V1、V2、V3的值是什么。

步骤	事务A	事务B
1	启动事务	启动事务
2	查询得到 c值1
3		查询得到 c值1
4		将 c值1 更新为2
5	查询得到 c值V1
6		提交事务B
7	查询得到 c值V2
8	提交事务A
9	查询得到 c值V3

读未提交

隔离级别为读未提交：V1=2，虽然这个时候事务B还未提交，但是事务B的结果已经可以被事务A看到。
V2与V3 都为 2 ，这很容易理解。

读提交

隔离级别为读提交：V1=1，这个时候事务B还未提交，事务A看不到事务B的结果。
V2=2 ，由于这个时候事务B已提交。
V3=2 ，这很容易理解。

可重复读

隔离级别为可重复读：V1=1，这个时候事务B还未提交，事务A看不到事务B的结果。
V2=1 ，这个时候事务B虽然已提交，但事务A还在执行过程中，事务的执行期间看到的数据前后必须一致。
V3=2 ，这个时候事务B已提交，事务A也已经执行完成。

串行化

隔离级别为串行化：V1与V2都为1。
执行到第4步，事务B将c值"由1更新为2"时，会被锁住，会等待事务A提交后才能继续执行。所以从事务A看到的V1与V2都为1。
V3=2 ，这个时候事务A已提交，事务B的更新操作会执行。

总结

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

• 可重复读隔离级别下，视图是在事务启动时创建的，整个事务存在期间都用这个视图。
• 读提交隔离级别下，视图是在每个SQL语句开始执行时时创建的。
• 读未提交隔离级别下，直接返回记录上的最新值，没有视图概念。
• 串行化隔离级别下，直接用加锁的方式来避免并行访问。

数据校对的业务场景适合使用可重复读隔离级别。

事务隔离的实现

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

-- c 1->2->3->4

假设将T表中c的值由1按顺序依次更新为2、3、4，在回滚日志中，会有类似下面的记录。

c 的当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。
在上图中，视图A、B、C里，这一记录的值分别是1、2、4，即同一记录在系统中可以存在多个版本。这个就是数据库的多版本并发控制(MVCC)。
对于read-view A，想要得到值1，就必须将当前值4依次执行途中所有的回滚操作才可得到。
即使现在有另外一个事务D正在将c的值由4改为5，这个事务D跟read-view A、B、C对应的事务是不会冲突的。

回滚日志在不需要的时候会删除。也就是说，系统判断当没有事务再需要使用到这些回滚日志时，回滚日志会被删除。
什么时候才不需要了呢？就是当系统中没有比这个回滚日志更早的read-view的时候。

• 尽量不要使用长事务

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

事务的启动方式

• 显式启动事务

begin 或 start transaction
commit
rollback

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

有些客户端连接框架会默认连接成功后先执行一个set autocommit = 0 命令，这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
因此，建议使用set autocommit = 1，再通过显式语句方式来启动事务。

• 在information_schema库的innodb_trx表中查询长事务。

-- 查询超过60s的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

对于一个需要频繁使用事务的业务，为了减少主动执行一次begin语句带来的交互次数问题，可以使用 commit work and chain 语法。
在autocommit为1的情况下，使用begin显式启动的事务，如果执行commit则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，省去了再次执行begin语句的开销，同时带来的好处是程序开发的角度明确的知道每个语句是否处于事务中。

参考资料

《高性能MySQL》
《MySQL实战45讲》 作者：丁奇

写在后面

之前学习了大神丁奇的《MySQL实战45讲》，目前在看《高性能高MySQL》，也想自己整理一下MySQL知识点，发现力不从心，也发现大神之所以是大神，那是因为真的牛。

推荐大家还是去学习丁奇的《MySQL实战45讲》，条理清晰，循序渐进，深入浅出，通俗易懂。而且每一讲后面都有高质量的留言评论， 从中能获益良多。感谢！

• 如有 错误之处 还请多多指正。希望能给您带来帮助。

---

1. MySQL基础架构 ↩︎