MySQL死锁导致无法查询

心已赠人 2021-06-24 13:59 491阅读 0赞

客服反馈后台无法查询，原因大概知道，是因为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;