multiprocessing - MySQL transactions deadlocking, but SHOW ENGINE INNODB STATUS does not show a true deadlock -

i having trouble 2 processes modifying same table in mysql database. causes deadlock , 1 or other of processes gets 'deadlock found when trying lock; try restarting transaction' error.

i found few answers why occurs on stackoverflow, , has got me of way solving issue. (just retry transaction). hoping better solution started investigating show engine innodb status.

i have been confused output of status command. can see not show true deadlock. first transaction waiting row locked buy second transaction, , first transaction holds no other locks. second transaction holds 4 locks, 1 being lock required first transaction, , waiting 5th lock. there no mention of 5th lock being held other transaction.

the output relating deadlock is:

------------------------ latest detected deadlock ------------------------ 130514  8:54:12 *** (1) transaction: transaction 0 487333931, active 0 sec, process no 1007, os thread id 2990889792 fetching rows mysql tables in use 1, locked 1 lock wait 3 lock struct(s), heap size 320, 24 row lock(s), undo log entries 3 mysql thread id 774102, query id 166772615 localhost 127.0.0.1 nesie updating delete devicestatus serialno=1234567 , subdevice=1 , (parameter='band' or parameter='arfcn' or parameter='txpower' or parameter='lac' or parameter='cellid' or parameter='channel' or parameter='rxreversepower' or parameter='reversesnr' or parameter='reversegmp' or parameter='reversebepm' or parameter='mobileheldon' or parameter='mobileheldband' or parameter='mobiletxpower' or parameter='mobilecommandedpower' or parameter='rxpathloss' or parameter='holdstate' or parameter='band' or parameter='channel' or parameter='arfcn' or parameter='rxforwardpower' or parameter='forwardsnr' or parameter='forwardgmp' or parameter='forwardbepm' or parameter='lac' or parameter='cellid') *** (1) waiting lock granted: record locks space id 1267 page no 3 n bits 96 index `primary` of table `nesie`.`devicestatus` trx id 0 487333931 lock_mode x waiting record lock, heap no 20 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 10; hex 6d6f6e69746f72696e67; asc monitoring;; 3: len 6; hex 00001d0c202a; asc      *;; 4: len 7; hex 00000000342dbd; asc     4- ;; 5: len 4; hex 80000000; asc     ;;  *** (2) transaction: transaction 0 487333930, active 0 sec, process no 1007, os thread id 3063302976 inserting, thread declared inside innodb 488 mysql tables in use 1, locked 1 5 lock struct(s), heap size 320, 6 row lock(s), undo log entries 4 mysql thread id 774099, query id 166772616 localhost nesie update replace devicestatus values (1381511,1,'scanning',1),(1381511,1,'monitoring',0),(1381511,1,'transmitting',0),(1381511,1,'power',-84),(1381511,1,'band',1),(1381511,1,'uarfcn',10661),(1381511,1,'scramblingcode',377) *** (2) holds lock(s): record locks space id 1267 page no 3 n bits 96 index `primary` of table `nesie`.`devicestatus` trx id 0 487333930 lock_mode x locks rec not gap record lock, heap no 20 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 10; hex 6d6f6e69746f72696e67; asc monitoring;; 3: len 6; hex 00001d0c202a; asc      *;; 4: len 7; hex 00000000342dbd; asc     4- ;; 5: len 4; hex 80000000; asc     ;;  record lock, heap no 21 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 5; hex 706f776572; asc power;; 3: len 6; hex 00001d0c202a; asc      *;; 4: len 7; hex 00000000342e11; asc     4. ;; 5: len 4; hex 7fffffac; asc     ;;  record lock, heap no 22 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 8; hex 7363616e6e696e67; asc scanning;; 3: len 6; hex 00001d0c202a; asc      *;; 4: len 7; hex 00000000342d96; asc     4- ;; 5: len 4; hex 80000001; asc     ;;  record lock, heap no 24 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 12; hex 7472616e736d697474696e67; asc transmitting;; 3: len 6; hex 00001d0c202a; asc      *;; 4: len 7; hex 00000000342de6; asc     4- ;; 5: len 4; hex 80000000; asc     ;;  *** (2) waiting lock granted: record locks space id 1267 page no 3 n bits 96 index `primary` of table `nesie`.`devicestatus` trx id 0 487333930 lock_mode x locks rec not gap waiting record lock, heap no 17 physical record: n_fields 6; compact format; info bits 0  0: len 4; hex 80151487; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 4; hex 62616e64; asc band;; 3: len 6; hex 00001d0c19ff; asc       ;; 4: len 7; hex 000000003428a7; asc     4( ;; 5: len 4; hex 80000001; asc     ;;  *** roll transaction (1) 

my questions are:

why flagged deadlock, transaction 1 can queued until transaction 2 completes holds no locks required transaction 2?

does 1 know if normal behaviour mysql or bug?

thanks,

simon.

you may use new 5.6 variable: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks

this way you'll able log deadlocks, , maybe find transaction initiating deadlock.