巧用MySQLInnoDB引擎鎖機制解決死鎖問題

最近，在項目開發過程中，碰到了數據庫死鎖問題，在解決問題的過程中，筆者對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)