java - MySQL错误: Lock wait timeout exceeded; try restarting transaction Query

Question

我发现很多人从 MySQL 收到相同的错误消息，具有相同的错误状态 = 41000 amd 相同的错误代码 = 1205。

然而，其中大多数涉及 2 个事务和一个死锁。

根据SHOW ENGINE INNODB STATUS输出，只有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 1612Mutex spin waits 0, rounds 10461, OS waits 205RW-shared spins 2051, OS waits 1024; RW-excl spins 407, OS waits 380------------TRANSACTIONS------------Trx id counter 0 8450727Purge done for trx's n:o < 0 8450719 undo n:o < 0 0History list length 10LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 0, not started, process no 8967, OS thread id 140114720237312MySQL thread id 31142, query id 6206904 localhost rootSHOW ENGINE INNODB STATUS---TRANSACTION 0 8450588, not started, process no 8967, OS thread id 140114589861632MySQL thread id 30909, query id 6153062 [my IP] [my username]---TRANSACTION 0 8450484, not started, process no 8967, OS thread id 140114719172352MySQL thread id 30899, query id 6152893 [my IP] [my username]---TRANSACTION 0 8450702, not started, process no 8967, OS thread id 140114586134272MySQL thread id 28747, query id 6206903 [my IP] [my username]---TRANSACTION 0 8450726, ACTIVE 34 sec, process no 8967, OS thread id 140114484233984 insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1216, 1 row lock(s)MySQL thread id 31147, query id 6206832 [my IP] [my username] updateINSERT 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 waitingRecord 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 140114588530432MySQL 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 140114590127872393 lock struct(s), heap size 47088, 31495 row lock(s), undo log entries 229MySQL 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: 0Pending flushes (fsync) log: 0; buffer pool: 010744 OS file reads, 25629 OS file writes, 18087 OS fsyncs0.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 mergesHash 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 2505544157Log flushed up to   5 2505544157Last checkpoint at  5 25055441570 pending log writes, 0 pending chkp writes15981 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 21017578; in additional pool allocated 1048576Dictionary memory allocated 466176Buffer pool size   512Free buffers       0Database pages     498Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 12504, created 289, written 108380.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue2 read views open inside InnoDBMain thread process no. 8967, id 140114495788800, state: waiting for server activityNumber of rows inserted 38311, updated 16430, deleted 36, read 64568220.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)

如果我在将自动提交设置为 true 的情况下建立连接(我使用的是 Apache Common DBCP 1.4)，则不存在此问题。

Answer 1

我发现了这个问题。

问题在于，MySQL 期望同一事务中的所有查询都在同一个 TCP 连接中完成，而通过 QueryRunner 执行的每个 SQL 都可能启动到数据库的新 TCP 连接(尽管可以通过多种方式进行控制)。