客服反馈后台无法查询,原因大概知道,是因为MySQL的事务产生了死锁,以往都不知道是哪个事务锁住了,只能很粗暴地重启MySQL
最近查找到一个方法,不用重启MySQL,记录如下
登录到MySQL,来看下有哪些MySQL事务
可以看到一个时间持续了比较久的事务,现在时间是13点了,而这个事务的开始时间是2012-11-09 12: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 | REPEATABLE READ | 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;