MySQL鎖檢測-kill

標籤：鎖

今天測試test庫的t表，發現delete from t;產生鎖等等，於是乎就想辦法查到是那個session造成的，不過很難找到，最終通過預估進行kill上鎖的session，操作如下：

show engine innodb status\G

TRANSACTIONS ------------ Trx id counter 387160 Purge done for trx‘s n:o < 387160 undo n:o < 0 state: running but idle History list length 1004 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 9060, OS thread handle 0x7f29504ee700, query id 4240509 localhost root init show engine innodb status ---TRANSACTION 307468, not started MySQL thread id 5294, OS thread handle 0x7f2938825700, query id 3384276 localhost root cleaning up ---TRANSACTION 307463, ACTIVE 72199 sec 4 lock struct(s), heap size 1184, 11 row lock(s), undo log entries 4 MySQL thread id 4256, OS thread handle 0x7f29388a7700, query id 3384251 localhost root cleaning up TABLE LOCK table `test`.`t` trx id 307463 lock mode IX RECORD LOCKS space id 122 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 307463 lock_mode X RECORD LOCKS space id 122 page no 4 n bits 80 index `idx_name` of table `test`.`t` trx id 307463 lock_mode X RECORD LOCKS space id 122 page no 4 n bits 80 index `idx_name` of table `test`.`t` trx id 307463 lock_mode X locks gap before rec

mysql> show processlist; +------+------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+-----------+---------------+ | Id   | User | Host                | db   | Command     | Time  | State                                                                 | Info             | Rows_sent | Rows_examined | +------+------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+-----------+---------------+ | 4256 | root | localhost           | test | Sleep       | 72573 |                                                                       | NULL             |         7 |             7 | | 4375 | repl | 172.16.52.131:49285 | NULL | Binlog Dump | 75947 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |         0 |             0 | | 5294 | root | localhost           | test | Sleep       | 72553 |                                                                       | NULL             |         0 |             0 | | 9060 | root | localhost           | test | Query       |     0 | init                                                                  | show processlist |         0 |             0 | | 9289 | root | localhost           | NULL | Sleep       |    40 |                                                                       | NULL             |         0 |             0 | +------+------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+-----------+---------------+ 5 rows in set (0.00 sec)

mysql> select * from t; +----+----------+------+ | i  | name     | age  | +----+----------+------+ |  1 | zhangsna |   20 | |  2 | zhangsna |   20 | |  3 | c        |   23 | |  4 | c        |   23 | |  5 | e        | NULL | |  6 | f        | NULL | | 19 | xiaoming |   23 | +----+----------+------+ 7 rows in set (0.00 sec)

判斷：

1. 通過innodb status發現對t表上了表鎖：TABLE LOCK table `test`.`t` trx id 307463 lock mode IX

2. 通過select * from t發現該表有7行：7 rows in set (0.00 sec)

3. 通過show processlist發現有一個會話的Rows_sent | Rows_examined為7 7 ，ID為4526

4. 
   

通過判斷kill掉id為4526會話：

mysql> kill 4256; Query OK, 0 rows affected (0.00 sec)

在執行delete操作，順利執行：

mysql> delete from t; Query OK, 7 rows affected (0.01 sec)

本文出自 “浮生鳳年” 部落格，請務必保留此出處http://liuzhanbin.blog.51cto.com/10060150/1647320

MySQL鎖檢測-kill