Mysql事务（详解）

一.基本感念

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

首先再回顾一下MySQL事务的基础知识。

1.逻辑架构和存储引擎

如上图所示，MySQL服务器逻辑架构从上往下可以分为三层：

（1）第一层：处理客户端连接、授权认证等。

（2）第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。

（3）第三层：存储引擎，负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务，事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。

2.事务的特性

ACID是衡量事务的四个特性

原子性（Atomicity）要么全部成功，要么全部失败，不可能执行一部分操作

一致性（Consistency）事务在执行之前和执行之后，数据库的状态必须保持一致

隔离性（Isolation）通常来说一个事务在完成提交之前，对其他事务是不可见的

持久性（Durability）一旦事务提交，他对数据库中的数据的改变就是永久不变。

特性	例子	结果
原子性	张三给李四转账100元，扣减张三账户余额和增加李四账户余额是两个操作	要么两个操作都成功，要么两个操作都失败
一致性	张三给李四转账100元，转账前后两人账户总额不变	转账成功后，张三账户少了100元，李四账户多了100元
隔离性	张三给李四转账100元，同时王五查询李四账户余额	王五只能查询到转账前或转账后的余额，不能查询到中间状态
持久性	张三给李四转账100元，转账成功后数据库崩溃	数据库恢复后，李四仍然能看到多了100元

二.InnoDB 事务的ACID如何保证

（1）基本概念

1. 重做日志 (Redo Log): 重做日志是一种用于持久化数据更改的机制。在MySQL中，重做日志以循环方式由多个文件（例如 ib_logfile0 和 ib_logfile1）组成，每个文件的大小通常为50MB。重做日志记录了对数据库进行的物理更改，例如插入、更新和删除操作。

2. Redo Log Buffer: 重做日志缓冲区是一个位于内存中的区域，用于临时存储正在进行的事务的重做日志条目。当事务执行修改操作时，相关的重做日志条目会先被写入重做日志缓冲区，然后再由后台线程异步地刷新到磁盘的重做日志文件中。

3. InnoDB 存储引擎: InnoDB是MySQL中一种常用的存储引擎，它负责管理数据行和索引的存储。每个表在InnoDB中对应一个以 .ibd 后缀结尾的文件，其中包含了存储在磁盘上的数据行和索引。

4. Buffer Pool: 缓冲池是InnoDB在内存中缓存数据库中的数据和索引页的区域。它是一个用于高效读取和写入数据的内存区域。当需要读取或写入数据时，InnoDB首先会检查缓冲池中是否存在对应的数据页，如果存在则直接操作内存中的数据页，提高访问速度。

5. LSN (Log Sequence Number): 日志序列号是用于标识和跟踪重做日志的顺序的唯一递增值。LSN用于确保数据的一致性，并在MySQL启动时进行检查，以确保磁盘上的数据与重做日志的内容一致。

6. WAL (Write Ahead Log): WAL是一种日志优先写的策略，确保在将数据页刷新到磁盘之前，相关的日志先被持久化到磁盘中。这种策略可以保证在发生系统故障时，可以通过重做日志将数据恢复到故障前的一致状态。

7. 脏页 (Dirty Page): 脏页是指在内存中已经被修改过但尚未写入磁盘的数据页。当数据被修改后，相应的内存页会被标记为脏页，这样在后续的时间里，系统可以将其异步刷写到磁盘，以保持内存和磁盘之间的数据一致性。

8. Checkpoint (CKPT): 检查点是指定时将内存中的脏页刷写到磁盘的操作。通过执行检查点，可以将脏页的修改持久化到磁盘，以减少系统故障时的数据损失。

9. TXID (Transaction ID): 事务号是InnoDB为每个事务分配的唯一标识符。事务号伴随整个事务的执行，用于跟踪和管理事务的一致性。

(2）redo log

（1）redo log是什么

REDO LOG（重做日志）是MySQL中一种用于持久化数据修改的机制。它记录了对数据库进行的物理更改操作，如插入、更新和删除。

重做日志的主要目的是确保在发生数据库崩溃、系统故障或意外断电等情况下，数据库可以恢复到最后一次正常运行时的状态。通过重做日志，MySQL可以在数据库重新启动时应用那些尚未写入磁盘的已提交事务的修改，从而保持数据的一致性。

重做日志是以循环方式组织的，通常由多个日志文件组成（如 `ib_logfile0`、`ib_logfile1` 等）。每个日志文件的大小通常设定为一定的固定值（如50MB）。当一个文件被填满后，新的写入会继续在下一个日志文件中进行，这样就实现了循环使用。

在事务进行数据修改时，相关的重做日志记录会先写入内存中的重做日志缓冲区（redo log buffer），然后由后台线程异步地将其刷新到磁盘上的重做日志文件中。这种异步刷新的方式能够提高数据库的性能，并减少对磁盘的频繁写入。

当MySQL数据库重新启动时，会检查磁盘上最后一个被写入的重做日志文件及其偏移量。系统会根据这些信息确定从哪个点开始进行崩溃恢复，将未应用的重做日志中的修改重新应用到数据库，以确保数据的完整性和一致性。

（2）redo日志位置

在MySQL中，重做日志（Redo Log）的位置在数据目录中的日志文件中。具体位置取决于你的MySQL配置和操作系统。

默认情况下，重做日志文件名为 `ib_logfile0` 和 `ib_logfile1`，它们位于数据目录下。数据目录的路径在MySQL配置文件（通常是 `my.cnf` 或 `my.ini`）中的 `datadir` 参数中指定。

你可以通过登录到MySQL服务器并执行以下命令来查找重做日志文件的位置：

SHOW VARIABLES LIKE 'datadir';

执行此命令会显示MySQL数据目录的路径，你可以在该路径下找到 `ib_logfile0` 和 `ib_logfile1` 文件。

注意，根据你的MySQL配置，可能还有其他的重做日志文件，它们可能命名为 `ib_logfile2`、`ib_logfile3` 等。这些文件都是循环使用的，当一个文件被填满后，MySQL会继续使用下一个文件，以此类推。

（3）redo buffer

重做缓冲区（Redo Buffer）是一个用于暂时存放数据页的变化信息和对应LSN号的内存区域。它是用于临时存储正在进行的事务的重做日志记录的地方。它可以帮助提高数据库系统的性能。而LSN是一种用于唯一标识数据库日志记录的序列号，用于跟踪和管理数据库的一致性和持久性。

    LSN（日志序列号）是一个递增的唯一标识符，用于标记数据库的每个日志记录。LSN可以在数据库中的多个地方使用，包括磁盘上的数据页、内存中的数据页、redo buffer和redo log（重做日志）。通过比较LSN的值，数据库系统可以确定哪些日志记录已经被应用到数据页，以及哪些日志记录还没有。

在事务执行期间，如果有数据修改操作（例如插入、更新或删除），相应的重做日志条目会首先写入到重做缓冲区中，然后再由后台线程异步地将它们刷新到磁盘上的重做日志文件中。

重做缓冲区的设计有助于提高数据库的性能和并发处理能力。将重做日志条目首先写入内存中的缓冲区，可以避免频繁地访问和写入磁盘，从而提高了数据修改操作的处理速度。

此外，重做缓冲区还是实现数据库事务的原子性和持久性的关键组件之一。在事务提交时，MySQL会将重做缓冲区中的相关日志条目写入到磁盘的重做日志文件中，以确保已提交的事务的修改持久化到磁盘，从而保证数据的一致性和持久性。

需要注意的是，重做缓冲区的大小是有限的，如果正在进行的事务写入的重做日志超过了缓冲区的容量，MySQL就会进行强制性的刷新操作，以确保重做日志能够持续写入到磁盘上的重做日志文件中。

（4）redo的刷新策略

在数据库系统中，为了确保事务的持久性（durability）和原子性（atomicity），会使用事务日志来记录每个事务的操作。当事务进行提交（commit）操作时，系统会将事务日志中的变更（redo buffer）刷新（flush）到磁盘中的持久存储介质，通常是硬盘。

刷新redo buffer的目的是将已经提交的事务持久化到磁盘，以确保在任何系统崩溃或断电的情况下，可以通过日志重放（log replay）的方式将未来的数据状态重新恢复到崩溃之前的一致状态。

在commit时，除了刷新当前事务的redo log buffer到磁盘外，还会顺便将一部分redo log buffer中没有提交的事务日志也刷新到磁盘。这是为了提高性能和减少磁盘IO操作。但是这些没有提交的事务日志并不会影响数据的一致性，因为它们都有对应的事务号和LSN（日志序列号），在MySQL启动时或者崩溃恢复时，会根据这些信息来判断哪些事务需要回滚或者前滚。

redo log的刷新策略是指在事务提交时，如何将redo log buffer中的日志写入到redo log file中。MySQL提供了一个参数innodb_flush_log_at_trx_commit来控制这个策略，它有三个可选的值：

    0：表示每秒将redo log buffer中的日志写入到OS buffer，并调用fsync()函数将OS buffer中的日志刷入到redo log file中。这种方式性能较好，但是如果系统崩溃，可能会丢失最近一秒内的数据。
    1：表示每次事务提交时，都将redo log buffer中的日志写入到OS buffer，并调用fsync()函数将OS buffer中的日志刷入到redo log file中。这种方式数据安全性最高，但是性能较差，因为需要频繁地进行磁盘IO操作。
    2：表示每次事务提交时，都将redo log buffer中的日志写入到OS buffer，但是不调用fsync()函数，而是每秒调用一次fsync()函数将OS buffer中的日志刷入到redo log file中。这种方式性能较好，但是如果系统崩溃或者电源故障，可能会丢失部分数据。

（5）MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一

我们做了一个事务,begin;update;commit.

1. 在begin ,会立即分配一个TXID=tx_01.

2. update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中

3. DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102

4. LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer

5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,在日志完 全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)

6. 假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失

7. MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是 LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证 磁盘数据页和redolog LSN一值.这时MySQL正长启动 以上的工作过程,我们把它称之为基于REDO的"前滚操作

（3）undo log回滚日志

（1）undo log是什么

当一个事务执行过程中发生错误或者被中断，需要回滚（Rollback）到事务开始前的状态。这意味着要撤销已经执行的操作，将数据库恢复到事务开始之前的一致状态。

为了支持回滚操作，数据库系统使用undo回滚日志。当数据库执行一个事务时，每个被修改的数据页的旧值会被记录在undo回滚日志中。这样，如果需要回滚事务，就可以使用undo日志中的旧值来恢复数据页到事务开始前的状态。

（2）undo log回滚日志的作用

undo（回滚）的作用是保证事务的原子性和隔离性，同时支持快照技术和数据恢复。

    原子性（Atomicity）：通过undo，可以在事务回滚时将数据恢复到修改之前的状态，确保事务要么完全执行，要么完全回滚，避免了部分操作的影响。
    隔离性（Isolation）：并发事务可能同时修改同一个数据，使用undo可以提供事务的私有数据版本，保证每个事务独立地看到修改之前的数据，从而实现隔离性。
    快照技术：undo提供了事务修改之前的数据状态的快照，这意味着在事务执行过程中，可以回退到之前的状态。这对于实现多版本并发控制（MVCC）非常重要，每个事务可以读取数据的一致版本，而不会受到其他事务的修改影响。
    数据恢复：undo日志记录了事务的修改操作和之前的数据状态，当系统发生故障或崩溃时，可以使用undo日志来恢复未提交的事务或未完成的操作。这种数据恢复机制对于数据库的持久性和可靠性非常重要。

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含 3 个隐藏列

    db_row_id：这是一个隐藏的行ID列。当表没有定义自定义主键或唯一键时，InnoDB会自动在聚簇索引记录中添加这个隐藏列作为主键。它用于唯一标识每一行数据。

    db_trx_id：这是操作数据的事务ID列。它记录了最后一个对该数据进行插入或更新的事务的ID。这个列用于支持多版本并发控制（MVCC）和事务的隔离性。

    db_roll_ptr：这是回滚指针列，它指向与这个记录相关的Undo Log（撤销日志）信息。Undo Log中存储了在事务回滚时需要用到的旧值和恢复数据的信息。这个列在数据恢复和事务回滚时起着重要的作用

三.事物的隔离级别

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据 时的准确性。 如果不考虑隔离性，可能会引发如下问题：

脏读：

a事务读取了b事务未提交的内容，而b事务后面进行了回滚。

事务A的操作

START TRANSACTION;
UPDATE your_table SET value = 'A' WHERE id = 1;
-- 不提交事务，保持事务处于未提交状态

事务B的操作：

START TRANSACTION;
SELECT value FROM your_table WHERE id = 1;
-- 基于读取到的值进行业务处理

事务A使用`ROLLBACK`回滚。

ROLLBACK;

结论：有两个事务A和B，并且使用默认的隔离级别（可重复读）。事务A修改了一条数据的值为A，但是还没有提交。事务B读取了这条数据的值为A，并基于该值进行了一些业务处理。此时，如果事务A回滚了，事务B读取到的数据就是无效的，而且基于该数据的业务处理可能出错。

幻读：

A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉“

事务A的操作代码如下：

START TRANSACTION;
SELECT * FROM example WHERE value LIKE 'A%';
-- 第一次查询，得到的结果集包含满足条件的所有行

-- 事务暂停一段时间，等待事务B的操作完成

SELECT * FROM example WHERE value LIKE 'A%';
-- 第二次查询，得到的结果集可能包含新增的满足条件的行

事务B的操作代码如下：

START TRANSACTION;
INSERT INTO example (id, value) VALUES (100, 'Apple');
COMMIT;

结论：在事务A第一次查询后，事务B插入了一行数据，符合事务A的查询条件。在事务A第二次查询时，得到的结果集可能会包含新增的符合条件的行，产生了幻读的现象。

不可重复读:

不可重复读重点是修改，幻读重点是新增和删除

START TRANSACTION;
SELECT value FROM example WHERE id = 1;
-- 第一次查询，得到的值为A
 
-- 事务暂停一段时间，等待事务B的操作完成
 
SELECT value FROM example WHERE id = 1;
-- 第二次查询，得到的值为B

事务B的操作代码如下：

START TRANSACTION;
UPDATE example SET value = 'B' WHERE id = 1;
COMMIT;

 结论：在事务A第一次查询到值为A后，事务B对该数据进行了更新并提交事务。在事务A第二次查询时，发现值已经变成了B，产生了不可重复读的现象。

事物的隔离级别

事务的隔离级别是指在并发事务中，一个事务对数据的修改是否对其他事务可见，以及其他事务对数据的修改是否对当前事务可见。不同的隔离级别会导致不同的并发问题，如脏读、不可重复读、幻读等。

SQL标准定义了四种隔离级别，由低到高分别是：

1.读未提交（Read Uncommitted）

读未提交其实就是事务没提交就可以读，很显然这种隔离级别会导致读到别的还没提交的数据，一旦读到的没有数据做了进一步处理，而另一个事务最终回滚了操作，那么数据就会错乱，总的来说说，读未提交级别会导致脏读(脏读、不可重复读、幻读都未解决)

2.读已提交（Read Committed）

读已提交是事务提交之后就可以读,这就是两个事务在执行时，事务A一开始读取了卡里有2000元，这个时候事务B把卡里的钱花完了，事务A最终再确认余额的时候发现卡里已经没有钱了。很显然，读已提交能解决脏读问题，但是解决不了不可重复读(解决脏读的问题，不可重复读和幻读都未解决)

3.可重复读 （Repeatable read）

事务A开始执行，无论事务B怎么改数据，事务A永远读到的就是它刚开始读的值。问题来了，假设事务B把id为1的数据改成了2，事务A并不知道id发生了变化，当事务A conmmit新增数据的时候却发现为2的id已经存在了，这就是幻读（MySQL5.1/5.7->幻读未解决,MySQL8->幻读查询问题解决(MVCC)

4.串行化（Serializable）

一个事务正在读取一个表格的数据，这个事务未结束，其他事务无权对这个表格做操作(三种现象都可以解决,会对表进行锁定) ，但性能最差，一般不使用。

可以通过命令 set transaction 命令设置事务隔离级别：

set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别