ps_thread_trx_info()

• 在events_transactions_current、events_transactions_history、events_statements_history表中查询并返回指定内部线程ID的事务、语句事件信息(json格式返回)，这些事件信息包括当前正在执行的事务以及已经执行完成的语句信息(必须启用events_transactions_current、events_transactions_history、events_statements_history对应的consumers配置才能够获取这些信息)

  • 如果ps_thread_trx_info()函数输出json格式字符串长度超过默认的65535字节长度，则返回json错误对象(如：{ "error": "Trx info truncated: Row 6 was cut by GROUP_CONCAT()" })，然后函数中会对该错误做进一步处理
  • 此函数在MySQL 5.7.9中新增

• 参数：

  • in_thread_id BIGINT UNSIGNED：用于返回事务、语句事件信息的内部线程ID，该值与performance_schema.threads表中的THREAD_ID列值对应

• 配置选项：

  • ps_thread_trx_info.max_length，@sys.ps_thread_trx_info.max_length：控制ps_thread_trx_info()函数输出的最大字节长度，默认为65535字节

• 返回值：一个LONGTEXT长文本值

• 定义语句（由于定义语句太长，详见如下文本链接）

• 示例

root@localhost : (none) 02:47:50> select sys.ps_thread_trx_info(50)\G
*************************** 1. row ***************************
sys.ps_thread_trx_info(50): [
  {
    "time": "10.99 m",
    "state": "ACTIVE",
    "mode": "READ WRITE",
    "autocommitted": "NO",
    "gtid": "AUTOMATIC",
    "isolation": "READ COMMITTED",
    "statements_executed": [
      {
        "sql_text": "select * from t_luoxiaobo limit 200",
        "time": "544.65 us",
        "schema": "luoxiaobo",
        "rows_examined": 200,
        "rows_affected": 0,
        "rows_sent": 200,
        "tmp_tables": 0,
        "tmp_disk_tables": 0,
        "sort_rows": 0,
        "sort_merge_passes": 0
      }
    ]
  }
]
1 row in set (0.01 sec)

root@localhost : (none) 02:58:42> select sys.ps_thread_trx_info(50)\G
*************************** 1. row ***************************
sys.ps_thread_trx_info(50): [
  {
    "time": "12.51 m",
    "state": "COMMITTED",
    "mode": "READ WRITE",
    "autocommitted": "NO",
    "gtid": "AUTOMATIC",
    "isolation": "READ COMMITTED",
    "statements_executed": [
      {
        "sql_text": "select * from t_luoxiaobo limit 200",
        "time": "544.65 us",
        "schema": "luoxiaobo",
        "rows_examined": 200,
        "rows_affected": 0,
        "rows_sent": 200,
        "tmp_tables": 0,
        "tmp_disk_tables": 0,
        "sort_rows": 0,
        "sort_merge_passes": 0
      },
      {
        "sql_text": "commit",
        "time": "55.36 us",
        "schema": "luoxiaobo",
        "rows_examined": 0,
        "rows_affected": 0,
        "rows_sent": 0,
        "tmp_tables": 0,
        "tmp_disk_tables": 0,
        "sort_rows": 0,
        "sort_merge_passes": 0
      }
    ]
  }
]
1 row in set (0.00 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-trx-info.html

---

上一篇： 函数 | 下一篇： 函数