mysql出现长事物，但是找不到与事物相关的sql
mysql> select * from information_schema.processlist where id=867278\G*************************** 1. row ***************************
ID: 867278
USER: smartbi
HOST: 192.168.3.4:58030
DB: smartbi
COMMAND: Sleep
TIME: 4531
STATE:
INFO: NULL
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 421449233294736
trx_state: RUNNING
trx_started: 2025-02-04 09:01:42
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 867278
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
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
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_locks\G
Empty set, 1 warning (0.00 sec)
mysql> select * from information_schema.innodb_lock_waits\G
Empty set, 1 warning (0.00 sec)
mysql> select * from performance_schema.threads where processlist_id=867278\G
*************************** 1. row ***************************
THREAD_ID: 867303
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 867278
PROCESSLIST_USER: smartbi
PROCESSLIST_HOST: 192.168.3.4
PROCESSLIST_DB: smartbi
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 4818
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 104147
1 row in set (0.00 sec)
mysql> select * from performance_schema.events_statements_current where thread_id=867303\G
*************************** 1. row ***************************
THREAD_ID: 867303
EVENT_ID: 4897
END_EVENT_ID: 4897
EVENT_NAME: statement/com/Close stmt
SOURCE:
TIMER_START: 6010392143442891768
TIMER_END: 6010392143452822768
TIMER_WAIT: 9931000
LOCK_TIME: 0
SQL_TEXT: NULL
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: smartbi
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_trx i,
-> performance_schema.events_statements_current c,
-> information_schema.processlist b,
-> performance_schema.threads t
-> where t.thread_id = c.thread_id
-> and i.trx_mysql_thread_id = b.id
-> and t.processList_id = b.id\G
*************************** 1. row ***************************
trx_id: 421449233294736
trx_state: RUNNING
trx_started: 2025-02-04 09:01:42
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 867278
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
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
THREAD_ID: 867303
EVENT_ID: 4897
END_EVENT_ID: 4897
EVENT_NAME: statement/com/Close stmt
SOURCE:
TIMER_START: 6010392143442891768
TIMER_END: 6010392143452822768
TIMER_WAIT: 9931000
LOCK_TIME: 0
SQL_TEXT: NULL
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: smartbi
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
ID: 867278
USER: smartbi
HOST: 192.168.3.4:58030
DB: smartbi
COMMAND: Sleep
TIME: 4930
STATE:
INFO: NULL
THREAD_ID: 867303
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 867278
PROCESSLIST_USER: smartbi
PROCESSLIST_HOST: 192.168.3.4
PROCESSLIST_DB: smartbi
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 4930
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 104147
1 row in set (0.01 sec)
mysql> select
-> now(),
-> a.trx_started,
-> (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,
-> b.id,b.user,
-> b.host,b.db,
-> d.SQL_TEXT
-> from information_schema.innodb_trx a inner join
-> information_schema.PROCESSLIST b
-> on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
-> inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
-> inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID\G
*************************** 1. row ***************************
now(): 2025-02-04 10:25:23
trx_started: 2025-02-04 09:01:42
diff_sec: 5021
id: 867278
user: smartbi
host: 192.168.3.4:58030
db: smartbi
SQL_TEXT: NULL
1 row in set (0.00 sec)
mysql> SELECT ps.id 'PROCESS ID', ps.USER,ps.HOST,ps.DB, esh.EVENT_ID, trx.trx_id, trx.trx_started, esh.event_name 'EVENT NAME', esh.sql_text 'SQL', ps.time FROM performance_schema.events_statements_history esh JOIN performance_schema.threads th ON esh.thread_id = th.thread_id JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_id IS NOT NULL AND ps.USER != 'SYSTEM_USER' ORDER BY esh.EVENT_ID;
+------------+---------+-----------------+---------+----------+-----------------+---------------------+--------------------------+------------------+------+
| PROCESS ID | USER | HOST | DB | EVENT_ID | trx_id | trx_started | EVENT NAME | SQL | time |
+------------+---------+-----------------+---------+----------+-----------------+---------------------+--------------------------+------------------+------+
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4888 | 421449233294736 | 2025-02-04 09:01:42 | statement/com/Execute | NULL | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4889 | 421449233294736 | 2025-02-04 09:01:42 | statement/com/Close stmt | NULL | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4890 | 421449233294736 | 2025-02-04 09:01:42 | statement/sql/set_option | SET autocommit=1 | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4891 | 421449233294736 | 2025-02-04 09:01:42 | statement/sql/set_option | SET autocommit=0 | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4892 | 421449233294736 | 2025-02-04 09:01:42 | statement/sql/commit | commit | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4893 | 421449233294736 | 2025-02-04 09:01:42 | statement/sql/set_option | SET autocommit=1 | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4894 | 421449233294736 | 2025-02-04 09:01:42 | statement/sql/set_option | SET autocommit=0 | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4895 | 421449233294736 | 2025-02-04 09:01:42 | statement/com/Prepare | NULL | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4896 | 421449233294736 | 2025-02-04 09:01:42 | statement/com/Execute | NULL | 5059 |
| 867278 | smartbi | 192.168.3.4:58030 | smartbi | 4897 | 421449233294736 | 2025-02-04 09:01:42 | statement/com/Close stmt | NULL | 5059 |
+------------+---------+-----------------+---------+----------+-----------------+---------------------+--------------------------+------------------+------+
10 rows in set (0.01 sec)
请问这是不是就是一个空的未提交的事物？
你的最后一条SQL查询结果表示该事务之前执行过的历史命令，看起来确实像是个只执行了修改 autocommit 模式的空事务，不过可能是因为收到参数 performance_schema_events_statements_history_size 默认值为 10 的限制，导致只能显式最近的 10 条命令。
总的来看，这个事务看起来没锁定任何表、行数据，也未修改任何数据，应该可以kill而不担心产生数据丢失等风险。 试试这个sql, 检查空闲的超过60秒,并且有未释放事务的线程.
select '==========INNODB_TRX==========', it.*,'==========processlist==========', p.*, '==========threads==========', t.*, '==========events_statements_history==========', es.*, '==========trx_runing_time==========', (unix_timestamp(now())-unix_timestamp(trx_started)) as trx_runing_time from information_schema.INNODB_TRX it, information_schema.processlist p, performance_schema.events_statements_history es, performance_schema.threads t where es.THREAD_ID=t.THREAD_ID and t.PROCESSLIST_ID=it.trx_mysql_thread_id and it.trx_mysql_thread_id=p.ID and p.COMMAND='Sleep' having (unix_timestamp(now())-unix_timestamp(it.trx_started))>60 order by es.TIMER_START asc\G;
页:
[1]