Why is this a deadlock (MySQL using InnoDB)

Question

SHOW INNODB ENGINE STATUS shows me this and calls it deadlock:

LATEST DETECTED DEADLOCK

100923 22:29:21
* (1) TRANSACTION:
TRANSACTION 0 5335752, ACTIVE 0 sec, OS thread id 7992 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 3
MySQL thread id 26, query id 14422 localhost 127.0.0.1 root update
insert into history_messagearguments (history_id, messageArguments_ORDER, messageArguments) values (69, 1, '1')
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335752 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;;

* (2) TRANSACTION:
TRANSACTION 0 5335748, ACTIVE 0 sec, OS thread id 6988 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 2
MySQL thread id 25, query id 14424 localhost 127.0.0.1 root update
insert into history_messagearguments (history_id, messageArguments_ORDER, messageArguments) values (71, 0, '0')
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335748 lock_mode X locks gap before rec
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;;

* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335748 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;;

* WE ROLL BACK TRANSACTION (2)

I cannot see why this is a deadlock. There is no line "WAITING FOR THIS LOCK TO BE GRANTED:" for transaction 1. If transaction 1 does not hold any locks, it cannot block anyone, so it cannot be part of a deadlock.

More theoretically, I cannot see condition 4 being satisfied as written here: http://en.wikipedia.org/wiki/Deadlock#Necessary_conditions

The way I see it, MySQL should let transaction 2 continue. After it completes, transaction 1 can then go on.

Here (http://stackoverflow.com/questions/1851528/mysql-deadlock-explanation-needed) BrainCore wrote:

Transaction 2 gets "stuck" behind Transaction 1's request, a la FIFO queue.

Can anyone point me to MySQL documentation to confirm this? I find it hard to believe that transactions are strictly executed in the order they arrive.

Before all the questions about the table layout, isolation level etc. show up: I do not ask for help right now about resolving the deadlock. I ask how to read the SHOW ENGINE STATUS OUTPUT.

Answer 1

Read transactions 1 and 2: Read from end to begining:

• what type and why: lock_mode X locks gap before rec insert intention waiting
• what is blocked:RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335748
• WAITING FOR THIS LOCK TO BE GRANTED

InnoDB has problem with concurrent writes, especially if You insert data in the end of innodb table.