ITPub博客

首页 > 数据库 > MySQL > 【Mysql】记一次锁问题

【Mysql】记一次锁问题

原创 MySQL 作者:小亮520cl 时间:2015-10-26 15:06:11 0 删除 编辑
接收到短信告警说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. 719325154 | haodaiwang | 192.168.1.148:36248 | hd | waiting table level  lock | 0 |
  3.                         | SELECT * from admin where ***Y
  4.   | 3 | 6 | 6 |
  5. | 719325155 | haodaiwang | 192.168.1.149:40395 | hd | waiting table level  lock | 0 |
  6.                         | SELECT * from admin where ***Y
  7.   | 1 | 1 | 1 |
  8. | 719325157 | haodaiwang | 192.168.1.147:38542 | hd | waiting table level  lock | 0 |
  9.                         | SELECT * from admin where ***Y
  10.   | 1 | 1 | 1 |
  11. | 719325158 | haodaiwang | 192.168.1.148:36281 | hd | Querywaiting table level  lock | 0 | Sorting result
  12.                         | SELECT * from admin where ***Y
  13.   | 0 | 0 | 0 |
  14. | 719325159 | root | localhost | | Query | 0 |
  15.                         | show processlist
  16.   | 0 | 0 | 0 |
  17. +-----------+------------+----------------------+----+-------------+---------+-----------------------------------------------
  18. ------------------------+----------------------------------------------------------------------------------------------------
  19. --+-----------+---------------+-----------+
。。。。。。
。。。。。。
。。。。。。
  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. MySQL thread id 719356645OS thread handle 0x7f86f5263700, query id 14429940006 192.168.1.148 haodaiwang Sending data
  4. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
毕业以后专业任职数据库工程师职位,itpub一直作为自己的笔记记录的地方,blog写的不详细,仅供参考!

注册时间:2013-09-12

  • 博文量
    531
  • 访问量
    970274