主页 > 知识库 > 由不同的索引更新解决MySQL死锁套路

由不同的索引更新解决MySQL死锁套路

热门标签:百度竞价排名 呼叫中心市场需求 网站排名优化 服务外包 Linux服务器 AI电销 铁路电话系统 地方门户网站

前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况。因为线上的表结构比较复杂,做了一些简化以后如下

CREATE TABLE `t3` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` varchar(5),
 `b` varchar(5),
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_a` (`a`),
 KEY `idx_b` (`b`) 
)
INSERT INTO `t3` (`id`, `a`, `b`) VALUES 
 (1,'1','2');
# sql语句如下

# 事务1:t1
update t3 set b = '' where a = "1";

# 事务2:t2
update t3 set b = '' where b = "2";


两条语句造成死锁的情况用手动的方式比较难复现,我们先来分析一下加锁的过程

第一条语句(通过唯一索引去更新记录)

update t3 set b = '' where a = "1";

 

整理一下,加了3个X锁,顺序分别是

序号 索引 锁类型
1 uk_a X
2 PRIMARY X
3 idx_b X

第二条语句

update t3 set b = '' where b = "2";

整理一下,加了 3 个 X 锁,顺序分别是

序号 索引 锁类型
1 idx_b X
2 PRIMARY X
3 idx_b X

两条语句从加锁顺序看起来就已经有构成死锁的条件了

手动是比较难模拟的,写个代码并发的去同时执行那两条 SQL 语句,马上就出现死锁了

------------------------
LATEST DETECTED DEADLOCK
------------------------
181102 12:45:05
*** (1) TRANSACTION:
TRANSACTION 50AF, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 34, OS thread handle 0x70000d842000, query id 549 localhost 127.0.0.1 root Searching rows for update
update t3 set b = '' where b = "2"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AF lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc P ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) TRANSACTION:
TRANSACTION 50AE, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updating
update t3 set b = '' where a = "1"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc P ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 5 n bits 72 index `idx_b` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

分析一下死锁日志

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AF lock_mode X locks rec but not gap waiting

事务2:想获取主键索引的 X 锁

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AE lock_mode X locks rec but not gap

事务1:持有主键索引的 X 锁

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 5 n bits 72 index idx_b of table d1.t3 trx id 50AE lock_mode X locks rec but not gap waiting

事务1:想获取普通索引 idx_b 的 X 锁

与我们分析的完全一致,也与线上的死锁日志一模一样

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

您可能感兴趣的文章:
  • 通过唯一索引S锁与X锁来了解MySQL死锁套路
  • 一个mysql死锁场景实例分析
  • 详解MySQL(InnoDB)是如何处理死锁的
  • 实例讲解MySQL中乐观锁和悲观锁
  • Mysql查询正在执行的事务以及等待锁的操作方式
  • 简单了解 MySQL 中相关的锁

标签:湖南 兰州 崇左 湘潭 衡水 铜川 仙桃 黄山

巨人网络通讯声明:本文标题《由不同的索引更新解决MySQL死锁套路》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266