MySQL(十)-事务 | HoleLin's Blog

参考文献

极客时间–MySQL实战45讲(林晓斌)
极客时间–SQL必知必会(陈旸)
解决死锁之路 - 学习事务与隔离级别
MySQL日志15连问,redo log与biglog
数据库事务隔离发展历史
MVCC 原理
图文解读MySQL InnoDB Undo log
The basics of the InnoDB undo logging and history system

`ACID`

Atomicity原子性

事务是最小的执行单位,不允许分割.事务的原子性确保动作要么全部完成,要么完全不起作用;
Consistency 一致性
一致性指事务将数据库从一种状态变为下一种一致的状态.在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏.
- 例如数据库表中有个字段有唯一性约束,事务中对该字段进行了修改,但在事务提交或事务操作发生回滚后,该字段的值变得不唯一了,这就破坏了事务的一致性要求,即事务将数据库从一种状态变为了一种不一致的状态.
- 事务的一致性的单位,如果事务中某个动作失败了,系统可以自动撤销事务–返回初始化的状态.
Isolation 隔离性

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离,即该事务提交之前对其他事务都不可见.

通常使用锁来实现.
Durability 持久性

一个事务被提交之后.它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响.
在这个四个特性中,原子性是基础,隔离性是手段,一致性是约束条件,而持久性是目的.

事务

事务是一个不可分割的数据库操作序列,也是数据库并发控制的基本单位,其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态.事务是逻辑上的一组操作,要么都执行,要么都不执行(支持连续SQL的集体成功或集体撤销.);
事务支持在引擎层实现的;
需要利用 InnoDB 或 BDB 存储引擎,对自动提交的特性支持完成.
InnoDB被称为事务安全型引擎.
注意:
- 数据定义语言(DDL)语句不能被回滚,比如创建或取消数据库的语句,和创建、取消或更改表或存储的子程序的语句.
- 事务不能被嵌套.

事务常用控制语句

SET autocommit = 0|1;: 0表示关闭自动提交,1表示开启自动提交.
- SET autocommit是永久改变服务器的设置,直到下次再次修改该设置.(针对当前连接)
- 而START TRANSACTION记录开启前的状态,而一旦事务提交或回滚后就需要再次开启事务.(针对当前事务)
START TRANSACTION或BEGIN:显示开启一个事务;
COMMIT 提交事务.当提交事务后,对数据库的修改是永久性的
ROLLABACK或者ROLLBACK TO [SAVEPOINT]为回滚,即撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点;

SAVEPOINT:再事务中创建保存点,方便后续针对保存点进行回滚.一个事务可以存在多个保存.

-- 设置一个事务保存点
SAVEPOINT 保存点名称 
-- 回滚到保存点
ROLLBACK TO SAVEPOINT 保存点名称 
-- 删除保存点
RELEASE SAVEPOINT 保存点名称

SET TRANSACTION:设置事务的隔离级别

事务的启动

显示启动事务,begin或start transaction配套的提交语句commit,回滚语句rollback;
- begin/start transaction命令并不是一个事务的起点,在执行到它们之后的第一个读取操作后,事务才真正启动;
  - begin/start transaction方式,一致性视图是在第一个快照读语句是创建的;
- 若想要马上启动一个事务,可以使用start transaction with consistent snapshot;这个命令;
  - start transaction with consistent snapshot;方式,一致性视图是在执行start transaction with consistent snapshot时创建的.
  - start transaction with consistent snapshot;意思是从这个语句开始,创建一个持续整个事务的一致性快照.所以在读提交隔离级别下,这个用法就没意义了,等效普通的start transaction
autocommit 的值默认是 1,含义是事务的自动提交是开启的.如果我们执行 set autocommit=0,这个命令会将这个线程的自动提交关掉.意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交.这个事务持续存在直到你主动执行commit或 rollback语句,或者断开连接.

查询MySQL全局事务隔离级别

-- 5.x
SELECT @@global.tx_isolation;
 -- 8.0
SELECT @@global.transaction_isolation;

// 设置 READ UNCOMMITTED级别: 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
// 设置 READ COMMITTED级别: 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
// 设置 REPEATABLE READ级别: 
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
// 设置 SERIALIZABLE级别: 
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

查询当前会话事务隔离级别

-- 5.x
SELECT @@tx_isolation;
-- 8.0
SELECT @@transaction_isolation;
SHOW VARIABLES LIKE 'transaction_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;

隔离级别与锁的关系

在Read Uncommitted级别下,读取数据不需要加共享锁,这样就不会跟被修改的数据上的排他锁冲突
在Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁;
在Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁.
Serializable是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成.

MySQL和Oracle默认隔离级别

Oracle默认隔离级别为Read Committed隔离级别;
MySQL 默认隔离级别为Repeatable Read隔离级别;
- InnoDB存储引擎默认的事务隔离级别是Repeatable Read,在该隔离级别下,其采用Next-Key Locking的方式来加锁.
- 而在事务隔离级别为Read Committed下,其仅采用Record Lock.

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准.

在"可重复读"隔离级别下,这个视图是在事务启动是创建的,整个事务存在期间都用这个视图.

在"读提交"隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的.

这里需要注意的是,"读未提交"隔离级别下直接返回记录上的最新值,没有视图的概念;

而"串行化"隔离别下直接用加锁的方式来避免并行访问.

事务隔离机制的实现基于锁机制和并发调度.其中并发调度使用的是MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性.

因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是Read-Committed(读取提交内容),但是你要知道的是InnoDB 存储引擎默认使用 **Repeatable-Read(可重读)**并不会有任何性能损失.

InnoDB 存储引擎在分布式事务的情况下一般会用到**Serializable(可串行化)**隔离级别.

对事务操作的统计

由于InnoDB存储引擎是支持事务的,因此InnoDB存储引擎的应用需要在考虑每秒请求数(Queries Per Second QPS)的同时,应该关注每秒事务的处理的能力(Transaction Per Second TPS)
计算TPS的方法是(com_commit + com_rollback) / time.但是利用这种方法进行计算的前提是:所有的事务必须都是显示提交的,如果存在隐式地提交和回滚(默认autocommit=1),不会计算到com_commit和com_rollback变量中.

隐式提交事务的SQL语句

DDL语句

-- ALTER
ALTER DATABASE,
ALTER EVENT,
ALTER PROCEDURE,
ALTER TABLE,
ALTER VIEW,
-- CREATE
CREATE DATABASE,
CREATE EVENT,
CREATE INDEX,
CREATE PROCEDURE,
CREATE TABLE,
CREATE TRIGGER,
CREATE VIEW,
-- DROP 
DROP DATABASE,
DROP EVENT,
DROP INDEX,
DROP PROCEDURE,
DROP TABLE,
DROP TRIGGER,
DROP VIEW,
-- OTHER
RENAME TABLE,
TRUNCATE TABLE

用来隐式地修改MySQL架构的操作:

CREATE USER,
DROP USER,
GRANT,
RENAME USE,
REVOKE,
SET PASSWORD

管理语句:

ANALYZE TABLE,
CACHE INDEX,
CHECK TABLE,
LOAD INDEX INTO CACHE,
OPTIMIZE TABLE,
REPAIR TABLE

事务中常出现的问题

脏读(Dirty Read)

某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个事务RollBack了操作,则后一个事务所读取的数据就会是不正确的.就是读到了别的事务回滚前的脏数据,

当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据.

不可重复读(Non-repeatable Read)

在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据.

当前事务先进行了一次数据读取,然后再次读取到的数据是别的事务修改成功的数据,导致两次读取到的数据不匹配.

同一条记录的内容被修改了,重点在于UPDATE或DELETE

幻读(Phantom Read)

在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的

当前事务读第一次取到的数据比后来读取到数据条目不一致.

查询某一个范围的数据行变多了或变少了,重点在与INSERT

丢失更新(lost update)

又称Read-Modify-Write 问题

简单而言就是一个事务的更新操作会被另一个事务的更新操作覆盖,从而导致数据的不一致.

事务T1将行记录r更新为v1,但是事务T1并未提交.

与此同时,事务T2将行记录r更新为v2,事务T2未提交.

事务T1提交.

事务T2提交.

要避免丢失更新发生,需要让事务在这种情况下的操作编程串行化,并不是并行操作.

SQL标准的事务隔离级别

读未提交(Read Uncommitted)

一个事务还没提交时,它做的变更就能被别的事务看到;
别人改数据的事务尚未提交,我在我的事务中也能读到;
最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读.

读提交(Read Committed)

一个事务提交之后,他的变更才能被其他事务看到;
别人改数据的事务已提交,我在我的事务中才能读到;
允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生.

可重复读(Repeatable Read)

一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致.当然在可重复读隔离级别,未提交变更对其他事务也是不可见的;
别人改数据的事务已经提交,我在我的事务中也不去读;
对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生.

串行(Serializable)

对于同一行记录,“写"会加"写锁”,“读"会加"读锁”.当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行;
我的事务尚未提交,别人就不能改我的数据;
最高的隔离级别,完全服从ACID的隔离级别.所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读.

事务的实现

持久性:指在事务完成之后,对数据的更改应该被永久保存,即使系统发生崩溃或故障也不会丢失.这一点通常可以通过**redo log(重做日志)**来实现,将事务执行的更改操作记录在日志中,当系统发生崩溃时可以通过日志恢复数据.
原子性:指事务中的所有操作要么全部执行成功,要么全部失败回滚,不会出现执行一部分操作而另一部分操作失败的情况.这一点通常可以通过**undo log(回滚日志)**来实现,记录事务执行前的数据状态,在回滚时可以通过undo log将数据恢复到事务执行之前的状态.
隔离性:指多个并发事务之间应该相互隔离,不应该互相干扰.这一点通常可以通过MVCC(多版本并发控制)或锁机制来实现,MVCC通过在数据库中保存多个版本的数据来实现隔离,而锁机制则通过对数据进行加锁来实现隔离.
一致性:指事务执行前后,数据库应该保持一致的状态,即保证事务执行前后数据的完整性和正确性.这一点可以通过持久性、原子性和隔离性来保证,只有当这三个条件都满足时,才能保证事务的一致性.

`Redo Log`

`undo log`

每当InnoDB中需要修改某个Record时,都会将其历史版本写入一个undo log中,对应的Undo Record是Update类型.
当插入新的Record时,还没有一个历史版本,但为了方便事务回滚时做逆向(Delete)操作,这里还是会写入一个Insert类型的Undo Record.
undo log 在MySQL中主要用于处理事务的回滚操作,存储的信息包括:
1. 对于所在数据页的定位信息,例如:该记录位于哪个数据页的哪个位置.
2. 记录修改的哪些列以及修改前的值.
3. 事务ID,即该修改操作所属的事务编号.
4. 操作类型,如删除、更新还是插入数据.
5. 物理页修改前的状态,即该修改操作执行前所在的数据页
当发生回滚操作的时候,undo log 中存储的信息会被用于恢复为回滚之前的状态.MySQL 通过读取 undo log 中改变前的记录,来将已经被修改的数据恢复到回滚之前的状态.在执行回滚操作时,MySQL 会反向遍历 undo log 中的操作,找到该事务执行的每个操作,并将操作的修改倒叙执行回去,从而得到执行回滚操作之前的数据状态.
需要注意的是,undo log 只存储已经被修改的记录,而没有记录未被修改的记录,因为未被修改的记录不需要进行回滚操作.此外,undo log 也可以用于 MVCC(多版本并发控制)的实现,当读取某个数据时,如果该数据已经被其他事务修改,则会读取该记录的 undo log 信息即旧值,而不会产生脏读的现象.
InnoDB将行记录快照保存在undo log里,可以在回滚段中找到.

`MVCC`

MVCC的英文全称为Multiversion Concurrency Control 即多版本并发控制技术.MVCC是通过数据行的多个版本管理来实现数据库的并发控制.
简单来说它的思想就是保存数据的历史版本,这样就可以通过比较版本号决定数据是否显示出来,读取数据的时候不需要加锁也可以保证事务的隔离效果.
一致性的非锁定读(consistent nonlocking read,即快照读)是指InnoDB存储引擎通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据.
- 如果读取的行正在执行DELETE或UPDATE操作,这时读取操作不会因此去等待行上锁的释放.相反的,InnoDB存储引擎会读取行的一个快照数据.

`MVCC`可以解决哪些问题

MySQL 引入MVCC(多版本并发控制)的原因是为了解决多个并发事务读写操作同一个数据时出现的数据不一致问题.在传统的锁机制下,会出现读取数据时需要等待其他事务释放锁才能进行操作的情况,而 MVCC 则提供了一个更加灵活和高效的处理方式.
读写之间阻塞的问题,通过MVCC可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提高事务的并发处理能力;
降低了死锁的概率,这是因为MVCC采用了乐观锁的方式,读取数据并不需要加锁,对于写操作,也只锁定必要的行;
解决一致性读的问题.一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前的事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果.
- 用于支持RC(Read Committed,读提交)和RR(Repeatable Read,可重复读)隔离级别的实现

快照读(一致性的非锁定读)/当前读(一致性的锁定读)

快照读读取的是快照数据,不加锁的简单SELECT都属于快照读.
- 快照数据是指该行的之前的数据,该实现是通过undo段来完成.而undo用来在事务中回滚数据,因此快照数据本身是没有额外的开销.
- 此外,读取快照数据是不需要上锁,因为没有事务需要对历史的数据进行修改操作.
- RC和RR事务隔离级别上对于快照数据的定义:
  - Read Committed事务隔离级别
    - 每次SELECT都生成一个快照读
    - 在RC事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新一份快照数据.
  - Read Repeatable事务隔离级别
    - 开启事务后第一个SELECT语句才是快照读的地方,而不是一开启事务就快照读
    - 在RR事务隔离级别下,对于快照数据,非一致性总数读取事务开始时的行数据版本.

当前读就是读取最新数据,而不是历史版本的数据.

加锁的SELECT或者对数据进行增删改都会进行当前读.

-- 读锁/共享锁(S锁)
SELECT * FROM test LOCK IN SHARE MODE;
-- 写锁/排他锁(X锁)
SELECT * FROM test FOR UPDATE;
INSERT INTO test VALUES ....;
DELETE FROM test WHERE ...;
-- 更新数据都是先读后写的
UPDATE test SET ...;

快照读就是普通的读操作,而当前读包括了加锁的读取和DML操作

不同隔离级别下的当前读

Read Uncommitted
Read Committed
- 针对当前读,RC隔离级别保证对读取到的记录加锁(记录锁),存在幻读现象;
Repeatable Read
- 针对当前读,RR隔离级别保证对读取到记录加锁(记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入(间隙锁),不存在幻读现象.
  
  尽管 RR 和 RC 隔离级别都实现了 MVCC 来满足读写并行,但是读的实现方式是不一样的:**RC 总是读取记录的最新版本,如果该记录被锁住,则读取该记录最新的一次快照,而 RR 是读取该记录事务开始时的那个版本.**虽然这两种读取方式不一样,但是它们读取的都是快照数据,并不会被写操作阻塞,所以这种读操作称为 快照读(Snapshot Read),有时候也叫做 非阻塞读(Nonlocking Read),RR 隔离级别下的叫做 一致性非阻塞读(Consistent Nonlocking Read).
Serializable
- 从MVCC并发控制退化为基于锁的并发控制,部分快照读与当前读,所有的读操作均为当前读,读加读锁(S锁),写加写锁(X锁).
- Serializable隔离级别下,读写冲突,因此并发度急剧下降.

`InnoDB`在不同的隔离级别下的一致性读及锁的差异

SQL	条件	READ UNCOMMITED	READ COMMITED	REPEATABLE READ	SERIALIZABLE
SELECT	相等	None Locks	Consistent Read/None Locks	Consistent Read/None Locks	Share Locks
	范围	None Locks	Consistent Read/None Locks	Consistent Read/None Locks	Share Next-Key Locks
UPDATE	相等	Exclusive Locks	Exclusive Locks	Exclusive Locks	Exclusive Locks
	范围	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks
INSERT	-	Exclusive Locks	Exclusive Locks	Exclusive Locks	Exclusive Locks
REPLACE	无键冲突	Exclusive Locks	Exclusive Locks	Exclusive Locks	Exclusive Locks
	有键冲突	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks
DELETE	相等	Exclusive Locks	Exclusive Locks	Exclusive Locks	Exclusive Locks
	范围	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks
SELECT…FROM… LOCK IN SHARE MODE	相等	Share Locks	Share Locks	Share Locks	Share Locks
	范围	Share Locks	Share Locks	Share Next-Key Locks	Share Next-Key Locks
SELECT…FROM… FOR UPDATE	相等	Exclusive Locks	Exclusive Locks	Exclusive Locks	Exclusive Locks
	范围	Exclusive Locks	Exclusive Locks	Exclusive Next-Key Locks	Exclusive Next-Key Locks

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

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

InnoDb中MVCC是如何实现的

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的,分别是DB_TRX_ID和DB_ROLL_PTR.
1. DB_TRX_ID:这个隐藏的列保存了当前行最新的更新事务ID,也就是最后一次修改该行数据的事务ID.在读取该行数据时,会根据当前事务的ID和这个字段的值进行比较,来确定该行数据是否可见.
2. DB_ROLL_PTR:这个隐藏的列保存了回滚指针,用于撤销行的修改操作.当一个事务开始修改一行数据时,InnoDB会将当前行的快照复制到回滚段中,并在当前行记录中保存一个指向回滚段中快照的指针.
在RR(可重复读)隔离级别,MVCC针对SELECT,INSERT,DELETE,UPDATE具体操作
- SELECT
  - InnoDB会根据以下两个条件检查每行记录:
    - InnoDB只查找早于当前版本的数据行(也就是行的事务版本号小于或等于当前事务的事务版本号),这样可以确保事务读取的行要么是在事务开始前已存在,要么是事务自身插入或修改过的.
    - 行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行,在事务开始之前未被删除.
  - 只有满足上述两个条件的记录,才能返回作为查询结果.
- INSERT: InnoDB会为每一行新插入的记录保存当前的事务版本号作为行版本号,以便后续事务可以根据版本号进行读取
- DELETE: InnoDB会为删除的每一行记录保存当前的事务版本号作为行删除标识,以便后续事务可以根据版本号进行读取
- UPDATE: InnoDB会为插入一行新记录保存当前事务版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识,以便后续事务可以根据版本号进行读取.

事务版本号

InnoDB里面每个事务有一个唯一的事务ID,叫做transaction id.它是在事务开始的时候向InnoDB的事务系统申请的,是按照申请顺序严格递增的.
每行数据也都是有多个版本的.每次事务更新数据的时候,都会生成一个新的数据版本,并且把transaction id赋值给这个版本的事务ID,记为row_trx_id.同时,旧的数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它.也就是说,数据表中的一行记录,其实可以有多个版本(row),每个版本有自己的row_trx_id

行记录的隐藏列

InnoDB的叶子段存储了数据页,数据页中保存了行记录,而在行记录中有一些重要的隐藏字段
- db_row_id:隐藏的行ID,用来生成默认聚集索引.若我们创建数据表的时候没有指定聚簇索引,这时InnoDB就会用这个隐藏ID来创建聚簇索引.采用聚簇索引的方式可以提升数据的查找效率;
- db_trx_id:操作这个数据的事务ID,也就是最后一个对该数据进行插入或更新的事务ID;
- db_roll_prt:回滚指针,也就是指向这个记录的undo log信息;

`Read View`是如何工作

在MVCC机制,多个事务对同一个行记录进行更新会产生多个历史快照,这些历史快照保存在undo log里.若一个事务想要查询这个行记录,想要读取哪个版本的行记录呢?
- 这时需要用到ReadView ,它可以帮助我们解决行的可见性问题.ReadView保存了当前事务开启时所有活跃(还没提交)的事务列表,也可以理解为ReadView保存了不应该让这个事务看到的其他的事务ID列表.

在Read View中有几个重要的属性:

trx_ids: 当前未提交的事务ID集合
low_limit_id:未提交的事务中最大的事务ID,表示高水位标记,用于表示当前事务可见的最早的系统版本号,它是由当前系统版本号减去事务ID列表中最大的已提交的事务ID得到的.
up_limit_id:未提交的事务中最小的事务ID,表示低水位标记,用于表示当前事务尚未可见的最新的系统版本号,它是由事务ID列表中最小的未提交的事务ID减去1得到的.
creator_trx_id:创建这个Read View的事务ID,用于标识该Read View所属的事务.

// storage/innobase/include/read0types.h
 /** The read should not see any transaction with trx id >= this
 value. In other words, this is the "high water mark". */
 trx_id_t m_low_limit_id;
  
 /** The read should see all trx ids which are strictly
 smaller (<) than this value.  In other words, this is the
 low water mark". */
 trx_id_t m_up_limit_id;
  
 /** trx id of creating transaction, set to TRX_ID_MAX for free
 views. */
 trx_id_t m_creator_trx_id;

示例:如下图,trx_ids为trx2,trx3,trx5和trx8的集合,活跃的最大事务ID(low_limit_id)为trx8,活跃的最小的事务ID(up_limit_id)为trx2

假设当前有事务creator_trx_id想要读取某个行记录,这个行记录的事务ID为trx_id,那么会出现以下几种情况:

如果trx_id<未提交最小的事务ID(up_limit_id),也就是说这个行记录在这些活跃的事务创建之前就已经提交了,那么这个行记录对该事务是可见的.
通过trx_id>=未提交最大的事务ID(low_limit_id),这说明该行记录在这些活跃的事务创建之后才创建,那么这个行记录对当前事务不可见.
如果up_limit_id<trx_id<low_limit_id,说明该行记录所在事务trx_id在目前creator_trx_id这个事务创建的时候,可能还处于活跃的状态,因此需要在trx_ids集合中进行遍历,
- 如果trx_id存在于trx_ids集合中,证明这个事务trx_id还处于活跃状态,不可见.
- 如果trx_id不存在与trx_ids集合中,证明事务trx_id已经提交了,该行记录可见.

 // storage/innobase/include/read0types.h
/** Check whether the changes by id are visible.
 @param[in]    id      transaction id to check against the view
 @param[in]    name    table name
 @return whether the view sees the modifications of id. */
 [[nodiscard]] bool changes_visible(trx_id_t id,
                                    const table_name_t &name) const {
   ut_ad(id > 0);
  
   // 如果ID小于Read View中的最小事务ID,则这条记录是可见的,说明这条记录在select这个事务之前就结束了
   if (id < m_up_limit_id || id == m_creator_trx_id) {
     return (true);
   }
  
   check_trx_id_sanity(id, name);
  
   // 如果ID大于Read View中的最大事务ID,则说明这条记录在事务开始之后进行修改,所以此条记录不可见
   if (id >= m_low_limit_id) {
     return (false);
  
   } else if (m_ids.empty()) {
     return (true);
   }
  
   const ids_t::value_type *p = m_ids.data();
  
   // 判断是否在Read View中,如果在,则说明在创建Read View时,此条记录还处于活跃状态,不应该被查询到;否则,说明在创建Read View时此条记录已经不处于活跃状态,可以被查询到
   return (!std::binary_search(p, p + m_ids.size(), id));
 }

查询一条记录的时候,系统如何通过多版本并发控制技术找到它:
- 首先获取事务自己的版本号,也就是事务ID;
- 获取Read View
- 查询得到的数据,然后与Read View中的事务版本号进行比较;
- 如果不符合Read View规则,就需要从undo log中获取历史快照;
- 最后返回符合规则的数据;
在InnoDB中,MVCC是通过undo log+Read View进行数据读取,undo log保存了历史快照,而Read View规则帮助我们判断当前版本的数据是否可见.
需要说明的是,在隔离级别为读已提交(Read Commit)时,一个事务中的每一次SELECT查询都会获取依次Read View.
- 在READ-COMMITTED读已提交的隔离级别下,同时的查询语句都会重新获取一次Read View,这是如果Read View不同,就可能产生不可重复读或者幻读的情况.
当隔离级别为REPEATABLE-READ可重复读的时候,就避免了不可重复读,这是因为一个事务只在第一次的SELECT的时候获取依次Read View,而在后面的所有SELECT都会复用这个Read View.

`InnoDB`是如何解决幻读的

在可重复读的情况下,InnoDB通过快照读和间隙锁来解决幻读问题.
在读已提交的情况下,即使采用了MVCC方式也会出先幻读.
- 若同时开始事务A和事务B,现在事务A中进行某个条件范围的查询,读取的时候采用排它锁,在事务B中加一条符合条件范围的数据,并进行提交,然后我们在事务A中再次查询该条件范围的数据,就会发现结果集中多出了一个符合条件的数据,这样就出现了幻读.
出现幻读的原因是在读已提交的情况下,InnoDB只采用记录锁(Record Locking)
- 记录锁:针对单个行记录添加锁
- 间隙锁(Gap Locking): 可以帮我们锁住一个范围(索引之间的空隙),但不包括记录本身.采用间隙锁的方式可以防止幻读情况的产生.
- Next-Key锁:帮我们锁住了一个范围,同时锁定记录本身,相当于间隙锁+记录锁,可以解决幻读的问题.

MySQL更新语句的执行流程

更新流程主要涉及两个重要的日志模块
- redo log(重做日志)
- binlog(归档日志)

`update`语句的执行流程

1	update T set c=c+1 where ID=2;

执行器先找到引擎ID=2这一行.ID主键,引擎直接用树搜索找到这一行.如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则需要先从磁盘读如内存,然后再返回;
执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据;
当数据被修改时,系统将原始数据块写入undo log中,以便于之后的回滚操作.
引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态.然后告知执行器完成了,随时可以提交事务;
执行器生成这个操作的binlog,并把binlog写入磁盘;
执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成;

`binlog`

binlog是MySQL数据库中的日志记录机制之一,可用于实现数据恢复、主从复制以及增量备份等操作.其主要作用包括:
1. 数据恢复:binlog记录了数据库的所有更新操作,包括插入、更新和删除等操作,因此可以用于数据恢复.例如,如果一条误删除数据的SQL语句被执行了,可以根据从binlog中找到该操作的时间戳等关键信息来还原数据.
2. 主从复制:binlog也是MySQL中用于实现主从复制的关键机制之一.在主数据库上,binlog记录了所有写操作的信息,可以将这些信息重新应用到从服务器中,从而保持所有数据的一致性.
3. 增量备份:binlog可用于实现MySQL逐增备份(incremental backup)的机制.通过备份存储在binlog文件中的更新操作,可以在每次备份中仅备份新增的数据,从而提高备份效率.
4. 审计与安全:binlog中包含了大量的数据库操作信息,例如执行每个更新操作的时间和用户等信息,在数据库审计和安全方面具有重要作用.
总之,由于binlog记录了MySQL数据库中所有的写操作信息,因此可以用于数据库的数据恢复、主从复制和备份等方面.同时,binlog也可以用于数据库的安全审计和追踪.
redo log是InnoDB引擎特有的日志,而Server层的日志称为binlog(归档日志);
MySQL的binlog记录的是数据库的更新操作,它可以用来进行数据恢复、主从复制以及增量备份等操作.具体而言,binlog会存储以下信息:
1. 更新语句的类型:INSERT、UPDATE或DELETE.
2. 被更新的表的名称和库名.
3. 更新前和更新后的数据值.
4. 更新的时间戳和事务ID.
5. 执行更新操作的线程ID.
6. MySQL所使用的字符集.
7. 是否存在外键依赖关系.
sync_binlog这个参数设置为1的时候,表示每次事务的binlog,表示每次事务的binlog都持久化磁盘.这个参数建议设置为1,这样可以保证MySQL异常重启之后binlog不丢失.
binlog有两种模式statement格式是记录SQL语句,row格式会记录行的内容,记录两条,更新前和更新后都有.
- statement模式下,每一条会修改数据的sql都会记录在binlog中.不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
- row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改.记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
- mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

两阶段提交

为什么必须有两阶段提交呢?这是为了让两份日志(redo log 和binog)之间的逻辑一致.
如果不使用两阶段提交,假设当前ID=2的行,字段c的值是0,再假设执行update语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了crash,会出现什么情况?
- 先写redo log后binlog.假设redo log写完,binlog还没有写完的时候,MySQL进程异常重启.redo log写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复这一行c的值是1.但是由于binlog没写完就crash了,这时候binlog里面没有记录这个语句.因此,之后备份日志的时候,存起来的binlog里面就没有这条语句.如果需要用这个binlog来恢复临时库的话,由于这个语句的binlog丢失,这个临时库就会少了这一次更新,恢复出来的这一行c的值就是0,与原库的值不同.
- 先写binlog后写redo log.若binlog写完之后crash,由于redo log还没写,崩溃恢复以后这个事务无效,所以这一行c的值是0.但是binlog里面已经记录了"把c从0改成1"这个日志.所以,在之后用binlog来恢复的时候就多了一个事务出来,恢复出来的这一行c的值就是1,与原库的值不同.
redo log和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这个两个状态保持逻辑上的一致.
崩溃恢复时的判断规则:
- 如果redo log里面的事务是完整的,也就是已经有了commit,则直接提交;
- 如果redo log里面的事务只有完整的prepare,则判断对应的事务binlog是否存在并完整:
  - 若完整,则提交事务;
  - 若不完整,则回滚事务.

Session          Server          Binary Log         Engine
  |                |                  |                |
  |   COMMIT       |                  |                |
  |--------------->|                  |                |
  |                |  							prepare            |
  |                |------------------|--------------->|
  |                |  								OK               | 
  |                |<-----------------|----------------|
  |                |  	write         |                |
  |                |----------------->|                |
  |                | 		 OK           |                |
  |                |<-----------------|                |
  |                |    fsync         |                |
  |                |----------------->|                |
  |                |     OK           |                |
  |                |<-----------------|                |
  |                |  							commit             |      
  |                |------------------|----------------|      
  |                | 								  OK               |    
  |                |<-----------------|----------------| 
  |      OK        |                  |                |
  |<---------------|                  |                |
  |                |                  |                |

判断`binlog`是否完整

一个事务的binlog是有完整格式的:
- statement格式的binlog,最后会有COMMIT;
- row格式的binlog,最后会有一个XID event;
另外,在MySQL5.6.2版本以后,还引入了binlog-checksum参数,用来验证binlog内容的正确性.对于binlog日志由于磁盘原因,可能会在日志中间出错的情况,MySQL可以通过校验checksum的结果来发现.

`redo log`和`binlog`是如何关联起来的

它们有一个共同的数据字段,叫做XID.崩溃恢复的时候,会顺序扫描redo log:
顺序扫描redo log
- 如果发现 redo log 中既有 prepare 又有 commit 的记录，则直接提交该事务。
- 如果发现 redo log 中只有 prepare，而没有 commit 的记录，则说明事务在提交过程中崩溃，需要进一步检查 binlog。
检查binlog
- 拿着 XID 去 binlog 中查找对应的事务记录。
- 如果在 binlog 中找到该 XID 对应的事务记录，说明事务已经提交，但 redo log 中的 commit 记录未写入，此时需要提交该事务。
- 如果在 binlog 中找不到该 XID 对应的事务记录，说明事务未提交，需要回滚该事务。

参考文献

ACID

事务

事务常用控制语句

事务的启动

查询MySQL全局事务隔离级别

查询当前会话事务隔离级别

设置当前会话的隔离级别

隔离级别与锁的关系

MySQL和Oracle默认隔离级别

对事务操作的统计

隐式提交事务的SQL语句

事务中常出现的问题

脏读(Dirty Read)

不可重复读(Non-repeatable Read)

幻读(Phantom Read)

丢失更新(lost update)

SQL标准的事务隔离级别

读未提交(Read Uncommitted)

读提交(Read Committed)

可重复读(Repeatable Read)

串行(Serializable)

事务的实现

Redo Log

undo log

MVCC

MVCC可以解决哪些问题

快照读(一致性的非锁定读)/当前读(一致性的锁定读)

不同隔离级别下的当前读

InnoDB在不同的隔离级别下的一致性读及锁的差异

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

InnoDb中MVCC是如何实现的

事务版本号

行记录的隐藏列

Read View是如何工作

InnoDB是如何解决幻读的

MySQL更新语句的执行流程

update语句的执行流程

binlog

两阶段提交

判断binlog是否完整

redo log和binlog是如何关联起来的

`ACID`

`Redo Log`

`undo log`

`MVCC`

`MVCC`可以解决哪些问题

`InnoDB`在不同的隔离级别下的一致性读及锁的差异

`Read View`是如何工作

`InnoDB`是如何解决幻读的

`update`语句的执行流程

`binlog`

判断`binlog`是否完整

`redo log`和`binlog`是如何关联起来的