MySQL长时间未提交事务分析

在数据库运维经验中经常处理的一类故障性能问题就是长时间未提交事务。在换版日常常有开发同事找过来说执行DDL操作时候报锁超时。而绝大部分大部分DDL锁超时问题都是由长时间未提交事务引起的。所以不管是做开发的朋友还是数据库运维的朋友都应该对长时间未提交事务有所了解并尽量避免。本文介绍长时间未提交事务的原因、造成的故障、优化方案。一、什么是长时间未提交事务未提交事务指的是显示开启的事务中sql执行完很长时间后仍然未执行commit。这里要和大事务做一个区分大事务指的是一个事务操作的行记录数较多并且执行时间较长。一般是由DBever客户端发起的sql容易造成长时间未提交事务。长时间未提交事务一般是从DBever客户端发起的select语句日常可能只是查询一些库、表信息然后忘了关闭客户端sql执行完了但事务没有提交。二、长时间未提交事务造成的危害1、元数据锁冲突今天时间有限这里我们留到下次讲解给大家留一个悬念。2、主从延迟陡增陡降我在“MySQL数据库binlog解析”这篇文章中总结过每生成一个新的binlog至少包含2个event第一个是FORMAT_DESCRIPTION_EVENT第二个是PREVIOUS_GTIDS_LOG_EVENT。当执行了DML语句或者DDL语句之后则会先生成GTID_EVENT或者Anonymous_GTID_EVENT接着是对应事务begin语句的QUERY_EVENT、MAP_EVENT以及sql语句对应的WRITE_EVENT/DELETE_EVENT/UPDATE_EVENT最后是对应事务commit语句的XID_EVENT。每产生一个事务主库就会对应生成一系列的EVENT每个EVENT都有一个时间戳。长时间未提交事务造成主从延迟陡增陡降的根本原因其实就是这些EVENT的时间戳差异。这些EVENT按照先后顺序整理出来如下。1PREVIOUS_GTIDS_LOG_EVENT记录了Previous-GTIDS描述前面所有binlog包含的gtid set。对应的时间戳简单理解为事务begin的时间。2GTID_EVENT描述组提交中的逻辑时钟相关信息即last commit和seq number这两个值是从库回放事务时能否并行回放的依据对应的时间戳为事务commit的时间。3QUERY_EVENT记录语句的运行环境比如说客户端字符集、SQL_MODE等信息同时还会记录执行时间。对于DML语句会记录sql语句更改第一行数据时候都时间语句部分记录的是“begin”对于DDL语句记录的是sql真正开始执行的时间语句记录的是DDL语句本身。这对应的时间戳简单理解为事务begin的时间。4MAP_EVENT行模式特有的事件记录表在数据库内部映射的table id。对应的时间戳简单理解为事务begin的时间。5WRITE_EVENT/DELETE_EVENT/UPDATE_EVENT这些是真正执行insert、delete、update语句产生的EVENT。对应的时间戳简单理解为事务begin的时间。6XID_EVENT携带XID的EVENTXID是数据库在两阶段提交策略中用来判断一个事务是需要回滚还是重做的重要依据。记录的语句为“commit”对应的时间戳为事务commit的时间。主库显式发起一个事务后在事务执行过程中就会在数据库缓存中生成对应的EVENT当事务执行commit后这些EVENT会从主库通过binlog转储线程发送到从库。从库接收到EVENT后会开始回放这些EVENT。而从库的主从延迟计算公式从库的系统时间-正在回放的EVENT的时间戳-主从时间差用符号代替即为Seconds_beind_masterT0 - T1 - DIFF。其中DIFF是固定值可以先忽略认为0。假设一个长时间未提交事务真正的sql执行时间很短15点开始执行并且立刻就执行完成等到17点才执行commit。在网络状态良好情况下从库17点接收到对应事务EVENT并开始回放。回放PREVIOUS_GTIDS_LOG_EVENT、GTID_EVENT、QUERY_EVENT、MAP_EVENT、WRITE_EVENT时都执行的相对较快这里计算主从延迟的时候T0值为17点T1对应的时间都是15点对应的主从延迟是T0 - T1 - DIFF17-15-DIFF(0)2小时即7200秒也就是从库会在回放到WRITE_EVENT时主从延迟瞬间冲高到7200秒当从库回放到commit对应的XID_EVENT时T0值还是17点T1对应的时间都是17点对应的主从延迟是T0 - T1 - DIFF17-17-DIFF(0)0延迟瞬间降为0。三、如何优化未提交事务今天时间有限这里我们留到下次讲解给大家留一个悬念。