Kill Long Running and Locked SQL Statements in MySQL

Posted on Edited on In ,

Kill Long Running Queries

Query information_schema.innodb_trx

  • trx_mysql_thread_id: thread ID
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 receives an 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
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

An Example of Locked SQL Statements

Create a table for test

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (1, 'Jack', 20);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (2, 'Tom', 30);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (3, 'John', 22);

Executing the SQL statement 1 to lock the table

SET autocommit = 0;  
START TRANSACTION;
update t_user set age = 2;

Executing the SQL statement 2, which will wait for the lock.

-- Temporarily set the lock wait timeout to 10 minutes. By default, it is 50 seconds. We need a longer timeout to find out the locked SQL statements.
SET SESSION innodb_lock_wait_timeout = 600;
update t_user set age = 3;

If waiting for lock is timeout (by default, it is 50 seconds), SQL statement 2 will receive an error message

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

After finished the lock test, you can COMMIT or ROLLBACK the transaction of SQL statement 1 and set autocommit to 1.

COMMIT;
ROLLBACK;
SET autocommit = 1;

Get thread IDs and SQL statements of lock-holding and lock-waiting executing SQLs

Query whether some SQL statement threads are waiting for lock

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_query: the executing SQL statement
  • 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

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_query: the executing SQL statement
  • 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

show open tables where in_use > 0;

Query Lock Information

Get transaction IDs and real Thread IDs of lock-holding and lock-waiting executing SQL.

SHOW ENGINE INNODB STATUS;

To find “TRANSACTION xxx, ACTIVE xxx sec” in the result text

lock-holding transaction Information

---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

lock-waiting transaction Information

---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

There are only one lock-holding transaction and one lock-waiting transaction. So we can guess that thread 16 block thread 17, or that transaction 580438 block transaction 580444.

Check lock dependency - what blocks what

MySQL 5.x

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

MySQL 8

Query lock dependency

SELECT * FROM performance_schema.data_lock_waits;
  • BLOCKING_ENGINE_TRANSACTION_ID: lock-holding transaction ID
  • REQUESTING_ENGINE_TRANSACTION_ID: lock-waiting transaction ID

The result is BLOCKING_ENGINE_TRANSACTION_ID blocked REQUESTING_ENGINE_TRANSACTION_ID. We can confirm that transaction 580438 blocked transaction 580444. You can get the real thread IDs from the result of SHOW ENGINE INNODB STATUS;. Therefore, we can confirm that thread 16 blocked thread 17.

Query lock-holding and lock-waiting transaction information

SELECT * FROM performance_schema.data_locks;
  • ENGINE_TRANSATION_ID: transation_id in SHOW ENGINE INNODB STATUS;
  • OBJECT_NAME: table name
  • LOCK_STATUS: “WATING”/“GRANT”

Kill the Locked Tread

kill {thread_ID};
kill 16;

References