The quickest way to kill the lock process is to restart MySQL, like in your case, with more than 1000 SQL locked, preferably a reboot
If I do not allow a restart, I provide a shell script that generates a Kill ID command to kill the lock thread, as follows:
------------------------------------
#!/bin/bash
Mysql-u root-e "Show processlist" |grep-i "Locked" >> locked.txt;
For line in awk ' {print '} ' locked.txt
Do
echo "Kill $line;" >>kill_lock.sql
Done
----------------------------------
After executing the script, a kill_lock.sql file is generated that resembles the following:
Kill 1;
Kill 2;
Kill 3;
-------------------These are the lock SessionID, directly copy the contents of the file, and then execute it in MySQL OK.
As for which SQL to troubleshoot, this is a bit difficult, but you can try to turn on slow log and no index logs to confirm more time-consuming queries and avoid clogging again
MySQL Kill (Kill) lock Process script