读MySQL技术内幕事务笔记

àì夳堔傛蜴生んèń 2023-02-25 10:29 121阅读 0赞

读《MySQL技术内幕 InnoDB存储引擎》事务笔记  
事务是数据库区别于文件系统的重要特性之一，数据库引入事物的主要目的：事务会把数据库从一种一致状态转换为另外一种一致的状态。事务的特性以及这些特性的实现方式：

重做日志redo log通常是物理日志，记录的是页的物理修改操作，回滚日志undo log是逻辑日志，根据每行记录进行记录。  
事物的隔离性，通过锁技术来实现，锁分为几种：

<table> 
 <thead> 
  <tr> 
   <th align="center">事物隔离性</th> 
   <th align="center">锁算法</th> 
   <th align="center">锁范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">读未提交（read-uncommitted）</td> 
   <td align="center">无</td> 
   <td align="center">–</td> 
  </tr> 
  <tr> 
   <td align="center">不可重复读（read-committed）</td> 
   <td align="center">记录锁 record lock</td> 
   <td align="center">行锁</td> 
  </tr> 
  <tr> 
   <td align="center">可重复读（repeatable-read）</td> 
   <td align="center">next-key lock</td> 
   <td align="center">锁范围</td> 
  </tr> 
  <tr> 
   <td align="center">串行化（serializable）</td> 
   <td align="center">gap lock</td> 
   <td align="center">锁范围</td> 
  </tr> 
 </tbody> 
</table>

对于serializable这种隔离级别，完全串行化的操作，是在每个select读的数据行上，加了共享锁，相当于select \*\*\* lock in share mode,在每个变更的数据行上加上了排它锁。  
repeatable-read和serializable通过主键操作数据的时候，next-key lock降级为 record lock  
参考：[加锁分析][Link 1]

# redo日志 #

重做日志用来实现事务的持久性，由两部分组成，一是内存中的重做日志缓冲，是易失的，而重做日志文件，是持久的。当事物提交（commit）的时候，必须先将该事物的所有日志写入到重做日志文件进行持久化。当然可以通过innodb\_flush\_log\_at\_trx\_commit参数配置策略，设置刷新日志的时机。

*  innodbflushlogattrx\_commit = 1表示事务提交时立即将事务日志写入磁盘。
 *  innodbflushlogattrx\_commit = 0 表示事务提交时不立即将事务日志写入磁盘，而是由master thread每隔1秒写入磁盘文件一次，并且刷新到磁盘。这样一来，如果mysqld崩溃，那么在内存中事务日志缓冲区最近1秒的数据将会丢失，这些更新将永远无法恢复。
 *  innodbflushlogattrx\_commit = 2 表示事务提交时，写入文件系统的缓存中，不进行刷盘操作，在这种情况下，即使mysqld崩溃后，位于内核缓冲区的事务日志仍然不会丢失，只有当操作系统崩溃的时候才会丢失最后1秒的数据。

## redo日志和二进制日志 ##

二进制日志也就是binlog，主要用来主从复制环境的建立。

*  首先重做日志是在InnoDB存储引擎层产生，而二进制日志是在Mysql数据库的上层产生的，任何存储引擎对于数据库的更改都会产生二进制日志，重做日志只是针对InnoDB存储引擎，其他存储引擎不一定有重做日志。
 *  其次二进制日志是一种逻辑日志，记录的是对应的sql语句，重做日志是物理格式日志，记录的是每个页的修改，因此，重做日志恢复的速度比二进制日志要快很多，因为重做日志是物理格式，所以重做日志是幂等的，而二进制日志不是幂等的。
 *  此外二进制日志只在事物提交完成后，进行一次写入，重做日志在事物进行中不断被写入。

## redo block日志块 ##

重做日志都是以512字节进行存储的，重做日志缓存、重做日志文件都是以块block的方式进行保存，每块的大小为512字节。日志块的大小和磁盘扇区的大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite双写技术。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 1]  
在InnoDB存储引擎运行过程中，log buffer根据一定的规则将内存中的log block刷新到磁盘。这个规则是：

*  事务提交时
 *  当log buffer中有一半的内存空间已经被使用时
 *  log checkpoint时  
    对于log block的写入追加在redo log file的最后部分，当一个redo log file被写满时，会接着写入下一个redo log file，其使用方式为round-robin轮询调度算法。

## LSN日志序列号 ##

LSN是Log Sequence Number的缩写，其代表的是日志序列号。LSN占用8字节，并且单调递增，LSN表示的含义有：

*  重做日志写入的总量
 *  checkpoint的位置
 *  页的版本

LSN表示事务写入重做日志的字节的总量，例如：当前重做日志的LSN为1000，有一个事务T1写入了100字节的重做日志，那么LSN就变为了1100，可见LSN记录的是重做日志的总量，其单位为字节。

LSN不仅记录在重做日志中，还存在于每个页中。在每个页的头部，有一个FIL\_PAGE\_LSN，记录了该页的LSN。在页中，LSN表示该页最后刷新时LSN的大小。因为重做日志记录的是每个页的日志，因此页中的LSN用来判断页是否需要进行恢复操作。

例如：页P1的LSN为10000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13000，并且该事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到P1页中。

# undo回滚日志 #

在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。  
与redo log放在文件不同，undo放在数据库内部的一个特殊段中，称为undo段，位于共享表空间中。

undo是逻辑日志，回滚时修改会被逻辑地取消，数据结构和页本身在回滚之后可能不太相同，因为这个过程中可能有其他并发的事务，比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个insert，innodb存储引擎会执行一个delete；对于每一个delete，innodb存储引擎会执行一个insert；对于每一个update，innodb存储引擎会执行一个相反的update，将修改前的行放回去。

除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成的。当用户读取一行记录时，若该行记录已经被其他事务占用了，当前事务可以通过undo读取之前的行版本信息，以此来实现非锁定读。

undo log会产生redo log，也就是undo log的产生会伴随redo log的产生，这是因为undo log也需要持久性的保护。  
事务提交时，InnoDB会做以下两件事情：

*  将undo log放入列表中，以供之后的purge操作
 *  判断undo log所在的页是否可以重用，若可以，分配给下个事务使用

事务提交后并不能马上删除undo log及undolog所在的页。因为可能还有其他事务需要通过undo log来得到行记录之前的版本。所以事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。

# purge清理线程 #

DELETE FROM t WHERE a=1;

表t上列a有聚集索引,列b上有辅助索引。对于上述的 delete操作,仅是将主键列等于1的记录delete flag设置为1,记录并没有被删除,即记录还是存在于B+树中。其次,对辅助索引上a等于1,b等于1的记录同样没有做任何处理,甚至没有产生 undo log。而真正删除这行记录的操作其实被“延时”了,最终在 purge操作中完成。  
purge用于最终完成 delete和 update操作。这样设计是因为 InnoDB存储引擎支持MVCC。是否可以删除该条记录通过 purge来进行判断。若该行记录已不被任何其他事务引用,那么就可以进行真正的 delete操作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 2]  
history list表示按照事务提交的顺序将undo log进行组织。在InnoDB存储引擎的设计中,先提交的事务总在尾端。 undo page存放了 undo log,由于可以重用,因此一个 undo page中可能存放了多个不同事务的undo log。trx5的灰色阴影表示该 undo log还被其他事务引用。

在执行 purge的过程中, InnoDB存储引擎首先从 history list中找到第一个需要被清理的记录,这里为txl,清理之后 InnoDB存储引擎会在trx1的 undo log所在的页中继续寻找是否存在可以被清理的记录,这里会找到事务tx3,接着找到tx5,但是发现trx5被其他事务所引用而不能清理,故去再次去 history list中查找,发现这时最尾端的记录为trx2,接着找到trx2所在的页,然后依次再把事务trx6、trx4的记录进行清理。由于 undo page2中所有的页都被清理了,因此该 undo page可以被重用。

InnoDB存储引擎这种先从 history list中找 undo log,然后再从 undo page中找undo log的设计模式是为了避免大量的随机读取操作,从而提高 purge的效率

# 分布式事务 #

## 外部XA事物 ##

在使用分布式事务时, InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。XA事务允许不同数据库之间的分布式事务,如一台服务器是 MySQL数据库的,另台是 Oracle数据库的,又可能还有一台服务器是 SQL Server数据库的,只要参与在全局事务中的每个节点都支持XA事务。

XA事务由一个或多个资源管理器(Resource Managers)、一个事务管理器(Transaction Manager)以及一个应用程序(Application Program)组成。在 MySQL数据库的分布式事务中,资源管理器就是 MySQL数据库,事务管理器为连接 MySQL服务器的客户端。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 3]  
分布式事务使用两段式提交的方式。在第一阶段,所有参与全局事务的节点都开始准备( PREPARE),告诉事务管理器它们准备好提交了。在第二阶段,事务管理器告诉资源管理器执行 ROLLBACK还是 COMMIT。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 4]

## 内部XA事物 ##

最常见的内部XA事务存在于binlog与InnoDB存储引擎之间，在事务提交时，先写二进制日志，再写InnoDB存储引擎的重做日志。对上述两个操作的要求也是原子的，  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 5]  
上图中，如果执行完①、②后在步骤③之前MySQL数据库发生了宕机，则会发生主从不一致的情况。为了解决这个问题，MySQL数据库在binlog与InnoDB存储引擎之间采用XA事务。当事务提交时，InnoDB存储引擎会先做一个PREPARE操作，将事务的xid写入，接着进行二进制日志的写入，如下图所示。如果在InnoDB存储引擎提交前，MySQL数据库宕机了，那么MySQL数据库在重启后会先检查准备的UXID事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 7]

# 不好的事物习惯 #

*  在循环中提交事物
 *  使用自动提交事物
 *  使用自动回滚事物
 *  使用执行时间较长的事物

[Link 1]: https://juejin.im/post/5d5671a2e51d45620821cea7
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70]: /images/20230209/b067b0904bf64f03a18b9bac3fff720e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 1]: /images/20230209/ada769654fb44cc39d8f791480042849.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 2]: /images/20230209/1064139c7800493fbb34dae7580ef4e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 3]: /images/20230209/578339e7a7c4431a87246758542db4ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 4]: /images/20230209/d7e49df669f84ce5973f59cab2bd9f95.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 5]: /images/20230209/df7f080ac2744eca9a0ebff419cc2c80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 6]: /images/20230209/f806acd5b322407ba65c86d1a7d95d9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpaHVheW9uZw_size_16_color_FFFFFF_t_70 7]: /images/20230209/8e53c262e3f24c98bf0bd34ca94bfa98.png