巧用MySQL InnoDB引擎锁机制解决死锁问题

发布: 2008-4-25 10:03 | 作者: buyan | 来源: 希赛网 | 查看: 60次 | 进入软件测试论坛讨论

　最近，在项目开发过程中，碰到了数据库死锁问题，在解决问题的过程中，笔者对MySQL InnoDB引擎锁机制的理解逐步加深。

　　案例如下：

　　在使用Show innodb status检查引擎状态时，发现了死锁问题：

　　*** (1) TRANSACTION:
　　TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read
　　mysql tables in use 1, locked 1
　　LOCK WAIT 3 lock struct(s), heap size 320
　　MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
　　update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting
　　Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
　　0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
　　*** (2) TRANSACTION:
　　TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
　　mysql tables in use 1, locked 1
　　3 lock struct(s), heap size 320, undo log entries 1
　　MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating
　　update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)
　　*** (2) HOLDS THE LOCK(S):
　　RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap
　　Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
　　0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
　　*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting
　　Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
　　0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
　　*** WE ROLL BACK TRANSACTION (1)

　　此死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，以下是相关字段及索引：

　　ID：主键；
　　MON_TIME：监测时间；
　　STATUS_ID：任务状态；