MySQL - 锁等待及死锁初探

悠悠 2023-03-03 12:27 65阅读 0赞

### 文章目录 ###

*  生猛干货
 *  版本信息
 *  MySQL 行锁分析
 *  MySQL死锁演示
 *  排查过程
 *  查看近期死锁日志信息
 *  查询锁等待命令及kill 锁
 *  优化建议
 *  搞定MySQL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

# 生猛干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

--------------------

# 版本信息 #

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.28    |
    +-----------+
    1 row in set
    
    mysql>

--------------------

# MySQL 行锁分析 #

变量说明：

*  Innodb\_row\_lock\_current\_waits 当前正在等待锁定的数量 单位毫秒
 *  Innodb\_row\_lock\_time 从系统启动到现在锁定总时间长度 单位毫秒
 *  Innodb\_row\_lock\_time\_avg 每次等待所花平均时间 单位毫秒
 *  Innodb\_row\_lock\_time\_max 从系统启动到现在等待最长的一次所花时间 单位毫秒
 *  Innodb\_row\_lock\_waits 系统启动后到现在总共等待的次数

重点关注 ： Innodb\_row\_lock\_time\_avg 、Innodb\_row\_lock\_waits 、Innodb\_row\_lock\_time

--------------------

# MySQL死锁演示 #

**事务隔离级别， 默认 可重复读**

【操作步骤】

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="right">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">begin 模拟开启事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center">select * from art_info where id=1 for update;</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">begin 模拟开启事务</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from account where id=2 for update;</td> 
  </tr> 
  <tr> 
   <td align="center">select * from art_info where id=2 for update; ----&gt;一直等待</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from art_info where id=1 for update; —&gt; Deadlock found when trying to get lock; try restarting transaction</td> 
  </tr> 
 </tbody> 
</table>

**大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁**

--------------------

# 排查过程 #

【模拟锁等待 】

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="right">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">begin 模拟开启事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center">select * from art_info where id=1 for update;</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">begin 模拟开启事务</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from account where id=2 for update;</td> 
  </tr> 
  <tr> 
   <td align="center">select * from account where id=2 for update;</td> 
   <td align="right"></td> 
  </tr> 
 </tbody> 
</table>

mysql> select * from art_info where id =2 for update ;    
    1205 - Lock wait timeout exceeded; try restarting transaction

--------------------

-- 查看事务 
    select  * from information_schema.INNODB_TRX;
    -- 查看锁 
    select * from information_schema.INNODB_LOCKS;
    -- 查看锁等待
    select * from information_schema.INNODB_LOCK_WAITS;
    
    
    -- 锁释放 information_schema.INNODB_TRX 查询 trx_mysql_thread_id 然后去 kill 对应的value 
    kill  trx_mysql_thread_id

来吧 ，用上面的SQL查吧

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]

--------------------

# 查看近期死锁日志信息 #

show engine innodb status \G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]

--------------------

# 查询锁等待命令及kill 锁 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 5]

**锁等待有自己的超时时间，超过后一般都会自动释放**

mysql> select * from art_info where id =2 for update ;    
    1205 - Lock wait timeout exceeded; try restarting transaction

--------------------

# 优化建议 #

*  尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
 *  合理设计索引，尽量缩小锁的范围
 *  尽可能减少检索条件范围，避免间隙锁
 *  尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
 *  尽可能低级别事务隔离

--------------------

# 搞定MySQL #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20230208/f2fb1349758a4add996a230db429cd56.png
[MySQL]: https://marketing.csdn.net/poster/3766
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]: /images/20230208/24a7165af3f9435ca267799916a8894f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]: /images/20230208/a7a1eb21859a4de88162c7cd78646752.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]: /images/20230208/134fbbb628544214802115489ca45aef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]: /images/20230208/a7cdf44f7ff1479f907490204bde0beb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 5]: /images/20230208/48e4e1ddb6bb414cb8ad5f4bc2909388.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 6]: /images/20230208/c3f595a2fa40423695bf611782d7b8fd.png