How do I kill a transaction in MySql as root?

Question

I’m using MySql 5.5.37. As root, I’m trying to kill a transaction that’s locking some tables. I run

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.

mysql> kill 158360; ERROR 1095 (HY000): You are not owner of thread 158360 

How do I clear this transaction from MySql?

Answer 1

Just to complete Bill answer if you are using RDS MySQL you can use rds_kill() procedure, as the following example:

Connect to MySQL

List process:

SHOW PROCESSLIST; 

In my case, I want to kill the process of id 1948452:

CALL mysql.rds_kill(1948452); 

Done

Answer 2

You can always kill your own thread, but you need the SUPER privilege to kill someone else's thread.

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.

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.