MySQL系统表information_schema.INNODB_TRX详解及查看当前运行事务

1. 目的

在日常管理数据库的过程中，有时需要查询MySQL数据库是否正在有正在执行的事务，便于排查业务问题。MySQL的系统库表有数据维护对应的信息，就在information_schema库中的INNODB_TRX表，包含事务中是否存在锁，事务开启时间，事务执行的语句等等。

2. 说明

2.1 查询正在执行的事务

SELECT * FROM information_schema.innodb_trx;

2.2 详细字段说明

3. 案例

3.1 表结构

MySQL [myhr]> desc city;         
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| city_code    | varchar(255) | NO   | PRI | NULL    |       |
| city_name    | varchar(255) | YES  |     | NULL    |       |
| parent_code  | varchar(255) | YES  | MUL | NULL    |       |
| key_cities   | varchar(255) | YES  |     | NULL    |       |
| abbreviation | varchar(255) | YES  |     | NULL    |       |
| city_level   | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11

数据：

MySQL [myhr]> select * from city limit 10;
+-----------+--------------+-------------+------------+--------------+------------+
| city_code | city_name    | parent_code | key_cities | abbreviation | city_level |
+-----------+--------------+-------------+------------+--------------+------------+
| 110000    | 北京市       | 0           | NULL       | NULL         | NULL       |
| 110100    | 北京市       | 110000      | NULL       | NULL         | NULL       |
| 110101    | 东城区       | 110100      | NULL       | NULL         | NULL       |
| 110102    | 西城区       | 110100      | NULL       | NULL         | NULL       |
| 110105    | 朝阳区       | 110100      | NULL       | NULL         | NULL       |
| 110106    | 丰台区       | 110100      | NULL       | NULL         | NULL       |
| 110107    | 石景山区     | 110100      | NULL       | NULL         | NULL       |
| 110108    | 海淀区       | 110100      | NULL       | NULL         | NULL       |
| 110109    | 门头沟区     | 110100      | NULL       | NULL         | NULL       |
| 110111    | 房山区       | 110100      | NULL       | NULL         | NULL       |
+-----------+--------------+-------------+------------+--------------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.2 查看事务

MySQL [(none)]> SELECT * FROM information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 22018017
                 trx_state: LOCK WAIT
               trx_started: 2022-11-23 12:43:48
     trx_requested_lock_id: 22018017:1028:5:2
          trx_wait_started: 2022-11-23 12:43:48
                trx_weight: 2
       trx_mysql_thread_id: 1635
                 trx_query: update city set city_level=12  where city_code=110100
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 22017539
                 trx_state: RUNNING
               trx_started: 2022-11-23 12:42:23
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 14
       trx_mysql_thread_id: 1633
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 13
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3686
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

参考文档：
MySQL官方文档