`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 是我们分析死锁的核心重点。...例如. lock->type_mode 可以是Lock_X 或者Lock_S locks gap before rec 表示为gap锁:lock->type_mode & LOCK_GAP
然后根据WAITING FOR THIS LOCK TO BE GRANTED,提示事务2 insert语句正在等待 lock_mode X locks gap before rec insert intention...`t2` trx id 462308444 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED:...找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec but not gap); b....delete持有的锁变为lock_mode X locks rec but not gap 。...X locks rec but not gap。
`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap...`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT...`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT...`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT...`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap
`t8` trx id 462308678 lock_mode X locks rec but not gap waiting ------------------ TABLE LOCK table...`t8` trx id 462308678 lock_mode X locks rec but not gap waiting ---TRANSACTION 462308676, ACTIVE 5113...`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:...Lock锁释放,在日志中显示lock_mode X locks gap before rec insert intention waiting。
`tx` trx id 1850 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 1849, ACTIVE...`tx` trx id 1849 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD...`tx` trx id 1849 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (1) 2.4 分析死锁日志...gap T2: sess1 语句update通过普通索引idx_c1更新c2,先获取idx_c1 c1=5的X锁lock_mode X locks rec but not gap,然后去申请对应主键id...但是sess1 以及持有该锁,故会出现index idx_c1 of table test.tx trx id 1849 lock_mode X locks rec but not gap 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...`ty` trx id 2473 lock_mode X locks rec but not gap waiting 发现update语句在等待行锁 lock_mode X locks rec but...sess1的S-Next-key Lock锁释放,在日志中显示lock_mode X locks gap before rec insert intention waiting.
`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 锁释放。
`t1` trx id 39474 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields...`t1` trx id 39475 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields...`t1` trx id 39474 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields...“lock_mode X locks rec but not gap waiting” 事务39475持有锁的信息 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space...`t1` trx id 39475 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields
`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
`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 。
`t1` trx id 53A7 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields...`t1` trx id 53A8 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields....t1 trx id 53A7 lock_mode X locks rec but not gap waiting 事务 1 想获取 uk_name 唯一索引上的 X 锁 (非 gap 锁的记录锁) *...: 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 事务 2 想获得 uk_name 唯一索引上的 X 锁(非 gap 锁的记录锁) 跟之前理论上推断的结论是一致。
新数据插入:LOCK_X + LOCK_REC_NOT_GAP 对于insert操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上S Next-key Lock。...`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。
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
**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 ) --经典案例分析-- 案例一:并发申请...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 。...但是 T1 尚及持有该锁, 故会出现 index idx_c1 of table test.tx trx id 2077 lock_mode X locks rec but not gap waiting
`locktest` trx id 10021140 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION...`locktest` trx id 10021141 lock mode S locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED...`locktest` trx id 10021141 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)...lock in share mode获取到S锁(lock mode S locks rec but not gap), Session1执行update操作申请X锁,等待Session2释放(lock_mode...X locks rec but not gap waiting),Session2执行update操作同样申请X锁,等待Session1释放(lock_mode X locks rec but not
`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...X locks gap before rec insert intention waiting是冲突的,也就是sess1 需要等待sess2 在 T4 持有的GAP X锁,发生了等待.图片sess2
`t1` trx id 18912896 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD...`t1` trx id 18912129 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD...`t1` --表示在等的是表t1 的辅助索引idx_status_createtime 上面的锁 lock_mode X locks rec but not gap waiting --...`t1` --表示在等的是表t1 的主键索引 上面的锁 lock_mode X locks rec but not gap waiting --表示需要加一个排他锁(写锁),当前的状态是等待中...`t1` trx id 212055 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD
X locks rec but not gap waiting select * from __test_t1 where id=1 for update; --- TRANSACTION 12119368...`__test_t1` trx id 12119368 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL...X locks rec but not gap waiting update __test_t1 set c2=2 where id=1; ---TRANSACTION 12119368, ACTIVE...`__test_t1` trx id 12119368 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL...`__test_t1` trx id 12119374 lock_mode X locks rec but not gap waiting Record lock, heap no 126 PHYSICAL
会阻塞其他事务对其插入、更新、删除 记录锁的事务数据(关键词:lock_mode X locks rec but not gap),记录如下: RECORD LOCKS space id 58 page...`t` trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 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),普通索引
`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,...X locks gap before rec insert intention waiting 事务2部分 ?...X locks gap before rec 该条语句正在等待索引songId_idx的插入意向排他锁: lock_mode X locks gap before rec insert intention...`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no
领取专属 10元无门槛券
手把手带您无忧上云