Deadlock analysis problems

[petor]

Hi,

I've created a PHP application using PDO transactions, which for some reason deadlocks. 

If I query MySQL with 'SHOW ENGINE INNODB STATUS' I receive the following:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-07-04 11:09:40 12c4
*** (1) TRANSACTION:
TRANSACTION 203531, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 1660, OS thread handle 0x2614, query id 3618 localhost ::1 petor statistics
DELETE answer FROM answer
JOIN question q 
ON answer.id_question = q.id_question 
JOIN product prod 
ON q.id_product = prod.id_product 
WHERE answer.name = 'answer3' 
AND prod.name = 'test' 
AND q.name = 'question3'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36 page no 5 n bits 440 index `value` of table `test`.`answer` trx id 203531 lock_mode X waiting
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 7; hex 616e7377657233; asc answer3;;
1: len 4; hex 800000f3; asc     ;;
2: len 4; hex 800000c5; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 203528, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 1658, OS thread handle 0x12c4, query id 3615 localhost ::1 petor statistics
DELETE answer FROM answer
JOIN question q 
ON answer.id_question = q.id_question 
JOIN product prod 
ON q.id_product = prod.id_product 
WHERE answer.name = 'answer3' 
AND prod.name = 'test' 
AND q.name = 'question3'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 36 page no 5 n bits 440 index `value` of table `test`.`answer` trx id 203528 lock_mode X locks rec but not gap
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 7; hex 616e7377657233; asc answer3;;
1: len 4; hex 800000f3; asc     ;;
2: len 4; hex 800000c5; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36 page no 5 n bits 440 index `value` of table `test`.`answer` trx id 203528 lock_mode X waiting
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 7; hex 616e7377657233; asc answer3;;
1: len 4; hex 800000f3; asc     ;;
2: len 4; hex 800000c5; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

I have some problems analysing this data though.

From what I see it looks like transaction 203528 (the second) is waiting for the same lock the be granted that he is holding, which does not seem like a situation that could happen...

Besides that, the two queries described in this log are the same (well they have the same query text), which is also something suspicious (as I did not intend two equal queries to be executed subsequently)

 

Can somebody explain this message to me?

Are my assumptions correct, or am I missing something here?

 

 

Thanks in advance!!!

Edited July 4, 2014 by petor