MySQL Lock detection-kill

Today the test library's T-table was tested, the delete from T was found, the lock was generated, and so on, so it was found that the session was caused, but it is difficult to find, finally through the prediction of the kill lock session, the operation is as follows:

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, 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 index ' PRIMARY ' of table ' test '. ' t ' Trx ID 307463 lock_mode X RECORD LOCKS Space ID 122 page No 4 n bits index ' idx_name ' of table ' test '. ' t ' Trx ID 307463 lock_mode X RECORD LOCKS Space ID 122 page No 4 n bits index ' idx_name ' of table ' test '. ' t ' Trx ID 307463 lock_mode X LOCKS Gap bef Ore 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)

Judge:

1. The table lock on the T-table was found through the InnoDB Status: Tablelock table ' test '. ' t ' Trx ID 307463 lock mode IX

2. The table is found with 7 rows by a SELECT * from T: 7 rows inSet (0.00 sec)

3. Discover a rows_sent with a session through show Processlist | Rows_examined is 7 7 with an ID of 4526

4. 
   

By judging the Kill ID to 4526 session:

Mysql> kill 4256; Query OK, 0 rows Affected (0.00 sec)

Perform a delete operation to successfully execute:

Mysql> Delete from T; Query OK, 7 rows affected (0.01 sec)

This article is from the "Floating Phoenix Year" blog, please be sure to keep this source http://liuzhanbin.blog.51cto.com/10060150/1647320

MySQL Lock detection-kill