|
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) 请问这是不是就是一个空的未提交的事物？ |
|
|
yejr
2025-2-4 22:13:08
| |
|
|
earl86
2025-2-7 09:21:34
| |
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
