数据库 ·

MySQL基础隔离性小结

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

ACID

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

事务启动方式

主要有以下方式:

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

有些客户端连接框架会默认连接成功后先执行一个 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讲

参与评论