如何以 root 身份终止 MySql 中的事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26476400/
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
How do I kill a transaction in MySql as root?
提问by Dave
I'm using MySql 5.5.37. As root, I'm trying to kill a transaction that's locking some tables. I run
我正在使用 MySql 5.5.37。作为 root，我试图终止锁定某些表的事务。我跑
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
and get the output
并获得输出
…
*************************** 6. row ***************************
trx_id: 143E6CDE
trx_state: RUNNING
trx_started: 2014-10-20 06:03:56
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2305887
trx_mysql_thread_id: 158360
trx_query: delete from event where id not in (select q.* from (select e.id FROM event e, (select object_id, max(date_processed) d from event group by object_id) o where e.object_id = o.object_id and e.date_processed = o.d) q)
trx_operation_state: NULL
trx_tables_in_use: 3
trx_tables_locked: 3
trx_lock_structs: 210634
trx_lock_memory_bytes: 19790264
trx_rows_locked: 10668793
trx_rows_modified: 2095253
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
But when I run a “kill” statement, I get an error.
但是当我运行一个“kill”语句时，我得到一个错误。
mysql> kill 158360;
ERROR 1095 (HY000): You are not owner of thread 158360
How do I clear this transaction from MySql?
如何从 MySql 清除此事务？
回答by Paulo Victor
Just to complete Bill answer if you are using RDS MySQL you can use rds_kill() procedure, as the following example:
如果您使用的是 RDS MySQL，只是为了完成 Bill 的回答，您可以使用 rds_kill() 过程，如下例所示：
Connect to MySQL
连接到 MySQL
List process:
列表过程：
SHOW PROCESSLIST;
In my case, I want to kill the process of id 1948452:
就我而言，我想杀死 id 1948452 的进程：
CALL mysql.rds_kill(1948452);
Done
完毕
回答by Bill Karwin
You can always kill your own thread, but you need the SUPER privilege to kill someone else's thread.
你总是可以杀死自己的线程，但你需要 SUPER 权限才能杀死其他人的线程。
Are you on RDS? If so, you do not have SUPER privilege even if your user name is 'root'. There's nothing implicitly special about the 'root' name, it's the privilege that counts.
你在 RDS 上吗？如果是这样，即使您的用户名为“root”，您也没有 SUPER 权限。“根”名称没有任何隐含的特殊之处，重要的是特权。
You can confirm your privileges by running:
您可以通过运行来确认您的权限：
mysql> SHOW GRANTS;
As for how to kill the thread, if this is RDS, you can call a procedure rds_kill()to do it for you.
至于如何杀死线程，如果这是RDS，你可以调用一个过程rds_kill()来帮你完成。
回答by remeika
If you are using Azure Database for MySQL, you can use the procedure az_kill:
如果您使用的是 Azure Database for MySQL，则可以使用以下过程az_kill：
List process:
列表过程：
SHOW PROCESSLIST;
In case the ID of the process you want to kill is 345, run:
如果要杀死的进程的 ID 为 345，请运行：
CALL mysql.az_kill(345);
This works even if the current logged in user does not own process 345.
即使当前登录的用户不拥有进程 345，这也有效。
