侧边栏壁纸
博主头像
cluski博主等级

  • 累计撰写 57 篇文章
  • 累计创建 23 个标签
  • 累计收到 4 条评论
标签搜索
Mysql

Mysql事务篇

cluski
cluski
2022-03-14 / 0 评论 / 0 点赞 / 164 阅读 / 10,905 字
温馨提示：
本文最后更新于 2022-03-15，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

Mysql事务篇

1 一条Insert语句

为了故事的顺利发展，我们需要创建一个表：

CREATE TABLE t
(
    id INT PRIMARY KEY,
    c  VARCHAR(100)
) Engine = InnoDB
  CHARSET = utf8;

然后向这个表里插入一条数据：

INSERT INTO t VALUES(1, '刘备');

现在表里的数据就是这样的：

mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
| 1 | 刘备     |
+----+--------+
1 row in set (0.01 sec)

二、执行流程

image-20220315211800714

2 事务介绍

2.1 事务概述

事务是数据库最为重要的机制之一，在MySQL中的事务是由存储引擎实现的，而且支持事务的存储引擎不多，我们主要讲解InnoDB存储引擎中的事务。

image-20220314212350460

事务的四大特性

数据库事务具有ACID四大特性。ACID是以下4个词的缩写：

  • 原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败 。
  • 一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏 。
    • undo log
  • 隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。
  • 持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。
    • 持久性是通过redo log保证的：force-log-at-commit机制、预写日志

2.2 隔离级别

2.2.1 未提交读（READ UNCOMMITTED/RU）

产生的并发问题：脏读：一个事务读取到另一个事务未提交的数据。

如果一个事务读到了另一个未提交事务修改过的数据，那么这种 隔离级别 就称之为 未提交读 （英文名： READ UNCOMMITTED ），示意图如下：

image-20220314212458005

如果 Session B 中的事务稍后进行了回滚，那么 Session A 中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读，就像这个样子：

image-20220314212539484

脏读 违背了现实世界的业务含义，所以这种 READ UNCOMMITTED 算是十分不安全的一种 隔离级别 。

2.2.2 已提交读（READ COMMITTED/RC）

产生的并发问题：不可重复读：一个事务因读取到另一个事务已提交的update。导致对同一条记录读取两次以上的结果不一致。

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种 隔离级别 就称之为 已提交读（英文名： READ COMMITTED ），如图所示：

image-20220314212743305

对于某个处在在 已提交读 隔离级别下的事务来说，只要其他事务修改了某个数据的值，并且之后提交了，那么该事务就会读到该数据的最新值，比方说：

image-20220314213604853

我们在 Session B 中提交了几个隐式事务，这些事务都修改了 id 为 1 的记录的列c的值，每次事务提交之后， Session A 中的事务都可以查看到最新的值。这种现象也被称之为不可重复读。

2.2.3 可重复读（REPEATABLE READ/RR）

产生的并发问题：幻读：一个事务因读取到另一个事务已提交的insert数据或者delete数据。导致对同一张表读取两次以上的结果不一致。

在一些业务场景中，一个事务只能读到另一个已经提交的事务修改过的数据，但是第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读（英文名: REPEATABLE READ），如图所示：

image-20220314213743653

从图中可以看出来， Session A 中的事务在第一次读取 id 为 1 的记录时，列 c 的值为 '刘备' ，之后虽然 Session B 中隐式提交了多个事务，每个事务都修改了这条记录，但是 Session A 中的事务读到的列 c 的值仍为 '刘备' ，与第一次读取的值是相同的。

Mysql的RR隔离级别是可以解决幻读的，所以不需要使用Serializable隔离级别

原因在于MySQL采用的是【间隙锁】

2.2.4 串行化（SERIALIZABLE）

以上3种隔离级别都允许对同一条记录进行 读-读 、 读-写 、 写-读 的并发操作，如果我们不允许 读-写 、 写-读 的并发操作，可以使用 SERIALIZABLE 隔离级别，示意图如下

image-20220314214107897

如图所示，当 Session B 中的事务更新了 id 为 1 的记录后，之后 Session A 中的事务再去访问这条记录时就被卡住了，直到 Session B 中的事务提交之后， Session A 中的事务才可以获取到查询结果。

备注：设置当前会话的事务隔离级别

// 查看当前事务级别：
SELECT @@tx_isolation;
// 设置read uncommitted级别：
set session transaction isolation level read uncommitted;
// 设置read committed级别：
set session transaction isolation level read committed;
// 设置repeatable read级别：
set session transaction isolation level repeatable read;
// 设置serializable级别：
set session transaction isolation level serializable;

3 事务和MVCC底层原地详解

3.1 思考：丢失更新

两个事务针对同一数据都发生修改操作时，会存在丢失更新问题。

image-20220314214252191

image-20220314215555636

再看一个例子：管理者要查询所有用户的存款总额，假设除了用户A和用户B之外，其他用户的存款总额都为0，A、B用户各有存款1000，所以所有用户的存款总额为2000。但是在查询过程中，用户A会向用户B进行转账操作。转账操作和查询总额操作的时序图如下图所示。
转账和查询的时序图：

image-20220314214303904

3.2 解决方案1：LBCC

使用LBCC（LBCC，基于锁的并发控制，英文全称Lock Based Concurrency Control）可以解决上述的问题。

LBCC特点：读写都加锁，读读不冲突，读写不冲突，写写更加冲突

查询总额事务会对读取的行加锁，等到操作结束后再释放所有行上的锁。因为用户A的存款被锁，导致转账操作被阻塞，直到查询总额事务提交并将所有锁都释放。 使用锁机制：

image-20220314214328566

这种方案比较简单粗暴，就是一个事务去读取一条数据的时候，就上锁，不允许其他事务来操作。MySQL加锁之后就是当前读。假如当前事务只是加共享锁，那么其他事务就不能有排他锁，也就是不能修改数据；而假如当前事务需要加排他锁，那么其他事务就不能持有任何锁。总而言之，能加锁成功，就确保了除了当前事务之外，其他事务不会对当前数据产生影响，所以自然而然的，当前事务读取到的数据就只能是最新的，而不会是快照数据(后文MVCC会解释快照读概念)。

此种方案，会大大影响性能，所以，我们需要采用更好的方案。

3.3 解决方案2：MVCC

当然使用MVCC（MVCC,多版本的并发控制，英文全称：Multi Version ConcurrencyControl）机制可以解决这个问题。

MVCC的特点：读不加锁，速写不冲突。

查询总额事务先读取了用户A的账户存款，然后转账事务会修改用户A和用户B账户存款，查询总额事务读取用户B存款时不会读取转账事务修改后的数据，而是读取本事务开始时的数据副本(在REPEATABLE READ隔离等级下)。使用MVCC机制（RR隔离级别下的演示情况）：

image-20220314214405353

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

3.4 InnoDB的MVCC实现

我们首先来看一下wiki上对MVCC的定义：

Multiversion concurrency control (MCC or MVCC), is a concurrency control method
commonly used by database management systems to provide concurrent access to the
database and in programming languages to implement transactional memory.

由定义可知，MVCC是用于数据库提供并发访问控制的并发控制技术。与MVCC相对的是基于锁的并发控制， Lock-Based Concurrency Control （LBCC）。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

多版本并发控制仅仅是一种技术概念，并没有统一的实现标准， 其核心理念就是数据快照，不同的事务访问不同版本的数据快照，从而实现不同的事务隔离级别。

MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的事务ID，一个保存了行的回滚指针。每开始一个新的事务，都会自动递增产生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中，当查询时需要用当前事务id和每行记录的事务id进行比较。

3.4.1 undo log

根据行为的不同，undo log分为两种： insert undo log 和 update undo log

  • insert undo log：

    是在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以 insert undo
    log 可以在事务提交后直接删除而不需要进行 purge 操作。

    为了更好的支持并发，InnoDB的多版本一致性读是采用了基于回滚段的的方式。另外，对于更新和删除操作，InnoDB并不是真正的删除原来的记录，而是设置记录的delete mark为1。因此为了解决数据Page和Undo Log膨胀的问题，需要引入purge机制
    进行回收。Undo log保存了记录修改前的镜像。在InnoDB存储引擎中，undo log分为：

    • insert undo log
    • update undo log

    insert undo log是指在insert操作中产生的undo log。由于insert操作的记录，只是对本事务可见，其他事务不可见，所以undo log可以在事务提交后直接删除，而不需要purge操作。

    update undo log是指在delete和update操作中产生的undo log。该undo log会被后续用于MVCC当中，因此不能提交的时候删除。提交后会放入undo log的链表，等待purge线程进行最后的删除。

如下图所示（初始状态）：

image-20220314221118741

  • update undo log :
    是 update 或 delete 操作中产生的 undo log。因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。

如下图所示（第一次修改）：

当事务2使用UPDATE语句修改该行数据时，会首先使用排他锁锁定该行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务ID，使用回滚指针指向undo log中修改前的行。

image-20220314221553524

当事务3进行修改与事务2的处理过程类似，如下图所示（第二次修改）：

image-20220314221603588

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式。

3.4.2 ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，直接读取记录的最新版本就好了。对于使用 SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录。对于使用 READCOMMITTED 和 REPEATABLE READ 隔离级别的事务来说，就需要用到我们上边所说的 版本链了。

核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的。所以设计 InnoDB 的设计者提出了一个ReadView的概念，这个 ReadView 中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids，并确定三个变量的值：

  • m_up_limit_id：m_ids事务列表中的最小事务id，如果当前列表为空那么就等于m_low_limit_id。事务id的下限.
  • m_low_limit_id：系统中将要产生的下一个事务id的值。事务id的上限。
  • m_creator_trx_id：当前事务id，m_ids中不包含当前事务id。

这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本（版本链中的版本）是否可见：

  • 如果被访问版本的 trx_id 属性值小于 m_up_limit_id ，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值等于 m_creator_trx_id 既当前事务id，可以被访问。
  • 如果被访问版本的 trx_id 属性值大于等于 m_low_limit_id ，在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值在 m_up_limit_id 和 m_low_limit_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中。
    • 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；
    • 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

版本链的trx_id和m_ids比较

  1. trx_id在m_ids里面，则不可见
  2. trx_id<m_ids最小事务号，可见
  3. trx_id>m_ids最大事务号，不可见

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成 ReadView 的时机不同，我们来看一下。

3.4.2.1 READ COMMITTED/RC

每次读取数据前都生成一个ReadView

比方说现在系统里有两个 id 分别为 100 、 200 的事务在执行：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...

小贴士： 事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），才会被分配一个单独的事务id，这个事务id是递增的。

此刻，表 t 中 id 为 1 的记录得到的版本链表如下所示：

image-20220314224250683

假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

这个SELECT1的执行过程如下：

  • 在执行SELECT语句的时候会生成一个ReadView，ReadView的m_ids列表的内容就是[100,200]。
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新的版本列c的内容是“张飞”，该版本的trx_id值为100，在m_ids列表中，所以不符合可见性的要求，根据roll_pointer调到下一个版本。
  • 下一版本的列c内容是"关羽"，该版本的trx_id的值也是100，也在m_ids中，所以也不符合要求，继续跳到下一个版本。
  • 下一个版本的列c的内容是“刘备”，该版本的trx_id值为80，小于m_ids列表中的值，所以这个这个版本是符合要求的，最后返回给用户的版本就是这条c列为“刘备“的记录。

之后，我们把事务id为 100 的事务提交一下，就像这样：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;

然后再到事务id为 200 的事务中更新一下表 t 中 id 为1的记录：

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE t SET c = '赵云' WHERE id = 1;
UPDATE t SET c = '诸葛亮' WHERE id = 1;

此刻，表 t 中 id 为 1 的记录的版本链就长这样：

image-20220314224346890

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个id为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'张飞'

总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

3.4.2.2 REPEATABLE READ/RR

在事务开始后第一次读取数据时生成一个ReadView

对于使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。比方说现在系统里有两个 id 分别为 100 、 200 的事务在执行：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...

此刻，表 t 中 id 为 1 的记录得到的版本链表如下所示：

image-20220314225504593

假设现在有一个使用 REPEATABLE READ 隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

之后，我们把事务id为 100 的事务提交一下，就像这样：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;

然后再到事务id为 200 的事务中更新一下表 t 中 id 为1的记录：

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE t SET c = '赵云' WHERE id = 1;
UPDATE t SET c = '诸葛亮' WHERE id = 1;

此刻，表 t 中 id 为 1 的记录的版本链就长这样：

image-20220314225920565

然后再到刚才使用 REPEATABLE READ 隔离级别的事务中继续查找这个id为 1 的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值仍为'刘备'

3.5 MVCC下的读操作

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？

  • 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析) 不加读锁 读历史版本
  • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。 加行写锁 读当前版本

3.5.1 当前读

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

image-20220315210123134

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQLServer会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个
Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。

Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

3.5.2 快照读

快照读也就是一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。

如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个最新可见快照。

image-20220315210157611

会话A和会话B示意图：

image-20220315210206268

如上图所示，当会话B提交事务后，会话A再次运行 SELECT * FROM test WHERE id = 1 的SQL语句时，不同的事务隔离级别下得到的结果就不一样了。

3.6 MVCC总结

从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成 ReadView的时机不同， READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个ReadView ，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个ReadView ，之后的查询操作都重复这个 ReadView 就好了。

4 事务回滚和数据恢复

事务的隔离性由多版本控制机制和锁实现，而原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log atCommit机制保证事务提交后redo log日志都已经持久化。

我们先来看一下redo log的原理，redo log顾名思义，就是重做日志，每次数据库的SQL操作导致的数据变化它都会记录一下，具体来说，redo log是物理日志，记录的是数据库页的物理修改操作。如果数据发生了丢失，数据库可以根据redo log进行数据恢复。

InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务COMMIT时，必须先将该事务的所有日志都写入到redo log文件进行持久化之后，COMMIT操作才算完成。

当事务的各种SQL操作执行时，即会在缓冲区中修改数据，也会将对应的redo log写入它所属的缓存。当事务执行COMMIT时，与该事务相关的redo log缓冲必须都全部刷新到磁盘中之后COMMIT才算执行成功。

数据库日志和数据落盘机制，如下图所示：

image-20220315210342074

我们再来总结一下数据库事务的整个流程，如下图所示。

image-20220315210351663

4.1 事务的相关流程

事务进行过程中，每次DML sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

0
Mysql 架构 运维
版权归属： cluski
本文链接： http://www.cluski.top/archives/mysql-shi-wu-pian
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区