新手学习 MySQL死锁分析

shaoling 发表于 2024-7-16 22:44:14

日志显示：
事务一表ACT_RU_TASK    主键记录16199238等待主键记录锁
事务二表ACT_RU_TASK    主键记录16199238持有主键记录锁
事务二表act_ru_execution 主键记录16199226等待主键记录锁

推算出：事务一持有表act_ru_execution的主键记录锁，记录为：16199226
建议：加锁的顺序不一致，调整程序发起的事务对ACT_RU_TASK表和act_ru_execution表的加锁的顺序为同一顺序。
请问：刚学习MySQL不久，面试被问类似问题不知道怎么回答，学习后通过一些案例，得出这样的分析过程，对这个案例这样分析对不对，是否还有什么遗漏补充么哈哈哈如果思路没问题以后面试就这么回答了哈哈哈:P

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-06-30 17:41:22 0x7f2dcf78d700
*** (1) TRANSACTION:
TRANSACTION 171327307, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 1064706, OS thread handle 139834756351744, query id 1435253358 172.16.5.145 safecloud updating
update ACT_RU_TASK
SET REV_ = 2,
   NAME_ = '整改任务1',
   PARENT_TASK_ID_ = null,
   PRIORITY_ = 50,
   CREATE_TIME_ = '2024-06-30 16:01:43.0',
   OWNER_ = null,
   ASSIGNEE_ = null,
   DELEGATION_ = null,
   EXECUTION_ID_ = '16199226',
   PROC_DEF_ID_ = 'task1003:2:10250010',
   DESCRIPTION_ = null,
   DUE_DATE_ = null,
   CATEGORY_ = null,
   SUSPENSION_STATE_ = 1,
   FORM_KEY_ = null
where ID_= '16199238'
   and REV_ = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1525 page no 151 n bits 200 index PRIMARY of table `safe_cloud`.`act_ru_task` trx id 171327307 lock_mode X locks rec but not gap waiting
Record lock, heap no 132 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 8; hex 3136313939323338; asc 16199238;;
1: len 6; hex 00000a363f4a; asc 6?J;;
2: len 7; hex 5b0001260425a2; asc [& % ;;
3: len 4; hex 80000005; asc ;;
4: len 8; hex 3136313939323236; asc 16199226;;
5: len 8; hex 3136313939323236; asc 16199226;;
6: len 19; hex 7461736b313030333a323a3130323530303130; asc task1003:2:10250010;;
7: len 13; hex e695b4e694b9e4bbbbe58aa131; asc          1;;
8: SQL NULL;
9: SQL NULL;
10: len 17; hex 7461736b31363133363331343637333436; asc task1613631467346;;
11: SQL NULL;
12: SQL NULL;
13: len 8; hex 5245534f4c564544; asc RESOLVED;;
14: len 4; hex 80000032; asc 2;;
15: len 4; hex 668110e7; asc f ;;
16: SQL NULL;
17: SQL NULL;
18: len 4; hex 80000001; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 171327306, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
22 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 29
MySQL thread id 1068213, OS thread handle 139834731058944, query id 1435253430 172.16.5.145 safecloud updating
update ACT_RU_EXECUTION set
   REV_ = 6,
   BUSINESS_KEY_ = '1807323616514129921',
   PROC_DEF_ID_ = 'task1003:2:10250010',
   ACT_ID_ = 'task1613631590938',
   IS_ACTIVE_ = 1,
   IS_CONCURRENT_ = 0,
   IS_SCOPE_ = 1,
   IS_EVENT_SCOPE_ = 0,
   PARENT_ID_ = null,
   SUPER_EXEC_ = null,
   SUSPENSION_STATE_ = 1,
   CACHED_ENT_STATE_ = 2,
   NAME_ = null
where ID_ = '16199226'
   and REV_ = 5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1525 page no 151 n bits 200 index PRIMARY of table `safe_cloud`.`act_ru_task` trx id 171327306 lock_mode X locks rec but not gap
Record lock, heap no 28 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 8; hex 3136323031333032; asc 16201302;;
1: len 6; hex 00000a363f4a; asc 6?J;;
2: len 7; hex db0001c01f01e5; asc    ;;
3: len 4; hex 80000001; asc ;;
4: len 8; hex 3136313939323236; asc 16199226;;
5: len 8; hex 3136313939323236; asc 16199226;;
6: len 19; hex 7461736b313030333a323a3130323530303130; asc task1003:2:10250010;;
7: len 13; hex e695b4e694b9e4bbbbe58aa132; asc          2;;
8: SQL NULL;
9: SQL NULL;
10: len 17; hex 7461736b31363133363331353930393338; asc task1613631590938;;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: len 4; hex 80000032; asc 2;;
15: len 4; hex 66812843; asc f (C;;
16: SQL NULL;
17: SQL NULL;
18: len 4; hex 80000001; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;

Record lock, heap no 132 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 8; hex 3136313939323338; asc 16199238;;
1: len 6; hex 00000a363f4a; asc 6?J;;
2: len 7; hex 5b0001260425a2; asc [& % ;;
3: len 4; hex 80000005; asc ;;
4: len 8; hex 3136313939323236; asc 16199226;;
5: len 8; hex 3136313939323236; asc 16199226;;
6: len 19; hex 7461736b313030333a323a3130323530303130; asc task1003:2:10250010;;
7: len 13; hex e695b4e694b9e4bbbbe58aa131; asc          1;;
8: SQL NULL;
9: SQL NULL;
10: len 17; hex 7461736b31363133363331343637333436; asc task1613631467346;;
11: SQL NULL;
12: SQL NULL;
13: len 8; hex 5245534f4c564544; asc RESOLVED;;
14: len 4; hex 80000032; asc 2;;
15: len 4; hex 668110e7; asc f ;;
16: SQL NULL;
17: SQL NULL;
18: len 4; hex 80000001; asc ;;
19: len 0; hex ; asc ;;
20: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1522 page no 151 n bits 208 index PRIMARY of table `safe_cloud`.`act_ru_execution` trx id 171327306 lock_mode X locks rec but not gap waiting
Record lock, heap no 96 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 8; hex 3136313939323236; asc 16199226;;
1: len 6; hex 00000a363f34; asc 6?4;;
2: len 7; hex 500003c00e0240; asc P @;;
3: len 4; hex 80000005; asc ;;
4: len 8; hex 3136313939323236; asc 16199226;;
5: len 19; hex 31383037333233363136353134313239393231; asc 1807323616514129921;;
6: SQL NULL;
7: len 19; hex 7461736b313030333a323a3130323530303130; asc task1003:2:10250010;;
8: SQL NULL;
9: len 17; hex 7461736b31363133363331343637333436; asc task1613631467346;;
10: len 1; hex 81; asc;;
11: len 1; hex 80; asc;;
12: len 1; hex 81; asc;;
13: len 1; hex 80; asc;;
14: len 4; hex 80000001; asc ;;
15: len 4; hex 80000002; asc ;;
16: len 0; hex ; asc ;;
17: SQL NULL;
18: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)

yejr 发表于 2024-7-17 09:23:00

可以先看看这部分内容 http://greatsql.io/docs/8.0.32-26/12-dev-guide/12-6-3-trx-mvcc-and-locking.html#%E6%AD%BB%E9%94%81

页: [1]

GreatSQL社区's Archiver

新手学习 MySQL死锁分析