lock wait timeout exceeded; try restarting transactio解决方案

谁践踏了优雅 2022-05-21 10:56 192阅读 0赞

问题原因：  
       今天线上环境，突然出现一个问题，追踪原因是数据库中的一条语句报错，错误内容：  
**lock wait timeout exceeded; try restarting transactio**  
       执行update table set status = 1 where id = 10002;是可以的。  
       而执行update table set status = 1 where id = 10001;这条语句执行失败。  
       错误提示的意思，很明显，是因为这条语句被锁住了。所以释放这个锁。

解决方案：  
       新创建的数据库，是这样子的：  
       ![新创建的数据库结构][SouthEast]  
         
       现在我们要查test库中使用情况，我们可以到information\_schema中查询  
       解释：information\_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information\_schema表里面。

我们可以用下面三张表来查原因：  
       innodb\_trx \#\# 当前运行的所有事务  
       innodb\_locks \#\# 当前出现的锁  
       innodb\_lock\_waits \#\# 锁等待的对应关系

如果数据库中有锁的话，那么在  
![查询当前运行的事务][SouthEast 1]  
         
       图中红色语句为占用系统资源的语句，我们需要杀掉这个锁，执行 kill 线程id号。上面这条记录的id为319618246  
所以我们执行：`kill 319618246`即可  
执行之后：  
![执行之后][SouthEast 2]  
         
其他的记录不需要关注，因为其他的记录状态为“RUNNING” 即正在执行的事务，并没有锁。。

我们可以进一步了解一下 那三张表的表结构：

**desc information\_schema.innodb\_locks;**

<table> 
 <thead> 
  <tr> 
   <th>Field</th> 
   <th align="center">Type</th> 
   <th align="right">Null</th> 
   <th>Key</th> 
   <th>Default</th> 
   <th>Remark</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>lock_id</td> 
   <td align="center">varchar(81)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td>锁ID</td> 
  </tr> 
  <tr> 
   <td>lock_trx_id</td> 
   <td align="center">varchar(18)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td>拥有锁的事务ID</td> 
  </tr> 
  <tr> 
   <td>lock_mode</td> 
   <td align="center">varchar(32)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td>锁模式</td> 
  </tr> 
  <tr> 
   <td>lock_type</td> 
   <td align="center">varchar(32)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td>锁类型</td> 
  </tr> 
  <tr> 
   <td>lock_table</td> 
   <td align="center">varchar(1024)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td>被锁的表</td> 
  </tr> 
  <tr> 
   <td>lock_index</td> 
   <td align="center">varchar(1024)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td>被锁的索引</td> 
  </tr> 
  <tr> 
   <td>lock_space</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td>被锁的表空间号</td> 
  </tr> 
  <tr> 
   <td>lock_page</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td>被锁的页号</td> 
  </tr> 
  <tr> 
   <td>lock_rec</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td>被锁的记录号</td> 
  </tr> 
  <tr> 
   <td>lock_data</td> 
   <td align="center">varchar(8192)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td>被锁的数据</td> 
  </tr> 
 </tbody> 
</table>

**desc information\_schema.innodb\_lock\_waits**

**desc information\_schema.innodb\_trx ;**

<table> 
 <thead> 
  <tr> 
   <th>Field</th> 
   <th align="center">Type</th> 
   <th align="right">Null</th> 
   <th>Key</th> 
   <th>Default</th> 
   <th>Extra</th> 
   <th>Remark</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>trx_id</td> 
   <td align="center">varchar(18)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>事务ID</td> 
  </tr> 
  <tr> 
   <td>trx_state</td> 
   <td align="center">varchar(13)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>事务状态：</td> 
  </tr> 
  <tr> 
   <td>trx_started</td> 
   <td align="center">datetime</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0000-00-00 00:00:00</td> 
   <td></td> 
   <td>事务开始时间；</td> 
  </tr> 
  <tr> 
   <td>trx_requested_lock_id</td> 
   <td align="center">varchar(81)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td></td> 
   <td>innodb_locks.lock_id</td> 
  </tr> 
  <tr> 
   <td>trx_wait_started</td> 
   <td align="center">datetime</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td></td> 
   <td>事务开始等待的时间</td> 
  </tr> 
  <tr> 
   <td>trx_weight</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>#</td> 
  </tr> 
  <tr> 
   <td>trx_mysql_thread_id</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务线程ID</td> 
  </tr> 
  <tr> 
   <td>trx_query</td> 
   <td align="center">varchar(1024)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td></td> 
   <td>具体SQL语句</td> 
  </tr> 
  <tr> 
   <td>trx_operation_state</td> 
   <td align="center">varchar(64)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td></td> 
   <td>事务当前操作状态</td> 
  </tr> 
  <tr> 
   <td>trx_tables_in_use</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务中有多少个表被使用</td> 
  </tr> 
  <tr> 
   <td>trx_tables_locked</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务拥有多少个锁</td> 
  </tr> 
  <tr> 
   <td>trx_lock_structs</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>#</td> 
  </tr> 
  <tr> 
   <td>trx_lock_memory_bytes</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务锁住的内存大小（B）</td> 
  </tr> 
  <tr> 
   <td>trx_rows_locked</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务锁住的行数</td> 
  </tr> 
  <tr> 
   <td>trx_rows_modified</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务更改的行数</td> 
  </tr> 
  <tr> 
   <td>trx_concurrency_tickets</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>事务并发票数</td> 
  </tr> 
  <tr> 
   <td>trx_isolation_level</td> 
   <td align="center">varchar(16)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>事务隔离级别</td> 
  </tr> 
  <tr> 
   <td>trx_unique_checks</td> 
   <td align="center">int(1)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>是否唯一性检查</td> 
  </tr> 
  <tr> 
   <td>trx_foreign_key_checks</td> 
   <td align="center">int(1)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>是否外键检查</td> 
  </tr> 
  <tr> 
   <td>trx_last_foreign_key_error</td> 
   <td align="center">varchar(256)</td> 
   <td align="right">YES</td> 
   <td></td> 
   <td>NULL</td> 
   <td></td> 
   <td>最后的外键错误</td> 
  </tr> 
  <tr> 
   <td>trx_adaptive_hash_latched</td> 
   <td align="center">int(1)</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>#</td> 
  </tr> 
  <tr> 
   <td>trx_adaptive_hash_timeout</td> 
   <td align="center">bigint(21) unsigned</td> 
   <td align="right">NO</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td>#</td> 
  </tr> 
 </tbody> 
</table>

[SouthEast]: /images/20220521/027780583bbc4aa8a4ff22d35bb9c65e.png
[SouthEast 1]: /images/20220521/f62bd377d9bd482eb9638228c46bd698.png
[SouthEast 2]: /images/20220521/8ef0483a043e4de789aa478c6b283570.png