MySQL 事务提交过程

开发老大要求通过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.cnblogs.com/exceptioneye/p/5451976.html
    
    

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

Data Guard跳归档恢复的案例 - 2016-08-18 14:08:08

自前些天写了一个脚本之后,今天特意测试了一下,没想到一下子发现了一个大问题。有一套一主两备的10gR2环境,一个异机备库一直在READ ONLY状态,也就意味着数据库在打开之后一直忘了恢复应用归档,然后在某一天发现时,已经延迟了好几个月。无论怎样,还得庆幸发现了这个问题。 目前来看一种行之有效的方法就是重搭备库,但是这种修复方式需要大量的磁盘空间,而且需要恢复的时间较长,怎么改进呢,可以考虑通过基于SCN的增量备份来跳归档恢复。目前的环境是一主两备,再怎么改进呢,我们可以基于备库1来完成基于SCN的增量备份
背景 本文对 5.6 主备场景下,在备库做物理备份遇到死锁的case进行分析,希望对大家有所帮助。 这里用的的物理备份工具是 Percona-XtraBackup(PXB),有的同学可能不清楚其备份流程,所以这里先简单说下,PXB的备份步骤是这样的: 拷贝 InnoDB redo log,这是一个单独的线程在拷,直到备份结束; 拷贝所有InnoDB ibd文件; 加全局读锁,执行 FLUSH TABLES WITH READ LOCK(FTWRL); 拷贝 frm、MYD、MYI 等文件; 获取位点信息,
链接: http://blog.itpub.net/28602568/viewspace-2123386/ 标题: Oracle not exist子查询全扫的优化   作者: lōττéry ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.] 前言:   之前写过  Oracle 针对子查询里有group by 表全扫的优化 ( 子查询和外层关系是left join);   本次介绍 子查询与外层是not exists的关系是如何优化子查询全扫的;    优化前: SQL SET
【故障处理】 ORA-12545: Connect failed because target host or object does not exist 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  错误 ORA-12545: Connect failed because target host or object does not exis
今天写了个脚本,虽然实现的功能不多,但是个人感觉是一个好的开始,架子出来了,后面要补充的细节加进来就逐步完善了。 这个脚本的运行效果如下: OS     Version  is :[ RHEL_6.3 ] Oracle Version  is :[ 11.2.0.3.0] Oracle Instance is :[ dgtest ] dgtest ORACLE_HOME     is :[ /U01/app/oracle/product/11.2.0.2/db_1  ] Oracle  status  is

DUAL系列 - 2016-08-12 17:08:18

DUAL 系列   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① DUAL遭到破坏后的重建(重点) ②  关于参数replication_dependency_tracking简介 ③ DUAL简介     Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.
今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。 现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合

Oracle如何删除表中重复记录 - 2016-08-11 14:08:09

Oracle如何删除表中重复记录 1    引言 在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来 读诸 多不便,那么怎么删除这些重复没有用的数据呢 ? 平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452  :不能创建唯一索引,发现重复记录。 2    处理过程 重复的数据可能有这样两种情况 : 第一种 是 表中只有某些字段一样,第二种是两行记录完全一样 。删除重复记录后的结果也分为 2 种, 第一种 是重复的

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=
今天总算抽了些时间把半自动化的脚本完成了大半,目前还缺少两部分的脚本,一部分是安装前的检查脚本,可以做一个预检查。虽然目前来看还不是必须,但是这些是标准和规范的地方,这些条件不满足,失败的概率会加大。另外一部分是安装后的补充脚本,其实安装后还有很多需要注意的地方。 大体想了下,补充的脚本包含下面的部分。 配置crontab,目前的常用job是定期删除归档,定期检查监听的情况 配置iptables ,把主库的防火墙信息拷贝过来,或者作为静态备份,需要是启用 配置大页,这个可以在优化的基础上进行计算,在内核参数