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)
判断：
通过innodb status发现对t表上了表锁： TABLE LOCK table `test`.`t` trx id 307463 lock mode IX
通过select * from t发现该表有7行： 7 rows in set (0.00 sec)
通过show processlist发现有一个会话的Rows_sent | Rows_examined为7 7 ，ID为4526
通过判断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测试数据/10060150/1647320
MySQL锁检测-kill
标签：锁