事务（Transaction）
事务是数据库中作为单个逻辑单元执行的一系列操作，这些操作要么全部成功，要么全部失败，以此来保证数据的一致性和完整性
事务特性 ACID⭐⭐⭐⭐⭐
原子性（Atomicity）：实现主要基于undo log 原子性表示一个事务生命周期中的DML语句，要么全成功要么全失败，不可以出现中间状态 Begin：DML01 DML02 DML03 Commit; 一致性（Consistency） 一致性表示一个事务发生前、中、后，数据都最终保持一致，即读和写都要保证一致性 例子：从账户A转账100元到账户B 账户A 账户B 1000 0 900 100 数据一致性 隔离性（Isolation） 隔离性表示一个事务操作数据行的时候，不会受到其他事务的影响，主要利用锁机制来保证隔离性 例子：买火车票 车票数量表：北京 -- 上海 （车票数量：1） A用户 B用户 查询北京到上海票数 查询北京到上海票数 A先开始购买(lock) Waiting 购票成功--commit 刷新后：查询票数为0 持久性（Durability）：实现主要基于redo log 持久性表示一旦事务进行了提交，即可永久生效（落盘） 保证事务提交后不会因为宕机等原因导致数据丢失
事务ACID相关知识官方说明：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html
事务提交方式⭐⭐⭐
事务生命周期控制语句
# 开启事务机制 begin; start transaction; # 提交事务任务 commit; # 回滚事务操作 rollback; 事务生命周期中，只能使用DML语句，其中包括：select、update、delete、insert；DDL语句会隐式进行提交
自动提交方式（auto_commit）
- 事务自动提交表示在没有显示的使用
begin语句的时候，执行DML操作语句 - 会在DML操作语句前自动添加
begin； - 并在DML操作语句执行后自动添加
commit； - 事务自动提交参数：select @@autocommit;
# 临时关闭事务自动提交功能 set global autoommit=0; -- 配置调整后，重新登录mysql数据库生效 # 永久关闭事务自动提交功能 [root@db01 ~]# vim /etc/my.cnf [mysqld] autocommit=0 -- 配置调整后，重新启动mysql数据库生效
事务自动提交方式设置方式优点缺点说明：
| autocommit=0 关闭事务自动提交 |
优势：可以编写多个关联的DML，进行一次性提交操作，若出现异常可以回滚 符合原子特性 |
| 劣势：可能出现多个关联的DML，只是完成了部分操作，这时就可能等待状态 基于隔离特性，操作的数据表或数据行就会进入锁定状态 |
|
| autocommit=1 开启事务自动提交 |
优势：可以出现多个关联的DML，逐行操作自动提交，就可以不用处于锁等待状态 |
| 劣势：可能出现多个关联的DML，，每执行一条就进行提交，会造成多个语句执行不符合原子性 |
隐式提交方式
在进行事务操作时，需要注意操作语句必须都是DML语句，如果中间插入了DDL语句，也会造成之前的事务操作自动提交
- 隐式自动提交方式语句：
在出现隐式自动提交时，可能导致提交的非事务语句有：
| 语句类型 | 涉及命令 |
|---|---|
| DDL语句类型 | alter、create、drop |
| DCL语句类型 | grant、revoke、set password |
| 锁定语句类型 | lock tables、unlock tables |
| 其他语句类型 | truncate table、load data infile、select for update |
说明：在多个数据库会话窗口中，A窗口的所有事务性DML操作，不会受到B窗口的非事务语句影响，同一会话窗口会有影响；
- 隐式自动回滚情况分析：
- 情况一：在事务操作过程中，会话窗口自动关闭了，会进行隐式自动回滚；
- 情况二：在事务操作过程中，数据库服务被停止了，会进行隐式自动回滚；
- 情况三：在事务操作过程中，出现事务冲突死锁了，会进行隐式自动回滚；
事务隔离级别、脏读、幻读、不可重复读⭐⭐⭐⭐⭐
事务隔离级别的作用：让每个事务都感觉自己是数据库唯一正在执行的操作，防止多个并发事务之间相互干扰，导致数据读写混乱（避免脏读、幻读、重复读）
mysql> select @@transaction_isolation;
四种事务隔离级别⭐⭐⭐⭐⭐
RU（READ-UNCOMMITTED 表示读未提交）
可以读取到事务未提交的数据，隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题;
RC（READ-COMMITTED 表示读已提交）
可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题；
RR（REPEATABLE-READ 表示可重复读）默认
可以防止脏读（当前内存读），防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差；
会使用next lock锁（行级锁+间隙锁），来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能；
SR（SERIALIZABLE 可串行化）
隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了；
这是事务的最高级别，在每条读的数据上，加上锁，使之不可能相互冲突
事务隔离级别官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
总结
| 隔离级别 | 英文描述 | 出现的问题 | 解释说明 |
|---|---|---|---|
| 读未提交 | RU-read uncommitted | 脏读、不可重复读、幻读 | 一个事务还没提交时，它做的变更就能被别的事务看到。 |
| 读提交 | RC-read committed | 不可重复读、幻读 | 一个事务提交之后，它做的变更才会被其他事务看到。 |
| 可重复读 | RR-repeatable read | 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。 当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 |
|
| 串行化 | serializable | 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。 |
脏读、幻读、不可重复读⭐⭐⭐⭐⭐
隔离级别测试数据表
mysql> create table t1 ( id int not null primary key auto_increment, a int not null, b varchar(20) not null, c varchar(20) not null ) charset=utf8mb4 engine=innodb; mysql> insert into t1(a,b,c) values(5,'a','aa'),(7,'c','ab'),(10,'d','ae'),
(13,'g','ag'),(14,'h','at'),(16,'i','au'),(20,'j','av'),(22,'k','aw'),
(25,'l','ax'),(27,'o','ay'),(31,'p','az'),(50,'x','aze'),(60,'y','azb');
脏读（dirty read）
在一个事务窗口中，可以读取其他事务没有提交的数据信息
-- RU级别
set global transaction_isolation='READ-UNCOMMITTED'; -- 重新开启两个SQL会话窗口 begin; begin; select * from t1 where id=1;(a=5) update t1 set a=10 where id=1; select a from t1 where id=1;(a=10) rollback select * from t1 where id=1;(a=5)
不可重复读（non-repeatable read）
在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的
-- RC级别 set global transaction_isolation='READ-COMMITTED'; begin; begin; select * from t1 where id=1;(a=5) update t1 set a=10 where id=1; commit; select * from t1 where id=1;(a=10) -- RR级别 set global transaction_isolation='REPEATABLE-READ'; begin; begin; select * from t1 where id=1;(a=10) select * from t1 where id=1;(a=10) update t1 set a=5 where id=1; commit; select * from t1 where id=1;(a=10) commit; select * from t1 where id=1;(a=5)
幻读（phantom read）
在一个事务中，由于其他事务插入新纪录/修改数据，导致先后两次相同条件的查询返回不同数量的行
- RC环境
set global transaction_isolation='READ-COMMITTED'; alter table t1 add index idx(a); -- 添加t1表的a列为索引信息 begin; begin; select * from t1 where id=18;(空) insert into t1 values(18,0,'A','B'); commit; select * from t1 where id=18;(拥有信息)
- RR环境（间隙锁）
set global transaction_isolation='REPEATABLE-READ'; alter table t1 add index idx(a); begin; begin; update t1 set a=20 where a>20; insert into t1(a,b,c) values(30,'sss','bbb') -- 语句执行时会被阻塞，没有反应，之后报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> show processlist; -- 在C窗口中，查看数据库连接会话信息，insert语句在执行，等待语句超时（默认超时时间是50s） -- 因为此时在RR机制下，创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock -- 区域间隙锁 < 左闭右开(可用临界值) ; 区域间隙锁 > 左开右闭（不可用临界值）
- RR环境（MVCC）
set global transaction_isolation='REPEATABLE-READ'; alter table t1 add index idx(a); begin; begin; select * from t1 where id=20;(空) insert into t1 values(20,30,'sss','bbb') commit; select * from t1 where id-20;(空) insert into t1 values(20,30,'sss','bbb') ERROR 1062 (23000): Duplicate entry '20' for key 't1.PRIMARY' -- 通过主键不可重复判断事务是否可以查看另外事务插入的值（验证没有出现幻读）
- MVCC和间隙锁
| 特性 | MVCC | 间隙锁 |
|---|---|---|
| 作用 | 解决读幻读 | 解决写幻读 |
| 机制 | 多版本快照 | 范围锁定 |
| 影响 | 读操作 | 写操作 |
| 性能 | 无阻塞 | 可能阻塞 |
| 可见性 | 基于快照 | 基于当前数据 |
数据库存储事务工作流程⭐⭐⭐⭐⭐
事务工作流程名词解释⭐⭐
redo log-Disk 表示重做日志，当出现异常情况，内存中数据直接写入磁盘失败时，可以通过重启数据库服务，读取此文件修复数据信息； 文件存储表项为：ib_logfile0~N 默认48M，轮询使用 redo log buffer-mem 表示重做日志生成缓冲区，相当于redo log的内存区域。redo log文件与redo log buffer是有IO关系的； 事务修改提交后：redo log buffer -> redo log，表示写入数据到redo log； 事务操作恢复时：redo log -> redo log buffer，表示读取数据从redo log； tablespace file-disk 表示存储表数据行和索引等信息的文件，含有表空间所有数据文件；ibd Innodb buffer pool-mem 表示数据缓冲区，主要用于缓冲事务要处理的数据和索引信息，tablespace文件与buffer pool是有IO关系的； LSN--日志序列号
在buffer pool中有数据页信息的变化就会记录到redo log buffer中，主要记录了变化了多少字节； 利用LSN记录相应数据页的变化量(LSN+变化字节量)，也可以理解为记录的是日志量的变化； MySQL每次数据库启动，都会比较磁盘数据页和redolog的LSN，必须要求两者一致，数据库才能正常启动； LSN不仅存在于redo log中，还存在于数据页中，通过数据页中的LSN值和redo log中的LSN值比较
如果页中的LSN值小于redo log中LSN值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到redo log中记录的LSN值时的状态 WAL（Write Ahead Log） 表示redo日志生成记录优先于数据页写入到磁盘的过程，并且是支持预写入机制（group commit）的
欲写入机制/组提交（group commit）
通过单个I/O将多个事务的日志写入磁盘（包括已提交的事务和未提交的事务）
大幅度减少了高延迟的磁盘同步次数，从而极大的提高了数据库吞吐量
Dirty page 表示在内存进行修改的数据页，在redo buffer中会记录数据页的数据量的变化，此时在数据页还未最终写入到磁盘中时， 就称之为脏页，所以一般所谓的脏读就是读取脏页的数据页信息 CheckPoint 表示为检查点，就是将脏页刷写到磁盘的动作 DB_TRX_ID（6字节）--事务ID号 InnoDB会为每一个事务生成一个事务号（由事务管理器管理TM），伴随着整个事务生命周期 其中事务ID号码信息，在redo和undo日志文件中都会有相应的标识； DB_ROLL_PTR（7字节）--回滚指针 在rollback时会使用undo日志回滚已修改的数据，DB_ROLL_PTR会指向此次事务的回滚业务点； 从而找到undo上的相应的日志信息
数据库名词解释官方参考：https://dev.mysql.com/doc/refman/8.0/en/glossary.html
持久性（redo log、CR机制）⭐⭐⭐⭐⭐
redo log应用流程
# redo log 正常应用流程 用户发起update操作事务语句，将磁盘数据页（page100，A=1，LSN=1000）加载到内存（buffer_pool）缓冲区； 将在内存中发生数据页修改操作（A=1改为A=2），形成数据页脏页，更改中数据页的变化会记录到redo buffer中； 加入10个字节日志信息，LSN=1000+10=1010; 当执行事务提交操作的时候，基于WAL机制，等到redo buffer中的日志完全落盘到ib_logfileN-redo log中； 此时ib_logfileN中记录了一条日志，内容为：page100数据页变化+LSN=1010 简单理解：记录内存数据页变化日志+undo（DB_TRX_ID，DB_ROLL_PTR），通过LSN和数据页建立关系 # redo log 异常应用流程 特殊情景分析：当此时，redo落盘了，数据页没有落盘，出现宕机情况了； MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN； 如果发现redo LSN > 数据页的LSN，加载原始数据页+变化redo指定内存，使用redo重构脏页（前滚）； 如果确认此次事务已经提交（commit标签），立即触发CKPT（checkpoint）动作，将脏页刷写到磁盘上； # 知识点补充： MySQL有一种机制，批量刷写redo的机制：会在A事务commit时，顺便将redo buffer中的未提交的redo日志也一并刷到磁盘； 为了区分不同状态的redo，日志记录时会标记是否commit；
Redo Log日志文件生成流程
Redo Log日志文件应用流程
存储引擎读写磁盘数据页IO信息： -- 用于处理读操作I/O请求的后台线程数量 mysql> select @@innodb_read_io_threads; -- 用于处理写操作I/O请求的后台线程数量 mysql> select @@innodb_write_io_threads; 存储引擎序LSN号号码信息查看： mysql> show engine innodb status\G Log sequence number 105377511 -- redo buffer中的LSN号码信息 Log flushed up to 105377511 -- redo buffer刷新到磁盘上的LSN号码信息 Last checkpoint at 105377511 -- 磁盘数据页的LSN号码信息 存储引擎redo buffer落盘的机制策略： mysql> select @@innodb_flush_log_at_trx_commit; -- 表示数据库配置与安全有关的两个双一配置 -- 当数值为1：表示每次事务提交就立刻进行redo buffer刷新落盘，若落盘不成功，则commit命令操作也不会成功；默认 -- 当数值为0：表示日志缓存信息写入磁盘是按照每秒种进行一次操作，未刷新日志的事务可能会在崩溃中丢失；不安全 -- 当数值为2：表示在事务提交后先生成日志缓存信息，然后再按照每秒钟进行一次写入磁盘操作；不安全 -- 参考官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
原子性（undo log、redo log、CR机制）⭐⭐⭐⭐⭐
事务发生数据页修改前，会申请一个undo事务操作，保存事务回滚日志
undo写完之后，事务修改数据页头部(会记录DB_TRX+DB+ROLL_PTR)，这个信息也会被记录在redo Log中
当执行rollback命令时，根据数据页的DB_TRX_ID+DB+ROLL_PTR信息，找到undo日志并进行回滚； # 异常情况分析 mysql> begin; mysql> update t1 set A=2 where A=1; -- 此时宕机了 # 假设：undo 有；redo 没有） 启动数据库时，检查redo和数据页的LSN号码，发现是一致的； 所以不需要进行redo的前滚，此时也不需要回滚。undo信息直接被标记为可覆盖状态； # 假设：undo 有；redo 也有（没有commit标签） MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN; 如果发现redo LSN>数据页的LSN。随即加载原始数据页+变化redo Log日志信息到相应内存位置，使用redo重构脏页（前滚）； 如果确认此次事务没有commit标记，立即触发回滚操作，根据DB_TRX_ID+DB_ROLL_PTR信息，找到undo回滚日志，实现回滚； 以上流程被称之为InnoDB的核心特性：自动故障恢复（CR），会先前滚再回滚，先应用redo再应用undo；
undo Log日志文件生成流程
undo Log日志文件应用流程
一致性（undo log、redo log、CR机制、DWR机制）⭐⭐⭐⭐⭐
InnoDB crash recovery：数据库意外宕机时刻，通过redo前滚+undo回滚保证数据的最终一致；
InnoDB doubewrite buffer：默认存储在ibdataN中，解决数据页写入不完整；DWB一共2M，分两次。每次1M写入；
redo日志只能恢复好的数据页的内容，但是不能恢复已经有异常的数据页内容；
可以参考官方资料：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html
DWB文件信息生成流程
DWB文件信息应用流程
隔离性（隔离级别+mvcc+锁 控制并发处理）⭐⭐⭐⭐⭐
数据库中锁的作用：
避免事务并发冲突问题
避免资源信息被抢占导致被迫释放（防止正在使用的CPU内存等资源被其他事务抢占）
数据的读隔离性：隔离级别+MVCC（锁机制-乐观锁）⭐⭐⭐⭐⭐
- 方式一：利用隔离级别保证
| 隔离级别 | 简单回顾 |
|---|---|
| RU | 可以直接读取其他事务内存中的数据信息 |
| RC | 可以直接读取其他事务提交的数据信息 |
| RR | 只能读取自己事务中的数据信息 |
| SR(SE) | 事务对行进行操作时（读或写）都会产生行锁，影响其他事务操作 |
- 方式二：利用MVCC(多版本并发控制)机制隔离（只保证读的隔离）
MVCC核心思想：为数据库中每行数据创建多个快照版本
MVCC通过为数据行保存多个快照版本（由undo log保存旧版本），并为每个事务提供一个“历史快照”视角（Read View）。
事务读取时，会根据这个视角选择其可见的合适快照版本，实现读写操作互不阻塞，从而极大提升了系统的并发吞吐量。
读写互不阻塞：读数据和写数据操作可以同时进行，谁也不用等谁，从而极大地提升了数据库的并发处理能力和整体性能
读不阻塞写：一个事务正在读数据，不会阻止其他事务来更新这行数据。
写不阻塞读：一个事务正在更新数据（甚至已经锁定了这行），其他事务仍然可以读取这行数据更新前的版本，而不会被阻塞
只有RC和RR级别可以使用MVCC，实现快照读机制
MVCC有一个非常重要的规则：当前事务总是能看到它自己所做的修改
RC：应用MVCC的快照读机制，是基于语句级别的；（不可重复读）
在事务期间，执行每个查询语句的时候，都会检查MVCC版本（快照列表），获取最新的已提交事务的快照；
RR：应用MVCC的快照读机制，是基于事务级别的；（可重复读）
在事务期间，执行首条查询语句的时候，就会生成MVCC版本（相应快照），将会一直读取此快照数据信息，直到事务生命周期结束；
以上的RR隔离级别利用MVCC的快照读机制，又称为一致性快照读；
- MVCC的乐观锁/悲观锁
MVCC进行多版本控制时，会应用两种锁机制：乐观锁/悲观锁
| 操作类型 | 锁机制 | 并发性 |
|---|---|---|
| 读操作 | 乐观锁 (无锁) | 完全并发：成百上千个事务可以同时读 |
| 写操作 | 悲观锁 (加锁) | 串行化：同一时间只能有一个事务修改某条数据 |
# 机制：每个事务执行一个读操作时，会生成一个读视图 在事务中第一次执行快照读（执行SELECT语句）时“定格”，生成一个全局一致的读视图（Read View） 后续select： RR级别，复用第一个select生成的读视图 RC级别，每次都会生成新的读视图 # 乐观锁在MVCC中的作用是： 让读操作无需加锁等待就能获得一个一致性的数据快照，从而实现读写操作的高并发执行 无需加锁等待 - 读不阻塞写，写不阻塞读 一致性数据快照 - 每个事务看到的是某个时间点的完整数据状态 高并发执行 - 读写操作可以同时进行，大幅提升性能
数据的写隔离性（隔离级别 + 锁机制）⭐⭐⭐⭐⭐
- 方式一：利用隔离级别保证
# 在应用不同隔离级别时也会有不同的锁机制 RC：具有记录锁机制； RR：具有间隙锁机制+下一键锁机制（next lock） 表锁
- 方式二：利用锁进制隔离（保护并发访问资源）
| 类型 | 锁机制 | 简述说明 |
|---|---|---|
| 内存资源锁 | latch（闩锁） | 主要是保护内存资源，避免不同程序争用相同地址区域的内存资源 RWLock--读写锁：读者共享，写者独占，适合读多写少的场景 Mutex--互斥锁：一把钥匙一把锁，保证同一时间只有一个线程能进入 |
| 元数据锁 | MDL | 主要是保护元数据资源，限制DDL操作，确保DDL和DML之间的一致性 |
| 表级别锁 | table_lock | 主要是保护整个数据表资源 |
| 命令方式锁表 |
LOCK TABLE t1 READ;（读锁/共享锁）： |
|
| 工具方式锁表 | 利用mysqldump、XBK(PBK)进行备份非InnoDB数据时，将触发FTWRL全局锁表 | |
| 行锁升级为表锁 | 比如做数据更新操作时，没有设置索引条件信息，就会出现全表扫描，出现表锁 | |
| 行级别锁 | row_lock | InnoDB默认锁粒度，加锁方式都是在索引上加锁的 |
| record lock | 记录锁，在聚簇索引锁定，在RC级别只有record lock | |
| gap lock | 间隙锁，在辅助索引间隙加锁，在RR级别存在，防止幻读 | |
| next look | 下一键锁，即GAP+Record，在RR级别存在，防止幻读 |
InnoDB的行锁模式及加锁方式⭐⭐⭐⭐⭐
InnoDB实现了以下两种类型的行锁 查询锁/读锁/共享锁(S)：对同一数据进行操作时，可以有多个锁申请 案例理解：一个房间锁与锁相连形成一个链条，每个人都有对应锁的钥匙，都可以进入房间) 一个事务持有某行的共享锁，其他事务仍然可以获得同一行的共享锁（即允许多个事务同时读取） 一个事务持有某行的共享锁，其他事务将无法获得该行的排他锁（即阻止其他事务写入） 写锁/排他锁(X)：对同一数据信息操作时，只用一个锁可以申请 案例理解：一个房间只有一把锁，你锁住了别人就进不来 一个事务持有某行的排他锁，其他事务将无法获得该行的共享锁（即阻止其他事务读取被锁定的版本） 一个事务持有某行的排他锁，其他事务将无法获得该行的排他锁（即阻止其他事务同时读写）
InnoDB实现了以下两种类型的表锁 意向共享锁(IS):事务在给一个数据行加共享锁前，必须先取得该表的IS锁; 意向排他锁(IX):事务在给一个数据行加排他锁前，必须先得到该表的IX锁;
官方参考资料链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
浙公网安备 33010602011771号