Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL query transaction status field example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. The trx_state field of information_schema.INNODB_TRX

The source code of this field is annotated as follows:

Trx_que_t que_state; / *!

< valid when trx->

State = = TRX_STATE_ACTIVE: TRX_QUE_RUNNING, TRX_QUE_LOCK_WAIT,... * / * * Transaction execution states when trx- > state = = TRX_STATE_ACTIVE * / enum trx_que_t {TRX_QUE_RUNNING, / *!

< transaction is running */ TRX_QUE_LOCK_WAIT, /*!< transaction is waiting for a lock */ TRX_QUE_ROLLING_BACK, /*!< transaction is rolling back */ TRX_QUE_COMMITTING /*!< transaction is committing */}; 在测试中发现即便是commit下的也会是RUNNING状态，如下： 因此主要取值有3个 TRX_QUE_RUNNING RUNNING TRX_QUE_LOCK_WAIT LOCK WAIT TRX_QUE_ROLLING_BACK ROLLING BACK 但是COMMITTING状态正确commit流程不触发。 二、information_schema.INNODB_TRX 的trx_operation_state字段 这个字段取值很多，标记了事务的各个阶段，主要来自于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\ gateway * 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. The STATE field of performance_schema.events_transactions_current

This value also has only three values:

Enum enum_transaction_state {TRANS_STATE_ACTIVE= 1, TRANS_STATE_COMMITTED= 2, TRANS_STATE_ROLLED_BACK= 3}

The conversion from TRANS_STATE_ACTIVE to TRANS_STATE_COMMITTED occurs after the innodb layer commit is completed, which is when the entire prapare flush sync commit is actually over, and it is of little use to divide it by the ACTIVE state. As follows:

# 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) n6117 DBUG_ASSERT (state! = NULL) (gdb) n6119 ulonglong timer_end= 0; (gdb) n6120 ulonglong wait_time= 0; (gdb) n6121 uint flags= state- > masks; (gdb) n6123 if (flags & STATE_FLAG_TIMED) (gdb) n6125 timer_end= state- > m_timer (); (gdb) n6126 wait_time= timer_end-state- > m_timer_start (gdb) n6131 if (flags & STATE_FLAG_THREAD) (gdb) n6133 PFS_thread * pfs_thread= reinterpret_cast (state- > m_thread); (gdb) n6134 DBUG_ASSERT (pfs_thread! = NULL); (gdb) n6137 stat= & pfs_thread- > write_instr_class_transactions_stats () [GLOBAL_TRANSACTION_INDEX] (gdb) n6139 if (flags & STATE_FLAG_EVENT) (gdb) n6141 PFS_events_transactions * pfs= reinterpret_cast (state- > m_transaction); (gdb) n6142 DBUG_ASSERT (pfs! = NULL); (gdb) n6145 if (unlikely (pfs- > m_class = = NULL)) (gdb) n6148 pfs- > makeshift = timer_end; (gdb) n6149 pfs- > makeshift eventual principid = pfs_thread- > m_event_id (gdb) n6151 pfs- > state = (commit? TRANS_STATE_COMMITTED: TRANS_STATE_ROLLED_BACK); (gdb) p pfs- > m_state$1 = TRANS_STATE_ACTIVE Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL query transaction status Field" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.