[MySQL Error] ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Errors

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

Solutions

Check InnoDB status for locks

SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

for kill slow query by view query SQL statement (trx_query)

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what

for kill locked query

SELECT * FROM `information_schema`.`innodb_locks`;

Show running threads

get query thread ID

SHOW PROCESSLIST;

SELECT * 
FROM information_schema.processlist
WHERE user <> 'system user' and 
COMMAND != 'Sleep'and
info like '%SELECT%'
order by `time` desc;

Id	`User`	Host	db	Command	`Time`	State	Info

• Id: thread ID.
• db: database name.
• Command: Query, Sleep(the session is idle).
• Time: the time in seconds that the thread has been in its current state. For a replica SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the replica host.
• Info: the statement the thread is executing, or NULL if it is executing no statement.

Kill Threads By Thread ID

Kill a Particular Thread

KILL {processlist_id};

get kill all your MySQL queries

SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 
FROM information_schema.processlist 
WHERE user <> 'system user' and
COMMAND != 'Sleep'and
info like '%SELECT%';

KILL 1; KILL 2;

SELECT CONCAT('KILL ',id,';') AS kill_list 
FROM information_schema.processlist 
WHERE user <> 'system user' and
COMMAND != 'Sleep'and
info like '%SELECT%';

+------------------------+
| kill_list              |
+------------------------+
| KILL 1;                |
| KILL 2;                |
+------------------------+

References

[1] Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?

[2] How To Kill MYSQL Queries

[3] 26.3.23 The INFORMATION_SCHEMA PROCESSLIST Table - MySQL

[4] 13.7.8.4 KILL Statement - MySQL