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

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

以下是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

*** WE ROLL BACK TRANSACTION (2)

------------

TRANSACTIONS

------------

感觉很奇怪，在插入和更新的操作中，会需要请求对关联表的锁吗？

(上面的日志貌似说：插入 和更新的时候都需要请求锁住 users表？)

问题补充：该问题是在hibernate中遇到的。

2012年6月11日 14:03