MySQL死锁导致无法查询

客服反馈后台无法查询，原因大概知道，是因为MySQL的事务产生了死锁，以往都不知道是哪个事务锁住了，只能很粗暴地重启MySQL
最近查找到一个方法，不用重启MySQL，记录如下

登录到MySQL，来看下有哪些MySQL事务

可以看到一个时间持续了比较久的事务，现在时间是13点了，而这个事务的开始时间是2012-11-0912:15:14，显然是不正常的，我们看这个事务对应的mysql的线程ID（trx_mysql_thread_id）是82230715，就是这个事务导致的

13:01:48pm> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id |trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query |trx_operation_state | trx_tables_in_use | trx_tables_locked |trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked |trx_rows_modified | trx_concurrency_tickets | trx_isolation_level |trx_unique_checks | trx_foreign_key_checks |trx_last_foreign_key_error | trx_adaptive_hash_latched |trx_adaptive_hash_timeout |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 31868CED0 | RUNNING | 2012-11-09 12:15:14 | NULL | NULL | 2 |82230715 | NULL | NULL | 0 | 0 | 2 | 376 | 1 | 0 | 0 | REPEATABLEREAD | 1 | 1 | NULL | 0 | 10000 |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+

我们登录到mysql把它kill掉

13:01:55pm> kill 82230715;

这样，就可以查询了

其他

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;