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    |       |+--------------+--------------+------+-----+---------+-------+

数据：

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       |+-----------+--------------+-------------+------------+--------------+------------+

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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0          trx_is_read_only: 0trx_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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0          trx_is_read_only: 0trx_autocommit_non_locking: 02 rows in set (0.00 sec)

参考文档：
MySQL官方文档

来源地址：https://blog.csdn.net/oschina_41731918/article/details/128005386