溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹了MySQL查詢事務(wù)狀態(tài)字段的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
這個字段源碼注釋如下：
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 */
};在測試中發(fā)現(xiàn)即便是commit下的也會是RUNNING狀態(tài)，如下：
因此主要取值有3個
TRX_QUE_RUNNING RUNNING
TRX_QUE_LOCK_WAIT LOCK WAIT
TRX_QUE_ROLLING_BACK ROLLING BACK
但是COMMITTING狀態(tài)正確commit流程不觸發(fā)。
這個字段取值很多，標記了事務(wù)的各個階段，主要來自于trx_t::op_info，在commit階段有如下取值：
其中preparing 和committing分別由函數(shù)trx_prepare_for_mysql和trx_commit_for_mysql進入，做完innodb層的prepare和commit后就會更改為NULL，實際的binlog的flush sync階段不包含其中，實際也很好測試，可以做一個大事務(wù)，會發(fā)現(xiàn) commit期間基本處于NULL狀態(tài)：
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的轉(zhuǎn)換會出現(xiàn)在innodb層提交完成后，也就是這個時候?qū)嶋H上整個prapare flush sync commit 已經(jīng)結(jié)束了，之前一直除以ACTIVE狀態(tài)，用處不大。如下：
#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查詢事務(wù)狀態(tài)字段的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼