由不同的索引更新解决MySQL死锁套路
程序员文章站
2023-11-30 12:59:46
前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 sql 就造成死锁的情况。因为线上的表结构比较复杂...
前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 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 锁
与我们分析的完全一致,也与线上的死锁日志一模一样
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
下一篇: AE空物体在哪儿? AE中空物体的用法
推荐阅读
-
由不同的索引更新解决MySQL死锁套路
-
由不同的索引更新解决MySQL死锁套路
-
MySQL中因字段字符集不同导致索引不能命中的解决方法
-
MySQL~InnoDB引擎解决脏读,不可重复读,幻读,丢失更新的原理(lock事务锁、自增长锁、Record Lock、Gap Lock、Next-Key Lock、死锁)
-
MySQL中由load data语句引起死锁的解决案例
-
MySQL中由load data语句引起死锁的解决案例_MySQL
-
MySQL中由load data语句引起死锁的解决案例
-
MySQL中由load data语句引起死锁的解决案例_MySQL
-
MySQL~InnoDB引擎解决脏读,不可重复读,幻读,丢失更新的原理(lock事务锁、自增长锁、Record Lock、Gap Lock、Next-Key Lock、死锁)