title: MySQL Error: Lock wait timeout exceeded; try restarting transaction Query
body: <p>I found many people got the same error message from MySQL with the same error state=41000 amd same error code=1205.</p>
  
  <p>However most of them involved 2 transactions and a deadlock.</p>
  
  <p>Based on <code>SHOW ENGINE INNODB STATUS</code> output, there is only 1 transaction:</p>
  
  <pre>
  =====================================
  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 &lt; 0 8450719 undo n:o &lt; 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 &gt;= 0 8450726, sees &lt; 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
  ============================
  </pre>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>This problem doesn't exist if I establish my connection with auto commit set to true (I am using Apache Common DBCP 1.4).</p>