【Mysql】记一次锁问题

接收到短信告警说mysql库的链接线程400了 告警了~
mysql> show status like '%connect%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| Aborted_connects         | 458276    |
| Connections              | 719644444 |
| Max_used_connections     | 4173      |
| Ssl_client_connects      | 0         |
| Ssl_connect_renegotiates | 0         |
| Ssl_finished_connects    | 0         |
| Threads_connected        | 430    ----查看一下 已经430了。。。


查看一下所有的进程情况
[root@bj150 soft]# mysqladmin -uroot -pXXXXXXX processlist>processlist.log

1. 查看一下，发现全是select语句 waiting table level lock。。。产生表锁了
2. 
   
3. 
   
4. 719325154 | haodaiwang | 192.168.1.148:36248 | hd | waiting table level  lock | 0 |
   
5.                         | SELECT * from admin where ***Y
   
6.   | 3 | 6 | 6 |
   
7. | 719325155 | haodaiwang | 192.168.1.149:40395 | hd | waiting table level  lock | 0 |
   
8.                         | SELECT * from admin where ***Y
   
9.   | 1 | 1 | 1 |
   
10. | 719325157 | haodaiwang | 192.168.1.147:38542 | hd | waiting table level  lock | 0 |
    
11.                         | SELECT * from admin where ***Y
    
12.   | 1 | 1 | 1 |
    
13. | 719325158 | haodaiwang | 192.168.1.148:36281 | hd | Querywaiting table level  lock | 0 | Sorting result
    
14.                         | SELECT * from admin where ***Y
15.   | 0 | 0 | 0 |
    
16. | 719325159 | root | localhost | | Query | 0 |
    
17.                         | show processlist
    
18.   | 0 | 0 | 0 |
    
19. +-----------+------------+----------------------+----+-------------+---------+-----------------------------------------------
    
20. ------------------------+----------------------------------------------------------------------------------------------------
    
21. --+-----------+---------------+-----------+

。。。。。。
。。。。。。
。。。。。。

1. 19325154 | haodaiwang | 192.168.1.148:36248 | hd | waiting table level  lock | 0 | 
   
2.                         | SELECT * from admin where ***Y 
   
3.   | 3 | 6 | 6 |
   
4. | 719325155 | haodaiwang | 192.168.1.149:40395 | hd | waiting table level  lock | 0 | 
   
5.                         | SELECT * from admin where ***Y 
   
6.   | 1 | 1 | 1 |
   
7. | 719325157 | haodaiwang | 192.168.1.147:38542 | hd | waiting table level  lock | 0 | 
   
8.                         | SELECT * from admin where ***Y 
   
9.   | 1 | 1 | 1 |
   
10. | 719325158 | haodaiwang | 192.168.1.148:36281 | hd | Querywaiting table level  lock | 0 | Sorting result 
    
11.                         | update xxx set colum=!!!!!!   where *****=****      ----一条更新语句！！！！
12.   | 0 | 0 | 0 |
    
13. | 719325159 | root | localhost | | Query | 0 | 
    
14.                         | show processlist 
    
15.   | 0 | 0 | 0 |
    
16. +-----------+------------+----------------------+----+-------------+---------+-----------------------------------------------
    
17. ------------------------+----------------------------------------------------------------------------------------------------
    
18. --+-----------+---------------+-----------+

初步分析可能是update admin这张表导致selec 全部堵塞了！！！

再查看一下innodb引擎的信息
[root@bj150 soft]# mysql -uroot -pXXXXXXX -e "show engine innodb status\G" >innodb.log

点击(此处)折叠或打开

1. mysql tables in use 1, locked 0
   MySQL thread id 719356646, OS thread handle 0x7f86f8e0e700, query id 14429940008 192.168.1.147 haodaiwang Sorting result
   SELECT * FROM `xindai` WHERE ( `zone_id` = 330100 ) AND ( `status` = 4 ) AND ( `type_id` = 1 ) ORDER BY `grade` DESC LIMIT 1
   ---TRANSACTION 0, not started
   MySQL thread id 719356606, OS thread handle 0x7f86f9773700, query id 14429939625 192.168.1.148 haodaiwang Sending data
   SELECT ****** from admin。。。。.
2. ---TRANSACTION 2AF000898, ACTIVE 0 sec    ----活动的事物以及事物id
   mysql tables in use 1, locked 0
   MySQL thread id 719356645, OS thread handle 0x7f86f5263700, query id 14429940006 192.168.1.148 haodaiwang Sending data
   SELECT * FROM `xindaiyuan` LEFT JOIN `xindaiyuan_info` ON `xindaiyuan`.id = `xindaiyuan_info`.xdy_id WHERE ( xindaiyuan.bank_
   id = '813' ) AND ( xindaiyuan.status = 4 ) ORDER BY xindaiyuan.id DESC LIMIT 0,3
   Trx read view will not see trx with id >= 2AF000899, sees < 2AF000899
   
   
3. 
   
4. MySQL thread id 719356645OS thread handle 0x7f86f5263700, query id 14429940006 192.168.1.148 haodaiwang Sending data
   
5. update admin set.........----最后是一条update语句，生产日志已经覆盖，所以都是自己造的，主要是个思路

通过processlist与innodb status的状态分析，就是update admin 这条语句导致的堵塞，因为admin是myisam引擎的表，所以update是产生了一个x的排它锁~所以select会被堵塞！！
解决办法：kill 掉update那条语句即可！！
mysql>kill  719325158;
再查看fullprocesslist  发现堵塞慢慢好了，连接数也变正常了！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1816360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1816360/