I found many people got the same error message from MySQL with the same error state=41000 amd same error code=1205.
我发现很多人从MySQL得到了相同的错误消息，错误状态相同= 4amd相同的错误代码=1205。
However most of them involved 2 transactions and a deadlock.
然而，大多数都涉及两个事务和一个死锁。
Based on SHOW ENGINE INNODB STATUS output, there is only 1 transaction:
基于SHOW ENGINE INNODB状态输出，只有1个事务:
===================================== 140715 4:42:37 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 21 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1630, signal count 1612 Mutex spin waits 0, rounds 10461, OS waits 205 RW-shared spins 2051, OS waits 1024; RW-excl spins 407, OS waits 380 ------------ TRANSACTIONS ------------ Trx id counter 0 8450727 Purge done for trx's n:o < 0 8450719 undo n:o < 0 0 History list length 10 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 8967, OS thread id 140114720237312 MySQL thread id 31142, query id 6206904 localhost root SHOW ENGINE INNODB STATUS ---TRANSACTION 0 8450588, not started, process no 8967, OS thread id 140114589861632 MySQL thread id 30909, query id 6153062 [my IP] [my username] ---TRANSACTION 0 8450484, not started, process no 8967, OS thread id 140114719172352 MySQL thread id 30899, query id 6152893 [my IP] [my username] ---TRANSACTION 0 8450702, not started, process no 8967, OS thread id 140114586134272 MySQL thread id 28747, query id 6206903 [my IP] [my username] ---TRANSACTION 0 8450726, ACTIVE 34 sec, process no 8967, OS thread id 140114484233984 inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31147, query id 6206832 [my IP] [my username] update INSERT INTO [My table] [the rest of the statement....] ------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 45066 n bits 432 index `PRIMARY` of table `[My DB]`.`[some table]` trx id 0 8450726 lock mode S locks rec but not gap waiting Record lock, heap no 359 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 000000000000bdca; asc ;; 1: len 6; hex 00000080f2a0; asc ;; 2: len 7; hex 80000000390110; asc 9 ;; 3: len 6; hex 73797374656d; asc system;; 4: len 1; hex 02; asc ;; 5: len 4; hex 53c4b11b; asc S ;; ------------------ ---TRANSACTION 0 8450725, ACTIVE 35 sec, process no 8967, OS thread id 140114588530432 MySQL thread id 31146, query id 6206822 [my IP] [my username] Trx read view will not see trx with id >= 0 8450726, sees < 0 8450720 ---TRANSACTION 0 8450720, ACTIVE 36 sec, process no 8967, OS thread id 140114590127872 393 lock struct(s), heap size 47088, 31495 row lock(s), undo log entries 229 MySQL thread id 31145, query id 6206823 [my IP] [my username] -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 10744 OS file reads, 25629 OS file writes, 18087 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 519 inserts, 519 merged recs, 76 merges Hash table size 17393, node heap has 12 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 5 2505544157 Log flushed up to 5 2505544157 Last checkpoint at 5 2505544157 0 pending log writes, 0 pending chkp writes 15981 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 21017578; in additional pool allocated 1048576 Dictionary memory allocated 466176 Buffer pool size 512 Free buffers 0 Database pages 498 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 12504, created 289, written 10838 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 8967, id 140114495788800, state: waiting for server activity Number of rows inserted 38311, updated 16430, deleted 36, read 6456822 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)
This problem doesn't exist if I establish my connection with auto commit set to true (I am using Apache Common DBCP 1.4).
如果我将我的连接与auto commit设置为true(我使用的是Apache Common DBCP 1.4)，那么这个问题就不存在了。
1 个解决方案
#1
0
I found the issue.
我发现这个问题。
The issue is that MySQL expects all queries within the same transaction to be done in the same TCP connection while each SQL execution through QueryRunner can potentially initiate a new TCP connection to the DB (though it can be controlled in couple ways).
问题是，MySQL希望相同事务中的所有查询都在相同的TCP连接中执行，而通过QueryRunner执行的每个SQL都可能初始化到DB的一个新的TCP连接(尽管它可以通过几种方式进行控制)。
#1
0
I found the issue.
我发现这个问题。
The issue is that MySQL expects all queries within the same transaction to be done in the same TCP connection while each SQL execution through QueryRunner can potentially initiate a new TCP connection to the DB (though it can be controlled in couple ways).
问题是，MySQL希望相同事务中的所有查询都在相同的TCP连接中执行，而通过QueryRunner执行的每个SQL都可能初始化到DB的一个新的TCP连接(尽管它可以通过几种方式进行控制)。