奇怪的mysql死锁，当有外键索引的时候，会需要请求对关联表的锁吗？

光辉岁月 2012-06-11 02:01:35

请教数据库死锁问题，现象是在回复帖子的时候出现死锁

以下是mysql show innodb status结果：

InnoDB | |
=====================================
120611 11:14:34 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 20196, signal count 20099
Mutex spin waits 0, rounds 608422, OS waits 1472
RW-shared spins 37967, OS waits 18286; RW-excl spins 4688, OS waits 290
------------------------
LATEST DETECTED DEADLOCK
------------------------
120608 10:53:29
*** (1) TRANSACTION:
TRANSACTION 0 3055951996, ACTIVE 38 sec, OS thread id 11112 inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 320, 2 row lock(s), undo log entries 2
MySQL thread id 823, query id 9503198 localhost 127.0.0.1 root update
insert into housesociety.housesubject_discuss (house_subject_id, user_id, isaviabled, content, istop, createtime) values (2356, 17841, 1, '没退学去创业！！', 0, '2012-06-08 10:52:58')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1318267 n bits 136 index `PRIMARY` of table `housesociety`.`users` trx id 0 3055951996 lock mode S locks rec but not gap waiting
Record lock, heap no 26 PHYSICAL RECORD: n_fields 33; compact format; info bits 0
0: len 4; hex 800045b1; asc E ;; 1: len 6; hex 0000b626206b; asc & k;; 2: len 7; hex 000000c0c10626; asc &;; 3: len 0; hex ; asc ;; 4: len 6; hex e58d83e5b886; asc ;; 5: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 6: SQL NULL; 7: len 1; hex 00; asc ;; 8: len 30; hex 557365722f5573657248656164496d6167652f31373834312f736d616c6c; asc User/UserHeadImage/17841/small;...(truncated); 9: len 30; hex 557365722f5573657248656164496d6167652f31373834312f3230313131; asc User/UserHeadImage/17841/20111;...(truncated); 10: SQL NULL; 11: SQL NULL; 12: len 20; hex 7169616e66616e407961686f6f2e636f6d2e636e; asc qianfan@yahoo.com.cn;; 13: len 4; hex 80000060; asc `;; 14: len 4; hex 80000060; asc `;; 15: len 4; hex 80000000; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: SQL NULL; 19: len 4; hex 80000000; asc ;; 20: len 8; hex 8000124a81e4e928; asc J (;; 21: len 4; hex 80000000; asc ;; 22: len 4; hex 80000000; asc ;; 23: len 4; hex 80000003; asc ;; 24: len 4; hex 80000000; asc ;; 25: SQL NULL; 26: len 1; hex 01; asc ;; 27: len 4; hex 80000000; asc ;; 28: SQL NULL; 29: SQL NULL; 30: len 8; hex 8000124a8231cd27; asc J 1 ';; 31: len 8; hex 696e7465726e616c; asc internal;; 32: len 8; hex 8000124cb1b2e308; asc L ;;

*** (2) TRANSACTION:
TRANSACTION 0 3055951979, ACTIVE 65 sec, OS thread id 15184 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 803, query id 9503531 localhost 127.0.0.1 root Updating
update housesociety.house_subjects set click_count=click_count+1 where id=2356
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1318267 n bits 136 index `PRIMARY` of table `housesociety`.`users` trx id 0 3055951979 lock_mode X locks rec but not gap
Record lock, heap no 26 PHYSICAL RECORD: n_fields 33; compact format; info bits 0
0: len 4; hex 800045b1; asc E ;; 1: len 6; hex 0000b626206b; asc & k;; 2: len 7; hex 000000c0c10626; asc &;; 3: len 0; hex ; asc ;; 4: len 6; hex e58d83e5b886; asc ;; 5: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 6: SQL NULL; 7: len 1; hex 00; asc ;; 8: len 30; hex 557365722f5573657248656164496d6167652f31373834312f736d616c6c; asc User/UserHeadImage/17841/small;...(truncated); 9: len 30; hex 557365722f5573657248656164496d6167652f31373834312f3230313131; asc User/UserHeadImage/17841/20111;...(truncated); 10: SQL NULL; 11: SQL NULL; 12: len 20; hex 7169616e66616e407961686f6f2e636f6d2e636e; asc qianfan@yahoo.com.cn;; 13: len 4; hex 80000060; asc `;; 14: len 4; hex 80000060; asc `;; 15: len 4; hex 80000000; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: SQL NULL; 19: len 4; hex 80000000; asc ;; 20: len 8; hex 8000124a81e4e928; asc J (;; 21: len 4; hex 80000000; asc ;; 22: len 4; hex 80000000; asc ;; 23: len 4; hex 80000003; asc ;; 24: len 4; hex 80000000; asc ;; 25: SQL NULL; 26: len 1; hex 01; asc ;; 27: len 4; hex 80000000; asc ;; 28: SQL NULL; 29: SQL NULL; 30: len 8; hex 8000124a8231cd27; asc J 1 ';; 31: len 8; hex 696e7465726e616c; asc internal;; 32: len 8; hex 8000124cb1b2e308; asc L ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1612378 n bits 80 index `PRIMARY` of table `housesociety`.`house_subjects` trx id 0 3055951979 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80000934; asc 4;; 1: len 6; hex 0000b626207c; asc & |;; 2: len 7; hex 000000c0c3039b; asc ;; 3: len 4; hex 80000128; asc (;; 4: len 4; hex 8000492d; asc I-;; 5: len 30; hex e38090e6b4bbe58aa8e5be81e99b86e38091e58f88e698afe4b880e5b9b4; asc ;...(truncated); 6: len 30; hex 3c70207374796c653d22746578742d616c69676e3a206c6566743b223e3c; asc <p style="text-align: left;"><;...(truncated); 7: len 1; hex 01; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 8; hex 8000124cb1b2dc19; asc L ;; 10: len 4; hex 80000003; asc ;; 11: len 1; hex 00; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 1; hex 00; asc ;; 14: len 4; hex 80000008; asc ;; 15: len 8; hex 8000124cb1b2e32a; asc L *;; 16: len 1; hex 01; asc ;; 17: len 30; hex 31383733332f32303132303630383130323230335f31383733332e6a7067; asc 18733/20120608102203_18733.jpg;; 18: len 30; hex e38090e6b4bbe58aa8e5be81e99b86e38091e6af95e4b89ae982a3e4ba9b; asc ;...(truncated);

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

感觉很奇怪，在插入和更新的操作中，会需要请求对关联表的锁吗？
（上面的日志貌似说：插入和更新的时候都需要请求锁住 users表？）

...全文