三个事务导致的死锁
最近查了下这个问题，并尝试复现了当时的死锁情况，下面是重现过程
|
时间点 |
事务A |
事务B |
事务C |
|
T0 |
Begin |
Begin |
Begin |
|
T1 |
|
|
update test.t1 set vflag=7, source=576792, fflag=1, mflag=7 where uid=2491598800 and vflag in(0,4,5,6,7,15,16) and status_id=3716431142880051; |
|
T2 |
|
update t1 set vflag=7, source=576792, fflag=93, mflag=7 where uid=2491598800 and vflag in(0,4,5 ,6,7,15,16) and status_id=3716431457497283; |
|
|
T3 |
update test.t1 set vflag=7, source=576792, fflag=1, mflag=7 where uid=2491598800 and vflag in(0,4,5,6,7,15,16) and status_id=3716431142880051;（语句同事务C） |
|
|
|
T4 |
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
Query OK, 1 row affected (9.97 sec) |
Rollback; |
上面这个过程复现了死锁的锁争用情况，但是InnoDB最后决定回滚的事务是不一致的，
初步分析是事务C回滚之后，A和B拿到锁的顺序不一致导致死锁
当时具体死锁信息如下
141111 15:23:29
*** (1) TRANSACTION:
TRANSACTION E652F8659, ACTIVE 0 sec, process no 768, OS thread id 1692539200 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 7 row lock(s), undo log entries 1
MySQL thread id 2707997, query id 1371160285 10.75.0.156 openapi Updating
update status_timeline_30.timeline_1405 set vflag=7, source=576792, fflag=1, mflag=7 where uid=2491598800 and vflag in(0,4,5,6,7,15,16) and status_id=3716431142880051
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 236 page no 198067 n bits 432 index `PRIMARY` of table `status_timeline_30`.`timeline_1405` trx id E652F8659 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482c7d0; asc ;;
1: len 1; hex 0f; asc ;;
2: len 8; hex 000d2d24dd66eb5e; asc -$ f ^;;
3: len 4; hex 00007a12; asc z ;;
4: len 4; hex 00020001; asc ;;
5: len 6; hex 000c636448d3; asc cdH ;;
6: len 7; hex 28000001ad1d61; asc ( a;;
7: len 1; hex 80; asc ;;
*** (2) TRANSACTION:
TRANSACTION E652F865A, ACTIVE 0 sec, process no 768, OS thread id 1256171840 updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 6 row lock(s), undo log entries 1
MySQL thread id 2707970, query id 1371160282 10.75.24.210 openapi Updating
update status_timeline_30.timeline_1405 set vflag=7, source=576792, fflag=93, mflag=7 where uid=2491598800 and vflag in(0,4,5,6,7,15,16) and status_id=3716431457497283
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 236 page no 198067 n bits 432 index `PRIMARY` of table `status_timeline_30`.`timeline_1405` trx id E652F865A lock_mode X locks gap before rec
Record lock, heap no 189 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482c7d0; asc ;;
1: len 1; hex 06; asc ;;
2: len 8; hex 000d2dc61663b517; asc - c ;;
3: len 4; hex 000000ae; asc ;;
4: len 4; hex 0000004d; asc M;;
5: len 6; hex 000c69baea0d; asc i ;;
6: len 7; hex 98000003900084; asc ;;
7: len 1; hex 83; asc ;;
Record lock, heap no 190 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482c7d0; asc ;;
1: len 1; hex 07; asc ;;
2: len 8; hex 000d29dea6592c73; asc ) Y,s;;
3: len 4; hex 0008cd18; asc ;;
4: len 4; hex 00000009; asc ;;
5: len 6; hex 000c44bfa2a7; asc D ;;
6: len 7; hex fa0000020f0084; asc ;;
7: len 1; hex 87; asc ;;
Record lock, heap no 207 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482c7d0; asc ;;
1: len 1; hex 0f; asc ;;
2: len 8; hex 000d2d24dd66eb5e; asc -$ f ^;;
3: len 4; hex 00007a12; asc z ;;
4: len 4; hex 00020001; asc ;;
5: len 6; hex 000c636448d3; asc cdH ;;
6: len 7; hex 28000001ad1d61; asc ( a;;
7: len 1; hex 80; asc ;;
Record lock, heap no 220 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482ceda; asc ;;
1: len 1; hex 00; asc ;;
2: len 8; hex 000d2ad5921fcd74; asc * t;;
3: len 4; hex 000000ae; asc ;;
4: len 4; hex 00000000; asc ;;
5: len 6; hex 000c4ceefc48; asc L H;;
6: len 7; hex e8000001ba0084; asc ;;
7: len 1; hex 80; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 236 page no 198067 n bits 432 index `PRIMARY` of table `status_timeline_30`.`timeline_1405` trx id E652F865A lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 000000009482c7d0; asc ;;
1: len 1; hex 0f; asc ;;
2: len 8; hex 000d2d24dd66eb5e; asc -$ f ^;;
3: len 4; hex 00007a12; asc z ;;
4: len 4; hex 00020001; asc ;;
5: len 6; hex 000c636448d3; asc cdH ;;
6: len 7; hex 28000001ad1d61; asc ( a;;
7: len 1; hex 80; asc ;;
*** WE ROLL BACK TRANSACTION (2)
