这篇文章主要介绍了Mysql查询事务状态字段的示例分析,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。一、infORMation_schema
这篇文章主要介绍了Mysql查询事务状态字段的示例分析,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
这个字段源码注释如下:
trx_que_t que_state;
enum trx_que_t {
TRX_QUE_RUNNING,
TRX_QUE_LOCK_WaiT,
TRX_QUE_ROLLING_BACK,
TRX_QUE_COMMITTING
};
在测试中发现即便是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 * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
trx_id: 66206
trx_state: RUNNING
trx_started: 2019-08-23 23:44:05
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1046257
trx_mysql_thread_id: 4
trx_query: commit
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2025
trx_lock_memory_bytes: 319960
trx_rows_locked: 1044232
trx_rows_modified: 1044232
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
...
这个值也只有3个取值:
enum enum_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状态,用处不大。如下:
#0 pfs_end_transaction_v1 (locker=0x7ffee0014ba8, commit=1 '\001') at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:6116
#1 0x0000000000f7d177 in inline_mysql_commit_transaction (locker=0x7ffee0014ba8)
at /mysqldata/percona-server-locks-detail-5.7.22/include/mysql/psi/mysql_transaction.h:206
#2 0x0000000000f8026c in ha_commit_trans (thd=0x7ffee00129d0, all=true, ignore_global_read_lock=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:1843
#3 0x00000000016dcd2f in trans_commit (thd=0x7ffee00129d0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/transaction.cc:239
#4 0x00000000015cf466 in mysql_execute_command (thd=0x7ffee00129d0, first_level=true) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:4526
#5 0x00000000015d2fde in mysql_parse (thd=0x7ffee00129d0, parser_state=0x7fffec5ee600) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#6 0x00000000015c6b72 in dispatch_command (thd=0x7ffee00129d0, com_data=0x7fffec5eed70, command=COM_QUERY)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#7 0x00000000015c58ff in do_command (thd=0x7ffee00129d0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#8 0x000000000170e578 in handle_connection (arg=0x67d6410) at /mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#9 0x0000000001945538 in pfs_spawn_thread (arg=0x67be520) at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#10 0x00007ffff7bcfaa1 in start_thread () from /lib64/libpthread.so.0
#11 0x00007ffff6b37c4d in clone () from /lib64/libc.so.6
(gdb) n
6117 DBUG_ASSERT(state != NULL);
(gdb) n
6119 ulonglong timer_end= 0;
(gdb) n
6120 ulonglong wait_time= 0;
(gdb) n
6121 uint flags= state->m_flags;
(gdb) n
6123 if (flags & STATE_FLAG_TIMED)
(gdb) n
6125 timer_end= state->m_timer();
(gdb) n
6126 wait_time= timer_end - state->m_timer_start;
(gdb) n
6131 if (flags & STATE_FLAG_THREAD)
(gdb) n
6133 PFS_thread *pfs_thread= reinterpret_cast<PFS_thread *> (state->m_thread);
(gdb) n
6134 DBUG_ASSERT(pfs_thread != NULL);
(gdb) n
6137 stat= &pfs_thread->write_instr_class_transactions_stats()[GLOBAL_TRANSACTION_INDEX];
(gdb) n
6139 if (flags & STATE_FLAG_EVENT)
(gdb) n
6141 PFS_events_transactions *pfs= reinterpret_cast<PFS_events_transactions*> (state->m_transaction);
(gdb) n
6142 DBUG_ASSERT(pfs != NULL);
(gdb) n
6145 if (unlikely(pfs->m_class == NULL))
(gdb) n
6148 pfs->m_timer_end= timer_end;
(gdb) n
6149 pfs->m_end_event_id= pfs_thread->m_event_id;
(gdb) n
6151 pfs->m_state= (commit ? TRANS_STATE_COMMITTED : TRANS_STATE_ROLLED_BACK);
(gdb) p pfs->m_state
$1 = TRANS_STATE_ACTIVE
感谢你能够认真阅读完这篇文章,希望小编分享的“MySQL查询事务状态字段的示例分析”这篇文章对大家有帮助,同时也希望大家多多支持编程网,关注编程网数据库频道,更多相关知识等着你来学习!
--结束END--
本文标题: MySQL查询事务状态字段的示例分析
本文链接: https://lsjlt.com/news/62278.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0