SELECT trx_mysql_thread_id, trx_state, trx_started, trx_query FROM information_schema.innodb_trx where trx_state = "RUNNING" and trx_query like "%SELECT%" ORDERBY `trx_started`;
kill {trx_mysql_thread_id};
-- To check again by query information_schema.innodb_trx. -- Sometimes need kill two times.
After killed the query thread. The client receive a error message 2013 - Lost connection to server during query.
Query information_schema.processlist
id: thread ID
time: cost time in seconds
state: Sending Data, executing
1 2 3 4 5 6 7 8 9 10
SELECT* FROM information_schema.processlist WHERE info like'%SELECT%' orderby `time` desc;
kill {ID};
-- To check again by query information_schema.processlist. -- Sometimes need kill two times.
Kill Locked SQL Statements
SQL statement 1 to lock the table
1 2 3
SET autocommit =0; START TRANSACTION; update t_user set age =2;
1 2 3
COMMIT; ROLLBACK; SET autocommit =1;
SQL statement 2 waiting for lock
1
update t_user set age =3;
If waiting for lock is timeout, SQL statement 2 will receive a error message
Query whether some SQL statement treads are running with lock
1 2 3 4
SELECT* FROM `information_schema`.`innodb_trx` where trx_state = "RUNNING" and trx_tables_locked >0and trx_rows_locked >0 ORDERBY `trx_started`;
trx_state: RUNNING
trx_started: 2022-10-21 14:09:57
trx_mysql_thread_id: 16 (thread ID)
trx_tables_locked: 1
trx_lock_structs: 2
trx_rows_locked: 3
trx_row_modified: 2
Get More Locked Information
Query what table is locked
1
showopen tables where in_use >0;
Query Lock Information
1
SHOW ENGINE INNODB STATUS;
To find “TRANSACTION xxx, ACTIVE xxx sec” in the result text
Locked transaction Information
1 2 3
---TRANSACTION 580438, ACTIVE 1862 sec 2 lock struct(s), heap size 1136, 3row lock(s), undo log entries 2 MySQL thread id 16, OS thread handle 20276, query id 278 localhost ::1 root
Waiting for lock transaction Information
1 2 3 4 5 6
---TRANSACTION 580444, ACTIVE 36 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1row lock(s) MySQL thread id 17, OS thread handle 16228, query id 454 localhost ::1 root updating update t_user set age =3 ------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED
Check lock dependency - what blocks what
1 2
-- MySQL 5.x SELECT*FROM INFORMATION_SCHEMA.INNODB_LOCKS;
1 2 3
-- MySQL 8 SELECT*FROM performance_schema.data_locks; SELECT*FROM performance_schema.data_lock_waits;