两个事务似乎互相阻塞，但没有一个事务在等待

Question

我有两个事务在 mysql 数据库中运行，两个事务似乎都没有取得任何进展。两者都持有一些锁，但据我了解，它们并不冲突。 两个事务都处于“ACTIVE”状态（我预计其中一个事务如果被另一个事务阻塞，则处于等待状态）。

我在下面附上了 innodb 状态输出（包括锁定监视器信息）的“TRANSACTIONS”和“ROW OPERATIONS”部分。

谁能告诉我为什么这些交易会相互影响？

我没有简单的方法来重现此问题，因为查询是由 EJB 应用程序发出的，因此我现在尝试大致了解此类问题通常是由什么引起的。

=====================================
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

是的，在 mysql

示例中：-如果您在 mysql 服务器上运行 4 个命令

，那么当第二个命令发送到服务器时，他等待第一个和第三个命令的响应，等待第二个命令响应，然后服务器接受它们。

如果上一个命令的响应没有从服务器发出，那么他就不能接受下一个命令。

那么您需要确保一次仅运行一个命令或事务。

您还可以增加连接字符串中命令执行的超时时间。