通过唯一索引S锁与X锁来了解MySQL死锁套路
16lz
2021-12-22
在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL 源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例。
这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇
我们来看一个简化过的例子
# 构造数据CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10), `level` int(11), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`));INSERT INTO `t1` (`name`, `level`) VALUES ('A',0);# 出现问题的sql语句如下,并发情况下就会出现死锁INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);update t1 set level = 1 where name = "A";
第一条语句
INSERT ignore INTO t1 (name, level) VALUES ('A',0);
可以看到这条语句对唯一键 uk_name 加共享锁(S锁),而且成功。
第二条语句
update t1 set level = 1 where name = "A";
这种情况在之前的文章已经介绍过,会对唯一索引加 X 锁,然后对主键索引加 X 锁
这样就可以非常轻松的复现死锁的问题了,步骤如下
1.开启两个 session,分别 begin
2.session1 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
3.session2 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
4.session1 执行update t1 set level = 1 where name = "A"; 进入等待状态
5.session2 执行update t1 set level = 1 where name = "A";,死锁产生,被回滚,同时事务 1 执行成功
详细的锁状态变化如下
t1 | t2 | 备注 |
---|---|---|
INSERT IGNORE INTO | - | t1成功获得uk的S锁 DB_SUCCESS |
- | INSERT IGNORE INTO | t2成功获得uk的S锁 DB_SUCCESS |
UPDATE | - | t1尝试获得uk的X锁,但没有成功,处于等待状态 DB_LOCK_WAIT |
- | UPDATE | t2尝试获得uk的X锁,发现死锁产生 DB_DEADLOCK |
- | Deadlock | t2释放S锁 |
成功 | - | - |
死锁日志如下:
LATEST DETECTED DEADLOCK------------------------181208 23:00:52*** (1) TRANSACTION:TRANSACTION 53A7, ACTIVE 162 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 12, OS thread handle 0x700010522000, query id 1424 localhost root Updatingupdate t1 set level = 1 where name = "A"*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 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 41; asc A;; 1: len 4; hex 80000001; asc ;;*** (2) TRANSACTION:TRANSACTION 53A8, ACTIVE 8 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 376, 2 row lock(s)MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updatingupdate t1 set level = 1 where name = "A"*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode SRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 41; asc A;; 1: len 4; hex 80000001; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 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 41; asc A;; 1: len 4; hex 80000001; asc ;;*** WE ROLL BACK TRANSACTION (2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 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(三) - 广为人知的诟病:频繁重启问题