数据库进阶（3）——MySQL事务

一、一条Insert语句

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

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

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

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

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

mysql> SELECT * FROM t;

二、事务介绍

可以先实操，再来理解原理：事务的应用

1、事务概述

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

数据库事务具有ACID四大特性。

ACID是以下4个词的缩写：

原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败 。

一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏 。

隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。

持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。

2、隔离级别

（1）未提交读（RU)

READ UNCOMMITTED

出现的问题：脏读：一个事务读取到另一个事务未提交的数据。

示意图如下：

发生时间编号	Session A	Session B
1	BEGIN;
2		BEGIN;
3		UPDATE SET = '关羽’WHERE id = 1;
4	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为"关羽")

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

发生时间编号	Session A	Session B
1	BEGIN;
2		BEGIN;
3		UPDATE SET = '关羽’WHERE id = 1;
4	SELECT · FROM t WHERE id = 1;(此时读到的列c的值为"关羽")
5		ROLLBACK;

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

（2）已提交读（RC）（常用）

READ COMMITTED/RC

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

如图所示：

发生时间编号	Session A	Session B
1	BEGIN;
2		BEGIN;
3		UPDATE SET = '关羽’WHERE id = 1;
4	SELECT * FROM WHERE id = 1;(此时读到的列c的值为"刘备")
5		COMMIT;
6	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为"关羽")

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

发生时间编号	Session A	Session B
1	BEGIN;
2	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’刘备’)
3		UPDATE t SET c= '关羽’WHERE id = 1;(隐式提交)
4	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’关羽")
5		UPDATE t SET c=张飞’ WHERE id = 1;(隐式提交)
6	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’张飞")

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

（3）可重复读（RR）（常用）

REPEATABLE READ/RR

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

发生时间编号	Session A	Session B
1	BEGIN;
2	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’刘备’)
3		UPDATE t SET c= '关羽’WHERE id = 1;(隐式提交)
4	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’刘备")
5		UPDATE t SET c=张飞’ WHERE id = 1;(隐式提交)
6	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’刘备")

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

MySQL的RR隔离级别是可以解决幻读的，所以不需要使用serializable隔离级别，原因在于MySQL采取的是【间隙锁】

（4）串行化（SERIALIZABLE）

不会出现并发问题

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

发生时间编号	Session A	Session B
1	BEGIN;
2		BEGIN;
3		UPDATE t SETc='关羽"WHERE id = 1;
4	SELECT * FROM t WHERE id = 1;(等待中…)
5		COMMIT;
6	SELECT * FROM t WHERE id = 1;(此时读到的列c的值为’关羽")

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

3、设置当前会话的事务隔离级别

//查看当前事务级别：
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;

三、事务和MVCC底层原理详解

思考：丢失更新

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

时间	取款事务A	转账事务B
T1	开始事务
T2		开始事务
T3	查询账户余额为1000元
T4		查询账户余额为1000元
T5		汇入100元把余额改为1100元
T6		提交事务
T7	取出100元把余额改为900元
T8	撤销事务
T9	余额恢复为1000元(丢失更新)

时间	取款事务A	转账事务B
T1		开始事务
T2	开始事务
T3		查询账户余额为1000元
T4	查询账户余额为1000元
T5		取出100元把余额改为900元
T6		提交事务
T7	汇入100
T8	提交事务
T9	把余额改为1100元(丢失更新)

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

1、解决方案1：LBCC

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

查询总额事务会对读取的行加锁，等到操作结束后再释放所有行上的锁。

因为用户A的存款被锁，导致转账操作被阻塞，直到查询总额事务提交并将所有锁都释放。

使用锁机制：

这种方案比较简单粗暴，就是一个事务去读取一条数据的时候，就上锁，不允许其他事务来操作。

LBCC的特点：读写都加锁，读读〈读锁和读锁）不冲突，读写会冲突，写写更会冲突。

此种方法大大影响性能，故需要采用更好的方案

2、解决方案2：MVCC

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

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

MVCC的特点是︰读不加锁、读写不冲突。提高了数据库的并发处理能力。

（1）InnoDB的MVCC实现

MVCC是用于数据库提供并发访问控制的并发控制技术。与MVCC相对的是基于锁的并发控制（LBCC）。

MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。

在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC，多版本并发控制仅仅是一种技术概念，并没有统一的实现标准， 其核心理念就是数据快照，不同的事务访问不同版本的数据快照，从而实现不同的事务隔离级别。

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

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

MVCC只在RR和RC两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容 ，因为READ UNCOMMITIED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

① 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保存了记录修改前的镜像。

update undo log

update undo log是指在delete和update操作中产生的undo log。

该undo log会被后续用于MVCC当中，因此不能提交的时候删除。提交后会放入undo log的链表，等待purge线程进行最后的删除。

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

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

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

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

② ReadView

核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的。

ReadView 中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，把这个列表命名为为m_ids，并确定三个变量的值：

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

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

如果被访问版本的trx_id属性值小于m_up_limit_id，表明生成该版本的事务在生成ReadvView前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的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列表中。

如果在，说明创建ReadVview时生孩版本的事务还是活跃的，该版本不可以被访问。

如果不在，说明创建Readview时生成该版本的事务已经被提交，该版本可以被访问。

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

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 的记录得到的版本链表如下所示

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

# 使用READ COMMITTED隔离级别的事务
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 的记录的版本链就长这样：

然后再到刚才使用 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的值为'张飞'

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

RR

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

对于使用 RR隔离级别的事务来说，只会在第一次执行查询语句时生成一个 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 的记录得到的版本链表如下所示

假设现在有一个使用 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 的记录的版本链就长这样：

然后再到刚才使用 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的值仍为'刘备

（2）MVCC下的读操作

① 当前读

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

快照读：
读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读：
读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

以MySQL InnoDB为例：
快照读：
简单的select操作，属于快照读，不加锁（也有例外） 不加读锁 读历史版本
当前读：
特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。 加行写锁 读当前版本

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锁 (排它锁)

一个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操作；然后在读取下一条加锁，直至读取完毕

②快照读

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

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

（3）MVCC总结

所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 RC、 RR这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。RC、RR这两个隔离级别的一个很大不同就是生成 ReadView的时机不同， READ COMMITTD 在每一次进行普通SELECT 操作前都会生成一个ReadView ，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个ReadView ，之后的查询操作都重复这个 ReadView 就好了。

四、事务回滚和数据恢复

事务的隔离性由多版本控制机制和锁实现，而原子性，持久性和一致性主要是通过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才算执行成功。

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

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

事务的相关流程：
事务进行过程中，每次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恢复数据。