为什么我不能在mysql中删除特定的线程/查询?

I've got some MySQL (innodb) with some deadlocking, and I'm just looking to kill the transactions and move on.

我有一些MySQL (innodb)，有一些死锁，我只是想要终止事务并继续前进。

"SHOW ENGINE INNODB STATUS" shows the following:

“显示引擎INNODB状态”显示如下:

*** (1) TRANSACTION:
TRANSACTION 74D88AFE, ACTIVE 14 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 4637121, OS thread handle 0x7f51f91be700, query id 979092335 ip-10-118-555-555.ec2.internal 10.118.555.555 csjournal Copying to tmp table
INSERT INTO eznode_count_688
        SELECT
            count( ezcot.main_node_id ) AS count
        FROM
            ezcontentobject_tree ezcot,
            ezcontentobject_tree ezcot_all
        WHERE
            ezcot.path_string like '/1/2/26428/26448/86046/86047/%' and
            ezcot.node_id != 86047 and 
                ezcot.contentobject_id = ezcot_all.contentobject_id
        GROUP BY ezcot_all.main_node_id
        HAVING count( ezcot.main_node_id ) <= 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 890933 n bits 136 index `PRIMARY` of table `mydatabase`.`ezcontentobject_tree` trx id 
74D88AFE lock mode S locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
 0: len 4; hex 8001501f; asc   P ;;
 1: len 6; hex 000074d86b27; asc   t k';;
 2: len 7; hex 73000c91131ac0; asc s      ;;
 3: len 4; hex 8001cd9d; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 80000005; asc     ;;
 7: len 4; hex 80000000; asc     ;;
 8: len 4; hex 80000000; asc     ;;
 9: len 4; hex 8001501f; asc   P ;;
 10: len 4; hex d0f62e3f; asc   .?;;
 11: len 4; hex 8001501e; asc   P ;;
 12: len 30; hex 6672616e636169732f6e756d65726f735f61726368697665732f32303131; asc path/to/2011; (total 32 bytes);
 13: len 29; hex 2f312f322f32363432382f32363434382f38363034362f38363034372f; asc /1/2/26428/26448/86046/86047/;;
 14: len 4; hex 80000000; asc     ;;
 15: len 30; hex 366538613266356538393432333465383232383963396466613963636437; asc 6e8a2f5e894234e82289c9dfa9ccd7; (total 32 bytes);
 16: len 4; hex 80000001; asc     ;;
 17: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 74D86B27, ACTIVE 194 sec starting index read
mysql tables in use 1, locked 1
215 lock struct(s), heap size 31160, 4514 row lock(s), undo log entries 9543
MySQL thread id 4636652, OS thread handle 0x7f51f90fa700, query id 979103970 ip-10-118-555-555.ec2.internal 10.118.555.555 csjournal updating
DELETE FROM ezcontentobject_tree
    WHERE path_string like '/1/2/26428/26448/86046/86047/%'  OR
          path_string = '/1/2/26428/26448/86046/86047/'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 890933 n bits 136 index `PRIMARY` of table `mydatabase`.`ezcontentobject_tree` trx id 74D86B27 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

I would have expected to be able to kill the thread or query from mysql, but running kill does not work:

我本来希望能够从mysql中删除线程或查询，但是运行kill不会起作用:

mysql> kill 4637121;
ERROR 1094 (HY000): Unknown thread id: 4637121
mysql> kill query 979092335;
ERROR 1094 (HY000): Unknown thread id: 979092335

If I use "SHOW ENGINE INNODB STATUS" again, I see the same threads/query ids referenced! Why are they recognized by SHOW ENGINE, but not by KILL, and how can I terminate the thread? Mysql's process / KILL documentation has been no help.

如果我再次使用“SHOW ENGINE INNODB STATUS”，我将看到引用的相同线程/查询id !为什么它们被显示引擎识别，而不是被杀死，我如何终止线程?Mysql的进程/ KILL文档没有帮助。

1 个解决方案

#1

The output of SHOW ENGINE INNODB STATUS is showing you the last deadlock. The deadlock has already been resolved and one of the queries was killed. Look further down in the message to see which one was killed.

显示引擎INNODB状态的输出显示了最后的死锁。死锁已经解决，其中一个查询被杀死。再往下看，看看谁被杀了。