今天测试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


通过判断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)