记一次线上的Lock wait timeout

电玩女神 2022-11-12 13:43 186阅读 0赞

### 记一次线上的Lock wait timeout ###

*   *  事发现场
     *  分析
     *  正常的锁等待
     *  死锁

## 事发现场 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4NDExODY5_size_16_color_FFFFFF_t_70_pic_center]  
报错信息如图，Lock wait timeout exceeded(锁等待超时)，第一次遇到的时候，同事通过增加innodb\_lock\_wait\_timeout参数到100s（默认是50s）试图来解决这个问题，情况有所好转。但是好景不长，之后再次抛出该异常，可见之前的解决方式并不完美。在比较深入地学习了mysql的相关课程之后（强烈推荐 [MYSQL实战45讲][MYSQL_45]），对该问题有了解决思路。

## 分析 ##

<table> 
 <thead> 
  <tr> 
   <th>innodb_deadlock_detect（死锁检测）</th> 
   <th>innodb_lock_wait_timeout（默认50s）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>on（默认开启）</td> 
   <td>不生效</td> 
  </tr> 
  <tr> 
   <td>of</td> 
   <td>生效</td> 
  </tr> 
 </tbody> 
</table>

Lock wait timeout的异常存在两种可能性

*  正常的锁等待
 *  死锁造成锁等待  
    判断的依据是，查看数据库参数innodb\_deadlock\_detect 是否是开启状态，如果是开启状态，那么当发生死锁的时候抛出的异常必然是类似于这样的异常：deadlock when… 所以，如果死锁检测是开启的，那么该异常不会是死锁造成的所等待，如果死锁检测是关闭的，那么是正常的锁等待，必然是有一个长事务存在，占用了锁，长时间没有释放。这里分别对这两种异常场景提供解决思路。

## 正常的锁等待 ##

该种场景导致的锁获取超时，必然对应了一个长事务。解决思路是缩短加锁到解锁的整个过程。

*  调整sql执行熟悉，将增删改的sql放到最后
 *  如果存在多个增删改的sql，将并发最多的操作放到最后
 *  优化代码，缩短事务begin到commit的时间

## 死锁 ##

*  缩短加锁到解锁的整个过程（**正常的锁等待的解决方案**）
 *  开启死锁检测，程序捕获到异常后重试（类似CAS锁，快速失败+重试），缺点是死锁检测会消耗cpu，如果innodb存在大量的事务线程，会导致cpu 100%，每秒处理不了几个事务
 *  关闭死锁检测，设置innodb\_lock\_wait\_timeout，捕获到异常后重试，缺点是等待一段时间才抛出异常，响应过慢，业务上是有损的
 *  控制事务的并发量（通过客户端或者中间件控制）
 *  分库分表，减少冲突

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4NDExODY5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221022/089b7b49162d4a58a8e308d615b6ea4f.png
[MYSQL_45]: https://time.geekbang.org/column/intro/100020801