MySQL 事务

 提到事务,肯定不陌生。最经典的例子就是转账,张三要给李四转 100 块钱。

 转账时要从张三的账户扣100块钱,然后给李四的账户加100块钱,张三账户扣钱李四账户加钱必须保证是一体的。

 简单来说,事务就是要保证一组数据库操作,要么全部成功,要么全部失败。

在 MySQL 中,事务支持是在引擎层实现的。

(1) 事务的四大特性

Atomicity、Consistency、Isolation、Durability

原子性:事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。 如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。
一致性:指在事务开始之前和事务结束以后,数据不会被破坏,假如 A 账户给 B 账户转 10 块钱,不管成功与否,A 和 B 的总金额是不变的。
隔离性:多个事务并发访问时,事务之间是相互隔离的,即一个事务不影响其它事务运行效果。
持久性:表示事务一旦提交,它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

原子性undo log来保证:回滚日志undo log在事务回滚时能够撤销所有已经成功执行的sql语句。

隔离性是通过数据库MVVC实现的。

持久性redo log 来保证,当做数据修改的时候,不仅在内存中操作,还会在redo log中记录这次操作。当事务提交的时候,会将redo log日志进行刷盘(redo log一部分在内存中,一部分在磁盘上)。当数据库宕机重启的时候,会将redo log中的内容恢复到数据库中,再根据undo logbinlog内容决定回滚数据还是提交数据。

一致性由 原子性、隔离性、持久性 来保证。


(2) 事务的隔离性与隔离级别

 提到事务,你肯定会想到 ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性),来说说其中 I,也就是”隔离性”。

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

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

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

 读未提交:别人改数据的事务尚未提交,我在我的事务中也能读到。
 读已提交:别人改数据的事务已经提交,我在我的事务中才能读到。
 可重复读:别人改数据的事务已经提交,我在我的事务中也不去读。
 串行:我的事务尚未提交,别人就别想改数据。
 这4种隔离级别,并行性能依次降低,安全性依次提高。

在当前隔离级别是否会出现对应情况 脏读(dirty read) 不可重复读(non-repeatable read) 幻读(phantom read)
读未提交(read uncommitted)
读提交(read committed) 不会
可重复读(repeatable read) 不会 不会
串行化(serializable) 不会 不会 不会

(2.1) 不同的隔离级别下事务的返回结果

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

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
事务A 事务B
启动事务 查询得到值1 启动事务
查询得到值1
将1改成2
查询得到值V1
提交事务B
查询得到值V2
提交事务A
查询得到值V3

 

 我们来看看在不同的隔离级别下,事务 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。
 

(3) MySQL在不同隔离级别的事务的区别

 InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view,用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔离级别的实现。

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

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

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

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql>

(4) 事务隔离的实现

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

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

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

mysql-transaction-log

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

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

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

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

 建议尽量不要使用长事务。

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

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

 除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库。


(5) 事务的启动方式

 如前面所述,长事务有这些潜在风险,我当然是建议你尽量避免。其实很多时候业务开发同学并不是有意使用长事务,通常是由于误用所致。MySQL 的事务启动方式有以下几种:

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

注意:begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才真正启动。
如果你想要马上启动一个事务,可以使用 start transaction with consistent snapshot 这个命令。

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

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

(6) “快照”在MVCC里是怎么工作的?

InnoDB 的行数据有多个版本,每个数据版本有自己的 row trx_id,每个事务或者语句有自己的一致性视图。普通查询语句是一致性读,一致性读会根据 row trx_id一致性视图确定数据版本的可见性。

在可重复读隔离级别下,事务在启动的时候就”拍了个快照”。

事务ID和数据版本ID

InnoDB 里面每个事务有一个唯一的事务ID,叫作 transaction id
它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个数据版本的事务 ID,记为 row trx_id

事务数组-数据版本的可见性规则

在实现上, InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。
数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。
而数据版本的可见性规则,就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id

// todo

到这里,我们把一致性读、当前读和行锁就串起来了。

(7) 事务的可重复读的能力是怎么实现的?

可重复读的核心就是一致性读(consistent read);而事务更新数据的时候,只能用当前读。如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待。

读提交的逻辑和可重复读的逻辑类似,它们最主要的区别是:
在可重复读隔离级别下,只需要在事务开始的时候创建一致性视图,之后事务里的其他查询都共用这个一致性视图;
在读提交隔离级别下,每一个语句执行前都会重新算出一个新的视图。

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

为什么表结构不支持“可重复读”?
这是因为表结构没有对应的行数据,也没有 row trx_id,因此只能遵循当前读的逻辑。
当然,MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了,也许以后会支持表结构的可重复读。

(8) 牛刀小试

(8.1)

 如果你是业务开发负责人同时也是数据库负责人,你会有什么方案来避免出现或者处理这种情况呢?

 在开发过程中,尽可能的减小事务范围,少用长事务,如果无法避免,保证逻辑日志空间足够用,并且支持动态日志空间增长。监控Innodb_trx表,发现长事务报警。

(8.2)


mysql> show processlist ;
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
| Id | User            | Host            | db      | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL    | Daemon  | 53302 | Waiting on empty queue | NULL             |
| 11 | root            | localhost:63553 | db_test | Sleep   |    43 |                        | NULL             |
| 45 | admin           | localhost:49961 | db_test | Sleep   |    54 |                        | NULL             |
| 46 | admin           | localhost:49962 | NULL    | Sleep   |    13 |                        | NULL             |
| 50 | admin           | localhost:50131 | db_test | Sleep   |    54 |                        | NULL             |
| 51 | admin           | localhost:50132 | NULL    | Sleep   |    54 |                        | NULL             |
| 53 | admin           | localhost:50159 | NULL    | Sleep   |    24 |                        | NULL             |
| 55 | admin           | localhost:50175 | NULL    | Sleep   |    39 |                        | NULL             |
| 56 | admin           | localhost       | db_test | Query   |     0 | init                   | show processlist |
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
9 rows in set (0.00 sec)

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql>  set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

mysql> update t set c=0 where id=c;
Query OK, 0 rows affected (11.70 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
|  4 |    5 |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql>

mysql>  show processlist ;
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
| Id | User            | Host            | db      | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL    | Daemon  | 53317 | Waiting on empty queue | NULL             |
| 11 | root            | localhost:63553 | db_test | Query   |     0 | init                   | show processlist |
| 45 | admin           | localhost:49961 | db_test | Sleep   |     6 |                        | NULL             |
| 46 | admin           | localhost:49962 | NULL    | Sleep   |    28 |                        | NULL             |
| 50 | admin           | localhost:50131 | db_test | Sleep   |     6 |                        | NULL             |
| 51 | admin           | localhost:50132 | NULL    | Sleep   |     6 |                        | NULL             |
| 53 | admin           | localhost:50159 | NULL    | Sleep   |    39 |                        | NULL             |
| 55 | admin           | localhost:50175 | NULL    | Sleep   |    54 |                        | NULL             |
| 56 | admin           | localhost       | db_test | Sleep   |     8 |                        | NULL             |
+----+-----------------+-----------------+---------+---------+-------+------------------------+------------------+
9 rows in set (0.01 sec)

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.01 sec)

mysql> update t set c=c+1 ;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
|  4 |    5 |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from t ;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
|  4 |    5 |
+----+------+
4 rows in set (0.00 sec)

mysql>

References

[1] 03 | 事务隔离:为什么你改了我还看不见? MySQL实战45讲
[2] 08 | 事务到底是隔离的还是不隔离的?
[3] 20 | 幻读是什么,幻读有什么问题?MySQL实战45讲
[4] 《高性能MySQL》 O’REILLY
[5] 深入学习MySQL事务:ACID特性的实现原理