省流:
查询正在进行的事务
SELECT * FROM information_schema.innodb_trx;
#根据条件查processlist
select * from information_schema.processlist where id='xx';
杀死进程
kill id;
正文:
这次遇到一个进程执行时间过长,10几天...非常离谱。
执行:
SELECT * FROM information_schema.innodb_trx;
结果:
trx_id |512305234990167
trx_state |RUNNING
trx_started |2023-09-22 23:13:16
trx_requested_lock_id |
trx_wait_started |
trx_weight |0
trx_mysql_thread_id |612871
trx_query |/*ApplicationName=DataGrip 2020.3.2 */ select xx from xx where x
trx_operation_state |fetching rows
trx_tables_in_use |3
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 |READ COMMITTED
trx_unique_checks |1
trx_foreign_key_checks |1
trx_last_foreign_key_error|
trx_adaptive_hash_latched |0
trx_adaptive_hash_timeout |0
trx_is_read_only |1
trx_autocommit_non_locking|1
拿到trx_mysql_thread_id,执行:
select * from information_schema.processlist where id='612871';
结果:
ID |612871
USER |root
HOST |10.20.160.150:56532
DB |ali_cloud
COMMAND|Query
TIME |1677612
STATE |Sending data
INFO |/* ApplicationName=DataGrip 2020.3.2 */ select xx from xx where x
通过两个查询结果,发现这个线程是一个查询,一直在running,从2023-09-22 23:13:16到今天,已经有1677612秒了,非常离谱。赶紧kill掉。
执行:
kill 612871;
kill">