需要在不重启数据库的情况下清除 MySQL InnoDB 锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10904044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need to clear MySQL InnoDB lock without restarting database
提问by modulaaron
I have an MySQL InnoDB lock that is preventing a user from logging in. I don't care about the cause of this lock at the moment - I just need to clear the lock without restarting the database. Killing the query process does nothing FYI. Any suggestions? Thanks.
我有一个阻止用户登录的 MySQL InnoDB 锁。我现在不关心这个锁的原因 - 我只需要在不重新启动数据库的情况下清除锁。终止查询过程没有任何作用仅供参考。有什么建议？谢谢。
回答by modulaaron
Here is how I wound up solving the problem:
这是我解决问题的方法：
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
120710 18:05:37 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 208374, signal count 196902
Mutex spin waits 0, rounds 39211638, OS waits 80663
RW-shared spins 588505, OS waits 68505; RW-excl spins 3204502, OS waits 53586
------------
TRANSACTIONS
------------
Trx id counter 1 3626791829
Purge done for trx's n:o < 1 3625948819 undo n:o < 0 0
History list length 6754
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 1 3625948818, ACTIVE 2892 sec, process no 1981, OS thread id 140020625811200
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
>>>>> MySQL thread id 14982, query id 232584561 localhost dbuser
Trx read view will not see trx with id >= 1 3625948819, sees < 1 3625948817
mysql> kill 14982;
Query OK, 0 rows affected (0.00 sec)
Note that the line with the >>>>> is where the MySQL thread ID is defined - it is this thread that should be killed.
请注意，带有 >>>>> 的行是定义 MySQL 线程 ID 的地方 - 应该杀死该线程。
Please see thispost as well - very useful.
也请参阅这篇文章 - 非常有用。
