MySql事务

1. 事务的基础概念及ACID特性

1.1 什么是事务

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。作为一个关系型数据库，MySQL支持事务。

1.2 事务的四大特性（ACID）

原子性（Atomicity）：事务是最小的执行单位，不可再分割。事务的操作要么全部成功，要么全部失败回滚。
一致性（Consistency）：事务执行前后，数据库始终处于一致的状态。比如资金转账时，两方账户余额的总和不变。
隔离性（Isolation）：事务的并发执行不会相互影响，保证每个事务的操作对其他事务不可见。不同的隔离级别会影响事务的隔离性。
持久性（Durability）：事务一旦提交，所做的修改会永久保存，即使系统崩溃也能恢复。

1.3 隐式事务和显式事务

隐式事务：在MySQL中，单条INSERT、UPDATE等语句会自动启动一个事务并在执行完成后提交，这就是隐式事务。适用于简单的操作。
- 示例：
  1
  INSERT INTO accounts (name, height) VALUES ('Alice', 172);
  实际上相当于：
  1
  2
  3
  BEGIN; INSERT INTO accounts (name, height) VALUES ('Alice', 172); COMMIT;

显式事务：显式事务通过BEGIN...COMMIT手动控制事务的开始和结束，用于多步操作的事务管理。

示例：

BEGIN;
INSERT INTO accounts (name, height) VALUES ('Alice', 172);
UPDATE accounts SET height = height / 100 WHERE name = 'Alice';
COMMIT;

2. 事务隔离级别及并发问题

2.1 并发问题

脏读：事务读取了另一个事务尚未提交的数据，导致数据不一致。
不可重复读：同一个事务在两次读取时发现数据不同，主要由其他事务的UPDATE引起。
幻读：在一次事务中，多次读取后发现数据的行数变化，通常由INSERT操作引起。

2.2 事务隔离级别

SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

读未提交（Read Uncommitted）：允许脏读，事务可以看到其他未提交事务的更改。
读已提交（Read Committed）：只能看到其他事务已提交的数据，避免脏读，但会出现不可重复读。
可重复读（Repeatable Read）：在同一事务中多次读取结果一致，避免不可重复读；MySQL InnoDB引擎默认隔离级别。
串行化（Serializable）：最高级隔离级别，完全串行执行事务，确保绝对一致性。

在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读。可通过命令transaction_isolation查看全局隔离级别和本次会话隔离级别。

MySQL 8.0 默认的隔离级别是可重复读 (REPEATABLE-READ)。

2.3 实现原理：MVCC（多版本并发控制）

概念: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

在内部实现中，InnoDB通过undo log保存每条数据的多个版本，并且能够找回数据历史版本提供给用户读，每个事务读到的数据版本可能是不一样的。在同一个事务中，用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。
原理：使用版本链（基于undo log实现）+Read View
MVCC机制：通过保存数据的多个版本来实现并发控制。MVCC为每一行数据维护创建版本号和删除版本号，使事务可以“读取”指定版本的数据，保证并发控制。
不同隔离级别下的实现：
- 读已提交：每次读取最新的提交版本，因此不同时间点读取可能出现不一致。（每个select都会创建最新的ReadView）
- 可重复读：在事务开始时创建一致性视图，确保整个事务期间读取的数据版本不变，避免不可重复读。（当事务中的第一个select请求才创建ReadView）
- 读未提交、串行化：不兼容

2.3.1 事务版本号

事务每次开启时，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。每当begin的时候，首先要做的就是从数据库获得一个自增长的事务ID,它也就是当前事务的事务ID。

2.3.2 隐藏字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer,如果数据表中存在主键或者非NULL的UNIQUE键时不会创建row_id,否则InnoDB会自动生成单调递增的隐藏主键row_id。

列名	是否必须	描述
row_id	否	单调递增的行ID，不是必需的，占用6个字节。这个跟MVCC关系不大
trx_id	是	记录操作该行数据事务的事务ID
roll_pointer	是	回滚指针，指向当前记录行的undo log信息

这里的记录操作，指的是insert|update|delete。对于delete操作而言，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted,并非真正删除。

2.3.3 版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链（undo log）。

2.3.4 快照读和当前读

快照读：读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读，如：

1	`select * from user where id = 1;`

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

1 2	`select * from user where id = 1 for update; select * from user where id = 1 lock in share mode;`

2.3.5 ReadView

ReadView 保存了当前事务开启时所有活跃的事务列表。可以理解为: ReadView 保存了不应该让这个事务看到的其他事务 ID 列表。

ReadView 的几个重要属性

trx_ids: 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。(重点注意:这里的trx_ids中的活跃事务，不包括当前事务自己和已提交的事务，这点非常重要)
low_limit_id: 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
up_limit_id: 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
creator_trx_id: 表示生成该 ReadView 的事务的事务id

访问某条记录的时候如何判断该记录是否可见,具体规则如下：

如果被访问版本的 事务ID = creator_trx_id，那么表示当前事务访问的是自己修改过的记录，那么该版本对当前事务可见；
如果被访问版本的 事务ID < up_limit_id，那么表示生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 事务ID > low_limit_id 值，那么表示生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的 事务ID在 up_limit_id和m_low_limit_id 之间，那就需要判断一下版本的事务ID是不是在 trx_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

何时创建ReadView？

在RC隔离级别下，是每个 select 都会创建最新的 ReadView；而在RR隔离级别下，则是当事务中的第一个 select 请求才创建 ReadView。

对于 insert，update，delete 这样的操作不会创建ReadView。但是这些操作在事务开启(begin)且其未提交的时候，那么它的事务ID,会存在在其它存在查询事务的ReadView记录中，也就是trx_ids中。

2.4 锁

在MySQL中，锁机制可以细分为表锁、行锁、全局锁、死锁等，不同的锁类型适用于不同的并发控制场景。

2.4.1 表锁

使用语法：LOCK TABLE table_name READ/WRITE
SELECT操作自动加读锁，UPDATE/DELETE/INSERT操作自动加写锁。
读锁：如果表加了读锁，允许其他线程进行读取，但会阻塞写操作。
写锁：如果表加了写锁，读和写操作都会被阻塞，直到写锁释放。

2.4.2 行锁

锁的类型

间隙锁（Gap Lock）
- 作用：锁定某个区间范围，防止幻读。只在**可重复读（Repeatable Read）**隔离级别下生效。
- 范围：左开右开区间，阻止多个事务在同一范围内插入记录，避免幻读。
记录锁（Record Lock）
- 作用：锁定行记录（即索引的某个值）。
- 特性：只对有索引的行有效；如果没有索引，则可能锁定整张表（表锁）。
临键锁（Next-Key Lock）
- 作用：结合了记录锁和间隙锁，锁定指定行记录及其前的间隙范围，以防止幻读。
- 范围：左开右闭，即锁定记录和前面的间隙。

锁的模式

SELECT ... FOR UPDATE：加写锁，其他事务不能加读锁或写锁。
SELECT ... LOCK IN SHARE MODE：加读锁，其他事务可加读锁，但不可加写锁。
共享锁（S锁）：读锁，多个事务可以同时持有。
排他锁（X锁）：写锁，其他事务无法持有读锁或写锁。
意向锁：
- 意向读锁和意向写锁，为表锁提供更高效的锁定机制。
自增锁：专为自增操作而设置的锁。

2.4.3 全局锁 - 全库逻辑备份

MySQL支持全局锁用于全库级别的操作，常用于逻辑备份，确保数据在备份期间的一致性。

2.4.4 死锁

定义：多个事务在相同资源上相互加锁并等待，形成无限的循环等待。
InnoDB处理：自动回滚持有最少排他行级锁的事务来解除死锁。
锁等待超时：可以设置锁等待时间，避免长时间的死锁。

2.4.5 乐观锁与悲观锁

悲观锁：基于数据库的内置锁机制，适用于写多的场景。
乐观锁：基于 version 版本号或 CAS（比较并交换）算法，适用于读多写少、冲突较少的情况。

3. 三大日志

3.1 redo log

作用：redo log 是物理日志，记录了数据页的变更，用于持久化事务的变更，保障崩溃后数据可恢复。（持久性）
与binlog的区别：redo log 是 InnoDB 特有的，用于崩溃恢复；binlog 是逻辑日志，记录 SQL 语句，用于恢复和复制。
相关配置：innodb_flush_log_at_trx_commit控制日志刷新时机，可以在性能和持久性之间平衡。
- 0：设置为0的时候，每次提交事务时不刷盘。（刷盘是指将数据从内存写入持久存储设备）这种方式性能较高，但在崩溃时可能导致最近的事务丢失。
- 1：设置为1的时候，每次提交事务时刷盘。这是最安全的设置，确保数据的持久性，但性能可能较低，因为每次提交都会导致一次磁盘写入。
- 2：设置为2的时候，每次提交事务时都只把 redo log buffer 写入 page cache。这种方式在性能上介于0和1之间，但在系统崩溃时可能导致数据丢失。
物理日志
- 作用：会记录事务开启后对数据做的修改，crash-safe
- 特性：空间一定，写完后会循环写，有两个指针write pos指向当前记录位置，checkpoint指向将擦除的位置，redolog相当于是个取货小车，货物太多时来不及一件一件入库太慢了这样，就先将货物放入小车，等到货物不多或则小车满了或则店里空闲时再将小车货物送到库房。用于crash-safe，数据库异常断电等情况可用redo log恢复。

3.1.1 刷盘时机

innodb_flush_log_at_trx_commit参数默认为1，当事务提交的时候会调用 fsync(fsync 是一个系统调用，用于将文件描述符对应的文件数据从内存缓冲区刷新到持久存储设备中)对 redo log 进行刷盘，将 redo log buffer，page cache写入 redo log 文件中。

另外，Innodb 存储引擎有一个后台线程，每隔1秒，就会把会 redo log buffer 中的内容写入到文件系统缓存 page cache，然后调用 fsync 刷盘。

如上图，所以说一个没有提交事务的 redo log 记录，也会被刷盘。

innodb_flush_log_at_trx_commit = 0

如上图，如果宕机了或者 MySQL 挂了可能造成1秒内的数据丢失。

innodb_flush_log_at_trx_commit = 1

如上图，只要事务提交成功，redo log 记录就一定在磁盘里，不会有任务数据丢失。

如果执行事务的时候 MySQL 挂了或者宕机了，这部分日志丢失了，但是因为事务没有提交，所以日志丢了也不会有损失。

innodb_flush_log_at_trx_commit = 2

如上图，当事务提交成功时，redo log buffer 日志会被写入 page cache，然后后台线程会刷盘写入 redo log，由于后台线程是1秒执行一次所以宕机或者 MySQL 挂了可能造成1秒内的数据丢失。

3.2 binlog

作用：用于 MySQL 主从复制及数据恢复。记录所有的 DDL 和 DML 语句，可以将数据恢复到指定时间点。
相关配置：sync_binlog控制 binlog 写入磁盘的时机。
- 0：binlog 不会被强制写入磁盘。只有在操作系统决定写入缓存时，binlog 才会被写入。这种设置能提高性能，但在系统崩溃时可能会导致最近的事务丢失。
- 1：每次事务提交时，binlog 会被刷新到磁盘。这是最安全的设置，确保数据的持久性，但可能会影响性能，因为每次提交都需要进行磁盘写入。
- N：每次事务提交时，binlog 只会写入到内存中的缓存，但不会立即刷到磁盘。积累N个事务后才会写入磁盘。这种设置在性能介于0和1之间，但仍然可能在系统崩溃时导致数据丢失。
逻辑日志
- 记录的是“在某个数据页做了什么修改”，属于 Innodb 存储引擎。
- 属于 MySQL Server 层，所有存储引擎在执行 DDL 和 DML 操作时都会产生 binlog。
  
  DDL（数据定义语言），数据库和表的创建、修改和删除。
  DML（数据操作语言），主要涉及数据的插入、更新和删除。

3.2.1 记录格式

binlog 日志有三种格式，可以通过binlog_format参数设置，有以下三种：

statement
- 说明：记录每个 SQL 语句（DDL 和 DML），而不是每个数据更改的具体内容。
- 优点：日志文件较小，便于理解。
- 缺点：在某些情况下可能导致不一致性。（例如，非确定性函数或使用了外部状态的语句）。
例如：

设置 statement 记录的内容是 SQL 语句原文，比如执行一条update T set update_time = now() where id = 1，记录内容如下：

同步数据时，会执行记录的 SQL 语句，但是有个问题update_time = now()这里会获取到当前系统问题。如果从库在执行相同的 SQL 时，调用的now()返回的是从库的当前时间（可能与主库不同），那么从库中的update_time将与主库的时间戳不一致。
row
- 说明：记录每一行数据的具体变化（在上一个例子中，从库不会再次做now()，而是直接拿到变换的值），而不是 SQL 语句。记录的内容看不到详细信息，通过 mysqlbinlog 工具解析出来。
- 优点：更精确，确保复制的一致性，适用于所有类型的数据修改。
- 缺点：日志文件较大，因为每次数据更改都会记录具体的行数据。并且在 statement 格式中，相同的 SQL 语句可以共享相同的操作信息，而在 row 格式中，即使是相同的操作也需要记录每一行的具体变化，会导致冗余信息。
mixed
- 说明：MySQL 会判断这条 SQL 语句是否会引起数据不一致，如果是就用 row 格式，否则就用 statement 格式。
- 优点：在大多数情况下提供了性能和一致性之间的良好平衡。
- 缺点：可能会增加配置和理解的复杂性。

怎么配置

要设置 binlog_format，可以在 MySQL 配置文件（如 my.cnf）中进行设置，或在运行时通过 SQL 命令进行设置：

1	`SET GLOBAL binlog_format = 'ROW';`

3.2.2 写入机制

binlog 的写入时机为事务执行过程中，先把日志写到 binlog cache，事务提交的时候再把 binlog cache 写到 binlog 文件中（实际先会写入 page cache，然后再由 fsync 写入 binlog 文件）。

因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一块内存作为 binlog cache。可以通过binlog_cache_size参数控制单线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘。

write 和 fsync 的时机可以由参数sync_binlog控制，可以配置成0、1、N(N>1)。

设置成0时：表示每次提交事务都只会 write，由系统自行判断什么时候执行 fsync。
设置成1时：表示每次提交事务都会执行 fsync，就和 redo log 日志刷盘流程一样。
设置成N时：表示每次提交事务都会 write，但是积累N个事务后才 fsync。

3.3 undo log

作用：undo log用于事务回滚，同时实现一致性视图。记录了事务操作前的“旧数据”。（原子性）如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。
内容：事务每次对数据进行修改时，会在undo log中记录修改前的数据，用于事务回滚和实现MVCC的读视图（版本链）。

当事务对数据库进行修改时，InnoDB 会生成对应的undo log，undo log 会保存事务开始前老版本的数据，当事务发生异常，便会rollback 回滚到老版本状态。当发生回滚时，InnoDB 会根据undo log的内容做相反逻辑操作。

insert 语句，回滚时会执行 delete;
delete 语句，回滚时会执行 insert;
update 语句，回滚时便执行相反的 update，把数据改回来。

4. 二阶段提交

目的：协调 redo log 和 binlog 的一致性问题。MySQL 在事务提交时先写 redo log 再写 binlog，避免部分提交情况。
两阶段提交过程：
1. 准备阶段：将 redo log 标记为“准备提交”。
2. 提交阶段：提交 binlog，并将 redo log 的“准备提交”标记改为“已提交”，确保日志一致。
怎么解决 redo log 和 binlog 的一致性问题

将 redo log 日志的写入拆分成两个步骤 prepare 和 commit。MySQL 根据 redo log 日志恢复数据时，发现 redo log 日志处于 prepare 阶段，并且没有对应 binlog 日志（根据事务id对应），所以就会回滚事务。