由不同的索引更新解决MySQL死锁套路
16lz
2021-12-22
前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 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:t1update t3 set b = '' where a = "1";# 事务2:t2update t3 set b = '' where b = "2";
第一条语句(通过唯一索引去更新记录)
update t3 set b = '' where a = "1";
序号 | 索引 | 锁类型 |
---|---|---|
1 | uk_a | X |
2 | PRIMARY | X |
3 | idx_b | X |
update t3 set b = '' where b = "2";
序号 | 索引 | 锁类型 |
---|---|---|
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 readmysql tables in use 1, locked 1LOCK 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 updateupdate 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 waitingRecord 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 deletingmysql tables in use 1, locked 14 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updatingupdate 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 gapRecord 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 waitingRecord 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) 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
*** (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
与我们分析的完全一致,也与线上的死锁日志一模一样
更多相关文章
- Android通过OpenSL ES播放音频套路详解
- Android后台保活套路分析
- Android的BUG(三) - 广为人知的诟病:频繁重启问题
- 腾讯Android面经
- Android中的WatchDog
- Android(安卓)framework Watchdog的监控过程
- Android的BUG - 广为人知的诟病:频繁重启问题
- Android(安卓)framework Watchdog的监控过程
- Android的BUG(三) - 广为人知的诟病:频繁重启问题