【Mysql】記一次鎖問題

小亮520cl發表於2015-10-26
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。。。產生表鎖了


  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


  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章