Inexplicable Deadlock (show InnoDB status)

[Jean Paul Ebejer]

Hi there at MySQL,

using MySQL 5.0.37-community-nt-log + InnoDB

I have a query regarding the following mysql 'show innodb status' result (latest detected deadlock fragment).

Can anyone explain to me why Transaction (2) is holding the lock to

space id 0 page no 13910 n bits 176 index `PRIMARY` of table `somedb/tbl_tablea` trx id 0 970362 lock_mode X locks gap before rec

and the SAME Transaction (2) is waiting for the lock on

space id 0 page no 13910 n bits 176 index `PRIMARY` of table `somedb/tbl_tablea` trx id 0 970362 lock_mode X locks gap before rec insert intention waiting

Why should Transaction (2) wait on a lock it owns ? Also why is a deadlock detected ?


----------------------------- Show InnoDB Status --------------------------------


*** (1) TRANSACTION:
TRANSACTION 0 970307, ACTIVE 17 sec, OS thread id 3032 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 17, query id 11198 localhost 127.0.0.1 userA update
insert into tbl_tablea (...) values (...)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13910 n bits 176 index `PRIMARY` of table `somedb/tbl_tablea` trx id 0 970307 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 25; hex 4d41584e554d4245525f5045524441595f5045524556454e54; asc MAXNUMBER_PERDAY_PEREVENT;; 1: len 19; hex 4144445f42414e4b5f434152447c4441494c59; asc ADD_PERSON|DAILY;; 2: len 6; hex 0000000ecb40; asc @;; 3: len 7; hex 0000003a09122a; asc : *;; 4: len 8; hex 80000113a9edaf76; asc v;; 5: len 1; hex 33; asc 3;; 6: len 5; hex 4649584544; asc FIXED;;

*** (2) TRANSACTION:
TRANSACTION 0 970362, ACTIVE 6 sec, OS thread id 4412 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1024
MySQL thread id 19, query id 11235 localhost 127.0.0.1 userA update
insert into tbl_tablea (...) values (...)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 13910 n bits 176 index `PRIMARY` of table `somedb/tbl_tablea` trx id 0 970362 lock_mode X locks gap before rec
Record lock, heap no 34 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 25; hex 4d41584e554d4245525f5045524441595f5045524556454e54; asc MAXNUMBER_PERDAY_PEREVENT;; 1: len 19; hex 4144445f42414e4b5f434152447c4441494c59; asc ADD_PERSON|DAILY;; 2: len 6; hex 0000000ecb40; asc @;; 3: len 7; hex 0000003a09122a; asc : *;; 4: len 8; hex 80000113a9edaf76; asc v;; 5: len 1; hex 33; asc 3;; 6: len 5; hex 4649584544; asc FIXED;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13910 n bits 176 index `PRIMARY` of table `somedb/tbl_tablea` trx id 0 970362 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 25; hex 4d41584e554d4245525f5045524441595f5045524556454e54; asc MAXNUMBER_PERDAY_PEREVENT;; 1: len 19; hex 4144445f42414e4b5f434152447c4441494c59; asc ADD_PERSON|DAILY;; 2: len 6; hex 0000000ecb40; asc @;; 3: len 7; hex 0000003a09122a; asc : *;; 4: len 8; hex 80000113a9edaf76; asc v;; 5: len 1; hex 33; asc 3;; 6: len 5; hex 4649584544; asc FIXED;;