`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting 表示事务2的insert 语句正在等待插入意向锁...lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_GAP ) 这里需要各位注意的是锁组合，类似lock_mode...X waiting ,lock_mode X,lock_mode X locks gap before rec insert intention waiting 是我们分析死锁的核心重点。...locks rec but not gap 表示为记录锁，非gap锁：lock->type_mode & LOCK_REC_NOT_GAP insert intention 表示为插入意向锁
`x` trx id 2235 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION...`x` trx id 2237 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD...`x` trx id 2237 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...lock_mode X locks gap before rec insert intention waiting 。...locks gap before rec insert intention waiting 。
`tx` trx id 2391 lock_mode X locks gap before rec insert intention waiting 分析: sess1 持有的Next-key lock...`ty` trx id 2463 lock_mode X locks gap before rec insert intention waiting 分析: 因为表ty没有c2=11的记录,sess1...会持有(8,13)的gap lock ,从事务日志 lock_mode X locks gap before rec insert intention waiting可以看出sess1 持有的gap lock...`t7` trx id 2506 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...sess1的S-Next-key Lock锁释放，在日志中显示lock_mode X locks gap before rec insert intention waiting.
`ix` trx id 1873 lock_mode X locks gap before rec insert intention waiting ------------------ ---TRANSACTION...但是 a=8 与sess1 持有的 gap lock [5-15] 冲突，于是等待lock_mode X locks gap before rec insert intention waiting，并进入等待队列里面...`ix` trx id 1866 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION...`ix` trx id 1865 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...(1) 日志分析 replace into ix(a,b) values(9,12); 和插入(8,10) 类似需要申请lock_mode X locks gap before rec insert
`t8` trx id 462308678 lock_mode X locks rec but not gap waiting ------------------ TABLE LOCK table...`t8` trx id 462308676 lock_mode X locks rec but not gap 从日志中可以看到 sess2的事务持有的锁多了一把 lock_mode X locks rec...`t7` trx id 462308660 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED:...`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...Lock锁释放，在日志中显示lock_mode X locks gap before rec insert intention waiting。
`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...然后根据WAITING FOR THIS LOCK TO BE GRANTED，提示事务2 insert语句正在等待 lock_mode X locks gap before rec insert intention...waiting， 因为insert语句 [4,2] 介于gap锁[1,2]-[2,5]之间，所以有了提示 "lock_mode X locks gap",insert语句必须等待前面 sess2中delete...找到满足条件的记录，并且记录有效，则对记录加X锁，No Gap锁(lock_mode X locks rec but not gap); b....delete持有的锁变为lock_mode X locks rec but not gap 。
`song_rank` trx id 27540 lock_mode X locks gap before rec insert intention waiting Record lock,...`song_rank` trx id 27539 lock_mode X locks gap before rec insert intention waiting Record lock,...weight=weight+1 该条语句正在等待索引songId_idx的插入意向排他锁： lock_mode X locks gap before rec insert intention waiting...： lock_mode X locks gap before rec insert intention waiting 锁相关概念补充（附）： 考虑到有些读者可能对上面insert intention...`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no
`tt` trx id A06213D6 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields...`ttp` trx id A0640DCF lock_mode X locks gap before rec Record lock, heap no 5 PHYSICAL RECORD: n_fields...info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Insert Intention Locks An insert intention...`tt` trx id A068EAF9 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4...`tt` trx id A068EAF9 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4
`dl` trx id 1422661 lock_mode X locks rec but not gapRecord lock, heap no 4 PHYSICAL RECORD: n_fields...`dl` trx id 1422661 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL...`dl` trx id 1422664 lock_mode X locks gap before recRecord lock, heap no 3 PHYSICAL RECORD: n_fields...图片sess2 在 T4 时刻执行了更新，affect rows 为0，同样在在c2的(0，3)区间中加了GAP锁 lock_mode X locks gap before rec，GAP 锁和GAP...) 的 lock_mode X locks gap before rec insert intention waiting是冲突的，也就是sess1 需要等待sess2 在 T4 持有的GAP X锁，发生了等待
`t4` trx id 462308535 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION:...`t4` trx id 462308534 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED:...`t4` trx id 462308534 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...T1 的insert 语句申请插入意向锁，但是插入意向锁和T2持有的X GAP (lock_mode X locks gap before rec) 冲突，故等待T2中的GAP 锁释放。...T2 的insert 语句申请插入意向锁，但是插入意向锁和T1持有 X GAP (lock_mode X locks gap before rec) 冲突，故等待T1中的GAP 锁释放。
ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;const lock_t* wait_for = lock_rec_other_has_conflicting...其中 sess2 sess3 等待申请lock_mode X locks gap before rec insert intention waiting，显然是被sess1持有的LOCK S Next...`t` trx id 2490 lock_mode X locks rec but not gap*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS...`t` trx id 2490 lock_mode X locks gap before rec insert intention waiting*** WE ROLL BACK TRANSACTION...T4 insert (6,5,4) 写入(3,6)的区间，申请lock_mode X locks gap before rec insert intention waiting,但是需要等待T3会话LOCK_S
`account` trx id 38049 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields...`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3...`account` trx id 38048 lock_mode X locks gap before rec insert intention waiting Record lock, heap no...FOR THIS LOCK TO BE GRANTED)，插入意向排他锁（lock_mode X locks gap before rec insert intention waiting），普通索引...into account values(null,'Yan',100) 3）持有锁(HOLDS THE LOCK)，间隙锁(lock_mode X locks gap before rec)，普通索引
into table (...) values (...) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12940...`table` trx id 96847162 lock_mode X locks gap before rec insert intention waiting Record lock, heap no...`table` trx id 96847161 lock_mode X locks gap before rec Record lock, heap no 292 PHYSICAL RECORD: n_fields...`table` trx id 96847161 lock_mode X locks gap before rec insert intention waiting Record lock, heap no...# 新增数据 insert into `db`.`table` values(...)
`t6` trx id 462308726 lock_mode X insert intention waiting *** WE ROLL BACK TRANSACTION (2) 2.3 死锁分析...`t6` trx id 462308737 lock_mode X locks rec but not gap 首先sess2的insert 申请了IX锁，因为sess1 会话已经插入成功并且持有唯一键...`t6` trx id 462308737 lock_mode X locks rec but not gap 与会话sess2 的加锁申请流程一致，都在等待sess1释放锁资源。...`t6` trx id 462308738 lock mode S locks gap before rec 死锁的原因 sess1 insert成功并针对a=15的唯一键加上X锁。...)成功,sess2和sess3都要请求索引a=15上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP),日志中提示 lock_mode X insert intention。
**student trx id 2321 lock_mode X locks gap before rec insert intention waiting 表示事务 2 的 insert 语句正在等待插入意向锁...lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap ) --经典案例分析-- 案例一:并发申请...T1 的 insert 语句申请插入意向锁，但是插入意向锁和 T2 持有的 X gap ( lock_mode X locks gap before rec ) 冲突，故等待 T2 中的 gap 锁释放...T2 的 insert 语句申请插入意向锁，但是插入意向锁和 T1 持有 X gap (lock_mode X locks gap before rec )冲突，故等待T1中的 gap 锁释放。...T1 的 S-Next-key Lock 锁释放, 在日志中显示 lock_mode X locks gap before rec insert intention waiting 。
`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION...`ty` trx id 1864 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD...新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP 对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。...已经持有的gap 锁会阻塞插入意向锁INSERT_INTENTION 另外 对于通过唯一索引更新或者删除不存在的记录，会申请加上 gap锁。...index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting T4:sess2
本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock) 1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。...****student trx id 2322 lock_mode X waiting RECORD LOCKS 表示记录锁， 此条内容表示事务 1 正在等待表 student 上的 idx_stuno...****student trx id 2321 lock_mode X locks gap before rec insert intention waiting 表示事务 2 的 insert 语句正在等待插入意向锁...lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap ) 经典案例分析 案例一:事务并发...T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。
`account` trx id 38049 lock_mode X locks gap before recRecord lock, heap no 6 PHYSICAL RECORD: n_fields...`child`trx id 8731 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL...`account` trx id 38048 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no...`account` trx id 38049 lock_mode X locks gap before recRecord lock, heap no 6 PHYSICAL RECORD: n_fields...)，插入意向排他锁（lockmode X locks gap before rec insert intention waiting），普通索引（idxname），物理记录(PHYSICAL RECORD
`tc` trx id 1999 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD...`tc` trx id 1999 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION...新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP 对于insert操作来说，若发生唯一约束冲突，需要对冲突的唯一索引申请加上S Next-key Lock。...已经持有的gap 锁会阻塞插入意向锁INSERT_INTENTION 另外 对于通过唯一索引更新或者删除不存在的记录，会申请加上 gap锁。...:LOCK_X + LOCK_REC_NOT_GAP。
`student` trx id 2656E2 lock_mode X locks rec but not gap XXXXXX *** (2) WAITING FOR THIS LOCK TO BE...`student` trx id 2656E2 lock_mode X locks gap before rec insert intention waiting Record lock, heap no...特殊锁 插入意向锁（Insert Intention Locks），行级锁，RR特有, 是一种特殊的间隙锁（所以有的地方把它简写成 II GAP），这个锁表示插入的意向，只有在 INSERT 的时候才会有这个锁...对应索引行值为name_age的记录锁写锁 lock_mode X locks rec but not gap 等待锁 WAITING FOR THIS LOCK TO BE GRANTED: 在插入意向锁之前等待间隙锁写锁释放...及lock_mode X locks gap before rec insert intention waiting 最终由于事务(1)权重小我们进行了回滚操作 4.
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
即时通信 IM
ICP备案
对象存储
实时音视频
