日志显示: 事务一 表ACT_RU_TASK 主键记录16199238 等待主键记录锁 事务二 表ACT_RU_TASK 主键记录16199238 持有主键记录锁 事务二 表act_ru_execution 主键记录16199226 等待主键记录锁 推算出:事务一 持有 表act_ru_execution的主键记录锁,记录为:16199226 建议:加锁的顺序不一致,调整程序发起的事务对ACT_RU_TASK表和act_ru_execution表的加锁的顺序为同一顺序。 请问:刚学习MySQL不久,面试被问类似问题不知道怎么回答,学习后通过一些案例,得出这样的分析过程,对这个案例这样分析对不对,是否还有什么遗漏补充么 哈哈哈 如果思路没问题 以后面试就这么回答了 哈哈哈 ![]() ------------------------ 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) |
合作电话:010-64087828
社区邮箱:greatsql@greatdb.com