title: Constant Lock Wait Timeout with MySQL InnoDB table
body: <p>I'm having horrible issues with lock wait timeouts with a MySQL InnoDB table created like so:</p>
  
  <pre><code>CREATE TABLE `TableX` (
    `colID` int(10) unsigned NOT NULL DEFAULT '0',
    `colFK` int(10) unsigned NOT NULL DEFAULT '0',
    `colX` smallint(5) unsigned NOT NULL DEFAULT '0',
    `colX` int(10) unsigned NOT NULL DEFAULT '0',
    `colX` smallint(5) unsigned NOT NULL DEFAULT '0',
    `colX` int(10) unsigned NOT NULL DEFAULT '0',
    `colX` smallint(5) unsigned NOT NULL DEFAULT '0',
    `colX` binary(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
    `colX` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
    `colX` smallint(5) unsigned NOT NULL DEFAULT '0',
    `colX` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
    `colX` smallint(5) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`colFK`),
    UNIQUE KEY `colID` (`colID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>The error is as follows: "[Err] 1205 - Lock wait timeout exceeded; try restarting transaction"</p>
  
  <p>This table never has more than 120 records in it but it gets hit hard with SELECT, UPDATE and DELETE statements. Very basic queries mostly filtering on the tableID but a join in some select statements to other tables with less than 2,000 records. I've tested all of the select queries and they take less than 100-200 ms to execute.</p>
  
  <p>InnoDB Status returns the following when the issue is happening:</p>
  
  <pre><code>---TRANSACTION 2605217, ACTIVE 1 sec inserting
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
   MySQL thread id 11826, OS thread handle 4104, query id 1940531 xxxx xxxxx xxxx update
   INSERT INTO TableX(cols) VALUES(values)
   ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 227 page no 3 n bits 168 index PRIMARY of table `TableX` trx id 2605217 lock mode S locks rec but not gap waiting
   Record lock, heap no 97 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
  </code></pre>
  
  <p>The general query log shows 4 selects and an insert happening within a second. The INSERT is the transaction failing with the lock wait timeout. So my question is, what can I do about this? I've tried reconfiguring the server, reinstalling MySQL, changing the transaction level..</p>
  
  <p>I apologize if the formatting is off I couldn't get the create table into a code block. Feel free to edit my post or ask for any more information that is required. Thanks!</p>
  
  <p>Edit: Adding general query log +-wait timeout</p>
  
  <pre><code>2017-05-02T02:06:26.443095Z 12195 Query SELECT SQL_BUFFER_RESULT * FROM TableX LEFT JOIN TableY USING (ColA) LEFT JOIN TableA USING (ColA) LEFT JOIN TableZ USING (ColA) LEFT JOIN TableH USING (ColA) LEFT JOIN TableI USING(ColA) WHERE UnindexedCol IS NOT NULL AND UnindexedColB &lt;= 0  ORDER BY UnindexedCol ASC
  2017-05-02T02:06:26.708769Z 11829 Query SELECT * FROM TableX LEFT JOIN TableA ON TableX.ColA = TableA.ColA WHERE UnindexedCol = 'text' LIMIT 1
  2017-05-02T02:06:27.021306Z 11826 Query SELECT * FROM TableX WHERE IDColA = 1000
  2017-05-02T02:06:27.068185Z 11826 Query INSERT INTO TableX(cols) VALUES(values)
  2017-05-02T02:06:27.224393Z 11829 Query SELECT colList, MIN(ColA) FROM TableX JOIN TableY USING (ColA) WHERE IF (IDColE &lt;&gt; 0, IDColE = (SELECT MAX(IDColE) FROM TableY WHERE IDColF = 22073), IDColF = 22073) GROUP BY UnIndexedColS, UnIndexedColT
  2017-05-02T02:06:27.224393Z  1697 Query Show engine innodb status
  2017-05-02T02:06:27.224393Z  1696 Query SELECT st.* FROM performance_schema.events_statements_current st JOIN performance_schema.threads thr ON thr.thread_id = st.thread_id WHERE thr.processlist_id = 1697
  2017-05-02T02:06:27.224393Z  1696 Query SELECT st.* FROM performance_schema.events_stages_history_long st WHERE st.nesting_event_id = 211
  2017-05-02T02:06:27.224393Z  1696 Query SELECT st.* FROM performance_schema.events_waits_history_long st WHERE st.nesting_event_id = 211
  2017-05-02T02:06:28.224501Z 11829 Query SELECT ColList FROM TableX WHERE UnIndexedCol = 2 OR UnIndexedCol = 2 GROUP BY ColList
  </code></pre>
  
  <p>Here is the C++ code being used to call the queries:</p>
  
  <pre><code>*  Executes a query.                                                    *
  
  int32 Sql_Query(Sql_t* self, const char* query, ...)
  {
      int32 res;
      va_list args;
  
      va_start(args, query);
      res = Sql_QueryV(self, query, args);
      va_end(args);
  
      return res;
  }
  
  *  Executes a query.                                                    *
  
  int32 Sql_QueryV(Sql_t* self, const char* query, va_list args)
  {
      if( self == NULL )
          return SQL_ERROR;
  
      Sql_FreeResult(self);
      StringBuf_Clear(&amp;self-&gt;buf);
      StringBuf_Vprintf(&amp;self-&gt;buf, query, args);
      if( mysql_real_query(&amp;self-&gt;handle, StringBuf_Value(&amp;self-&gt;buf), (uint32)StringBuf_Length(&amp;self-&gt;buf)) )
      {
          ShowSQL("DB error - %s\n", mysql_error(&amp;self-&gt;handle));
          ShowSQL("Query: %s\n", StringBuf_Value(&amp;self-&gt;buf));
          return SQL_ERROR;
      }
      self-&gt;result = mysql_store_result(&amp;self-&gt;handle);
      if( mysql_errno(&amp;self-&gt;handle) != 0 )
      {
          ShowSQL("DB error - %s\n", mysql_error(&amp;self-&gt;handle));
          ShowSQL("Query: %s\n", StringBuf_Value(&amp;self-&gt;buf));
          return SQL_ERROR;
      }
      return SQL_SUCCESS;
  }
  
  int     STDCALL mysql_real_query(MYSQL *mysql, const char *q,
                      unsigned int length);
  
  MYSQL_RES * STDCALL mysql_store_result(MYSQL *mysql);
  </code></pre>