Kill Long Running and Locked SQL Statements in MySQL

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. An Example of Locked SQL Statements
   2. 2.2. Get thread IDs and SQL statements of lock-holding and lock-waiting executing SQLs
   3. 2.3. Get More Locked Information
      1. 2.3.1. Query what table is locked
      2. 2.3.2. Query Lock Information
      3. 2.3.3. Check lock dependency - what blocks what
   4. 2.4. Kill the Locked Tread
3. 3. References

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

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

• 26.4.28 The INFORMATION_SCHEMA INNODB_TRX Table
• The INFORMATION_SCHEMA INNODB_LOCKS Table - MySQL 5.x
• 27.12.13.2 The data_lock_waits Table - MySQL 8.x