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!!!