One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.
The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).
Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):
------------------------ LATEST DETECTED DEADLOCK ------------------------ 111109 20:10:03 *** (1) TRANSACTION: TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1 MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating UPDATE parent SET age=age+1 WHERE id=1 *** (1) WAITING FOR THIS LOCK TO BE …[Read more]