处理方式:
1. 检查有多少sleep/lock进程:
实时监控有没有锁表情况:
watch -d 'mysql -uroot -pXXXXX -e "show processlist" | grep -i "Locked"'
mysql -uroot -pXX -e "show processlist" | grep Lock | wc -l
mysql -uroot -pXX -e “Show innodb status” | grep lock > lock_sql.log #########Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁
2.
mysql> show status like "table%";
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| Table_locks_immediate | 1845414803 |
| Table_locks_waited | 2174682 |
+-----------------------+------------+
Table_locks_waited的值比较高,说明存在着较严重的表级锁争用情况。当数据表有一个写锁时,其它进程的读写操作都需等待读锁释放后才会执行。
3. (这一步我还未使用,wait_timeout 还是采取默认值 28800 )
# vi /etc/my.cnf
[mysqld]
wait_timeout=10
# /etc/init.d/mysql restart
不过这个方法太生硬了,线上服务重启无论如何都应该尽可能避免,看看如何在MySQL命令行里通过SET来设置:
[html]
mysql> set global wait_timeout=10; (揭阳已经设置成这个数值)
mysql> show global variables like '%timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| wait_timeout | 10 |
+----------------------------+-------+
4.
简单处理方法:
先执行show processlist找到死锁线程号.然后Kill。
4.较多僵死进程时,采取临时脚本杀死僵死进程。
关于Lock进程处理脚本:
#!/bin/bash
mysql_pwd="XX"
mysql_exec="/usr/bin/mysql"
tmp_dir="/tmp"
file_sh="$tmp_dir/mysql_kill_locked.sh"
file_tmp="$tmp_dir/mysql_kill_locked.tmp"
file_log="$tmp_dir/mysql_kill_locked.log"
$mysql_exec -uroot -p$mysql_pwd -e "show processlist" | grep -i "Locked" > $file_tmp
cat $file_tmp >> $file_log
for line in `cat $file_tmp | awk '{print $1}'`
do
echo "$mysql_exec -uroot -p$mysql_pwd -e \"kill $line\"" >> $file_sh
done
chmod +x $file_sh
sh $file_sh
cat /dev/null > $file_sh
关于sleep进程处理脚本:
#!/bin/bash
while :
do
n=`/usr/bin/mysqladmin -uroot -pXX processlist | grep -i sleep | wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $n
if [ "$n" -gt 100 ]
then
for i in `/usr/bin/mysqladmin -uroot -pcoship processlist | grep -i sleep | awk '{print $2}'`
do
/usr/bin/mysqladmin -uroot -pcoship kill $i
done
fi
sleep 5
done
希望以上对研发或者运维有一点用处。希望研发和我们再加把劲。这个问题影响比较严重。
如果锁表情况非常频繁。所以做到实时清理lock表,用以下脚本,加多一个whie
while :
do
#!/bin/bash
mysql_pwd="coship"
mysql_exec="/usr/bin/mysql"
tmp_dir="/tmp"
file_sh="$tmp_dir/mysql_kill_locked.sh"
file_tmp="$tmp_dir/mysql_kill_locked.tmp"
file_log="$tmp_dir/mysql_kill_locked.log"
$mysql_exec -uroot -p$mysql_pwd -e "show processlist" | grep -i "Locked" > $file_tmp
cat $file_tmp >> $file_log
for line in `cat $file_tmp | awk '{print $1}'`
do
echo "$mysql_exec -uroot -p$mysql_pwd -e \"kill $line\"" >> $file_sh
done
chmod +x $file_sh
sh $file_sh
cat /dev/null > $file_sh
sleep 1
done
后台运行.................
nohup /root/clean_lock.sh 2>&1 > /dev/null &
MySQL的表鎖定代碼是不會死鎖的(表锁定并不表示死锁)
如:
MySQL使用表級鎖定(而不是行級鎖定或列級鎖定)以達到很高的鎖定速度。對于大表,表級鎖定對大多數應用程序來說比行級鎖定好一些,但是當然有一些缺陷。
在MySQL3.23.7和更高版本中,一個人能把行插入到MyISAM表同時其他線程正在讀該表。注意,目前只有在表中內有刪除的行時才工作。
表級鎖定使很多線程能夠同時讀一個表,但是如果一個線程想要寫一個表,它必須首先得到獨占存取權。在更改期間,所有其他想要存取該特定表的線程將等到更改就緒。
因為數據庫的更改通常被視為比SELECT更重要,更新一個表的所有語句比從一個表中檢索信息的語句有更高的優先級。這應該保証更改不被“餓死”,因為一個人針對一個特定表會發出很多繁重的查詢。
從MySQL 3.23.7開始,一個人可以能使用max_write_lock_count變量強制MySQL在一個表上一個特定數量的插入後發出一個SELECT。
對此一個主要的問題如下:
一個客戶發出一個花很長時間運行的SELECT。
然後其他客戶在一個使用的表上發出一個UPDATE;這個客戶將等待直到SELECT完成。
另一個客戶在同一個表上發出另一個SELECT語句;因為UPDATE比SELECT有更高的優先級,該SELECT將等待UPDATE的完成。它也將等待第一個SELECT完成!
對這個問題的一些可能的解決方案是:
試著使SELECT語句運行得更快;你可能必須創建一些摘要(summary)表做到這點。
用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下,在先前情形的最後的SELECT語句將在INSERT語句前執行。
你可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。
為max_write_lock_count指定一個低值來啟動mysqld使得在一定數量的WRITE鎖定後給出READ鎖定。
通過使用SQL命令:SET SQL_LOW_PRIORITY_UPDATES=1,你可從一個特定線程指定所有的更改應該由用低優先級完成。見7.25 SET OPTION句法。
你可以用HIGH_PRIORITY屬性指明一個特定SELECT是很重要的。見7.12 SELECT句法。
如果你有關于INSERT結合SELECT的問題,切換到使用新的MyISAM表,因為它們支持並發的SELECT和INSERT。
如果你主要混合INSERT和SELECT語句,DELAYED屬性的INSERT將可能解決你的問題。見7.14 INSERT句法。
如果你有關于SELECT和DELETE的問題,LIMIT選項的DELETE可以幫助你。見7.11 DELETE句法。