By

Contents
  1. 1. Kill Long Running Queries
    1. 1.1. Query information_schema.innodb_trx
    2. 1.2. Query information_schema.processlist
  2. 2. Kill Locked SQL Statements
    1. 2.1. Get locked and waiting for lock thread IDs
    2. 2.2. Get More Locked Information
    3. 2.3. Kill the Locked Tread
  3. 3. References

Kill Long Running Queries

Query information_schema.innodb_trx

  • trx_mysql_thread_id: thread ID
1
2
3
4
5
6
7
8
9
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%"
ORDER BY `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%'
order by `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

1
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Get locked and waiting for lock thread IDs

Query whether some SQL statement threads are waiting for lock

1
2
3
4
SELECT *
FROM `information_schema`.`innodb_trx` 
where trx_state = "LOCK WAIT"
ORDER BY `trx_started`;
  • trx_state: LOCK WAIT
  • trx_started: 2022-10-21 14:13:38
  • trx_mysql_thread_id: 17 (thread ID)
  • trx_requested_lock_id: 1207867061312:1021:4:2:1207832760632
  • trx_wait_started: 2022-10-21 14:13:38

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 > 0 and trx_rows_locked > 0
ORDER BY `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
show open 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, 3 row 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, 1 row 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;

Kill the Locked Tread

1
2
kill {thread_ID};
kill 16;

References

Contents
  1. 1. Kill Long Running Queries
    1. 1.1. Query information_schema.innodb_trx
    2. 1.2. Query information_schema.processlist
  2. 2. Kill Locked SQL Statements
    1. 2.1. Get locked and waiting for lock thread IDs
    2. 2.2. Get More Locked Information
    3. 2.3. Kill the Locked Tread
  3. 3. References