隨筆：MySQL 查詢事務狀態欄位說明

今天一個朋友想檢視一下的MySQL層事務提交狀態經歷的過程，比如我們常說的prapare flush sync commit 幾個階段，但是找了一下發現視乎沒有檢視可以看到一共看了3個地方：

• information_schema.INNODB_TRX 的trx_state欄位
• information_schema.INNODB_TRX 的trx_operation_state欄位
• performance_schema.events_transactions_current 的STATE欄位

其中information_schema.INNODB_TRX表由函式fill_innodb_trx_from_cache進行填充，最終發現他們均不能滿足需求。

歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：

一、information_schema.INNODB_TRX 的trx_state欄位

這個欄位原始碼註釋如下：

    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 \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
...

三、performance_schema.events_transactions_current 的STATE欄位

這個值也只有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

因此上面3個取值都不是那麼有用。