List:General Discussion« Previous MessageNext Message »
From:hiu Date:December 29 2011 4:03pm
Subject:puzzled issue for deadlock due to locking upgrade from LOCK_S to LOCK_X
View as plain text  
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?

Thread
puzzled issue for deadlock due to locking upgrade from LOCK_S to LOCK_Xhiu29 Dec