这篇文章主要介绍了MySQL查询事务状态字段的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

这个字段源码注释如下：

trx_que_tque_state;/*!<validwhentrx->state==TRX_STATE_ACTIVE:TRX_QUE_RUNNING,TRX_QUE_LOCK_WAIT,...*//**Transactionexecutionstateswhentrx->state==TRX_STATE_ACTIVE*/enumtrx_que_t{TRX_QUE_RUNNING,/*!<transactionisrunning*/TRX_QUE_LOCK_WAIT,/*!<transactioniswaitingforalock*/TRX_QUE_ROLLING_BACK,/*!<transactionisrollingback*/TRX_QUE_COMMITTING/*!<transactioniscommitting*/};

在测试中发现即便是commit下的也会是RUNNING状态，如下：

因此主要取值有3个

TRX_QUE_RUNNING RUNNING

TRX_QUE_LOCK_WAIT LOCK WAIT

TRX_QUE_ROLLING_BACK ROLLING BACK

但是COMMITTING状态正确commit流程不触发。

这个字段取值很多，标记了事务的各个阶段，主要来自于trx_t::op_info，在commit阶段有如下取值：

其中preparing 和committing分别由函数trx_prepare_for_mysql和trx_commit_for_mysql进入，做完innodb层的prepare和commit后就会更改为NULL，实际的binlog的flush sync阶段不包含其中，实际也很好测试，可以做一个大事务，会发现 commit期间基本处于NULL状态：

mysql>select*frominformation_schema.INNODB_TRX\G***************************1.row***************************trx_id:66206trx_state:RUNNINGtrx_started:2019-08-2323:44:05trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:1046257trx_mysql_thread_id:4trx_query:committrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:1trx_lock_structs:2025trx_lock_memory_bytes:319960trx_rows_locked:1044232trx_rows_modified:1044232trx_concurrency_tickets:0trx_isolation_level:READCOMMITTED...三、performance_schema.events_transactions_current 的STATE字段

这个值也只有3个取值：

enumenum_transaction_state{TRANS_STATE_ACTIVE=1,TRANS_STATE_COMMITTED=2,TRANS_STATE_ROLLED_BACK=3};

TRANS_STATE_ACTIVE到TRANS_STATE_COMMITTED的转换会出现在innodb层提交完成后，也就是这个时候实际上整个prapare flush sync commit 已经结束了，之前一直除以ACTIVE状态，用处不大。如下：

#0pfs_end_transaction_v1(locker=0x7ffee0014ba8,commit=1'\001')at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:6116#10x0000000000f7d177ininline_mysql_commit_transaction(locker=0x7ffee0014ba8)at/mysqldata/percona-server-locks-detail-5.7.22/include/mysql/psi/mysql_transaction.h:206#20x0000000000f8026cinha_commit_trans(thd=0x7ffee00129d0,all=true,ignore_global_read_lock=false)at/mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:1843#30x00000000016dcd2fintrans_commit(thd=0x7ffee00129d0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/transaction.cc:239#40x00000000015cf466inmysql_execute_command(thd=0x7ffee00129d0,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:4526#50x00000000015d2fdeinmysql_parse(thd=0x7ffee00129d0,parser_state=0x7fffec5ee600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#60x00000000015c6b72indispatch_command(thd=0x7ffee00129d0,com_data=0x7fffec5eed70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#70x00000000015c58ffindo_command(thd=0x7ffee00129d0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#80x000000000170e578inhandle_connection(arg=0x67d6410)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#90x0000000001945538inpfs_spawn_thread(arg=0x67be520)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#100x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#110x00007ffff6b37c4dinclone()from/lib64/libc.so.6(gdb)n6117DBUG_ASSERT(state!=NULL);(gdb)n6119ulonglongtimer_end=0;(gdb)n6120ulonglongwait_time=0;(gdb)n6121uintflags=state->m_flags;(gdb)n6123if(flags&STATE_FLAG_TIMED)(gdb)n6125timer_end=state->m_timer();(gdb)n6126wait_time=timer_end-state->m_timer_start;(gdb)n6131if(flags&STATE_FLAG_THREAD)(gdb)n6133PFS_thread*pfs_thread=reinterpret_cast<PFS_thread*>(state->m_thread);(gdb)n6134DBUG_ASSERT(pfs_thread!=NULL);(gdb)n6137stat=&pfs_thread->write_instr_class_transactions_stats()[GLOBAL_TRANSACTION_INDEX];(gdb)n6139if(flags&STATE_FLAG_EVENT)(gdb)n6141PFS_events_transactions*pfs=reinterpret_cast<PFS_events_transactions*>(state->m_transaction);(gdb)n6142DBUG_ASSERT(pfs!=NULL);(gdb)n6145if(unlikely(pfs->m_class==NULL))(gdb)n6148pfs->m_timer_end=timer_end;(gdb)n6149pfs->m_end_event_id=pfs_thread->m_event_id;(gdb)n6151pfs->m_state=(commit?TRANS_STATE_COMMITTED:TRANS_STATE_ROLLED_BACK);(gdb)ppfs->m_state$1=TRANS_STATE_ACTIVE

感谢你能够认真阅读完这篇文章，希望小编分享的“MySQL查询事务状态字段的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!