mysql 严重锁表解决方案

今天是我在小省网值班。一来到就是这个问题。关于锁表的问题，以下脚本已经部署到揭阳和梅州两个地市，暂时可以临时解决，但是治标不治本。
处理方式：

1.  检查有多少sleep/lock进程：

     实时监控有没有锁表情况：

     watch -d 'mysql -uroot -pXXXXX -e "show processlist" | grep -i "Locked"'

     mysql -uroot -pXX -e "show processlist"   | grep Sleep | wc -l
     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 &

查询优化

 

查询速度慢的原因很多，常见如下几种：　　

　　 1 、没有索引或者没有用到索引 ( 这是查询慢最常见的问题，是程序设计的缺陷 ) 　　

　　 2 、 I/O 吞吐量小，形成了瓶颈效应。　　

　　 3 、没有创建计算列导致查询不优化。　　

　　 4 、内存不足　　

　　 5 、网络速度慢　　

　　 6 、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）　　

　　 7 、锁或者死锁 ( 这也是查询慢最常见的问题，是程序设计的缺陷 ) 　　

　　 8 、 sp_lock,sp_who, 活动的用户查看 , 原因是读写竞争资源。　　

　　 9 、返回了不必要的行和列　　

10 、查询语句不好，没有优化

MySQL的表鎖定代碼是不會死鎖的(表锁定并不表示死锁)

  就MySql而言，内部是有行锁的机制的，对数据进行Update操作时候，数据库内部为保证数据库的安全，

   MySql对优先执行Update语句会设置成 Locked状态（所以，show Processlist查看到某些语句是Locked状态是正常的,这并不代表是死锁），

   当某个操作处于Locked状态（注意：Locked不一定是死锁）,后面的Sql就处于Sleep的等待状态。

   关键是外部模块的对DB的某张表同时进行Update操作时，应该保持并行，这样就不会造成，在同一时间片内对数据的同时写入的可能性，

   所以就不会造成 “死锁”。

 

如：

[root@JY-MYSQL1 tmp]# tail -200f mysql_kill_locked.log 

26497   root    172.29.238.13:56192     erm     Query   2       Locked  UPDATE edge_device_info      set                                               update_time = '2014-0

37380   root    172.29.238.11:55389     JYAAA   Query   0       Locked  update T_AAA_REALASSET          set CHANNEL_ID = '101',              START_TIME = '2014-03-07 01:26:

37379   root    172.29.238.11:55388     JYAAA   Query   1       Locked  update T_AAA_REALASSET          set CHANNEL_ID = '137',              START_TIME = '2014-03-06 12:29:

37382   root    172.29.238.11:55391     JYAAA   Query   1       Locked  update T_AAA_REALASSET          set CHANNEL_ID = '497',              START_TIME = '2014-03-06 12:26:

37381   root    172.29.238.11:55390     JYAAA   Query   0       Locked  update T_AAA_REALASSET          set CHANNEL_ID = '497',              START_TIME = '2014-03-07 01:26:

147     root    172.29.238.6:55018      JYBMS   Query   0       Locked  update T_MESSAGESYNC set STATUS=1 where 1=1       and(     MESSAGE_ID='4465654'    )

140     root    172.29.238.6:55011      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

148     root    172.29.238.6:55019      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

142     root    172.29.238.6:55012      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

149     root    172.29.238.6:55021      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

138     root    172.29.238.6:55009      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

144     root    172.29.238.6:55014      JYBMS   Query   1       Locked  select SEQUENCES('SEQ_SYNCMSG') as messageID

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句法。