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?
