Mysql事务

发表于 更新于

Mysql依靠锁来实现事务隔离。

如何设置事务隔离级别

事务 ACID特性

ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

  1. 原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性：事务前后数据的完整性保持一致。例如转账后，双方总额不变。
  3. 隔离性：多个用户并发访问时，一个用户的事务不被其他用户事务干扰，多个并发事务数据相互隔离。
  4. 持久性：一个事务一旦提交，它对数据库的改变就是永久性，即便数据库发生故障也应该有任何影响。

MySQL事务隔离级别

  • 读未提交 Read UnCommited：完全不加锁，性能最佳事务在执行过程中可以看到其他事务尚未提交的记录，
  • 可重复读 Repeatable Read：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是mysql的默认隔离级别
  • 读已提交数据 Read Commited，一个事务在执行过程中可以看到其他事务已经提交的记录，而且能看到其他事务对已有记录的更新。
  • 串行化 Serializable：事务之间串行执行，彼此不存在并发关系，不会产生任何并发问题，性能最差。

Read UnCommitted

完全不加锁，性能最佳，但是会出现一切的并发问题。最为直接的就是脏读了。

脏读示例:

同时存在事务A 与 事务B，事务A执行完一半由于失败触发事务回滚机制(基于Undo log & CLR实现)，撤销修改。此时事务B读取了事务A所修改的脏数据，这就是脏读。

Read Committed

定义: 一个事务只能读取到其他事务已经提交的数据，也就是已经commit的数据，从而解决了脏读问题。仍然存在不可重复读的问题。

Read Committed是绝大多数数据库的默认事务隔离级别，比如Oracle，但MySQL的默认事务隔离级别是Repeatable Read.

不可重复读示例:

事务A修改某一行数据为10，原先值是1，那么事务B中多次查询该行数据可能获得1或者10，因为当事务A提交后，对该行的修改则对事务B可见。

Repeatable Read

定义: 事务不会读到其他事务对已有数据的修改，即便其他事务已经提交。也就是说在Repeatable Read的隔离级别下，每次查询出的数据视图中的已有数据不会变化。但却可以读到新插入的数据，这就是幻读。

在实际的情况中，在Repeatable Read隔离级别下，MySQL不会出现幻读，这是通过行锁和间隙锁实现的。

Serializable

类似单线程，事务串行执行，解决了幻读，脏读，不可重复读等所有问题，但是性能很差。

MVCC 如何实现事务隔离

必要背景知识

  • 数据表中的隐藏列

    当使用 InnoDB 引擎时，Mysql 会创建如下的几个隐藏的数据列。

    • RowID：隐藏的自增 ID，当建表时没有指定主键时，就会使用 RowID 作为主键来创建聚簇索引
    • DB_TRX_ID： 最近修改（更新/删除/插入）该记录的事务ID。事务 ID，是事务创建时，按时间先后顺序获取的一个自增数字。
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本。

    其实还有一个删除的flag字段，用来判断该行记录是否已经被删除。

  • UndoLog: 事务的回滚日志，用于当事务失败时回滚事务操作，从而保证事务原子性。 Undo Log 可以分为以下两种

    • Insert Undo Log: 插入操作生成，事务执行完可以立即删除
    • Update Undo Log: 更新操作生成（删除也是一种更新）。事务执行完，不能直接删除。因为 Undo Log 除了用于回滚当前事务操作以外，也能为其他事务提供该行数据的历史版本。因此只有当系统中没有比这个undo-log更早的read-view了的时候才能删除。

  • Read-View 读视图: 当一个事务执行查询操作时会生成视图，视图是由就是三个变量组成

    1. 所有活跃的事务 ID 列表
    2. 事务中活跃的最小事务 ID
    3. 事务中活跃的最大事务 ID

    在 Repeatable Read 和 Read Committed 隔离级别中，生成视图的不同造成了，这两者事务隔离程度的不同

    Repeatable Read: 在事务开始时生成视图，只有一次

    Read Committed: 在事务中的每次查询操作都会生成视图

MVCC 如何实现

MVCC 是通过 “三个” 隐藏字段 (事务id,回滚指针,删除标志) 加上undo log和可见性算法来实现的版本并发控制。

  1. 获取该行 DB_TRX_ID，如果小于 Read-View 中的最小事务 ID 或者 等于当前事务 ID，表明该行事务在生成Read-View 之前已经提交， 该行数据可见，否则继续判断.
  2. 判断该行事务 ID 是否大于视图中最大事务 ID，如果大于，表明该行事务在生成 Read-View 之后才生成，该行不可见，需要通过 Undo Log 查看历史数据。
  3. 判断该行事务 ID 是否属于视图中活跃事务列表，如果是，表明在创建 Read-View 时生成该版本记录的事务仍活跃，因此数据不可见，需要通过 Undo Log 查看历史数据。如果不是，则表眀修改该行的事务已提交，该行可见。

并发写场景

当两个事务同时对一个数据行进行更新操作时，存在并发写的问题，先来的事务对该目标行设置行锁，当事务执行完毕释放行锁，后来的事务等待行锁释放，再继续进行修改。但是在一些无索引场景就会存在例外，看具体例子：

存在索引时：

update user set age=11 where id = 1

在InnoDB中必存在主键索引，因此可以找到对应行数据添加行锁。

不存在索引时:

update user set age=11 where age=10

当未对age建立索引时，MySQL无法定位该行，则会对所有行数据先加上行锁，然后迭代所有数据并逐步释放不符合过滤条件的行锁，这就产生了极大的资源消耗。

因此在写SQL时，避免对非索引字段进行过滤查询。

Reference

你真的懂MVCC吗？来手动实践一下？

一文讲清楚MySQL事务隔离级别和实现原理