Hi Guys, I got a deadlock problem, and it puzzled me days. Hope some body could help with some explanation for the reason of deadlock, better if some extra advises. * * *DeadLock Info:* * *
- - - (1) TRANSACTION: TRANSACTION 13D947E32, ACTIVE 0 sec, process no 10928, OS thread id 1470925120 starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2343068, query id 874146900 xxx.yyy.zzz.183 feel Updating UPDATE feed_daily_sum_1124 SET gmt_modified = now() ,GOOD_SELLER = IFNULL(GOOD_SELLER,0)+1 WHERE USER_ID = 376169572 AND RATEDATE = '2011-11-28 00:00:00' - (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E32 lock_mode X locks rec but not gap waiting Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 80000000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 00000000443449df; asc D4I ;; - - - (2) TRANSACTION: TRANSACTION 13D947E3B, ACTIVE 0 sec, process no 10928, OS thread id 1538029888 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2309035, query id 874146901 xxx.yyy.zzz.56 feel Updating UPDATE feed_daily_sum_1124 SET gmt_modified = now() ,GOOD_SELLER = IFNULL(GOOD_SELLER,0)+1 WHERE USER_ID = 376169572 AND RATEDATE = '2011-11-28 00:00:00' - (2) HOLDS THE LOCK(S): RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E3B lock mode S Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 80000000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 00000000443449df; asc D4I ;; - - - (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E3B lock_mode X locks rec but not gap waiting Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 80000000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 00000000443449df; asc D4I ;; - - - WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ *Problem:* * * It's a typical deadlock problem, and the two SQL shown are the same. uk_daily_sum_userid is the index that contains user_id and ratedate. Seen from the innodb provided information, we could deduce that: T1 Waiting for a LOCK_X for Record R1 T2 Hods a LOCK_S of Record R1, and waiting for a LOCK_X for R1 As T2 needs a lock upgrade but that is a LOCK_X waiting before T2 requests LOCK_X for the same record, that caused the deadlock. OK, it's all ok for me to understand the deadlock from innodb deadlock info. But my question is: WHY LOCK_S is existed in T2? Let's me show you the SQL in Transaction. T1 and T2 are the same logic SQL assembles as: sql1: INSERT INTO feed_receive values (xxx, yyy); sql2: UPDATE feed_daily_sum sql3: UPDATE feed_all_sum sql4: commit There is no FK constrains in the three tables, all are depended tables!!! WHY LOCK_S should occur in this case? For my understanding of LOCK_S, FK contains (update child but parent related record should have LOCK_S), or SELECT xxx LOCK in shared mode. Any idea?