1. 首页
  2. IT资讯

随笔: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讲 》,如下:

image.png
如果图片不能显示可查看下面链接:
https://www.jianshu.com/p/d636215d767f

一、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状态,如下:
t1.png

因此主要取值有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 '01') 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个取值都不是那么有用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2655092/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183934.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code