在数据库运维经验中，经常处理的一类故障性能问题就是长时间未提交事务。在换版日，常常有开发同事找过来，说执行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按照先后顺序整理出来如下。
1）PREVIOUS_GTIDS_LOG_EVENT：记录了Previous-GTIDS，描述前面所有binlog包含的gtid set。对应的时间戳简单理解为事务begin的时间。
2）GTID_EVENT：描述组提交中的逻辑时钟相关信息，即last commit和seq number，这两个值是从库回放事务时能否并行回放的依据，对应的时间戳为事务commit的时间。
3）QUERY_EVENT：记录语句的运行环境，比如说客户端字符集、SQL_MODE等信息，同时还会记录执行时间。对于DML语句，会记录sql语句更改第一行数据时候都时间，语句部分记录的是“begin”；对于DDL语句，记录的是sql真正开始执行的时间，语句记录的是DDL语句本身。这对应的时间戳简单理解为事务begin的时间。
4）MAP_EVENT：行模式特有的事件，记录表在数据库内部映射的table id。对应的时间戳简单理解为事务begin的时间。
5）WRITE_EVENT/DELETE_EVENT/UPDATE_EVENT：这些是真正执行insert、delete、update语句产生的EVENT。对应的时间戳简单理解为事务begin的时间。
6）XID_EVENT：携带XID的EVENT，XID是数据库在两阶段提交策略中用来判断一个事务是需要回滚还是重做的重要依据。记录的语句为“commit”，对应的时间戳为事务commit的时间。
主库显式发起一个事务后，在事务执行过程中就会在数据库缓存中生成对应的EVENT，当事务执行commit后，这些EVENT会从主库通过binlog转储线程，发送到从库。从库接收到EVENT后，会开始回放这些EVENT。而从库的主从延迟计算公式：从库的系统时间-正在回放的EVENT的时间戳-主从时间差，用符号代替即为Seconds_beind_master=T0 - 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 - DIFF=17-15-DIFF(0)=2小时即7200秒，也就是从库会在回放到WRITE_EVENT时，主从延迟瞬间冲高到7200秒，当从库回放到commit对应的XID_EVENT时，T0值还是17点，T1对应的时间都是17点，对应的主从延迟是T0 - T1 - DIFF=17-17-DIFF(0)=0，延迟瞬间降为0。
三、如何优化未提交事务
今天时间有限，这里我们留到下次讲解，给大家留一个悬念。
