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