Mysql 事务（标贝科技）

欢迎体验标贝科技AI开放平台 https://ai.data-baker.com/#/?source=qwer12

事务

InnoDB对ACID的支持

特性	说明	InnoDB支持
原子性	一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样	autocommit，commit，rollcack
一致性	在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)	双InnoDB写缓冲区，“双写缓冲区”，InnoDB崩溃恢复
隔离性	数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（例：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）	事务隔离级别
持久性（涉及硬件选购）	事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失	双InnoDB写缓冲区，“双写缓冲区”(innodb_flush_log_at_trx_commit 变量。(例如sync_binlog变量,innodb_file_per_table 变量)，存储设备中的写入缓冲区(例如磁盘驱动器、SSD 或 RAID 阵列,存储设备中的电池后备缓存。用于运行 MySQL 的操作系统，特别是它对fsync()系统调用的支持)

隔离级别

不同隔离级别下读读取数据可能出现的情况

脏读：一个事务处理过程里读取了另一个未提交的事务中的数据
不可重复读：一个事务在它运行期间，两次查找相同的表，出现了不同的数据
幻读：在一个事务中读取到了别的事务插入的数据，导致前后不一致
串行化：既不允许脏读，也不允许不可重复读，并且还不允许幻读

隔离级别	说明	脏读	不可重复读）	幻读
未提交读（Read uncommitted）	所有事务都可以看到没有提交事务的数据	可能	可能	可能
已提交读（Read committed）	事务成功提交后才可以被查询到	不可能	可能	可能
可重复读（Repeatable read）	同一个事务内多次查询却返回了不同的数据值	不可能	不可能	可能
可串行化（Serializable ）	强制的进行排序，在每个读读数据行上添加共享锁	不可能	不可能	不可能

隔离级别是在多个事务同时进行更改和执行查询时微调结果的性能、可靠性、一致性和可再现性之间的平衡的设置

不可重复读和幻读区别

（1）不可重复读是读取了其他事务更改的数据，针对update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据

（2）幻读是读取了其他事务新增的数据，针对insert与delete操作
解决：使用间隙锁，表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据

幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除，不可重复读重点在修改

redo log （共享表空间）

innodb通过预写日志（force log at commit）机制实现事务的持久性

优点:日志顺序写速度远远大于数据页随机写磁盘

两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）

redo log写入流程

redo log block

redo log以块为单位进行存储的，每个块占512字节。log buffer、redo log file中，都是这样以512字节的块存储的

redo log block包含4部分：

• Ÿ log_block_hdr_no：(4字节)该日志块在redo log buffer中的位置ID
• Ÿ log_block_hdr_data_len：(2字节)该log block中已记录的log大小。写满该log block时为0x200，表示512字节
• Ÿ log_block_first_rec_group：(2字节)该log block中第一个log的开始偏移位置
• Ÿ lock_block_checkpoint_no：(4字节)写入检查点信息的位置

relog block块头的第三部分 log_block_first_rec_group ，因为有时候一个数据页产生的日志量超出了一个日志块，这是需要用多个日志块来记录该页的相关日志。例如，某一数据页产生了552字节的日志量，那么需要占用两个日志块，第一个日志块占用492字节，第二个日志块需要占用60个字节，那么对于第二个日志块来说，它的第一个log的开始位置就是73字节(60+12)。如果该部分的值和 log_block_hdr_data_len 相等，则说明该log block中没有新开始的日志块，即表示该日志块用来延续前一个日志块

默认redolog由ib_logfile0、ib_logfile1组成，以追加写入的方式循环轮训写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入

刷redo log策略：

1.发出commit动作时。commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制

2.每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关

3.当log buffer中已经使用的内存超过一半时

4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。

• 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差
• 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据
• 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

innodb存储引擎中checkpoint：

redolog日志太大怎么办？

解决问题：1、缩短数据库的恢复时间 2、缓冲池不够用时，将脏页刷新到磁盘 3、重做日志不可用时，刷新脏页

触发逻辑

• sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘
• fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：
  • master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘
  • flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页
  • async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘
  • dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘

解决问题

• 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间
• 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘
• 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置

Log sequence number日志序列号

LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大

根据LSN，可以获取到几个有用的信息：

1.数据页的版本信息

2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量

3.可知道检查点的位置

实际上还可以获得很多隐式的信息

innodb从执行修改语句开始：

(1).首先修改内存中的数据页，并在数据页中记录LSN，暂且称之为data_in_buffer_lsn

(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log，并记录下对应的LSN，暂且称之为redo_log_in_buffer_lsn

(3).写完buffer中的日志后，当触发了日志刷盘的几种规则时，会向redo log file on disk刷入重做日志，并在该文件中记下对应的LSN，暂且称之为redo_log_on_disk_lsn

(4).数据页不可能永远只停留在内存中，在某些情况下，会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘，所以会在本次checkpoint脏页刷盘结束时，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint_lsn

(5).要记录checkpoint所在位置很快，只需简单的设置一个标志即可，但是刷数据页并不一定很快，例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成，中途刷入的每个数据页都会记下当前页所在的LSN，暂且称之为data_page_on_disk_lsn

undo log 日志（共享表空间）

当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录

作用：

• 提供回滚
• 多个行版本控制(MVCC)当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取

记录日志的方式

• insert操作记录delete操作，但是可能只是打delete flag标签，由最终的主线程完成
• delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的
• update分为两种情况：update的列是否是主键列
  • 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的
  • 如果是主键列，update分两部执行：先删除该行，再插入一行目标行

<!–提交后的undolog不会立即删除，后续事务可能还需要基于mvcc读快照–>

参考文献
[1]https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html
[2]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_packets.html#sect_protocol_basic_packets_packet
[3]https://www.jianshu.com/p/5e6b33d8945f
[4]https://cloud.tencent.com/developer/article/1768901
[5]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods.html
[6]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase.html
[7]https://dev.mysql.com/doc/internals/en/
[8]https://www.cnblogs.com/wyq178/p/11576065.html
[9]Mysql技术内幕：InnoDB存储引擎 (第2版). 姜承尧
[10]MySQL运维内参：MySQL、Galera、Inception核心原理与最佳实践. 周彦伟，王竹峰，强昌金
[11]https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
[12]https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html