Why does this InnoDB deadlock occur on a simple UPDATE?

Question

We have a Symphony2 web application and use Doctrine as ORM.

Backend we use AWS RDS MySQL with the InnoDB engine.

SHOW VARIABLES LIKE "%version%";
innodb_version  5.6.22
protocol_version    10
slave_type_conversions  
version 5.6.22-log
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os  Linux

We can trigger an unexpected deadlock on the database server by clicking repeatedly on a submit button. This is part of the error message we get:

------------------------
LATEST DETECTED DEADLOCK
------------------------


2015-06-16 07:13:17 2aebdd723700
*** (1) TRANSACTION:
TRANSACTION 2009024, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 2
MySQL thread id 11125, OS thread handle 0x2aebdd6e2700, query id 1666530 172.31.26.135 db_name updating
UPDATE cmp_item SET title = 'Contract of employment 456', date_modification = '2015-06-16 09:14:37' WHERE id = 64297
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15706 page no 10780 n bits 80 index `PRIMARY` of table `db_name`.`cmp_item` trx id 2009024 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 102; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 2009010, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
12 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 4
MySQL thread id 11124, OS thread handle 0x2aebdd723700, query id 1666536 172.31.26.135 db_name updating
UPDATE cmp_item SET title = 'Contract of employment 456', date_modification = '2015-06-16 09:14:38' WHERE id = 64297
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15706 page no 10780 n bits 80 index `PRIMARY` of table `db_name`.`cmp_item` trx id 2009010 lock mode S locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 102; compact format; info bits 0

We don't understand why this deadlock would occur.

Why does it happen, and how can we avoid this?

Answer 1

A deadlock usually (always?) requires multiple statements to have been executed in each of two (or more) threads. Presumably your two Updates were each preceded by some other statements that locked various rows in various tables. Look through the entire BEGIN...COMMIT for each. Show them to us.