MySQLinnodb引擎的事务执行过程

通过这篇文章可以了解到下面几个问题

问题1：MySQL innodb引擎的update的流程；

问题2：以及写redo,undo,binlog的顺序，然后刷盘的顺序又是什么呢？

问题3：以及刷新redo和脏数据的相关进程；

总结以上的三个问题，其实就是关于MySQL innodb事务的流程；那么接下来，我将详细总结下一一一：MySQL innodb的事务流程：

1.接下来我就以update为例，讲解下MySQL5.6的innodb的事务流程，总结起来就是：

镇对update he set name='liuwenhe' where id=5;

1）事务开始

2）对id=5这条数据上排他锁，并且给5两边的临近范围加gap锁，防止别的事务insert新数据；

3）将需要修改的数据页PIN到innodb_buffer_cache中；

4）记录id=5的数据到undo log. 5）记录修改id=5的信息到redo log.

6）修改id=5的name='liuwenhe'.

7）刷新innodb_buffer_cache中脏数据到底层磁盘，这个过程和commit无关；

8）commit，触发page cleaner线程把redo从redo buffer cache中刷新到底层磁盘，并且刷新innodb_buffer_cache中脏数据到底层磁盘也会触发对redo的刷新；

9）记录binlog （记录到binlog_buffer_cache中）

10）事务结束；

2.关于事务的四大特性ACID

事务的原子性(Atomicity)：事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

事务的持久性(Durability)：事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。

事务的隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果

事务的一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

二：redo和undo保证MySQL innodb事务的原子性和持久性：

总起来概述可以认为：

undo用来保存数据更改之前的数据；保证原子性

redo用来保存数据更改之后的数据（注意是物理的修改信息），保证持久性

1）首先介绍Undo Log

Undo Log 主要是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制(简称：MVCC)，之后的文章将会介绍mvcc；

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方

也就是 Undo Log，然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

需要注意在MySQL 5.6之前，undo log是放在了共享表空间 ibdata1中的，MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下；采用独立undo表空间，再也不用担心undo会把 ibdata1 文件搞大。

undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到innodb_buffer_pool中的undo buffer（或者叫undo page)，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容也会被刷新到磁盘；并且innodb_purge_threads后台线程会清空undo页、清理“deleted”page，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来

2）接下来介绍 Redo Log,注意是先写redo,然后才修改buffer cache中的页，因为修改是以页为单位的，所以先写redo才能保证一个大事务commit的时候，redo已经刷新的差不多了。反过来说假如是先改buffer cache中的页，然后再写redo，就可能会有很多的redo需要写，因为一个页可能有很多数据行；而很多数据行产生的redo也可能比较多，那么commit的时候，就可能会有很多redo需要写；

和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，

不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。需要注意的是，事务过程中，先把redo写进redo log buffer中，然后MySQL后台进程page cleaner thread适当的去刷新redo到低层磁盘永久保存；

因为刷新buffer pool的脏数据之前，必须要先刷新redo（从redo log buffer到磁盘），所以触发刷新脏数据buffer pool的脏数据的条件也同时会触发刷新redo。还需要注意：MySQL 5.6版本之前都是master thread来完成刷脏数据的任务（包括buffer pool中的脏数据以及redo log buffer中的redo),MySQL 5.6版本，刷新操作放入到了单独的Page Cleaner Thread中；

Checkpoint（检查点）技术目的是解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。

在InnoDB存储引擎内部，有两种Checkpoint

分别为：Sharp Checkpoint、Fuzzy Checkpoint

Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1。但是若数据库在运行时也使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

Fuzzy Checkpoint：

1、Master Thread Checkpoint；

2、FLUSH_LRU_LIST Checkpoint；

3、Async/Sync Flush Checkpoint；

4、Dirty Page too much Checkpoint

1、Master Thread Checkpoint

以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。

2、FLUSH_LRU_LIST Checkpoint

因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB1.1.x版本之前，需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。

而从MySQL 5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024，如：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_lru_scan_depth | 1024 |

+-----------------------+-------+

3、Async/Sync Flush Checkpoint

指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn，则可定义：

checkpoint_age（可以理解脏页，或者待刷新的脏页） = redo_lsn - checkpoint_lsn

再定义以下的变量：

async_water_mark = 75% * total_redo_log_file_size

sync_water_mark = 90% * total_redo_log_file_size

若每个重做日志文件的大小为1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为2GB。那么async_water_mark=1.5GB，sync_water_mark=1.8GB。则：

当checkpoint_age

当async_water_mark

checkpoint_age>sync_water_mark这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似LOAD DATA的BULK INSERT操作。此时触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age

可见，Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始——也就是MySQL 5.6版本，这部分的刷新操作同样放入到了单独的Page Cleaner Thread中，故不会阻塞用户查询线程。

解释下为什么重做日志文件不可用时，这时需要强制将一些脏页刷新回磁盘？

因为我们知道redo的作用是保证数据库的一致性，当数据库异常停机时，需要借助redo+undo进行实例恢复，redo前滚---恢复出buffer pool中的脏数据（包括已经commit还没有刷新到磁盘的，也可能包括没有commit，但是已经刷新到磁盘的，）然后借助undo完成回滚---将没有commit，但是已经刷新到磁盘的数据，回滚到之前的状态。那么为啥重做日志文件不可用时，这时需要强制将一些脏页刷新回磁盘？原因就在于，redo 是循环覆写的，当redo log 文件不可用，也就是说此时所有的redo 文件里面的redo都是实例恢复需要的，也就是不能被覆盖的redo, 那么什么是实例恢复需要的redo呢？就是buffer pool中的的脏数据，还没有刷新到磁盘，而这些脏数据相关的redo是不能被覆盖的，这些redo就是实例恢复需要的redo，所以没有可用的重做日志文件，需要强制将一些脏页刷新回磁盘，这样就会有一些redo是实例恢复不需要的了，也就可以被覆盖了。

4、Dirty Page too much

即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct' ;

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| innodb_max_dirty_pages_pct | 75 |

+----------------------------+-------+

innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。在InnoDB 1.0.x版本之前，该参数默认值为90，之后的版本都为75，其可以通过参数innodb_max_dirty_pages_pct来设置；

总结下redo刷新的条件（因为刷新innodb_buffer_pool中的脏数据之前需要刷新redo,所以触发刷新buffer_pool会同时触发刷新redo）：

1）当redo log buffer达到一定比值后，

2）刷新innodb_buffer_pool中的脏数据之前，

3）redo log buffer满的时候，没有可用buffer；

4）每秒刷新一次；

5）commit的时候；

6）数据库关闭时发生harp Checkpoint，触发将所有脏页刷回磁盘

7）手工flush logs;

8）重做日志不可用时，触发刷新innodb_buffer_pool中的脏数据，进而触发redo刷新；

三：MySQL binlog：主从同步主库binlog先写入到 binlog_buffer中，然后刷新到磁层磁盘也就是binlog文件，主库dump进程读取的binlog文件，发送给从库；

binlog日志是针对整个MySQL server而言的，前面介绍的redo和undo是针对innodb引擎而言的，binlog的存在就是方便那些不支持事务的引擎表来同步数据到slave；

那么到底是先刷新redo还是先写binlog呢？

伴随着这个问题，我重点说下，MySQL innodb 引擎事务commit的过程：

MySQL为了保证master和slave的数据一致性，就必须保证binlog和InnoDB redo日志的一致性，为此MySQL引入二阶段提交（two phase commit or 2pc），MySQL通过两阶段提交(内部XA的两阶段提交)很好地解决了这一问题，两阶段提交关键在于保证redo刷盘之后才能刷新binlog到底层文件，以 binlog 的写入与否作为事务提交成功与否的标志，最后判断 binlog中是否有 redo里的xid,MySQL5.6以前，为了保证数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致，MySQL数据库内部使用了prepare_commit_mutex锁。但是持有这把锁之后，会导致组提交失败；直到MySQL5.6之后，才解决了这个问题，借助序列来保证binlog刷新也可以组提交；关于redo 和binlog组提交，请看下一篇文章，

事务崩溃恢复过程如下：

1.崩溃恢复时，扫描最后一个Binlog文件，提取其中的xid；

2.InnoDB维持了状态为Prepare的事务链表（commit两阶段提交中的第一阶段，为Prepare阶段，会把事务设置为Prepare状态）将这些事务的xid和Binlog中记录的xid做比较，如果在Binlog中存在，则提交，否则回滚事务。

通过这种方式，可以让InnoDB redo 和Binlog中的事务状态保持一致。

四：简单介绍下MySQL的后台进程：

InnoDB存储引擎是多线程模型，因此其后台有多个不同的后台线程，负责处理不同的任务：

1）Master Thread

Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

2）IO Thread

InnoDB存储引擎中大量使用了Async IO来处理写IO请求，这样可以极大提高数据库的性能，而IO Thread的主要工作是负责这些IO请求的回调处理，可以使用show engine innodb status命令查看InnoDB存储引擎中的IO进程：

mysql> show engine innodb status\g;

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (read thread)

I/O thread 7 state: waiting for completed aio requests (read thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

I/O thread 10 state: waiting for completed aio requests (write thread)

I/O thread 11 state: waiting for completed aio requests (write thread)

Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,

ibuf aio reads:, log i/o’s:, sync i/o’s:

Pending flushes (fsync) log: 0; buffer pool: 0

451 OS file reads, 54 OS file writes, 7 OS fsyncs

3.77 reads/s, 16384 avg bytes/read, 1.05 writes/s, 0.13 fsyncs/s

如上显示的是6个io read thread和4个io write thread，但是关于log的io thread 和insert buffer thread的io thread 只有一个；从MySQL 5.6开始默认是四个io read thread和4个io write thread，并且可以通过innodb_read_io_threads 和innodb_write_io_threads 参数进行设置：

mysql> show variables like '%io_threads%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_read_io_threads | 6 |

| innodb_write_io_threads | 4 |

+-------------------------+-------+

2 rows in set (0.00 sec)

3）Purge Thread

事务被提交后，其所使用的undo log可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。从InnoDB1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率、提升存储引擎的性能。可以通过在MySQL数据库的配置文件中添加相关的命令来启用独立的Purge Thread，如下参数：

mysql> show variables like 'innodb_purge_threads';

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| innodb_purge_threads | 1 |

+----------------------+-------+

1 row in set (0.00 sec)

Page Cleaner Thread

4）Page Cleaner Thread
是在InnoDB 1.2.x版本中引入的，其作用是将之前版本中的脏页的刷新操作都放入到单独的进程中来完成，目的就是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。
然后回答最开始的问题:

在内存中先写undo,然后写redo,至于redo和binlog顺序不确定, 刷盘是先刷undo,然后刷redo,最后刷新binlog;

网站题目：MySQLinnodb引擎的事务执行过程
URL分享：http://dzwzjz.com/article/gsjghs.html

大橙子网站建设，新征程启航

MySQLinnodb引擎的事务执行过程

其他资讯