MySQL 事务提交过程

共计 5627 个字符，预计需要花费 15 分钟才能阅读完成。

开发老大要求通过 binlog 查询一条被修改的数据，数据被查出后问我，有没有可能 binlog 中不会记录，回答不会，因为数据被修改，若失败直接回滚，不会在 binlog 中记录，此刻一个朋友用了洪荒之力告诉我，失败的话也会记录，坐地无语，因为他 sqlserver dba，用 sqlserver 的思维考虑 MySQL，哈哈哈哈哈，用实验让他闭嘴！

简单测试步骤如下：
root(yoon)> flush logs;
Query OK, 0 rows affected (0.01 sec)

root((none))> show binlog events in ‘mysql-bin.000041’; 
+——————+—–+————-+———–+————-+—————————————+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+——————+—–+————-+———–+————-+—————————————+
| mysql-bin.000041 |  4 | Format_desc |    232242 |        120 | Server ver: 5.6.26-log, Binlog ver: 4 |
+——————+—–+————-+———–+————-+—————————————+

root(yoon)> begin;
Query OK, 0 rows affected (0.00 sec)

root(yoon)> update yoon set id=7 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在没有 commit 情况下，二进制日志的位置偏移量未发生变化：
root(yoon)> show binlog events in ‘mysql-bin.000041’;
+——————+—–+————-+———–+————-+—————————————+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+——————+—–+————-+———–+————-+—————————————+
| mysql-bin.000041 |  4 | Format_desc |    232242 |        120 | Server ver: 5.6.26-log, Binlog ver: 4 |
+——————+—–+————-+———–+————-+—————————————+

root(yoon)> commit;
Query OK, 0 rows affected (1.01 sec)

提交后再次查看日志偏移量，发生变化，并记录在 binlog 中
root(yoon)> show binlog events in ‘mysql-bin.000041’;
+——————+—–+————-+———–+————-+———————————————+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+——————+—–+————-+———–+————-+———————————————+
| mysql-bin.000041 |  4 | Format_desc |    232242 |        120 | Server ver: 5.6.16-log, Binlog ver: 4      |
| mysql-bin.000041 | 120 | Query      |    232242 |        199 | BEGIN                                      |
| mysql-bin.000041 | 199 | Query      |    232242 |        304 | use `yoon`; update yoon set id=7 where id=1 |
| mysql-bin.000041 | 304 | Xid        |    232242 |        335 | COMMIT /* xid=18 */                        |
+——————+—–+————-+———–+————-+———————————————+

MySQL 事务提交过程
开启 binlog 后事务提交流程会变成两阶段提交，这里的两阶段提交并不涉及分布式事务，当然 mysql 把它称之为内部 xa 事务（Distributed Transactions），与之对应的还有一个外部 xa 事务。

这里所谓的两阶段提交分别是 prepare 阶段和 commit 阶段。

内部 xa 事务主要是 mysql 内部为了保证 binlog 与 redo log 之间数据的一致性而存在的，这也是由其架构决定的(binlog 在 mysql 层，而 redo log 在存储引擎层)；

外部 xa 事务则是指支持多实例分布式事务，这个才算是真正的分布式事务。

既然是 xa 事务，必然涉及到两阶段提交，对于内部 xa 而言，同样存在着提交的两个阶段。

下文会结合源码详细解读内部 xa 的两阶段提交过程，以及各种情况下，mysqld crash 后，mysql 如何恢复来保证事务的一致性。

数据库版本：5.6.16

操作系统版本：CentOS 6.5

配置文件参数：
log-bin=/my/log/mysql-bin

binlog_format=ROW

set autocommit=0

innodb_support_xa=1

sync_binlog=1

innodb_flush_log_at_trx_commit=1

【innodb_flush_log_at_trx_commit=1，sync_binlog=1

不同的模式区别在于，写文件调用 write 和落盘 fsync 调用的频率不同，所导致的后果是 mysqld 或 os crash 后，不严格的设置可能会丢失事务的更新。

双一模式是最严格的模式，这种设置情况下，单机在任何情况下不会丢失事务更新。】

测试条件：
set autocommit=0;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(20) NOT NULL,

`account` varchar(20) NOT NULL,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `id` (`id`) USING BTREE,

KEY `name` (`name`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试语句：
insert into user values(1, ‘sanzhang’, ‘ 张三 ’);

commit;

prepare 阶段：

    1. 设置 undo state=TRX_UNDO_PREPARED；//trx_undo_set_state_at_prepare 调用

    2. 刷事务更新产生的 redo 日志；【步骤 1 产生的 redo 日志也会刷入】
   
   
MYSQL_BIN_LOG::prepare

ha_prepare_low

    {

engine:

binlog_prepare

innobase_xa_prepare

mysql:

trx_prepare_for_mysql

{

                1.trx_undo_set_state_at_prepare    // 设置 undo 段的标记为 TRX_UNDO_PREPARED

                2. 设置事务状态为 TRX_STATE_PREPARED

                3.trx_flush_log_if_needed  // 将产生的 redolog 刷入磁盘

            }

    }
   
   

commit 阶段：

  1. 将事务产生的 binlog 写入文件，刷入磁盘；

  2. 设置 undo 页的状态, 置为 TRX_UNDO_TO_FREE 或 TRX_UNDO_TO_PURGE;  // trx_undo_set_state_at_finish 调用

  3. 记录事务对应的 binlog 偏移，写入系统表空间; //trx_sys_update_mysql_binlog_offset 调用
 
MYSQL_BIN_LOG::commit

    ordered_commit

  {

1.FLUSH_STAGE

        flush_cache_to_file  //  刷 binlog

2.SYNC_STAGE

        sync_binlog_file    //Call fsync() to sync the file to disk.

3.COMMIT_STAGE

        ha_commit_low

        {

            binlog_commit

            innobase_commit 

                trx_commit(trx)

                {

                    trx_write_serialisation_history(trx, mtr);  // 更新 binlog 位点，设置 undo 状态

                    trx_commit_in_memory(trx, lsn); // 释放锁资源，清理保存点列表，清理回滚段

                }       

        }

    }
   
在任何情况下（机器掉电）mysqld crash 或者 os crash，MySQL 仍然能保证数据库的一致性。数据的一致性是如何做到的哪？正是二阶段提交。

我们结合几种场景来分析下二阶段提交是如何做到的：

1.prepare 阶段，redo log 落盘前，mysqld crash

2.prepare 阶段，redo log 落盘后，binlog 落盘前，mysqld crash

3.commit 阶段，binlog 落盘后，mysqld crash

对于第一种情况，由于 redo 没有落盘，毫无疑问，事务的更新肯定没有写入磁盘，数据库的一致性受影响；

对于第二种情况，这时候 redo log 写入完成，但 binlog 还未写入，事务处于 TRX_STATE_PREPARED 状态，这是提交还是回滚呢？

对于第三种情况，此时，redo log 和 binlog 都已经落盘，只是 undo 状态没有更新，虽然 redo log 和 binlog 已经一致了，事务是否应该提交？

我们结合 mysqld 异常重启后的执行逻辑以及关键的源代码。

对于第三种情况，我们可以搜集到未提交事务的 binlog event，所以需要提交；

对于第二种情况，由于 binlog 未写入，需要通过执行回滚操作来保证数据库的一致性。

异常重启后，如何判断事务该提交还是回滚

1. 读 binlog 日志，获取崩溃时没有提交的 event；//info->commit_list 中含有该元素

2. 若存在，则对应的事务要提交；否则需要回滚。

判断事务提交或回滚源码如下：

上面讨论了两阶段提交的基本流程，以及服务器异常 crash 后，mysql 如何重启恢复保证 binlog 和数据的一致性。

简而言之，对于异常的 xa 事务，若 binlog 已落盘，则事务应该提交；binlog 未落盘，则事务就应该回滚。

// 异常重启后，回滚流程

 innobase_rollback_by_xid

rollback_by_xid

trx_rollback_resurrected

    trx_rollback_active

        row_undo

        {// 从回滚页获取 undo 记录 // 分析 undo 记录类型 if (insert)

                row_undo_ins else row_undo_mod

        }

// 异常重启后，提交流程
 commit_by_xid

trx_commit_for_mysql

// 写 binlog 接口
 handler.cc:binlog_log_row

sql/binlog.cc:commit

mysys/my_sync:my_sync

sql/binlog.cc:sync_binlog_file

handler/ha_innodb.cc:innobase_xa_prepare

binlog 日志文件是为了解决 MySQL 主从复制功能而引入的���份新日志文件，它包含了引发数据变更的事件日志集合。

从库请求主库发送 binlog 并通过日志事件还原数据写入从库，所以从库的数据来源为 binlog。

这样 MySQL 主库只需做到 binlog 与本地数据一致就可以保证主从库数据一致（暂且忽略网络传输引发的主从不一致）。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134648.htm