How to find blocking InnoDB connections in MySQL and then kill those connections

As of MySQL 5.5, you can use these 2 INFORMATION_SCHEMA views :

• INNODB_LOCK_WAITS
• INNODB_TRX

Here is an example:

session 1

create table t(id int not null auto_increment, c1 int, primary key(id));
insert into t(id,c1) values (1,1);

set autocommit = 0;
update t set c1 = 2 where id = 1;

session 2

update t set c1 = 3 where id = 1;

You can see that:

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 60A9
trx_state: LOCK WAIT
trx_started: 2012-02-23 12:50:22
trx_requested_lock_id: 60A9:4658:3:2
trx_wait_started: 2012-02-23 12:50:22
trx_weight: 2
trx_mysql_thread_id: 849
trx_query: update t set c1 = 3 where id = 1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
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
*************************** 2. row ***************************
trx_id: 60A8
trx_state: RUNNING
trx_started: 2012-02-23 12:49:32
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 848
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
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
2 rows in set (0.52 sec)

mysql> select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
requesting_trx_id: 60A9
requested_lock_id: 60A9:4658:3:2
blocking_trx_id: 60A8
blocking_lock_id: 60A8:4658:3:2
1 row in set (0.00 sec)

Transaction 60A8 is blocking 60A9. So if you want to kill 60A8, you need to kill the trx_mysql_thread_id of that transaction:

$ kill 848;

When you have a lot of transactions, you can use this statement:

SELECT r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
  FROM information_schema.innodb_lock_waits w
       INNER JOIN information_schema.innodb_trx b
          ON b.trx_id = w.blocking_trx_id
       INNER JOIN information_schema.innodb_trx r
          ON r.trx_id = w.requesting_trx_id;

This way, you can see the waiting_thread and the blocking_thread.