A solution to the MYSQL lock table problem

This article illustrates the solution to the MYSQL lock table problem. To share for your reference, the details are as follows:

Lots of times! 1 accidentally lock table! Here is the ultimate way to solve the lock table!

Case 1

mysql>show processlist;

See the sql statement

A few words

mysql>kill thread_id;

You can solve it

The process of kill dropping the first lock table is still not improved. Since it is not improved, let's find a way to drop all the lock table process kill, the simple script is as follows.

#!/bin/bash
mysql - u root - e " show processlist " | grep - i " Locked " >> locked_log . txt
for line in ` cat locked_log.txt | awk '{print $1 }' `
do
echo " kill $line ; " >> kill_thread_id . sql
done

Now the contents of kill_ES27en_id.sql look like this

kill 66402982 ;
kill 66402983 ;
kill 66402986 ;
kill 66402991 ;
.....

Ok, we execute in mysql's shell, and we can kill all the locked table processes.

mysql > source kill_thread_id . sql

Of course, you could have done it in 1 row

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
mysqladmin kill ${id}
done

Case 2

If high-volume operations can be generated through a series 1 select statement, then in theory these results can be batched.

However, mysql does not provide eval's ability to perform analysis operations on result sets. So you can only now save the select results to a temporary file, and then execute the instructions in the temporary file.

The specific process is as follows:

mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
+------------------------+
| concat('KILL ',id,';')
+------------------------+
| KILL 3101;      
| KILL 2946;      
+------------------------+
2 rows IN SET (0.00 sec)
mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql> source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

Case 3

The pattern of MySQL + PHP often leads to a large number of dead processes in MySQL under high concurrency pressure, leading to service suspension. To automate these processes, a script was created that was placed in the background of the server and executed automatically via crontab. I found that when I did this, It really alleviated the problem. Send this script to everyone Share.

According to my actual needs, I have made some modifications:

SHELL script: mysqld_kill_sleep sh

#!/bin/sh
mysql_pwd="root The password "
mysqladmin_exec="/usr/local/bin/mysqladmin"
mysql_exec="/usr/local/bin/mysql"
mysql_timeout_dir="/tmp"
mysql_timeout_log="$mysql_timeout_dir/mysql_timeout.log"
mysql_kill_timeout_sh="$mysql_timeout_dir/mysql_kill_timeout.sh"
mysql_kill_timeout_log="$mysql_timeout_dir/mysql_kill_timeout.log"
$mysqladmin_exec -uroot -p"$mysql_pwd" processlist | awk '{ print $12 , $2 ,$4}' | grep -v Time | grep -v '|' | sort -rn > $mysql_timeout_log
awk '{if($1>30 && $3!="root") print "'""$mysql_exec""' -e " "\"" "kill",$2 "\"" " -uroot " "-p""\"""'""$mysql_pwd""'""\"" ";" }' $mysql_timeout_log > $mysql_kill_timeout_sh
echo "check start ...." >> $mysql_kill_timeout_log
echo `date` >> $mysql_kill_timeout_log
cat $mysql_kill_timeout_sh

So let's write this to ES76en_ES77en_sleep.sh. Then chmod 0 es81EN_kill_sleep.sh,chmod u+rx mysqld_kill_sleep.sh, then use the root account to run into cron, the time can be adjusted by yourself.

After execution, it shows:

www# ./mysqld_kill_sleep.sh
/usr/local/bin/mysql -e "kill 27549" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27750" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27840" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27867" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27899" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27901" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27758" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27875" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27697" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27888" -uroot -p"mysql root The password ";
/usr/local/bin/mysql -e "kill 27861" -uroot -p"mysql root The password ";

If you confirm that there is no problem, change the last cat to sh.

I rewrote the above script:

#!/bin/bash
mysql_pwd=" password "
mysql_exec="/usr/local/mysql/bin/mysql"
mysql_timeout_dir="/tmp"
mysql_kill_timeout_sh="$mysql_timeout_dir/mysql_kill_timeout.sh"
mysql_kill_timeout_log="$mysql_timeout_dir/mysql_kill_timeout.log"
$mysql_exec -uroot -p$mysql_pwd -e "show processlist" | grep -i "Locked" >> $mysql_kill_timeout_log
chmod 777 $mysql_kill_timeout_log
for line in `$mysql_kill_timeout_log | awk '{print $1}'`
do
echo "$mysql_exec -uroot -p$mysql_pwd -e \"kill $line\"" >> $mysql_kill_timeout_sh
done
chmod 777 $mysql_kill_timeout_sh
cat $mysql_kill_timeout_sh

For more information about MySQL, please refer to MySQL Transaction Operation Skills Summary, MySQL Stored Procedure Skills collection and php+mysql Database Operation Tutorial.

I hope that this article is helpful to the MySQL database programming.