MySQL基础隔离性小结

2020 年 2 月 10 日
筆記

Tip:此为MySQL实战45讲阅读整理笔记。

ACID

名词	解释
原子性	事务是一个原子性质的操作单元，在事务中的操作要么全部执行，要么都不执行。
一致性	从事务开始到结束，数据必须保持一致状态，保证数据库的完整性，亦既数据的正确性，合理性。
隔离性	各个事务相互独立，在提交完成前，中间的任何数据变化对其他事务都是不可见的。
持久性	一个事务执行完成，其对数据库的修改是永久的，即使系统故障也不会丢失。

事务启动方式

主要有以下方式：

显式启动事务语句， begin或 start transaction。配套的提交语句是 commit ，回滚语句是 rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

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

建议使用方法一，如果担心多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，如查询时长超过60s的事务：

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

事务的启动时机

上面是启动方式，但begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。两者区别如下：

第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot时创建的；

至于“一致性视图”是什么后面会详细说到。

隔离性

隔离级别

# 查询数据库隔离级别    mysql> show variables like 'transaction_isolation';    +-----------------------+----------------+    | Variable_name | Value |    +-----------------------+----------------+    | transaction_isolation | REPEATABLE-READ |    +-----------------------+----------------+

具体隔离级别如下：

隔离级别	描述
读未提交	一个事务还未提交，其所做的变更就能被其他事务看到。
读提交	一个事务提交后，其所做的变更才会被其他事务看到。
可重复读	一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据时一致的。在可重复读的隔离级别下，未提交变更对其他事务也是不可见的。
串行化	对同一行记录，写加“写锁”，读加“读锁”。当出现读写冲突时，后访问的事务需等前一个事务执行完才可继续执行。

事务隔离的实现

MySQL数据库会创建一个视图，访问时以视图的逻辑结果为准。这个快照（即视图）是基于整个库的。

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

在MySQL中存在两种视图：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

这里说的视图就是第二种，开头“事务的启动时机”中的“一致性视图”也是这里所说的第二种视图，其仅在可重复读和读提交这两种隔离级别下存在。

InnoDB 里面每个事务有一个唯一的事务ID(transaction id)，在事务开始的时候向 InnoDB 的事务系统申请的，按申请顺序严格递增。

数据表中的一行记录，其实可能有多个版本 (row)，就是数据库的多版本并发控制（MVCC）,每个版本有自己 row trx_id（即事务将transaction id赋值赋值给这个数据版本的事务 ID）。

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。系统会判断，当没有事务再需要用到这些回滚日志(undo log)时（即当系统里没有比这个回滚日志更早的 read-view 的时候），回滚日志会被删除。

不同时刻启动的事务会有不同的 read-view。在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

快照读与当前读

普通查询语句是一致性读（也常说是 “快照读” ），一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性：

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；

实例：

简单的select操作即可

更新数据都是先读后写的，而这个读，只能读当前的值，称为 “当前读”（current read），包含：

select ... lock in share mode    select ... for update    insert    update    delete

为什么表结构不支持“可重复读”？

这是因为表结构没有对应的行数据，也没有 row trx_id，因此只能遵循当前读的逻辑。

隔离现象

事务隔离可能会产生几种现象：

脏读一个事务访问到另一个事务修改但未提交的数据。
不可重复读一个事务中，两次查询同一行数据得到不同的结果。
幻读 The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. 原文简单来说就是：一个事务中，同一个查询语句在不同时间查询出的数据行数不同。

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读提交	N	Y	Y
可重复读	N	N	Y
串行化	N	N	N

以上是ANSI/ISO SQL定义的标准隔离级别可能产生的现象。

对于“可重复读”，基于锁机制并发控制的DBMS需要对选定对象的读锁（read locks）和写锁（write locks）一直保持到事务结束，但不要求“范围锁”，因此可能会发生“幻读”。（参考）

MySQL默认是“可重复读”（REPEATABLE-READ）级别，但通过gap lock锁定索引记录之间的间隙从而消灭幻读,更准确的说：

在快照读的情况下，mysql通过mvcc来避免幻读。
在当前读的情况下，mysql通过next-key锁来避免幻读

参考资料

MySQL实战45讲