内容简介:前面几篇文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表sql语句如下我们先用之前debug的方式来看一下,这里两条语句分别加了哪些锁
前面几篇文章介绍了利用调试 MySQL 源码的方式来调试锁相关的信息,这里利用这个 工具 来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表
CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `b` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`),UNIQUE KEY `uk_a` (`a`), KEY `idx_b` (`b`) ) 复制代码
sql语句如下
t1 update t3 set b = '' where a = "1"; t2 update t3 set b = '' where b = "2"; 复制代码
我们先用之前debug的方式来看一下,这里两条语句分别加了哪些锁
第一条语句(通过唯一索引去更新记录)
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 |
两条语句从加锁顺序看起来就已经有构成死锁的条件了
手动是比较难模拟的,写个代码去跑一下
马上就出现了
------------------------ 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) 复制代码
跟我们线上的死锁日志一模一样
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- MySQL 死锁套路:唯一索引下批量插入顺序不一致
- MySQL 死锁套路:唯一索引 S 锁与 X 锁的爱恨情仇
- 慢SQL诊断优化套路包,套路用的对,速度升百倍
- 数据迁移的套路
- 数据迁移的套路
- 常见的软件架构套路
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Types and Programming Languages
Benjamin C. Pierce / The MIT Press / 2002-2-1 / USD 95.00
A type system is a syntactic method for automatically checking the absence of certain erroneous behaviors by classifying program phrases according to the kinds of values they compute. The study of typ......一起来看看 《Types and Programming Languages》 这本书的介绍吧!