两个小工具，MySQL死锁分析，新技能又Get！

数据库死锁，是最难调试与追踪的。

目前成都创新互联已为成百上千的企业提供了网站建设、域名、网络空间、成都网站托管、企业网站设计、潜山网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

场景如下：

同一个表，事务内先插入一条记录，再更新这条记录，并发时会死锁。

并且能够复现。

可以通过什么工具模拟并发事务，查看信息，解决问题呢?这是今天要分享的内容。

一、前置准备

 
 
 
 
    
  
  
  
  
  1. set session transaction isolation level repeatable read; 
    2.   
  
  
  
  2. set session autocommit=0; 
    3.   
  
  
  
  3.   
    4.   
  
  
  
  4. create table t ( 
    5.   
  
  
  
  5. id int(20) primary key AUTO_INCREMENT, 
    6.   
  
  
  
  6. cell varchar(20) unique 
    7.   
  
  
  
  7. )engine=innodb; 
    8.   
  
  
  
  8.   
    9.   
  
  
  
  9. start transaction; 
    10.   
  
  
  
  10. insert into t(cell) values(11111111111); 
    11.   
  
  
  
  11. insert into t(cell) values(22222222222); 
    12.   
  
  
  
  12. insert into t(cell) values(33333333333); 
    13.   
  
  
  
  13. commit; 
    14.

说明：

  • 案发时，事务隔离级别RR;
  • 多终端实验，需要关闭事务自动提交;
  • 建表，设置PK与unique，初始化数据;

二、并发事务模拟

 
 
 
 
    
  
  
  
  
  1. Session A： 
    2.   
  
  
  
  2. start transaction; 
    3.   
  
  
  
  3. insert into t(cell)values(44444444444);  [1] 
    4.   
  
  
  
  4.         Session B： 
    5.   
  
  
  
  5.         start transaction; 
    6.   
  
  
  
  6.         insert into t(cell) values(55555555555); [2] 
    7.   
  
  
  
  7. update t set cell=123 where cell=44444444444; [3]    
    8.   
  
  
  
  8.         update t set cell=456 where cell=55555555555; [4] 
    9.

开启两个终端模拟并发事务：

  • 红色SQL为事务A;
  • 黑色SQL为事务B;
  • [1][2][3][4]为执行时序;

三、实验现象

 
 
 
 
    
  
  
  
  
  1. insert into t(cell)values(44444444444);  [1] 
    2.

事务A插入数据，***执行

结果：插入成功

 
 
 
 
    
  
  
  
  
  1. insert into t(cell) values(55555555555); [2] 
    2.

事务B插入数据，第二执行

结果：插入成果

 
 
 
 
    
  
  
  
  
  1. update t set cell=123 where cell=44444444444; [3] 
    2.

事务A修改[1]中插入的数据，第三执行

结果：阻塞，等待执行结果

画外音：修改一条自己插入的数据，在等待什么呢?

 
 
 
 
    
  
  
  
  
  1. update t set cell=456 where cell=55555555555; [4] 
    2.

事务B修改[2]中插入的数据，***执行

结果：

  • 事务B死锁，事务B被回滚;

  • 事务A中，[3]语句阻塞结束，执行成功;

画外音：说明事务A中阻塞的语句，确实在等事务B中的某个锁。

四、结果分析

两个事务，各自修改自己插入的数据，却产生了死锁，确实诡异。

上述实验现象的两个核心问题是：

  • 语句[3]阻塞，在等待什么锁?
  • 语句[4]死锁，此时事务A和事务B一定是彼此占住一把锁，请求彼此的锁，这些锁又是什么呢?

工具一：

 
 
 
 
    
  
  
  
  
  1. show engine innodb status; 
    2.

执行之后，显示的内容如下(放大仔细看)：

信息很多，别急，楼主娓娓道来。

***部分，关键词是：

  • Transaction 1，事务3998;
  • 在执行
    •   
  
  
  
      
   
   
   
   
    1. update t set cell=123 where cell=44444444444; 
      2.
  • 正在等待锁释放(waiting for this lock to be granted)，记录锁(record locks)，主键索引上(index primary)，互斥锁(lock_mode X)，物理记录(physical record)，asc 55555555555;

画外音：英文比较差没事，抓关键词。

画外音，InnoDB存储引擎，聚集索引与非聚集索引的实现方式，决定了锁会加在聚集索引上，详见文章：

《1分钟了解MyISAM与InnoDB的索引差异》。

第二部分，关键词是：

  • Transaction 2，事务3999;
  • 正在执行
    •   
  
  
  
      
   
   
   
   
    1. update t set cell=456 where cell=55555555555; 
      2.
  • 持有锁(holds the lock)，记录锁(record locks)，主键索引上(index primary)，互斥锁(lock_mode X)，物理记录(physical record)，asc 55555555555;
  • 正在等待锁释放(waiting for this lock to be granted)，记录锁(record locks)，主键索引上(index primary)，互斥锁(lock_mode X)，物理记录(physical record)，asc 11111111111;
  • 事务2回滚(we roll back transaction 2);

通过show engine innodb status; 能够看到很多事务与锁之间的信息，对分析问题十分有帮助，这些信息，能够解释一些问题，但仍有两个疑惑：

(1)事务1为啥想拿55555555555的锁?

画外音：这正是，事务1被阻塞的原因。

(2)事务2为啥想拿11111111111的锁?死锁的发生，说明事务1此时真占着11111111111的锁，这又是为什么呢?

画外音：***个事务占111抢555，第二个事务占555抢111，循环嵌套，才会死锁。

工具二：

 
 
 
 
    
  
  
  
  
  1. explain 
    2.

为了进一步寻找原因，可以通过explain看下导致死锁语句的执行计划。

 
 
 
 
    
  
  
  
  
  1. explain update t set cell=456 where cell=55555555555; 
    2.

(1) select_type：SIMPLE

这是一个简单类型的SQL语句，不含子查询或者UNION。

(2) type：index

访问类型，即找到所需数据使用的遍历方式，潜在的方式有：

  • ALL(Full Table Scan)：全表扫描;
  • index：走索引的全表扫描;
  • range：***where子句的范围索引扫描;
  • ref/eq_ref：非唯一索引/唯一索引单值扫描;
  • const/system：常量扫描;
  • NULL：不用访问表;

上述扫描方式，ALL最慢，逐步变快，NULL最快。

怀疑点1：明明cell字段有uniq索引，为何要进行走PK索引的全表扫描呢?

(3) possible_keys：NULL

可能在哪个索引找到记录。

(4) key：PRIMARY

实际使用索引。

画外音：使用PK进行的全表扫描。

(5) ref：NULL

哪些列，或者常量用于查找索引上的值。

怀疑点2：where条件中的查询条件55555555555，本来应该作为在索引上被检索的值呀?

(6) rows：5

找到所需记录，预估需要读取的行数。

怀疑点3：明明修改的是5，为何初始化的1，2，3，以及***个事务插入的4，以及第二个事务插入的5，都要被读取呢?不应该全表扫描呀。

通过explain，基本已经可以判断：

 
 
 
 
    
  
  
  
  
  1. update t set cell=456 where cell=55555555555; 
    2.

并没有和我们预想一样，走cell索引进行查询，而是走了PK索引进行了全表扫描。

再仔细一看：

 
 
 
 
    
  
  
  
  
  1. create table t ( 
    2.   
  
  
  
  2. id int(20) primary key AUTO_INCREMENT, 
    3.   
  
  
  
  3. cell varchar(20) unique 
    4.   
  
  
  
  4. )engine=innodb; 
    5.

建表的时候cell定义的是字符串类型。

而更新的时候，

 
 
 
 
    
  
  
  
  
  1. update t set cell=456 where cell=55555555555; 
    2.

使用的是整数类型。

类型转换，会导致全表扫描，出现锁升级，锁住全部记录。

加上引号，再次通过explain验证一下：

 
 
 
 
    
  
  
  
  
  1. explain update t set cell= '456 ' where cell= '55555555555 '; 
    2.

果然印证了猜想：

  • type：range，变为了走索引的字符串比对，范围扫描;
  • possible_keys：cell，通过cell索引找到了记录;
  • key：cell，实际使用cell索引;
  • ref：const，使用了常量' 555'进行比对;
  • rows：1，预估读取行数是1;

这下全部可以解释了。

总结

就本例而言：需要注意字符串与整数之间的强制类型转换，有时候少一个引号，就会使得行锁升级为表锁。

死锁是MySQL中非常难调试的问题，常见的思路与方法有：

  • 通过多终端模拟并发事务，复现死锁;
  • 通过show engine innodb status; 可以查看事务与锁的信息;
  • 通过explain可以查看执行计划;

思路比结论更重要，希望大家有收获。

【本文为专栏作者“58沈剑”原创稿件，转载请联系原作者】

本文名称：两个小工具，MySQL死锁分析，新技能又Get！
文章来源：http://www.mswzjz.cn/qtweb/news28/217878.html

攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能，是专注品牌与效果的网络营销公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 贝锐智能

贝锐智能技术为您推荐以下文章

ChatGPT知识

分类信息网站

彭山网站制作　　 　绵阳托管服务器　　 　成都商城网站制作　　 　犀浦防护栏定制　　 　广安网站建设　　 　网站免费收录　　 　品牌网站建设　　 　香港主机　　 　成都电信服务器托管　　 　成都封阳台　　 　IDC机房托管　　 　成都网站建设　　 　成都网站改版　　 　成都网站建设　　 　成都app软件开发　　 　软文推广公司　　 　成都展柜定制　　 　成都西信机房　　 　网站开发　　 　成都外贸网站建设