Two transactions seem to block each other but none of them is waiting

Question

I have two transactions running in a mysql database, neither of which seems to make any progress. Both hold some locks but as far as I understand they don't conflict. Both transactions are in state "ACTIVE" (I would have expected that one of them be in a waiting state if it were blocked by the other).

I've attached the "TRANSACTIONS" and "ROW OPERATIONS" part of the innodb status output (including lock monitor info) below.

Can anyone give me a hint as to why these transactions are influencing each other?

I don't have an easy way to reproduce this because the queries are issued by an EJB application, so I'm now trying to generally understand what this kind of problem typically arises from.

=====================================
100823 16:04:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 25808, signal count 25608
Mutex spin waits 0, rounds 108338, OS waits 1400
RW-shared spins 18372, OS waits 17512; RW-excl spins 7055, OS waits 6782
------------
TRANSACTIONS
------------
Trx id counter 0 39456296
Purge done for trx's n:o < 0 39455079 undo n:o < 0 0
History list length 2
Total number of lock structs in row lock hash table 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 39456295, not started, process no 4183, OS thread id 140415939369296
MySQL thread id 21, query id 90163 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, process no 4183, OS thread id 140415938767184
MySQL thread id 15, query id 89364 cl1010wo2431.inter.rsag.site 172.27.10.38 root
---TRANSACTION 0 0, not started, process no 4183, OS thread id 140415939168592
MySQL thread id 8, query id 90177 localhost root
---TRANSACTION 0 39456204, ACTIVE 94 sec, process no 4183, OS thread id 140415938967888
3 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 23, query id 89811 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 39456205, sees < 0 39454738
TABLE LOCK table `server_database/error_data` trx id 0 39456204 lock mode IX
TABLE LOCK table `server_database/error_status` trx id 0 39456204 lock mode IS
RECORD LOCKS space id 2754 page no 3 n bits 72 index `PRIMARY` of table `server_database/error_status` trx id 0 39456204 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 800001; asc    ;; 1: len 6; hex 000000d7287f; asc     ( ;; 2: len 7; hex 00000030c816ce; asc    0   ;; 3: len 9; hex 7374617475734e6577; asc statusNew;;
Aug 23 16:04:53 sv1010vm0007 mysqld[4184]:
---TRANSACTION 0 39454738, ACTIVE 1594 sec, process no 4183, OS thread id 140416557721936
6 lock struct(s), heap size 1216, undo log entries 5
MySQL thread id 24, query id 83802 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 39454739, sees < 0 39454739
TABLE LOCK table `server_database/error_data` trx id 0 39454738 lock mode IX
TABLE LOCK table `server_database/error_status` trx id 0 39454738 lock mode IS
RECORD LOCKS space id 2754 page no 3 n bits 72 index `PRIMARY` of table `server_database/error_status` trx id 0 39454738 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 800001; asc    ;; 1: len 6; hex 000000d7287f; asc     ( ;; 2: len 7; hex 00000030c816ce; asc    0   ;; 3: len 9; hex 7374617475734e6577; asc statusNew;;
Aug 23 16:04:53 sv1010vm0007 mysqld[4184]:
TABLE LOCK table `server_database/wtu_counter_daily` trx id 0 39454738 lock mode IX
TABLE LOCK table `server_database/wtu` trx id 0 39454738 lock mode IS
RECORD LOCKS space id 4349 page no 3 n bits 128 index `PRIMARY` of table `server_database/wtu` trx id 0 39454738 lock mode S locks rec but not gap
Record lock, heap no 62 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
 0: len 3; hex 802715; asc  ' ;; 1: len 6; hex 00000258fa7b; asc    X {;; 2: len 7; hex 000000003401e8; asc     4  ;; 3: SQL NULL; 4: len 3; hex 800005; asc    ;; 5: len 12; hex 31302e37372e33322e313830; asc 10.77.32.180;; 6: len 4; hex 00000040; asc    @;; 7: SQL NULL; 8: SQL NULL; 9: SQL NULL; 10: len 3; hex 8004d2; asc    ;; 11: len 1; hex 37; asc 7;; 12: len 0; hex ; asc ;; 13: len 1; hex 80; asc  ;; 14: len 1; hex 80; asc  ;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 35; asc 5;; 18: SQL NULL; 19: len 12; hex 77656273746172742e6a6e6c; asc webstart.jnl;; 20: SQL NULL; 21: len 1; hex 80; asc  ;; 22: len 1; hex 81; asc  ;; 23: len 1; hex 81; asc  ;; 24: len 1; hex 80; asc  ;; 25: len 2; hex 8000; asc   ;; 26: len 13; hex 3139322e3136382e302e313830; asc 192.168.0.180;; 27: len 1; hex 81; asc  ;; 28: SQL NULL; 29: len 4; hex 80000000; asc     ;; 30: len 1; hex 81; asc  ;; 31: SQL NULL;

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 4183, id 140415947962704, state: waiting for server activity
Number of rows inserted 64, updated 450, deleted 0, read 89832081
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

yeah in mysql

example:- if you run 4 command on mysql server

then when 2nd command goes to server then he wait for response of first and third wait for 2nd command respond out then server accept them.

if last command's response not goes out from server then he can't accept next command.

then you need to sure that only one command or transaction run on a one time.

you can also increase your time-out time of your command execution in connectionstring.