MySQL死锁案例

Arce

　　版本：MySQL 5.1.56-log
　　LATEST DETECTED DEADLOCK
　　------------------------
　　140111  2:44:29
　　*** (1) TRANSACTION:
　　TRANSACTION 5 871020258, ACTIVE 0 sec, process no 23080, OS thread id 1367480640 starting index read
　　mysql tables in use 1, locked 1
　　LOCK WAIT 3 lock struct(s), heap size 1216
　　MySQL thread id 25992539, query id 2395069705 192.168.1.60 hddoc_oper Updating
　　update t_wiki_audit_his set HIS_STATE=0  where PRODUCT_ID = '01020702' AND RESOURCE_NAME = '鄂尔多斯办假存款证明Q.272689.7451'
　　*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 0 page no 19850654 n bits 144 index`PRIMARY` of table `hddoc/t_wiki_audit_his` trx id 5 871020258 lock_mode X locks rec but not gap waiting
　　Record lock, heap no 46 PHYSICAL RECORD: n_fields 31; compact format; info bits 32
　　0: len 8; hex 8000000000af07dd; asc         ;; 1: len 6; hex 000533eab6d9; asc   3   ;; 2: len 7; hex 000138c0021145; asc   8   E;; 3: len 8; hex 3031303230373032; asc 01020702;; 4: len 8; hex 8000001748ac6049; asc     H `I;; 5: len 21; hex e58588e5aea1e5908ee58f91e8af84e5aea1e5aea4; asc                      ;; 6: len 17; hex 4f57316c425655564241676b4151566c57; asc OW1lBVUVBAgkAQVlW;; 7: len 30; hex e98482e5b094e5a49ae696afe58a9ee58187e5ad98e6acbee8af81e6988e; asc                               ;...(truncated); 8: len 4; hex 80000001; asc     ;; 9: len 6; hex 636e776c6b63; asc cnwlkc;; 10: len 17; hex 595131315255454263415746666655524a; asc YQ11RUEBcAWFffURJ;; 11: len 4; hex 52d03af5; asc R : ;; 12: len 1; hex 80; asc  ;; 13: len 1; hex 81; asc  ;; 14: len 28; hex e4ba92e58aa8e7aea1e79086e591982de887aae58aa8e8af84e5aea1; asc                -            ;; 15: len 17; hex 735a33466e6457563656516c59566c466d; asc sZ3FndWV6VQlYVlFm;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 4; hex 52d04092; asc R @ ;; 19: len 30; hex e5898de58fb0e689b9e9878fe8af84e5aea12de8af8de69da1e586bbe7bb; asc                   -           ;...(truncated); 20: SQL NULL; 21: len 1; hex 43; asc C;; 22: len 1; hex 43; asc C;; 23: len 1; hex 80; asc  ;; 24: len 1; hex 80; asc  ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 4; hex 80000000; asc     ;; 29: len 4; hex 80000001; asc     ;; 30: len 4; hex 80000001; asc     ;;
　　*** (2) TRANSACTION:
　　TRANSACTION 5 871020249, ACTIVE 0 sec, process no 23080, OS thread id 1744804160 updating or deleting, thread declared inside InnoDB 499
　　mysql tables in use 1, locked 1
　　3 lock struct(s), heap size 1216, undo log entries 1
　　MySQL thread id 25993964, query id 2395069707 192.168.1.92 hddoc_oper updating
　　delete from t_wiki_audit_his where ID='11470813'
　　*** (2) HOLDS THE LOCK(S):
　　RECORD LOCKS space id 0 page no 19850654 n bits 144 index `PRIMARY` of table `hddoc/t_wiki_audit_his` trx id 5 871020249 lock_mode X locks rec but not gap
　　Record lock, heap no 46 PHYSICAL RECORD: n_fields 31; compact format; info bits 32
　　0: len 8; hex 8000000000af07dd; asc         ;; 1: len 6; hex 000533eab6d9; asc   3   ;; 2: len 7; hex 000138c0021145; asc   8   E;; 3: len 8; hex 3031303230373032; asc 01020702;; 4: len 8; hex 8000001748ac6049; asc     H `I;; 5: len 21; hex e58588e5aea1e5908ee58f91e8af84e5aea1e5aea4; asc                      ;; 6: len 17; hex 4f57316c425655564241676b4151566c57; asc OW1lBVUVBAgkAQVlW;; 7: len 30; hex e98482e5b094e5a49ae696afe58a9ee58187e5ad98e6acbee8af81e6988e; asc                               ;...(truncated); 8: len 4; hex 80000001; asc     ;; 9: len 6; hex 636e776c6b63; asc cnwlkc;; 10: len 17; hex 595131315255454263415746666655524a; asc YQ11RUEBcAWFffURJ;; 11: len 4; hex 52d03af5; asc R : ;; 12: len 1; hex 80; asc  ;; 13: len 1; hex 81; asc  ;; 14: len 28; hex e4ba92e58aa8e7aea1e79086e591982de887aae58aa8e8af84e5aea1; asc                -            ;; 15: len 17; hex 735a33466e6457563656516c59566c466d; asc sZ3FndWV6VQlYVlFm;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 4; hex 52d04092; asc R @ ;; 19: len 30; hex e5898de58fb0e689b9e9878fe8af84e5aea12de8af8de69da1e586bbe7bb; asc                   -           ;...(truncated); 20: SQL NULL; 21: len 1; hex 43; asc C;; 22: len 1; hex 43; asc C;; 23: len 1; hex 80; asc  ;; 24: len 1; hex 80; asc  ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 4; hex 80000000; asc     ;; 29: len 4; hex 80000001; asc     ;; 30: len 4; hex 80000001; asc     ;;
　　

　　*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 0 page no 19845946 n bits 480 index `IDX_RESOURCE_NAME_PRODUCT_ID` of table `hddoc/t_wiki_audit_his` trx id 5 871020249 lock_mode X locks rec but not gap waiting
　　Record lock, heap no 411 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
　　0: len 15; hex e98482e5b094e5a49ae696afe58a9e; asc                ;; 1: len 8; hex 3031303230373032; asc 01020702;; 2: len 8; hex 8000000000af07dd; asc         ;;
　　

　　*** WE ROLL BACK TRANSACTION (1)
　　以上是死锁信息，从上面的两个sql语句分析出：update语句，走的是IDX_RESOURCE_NAME_PRODUCT_ID索引，先对此索引上的记录加锁，然后再对primary key上的记录加锁；
　　delete语句，走的是主键索引，先对主键索引记录加锁，然后再对IDX_RESOURCE_NAME_PRODUCT_ID索引记录加锁；
　　这两个顺序正好相反，如果并发同时操作同一条记录，就有可能产生死锁。- 事实发现确实是并发updta和delete同一条记录……
　　结论：因此此死锁时不可避免的。