title: innodb table level lock
body: <p>We face the table level lock issues, almost every day on our test servers. </p>
  
  <pre><code>TRANSACTION 0, not started
  mysql tables in use 97, locked 97
  MySQL thread id 429, OS thread handle 0x2aff6ff59700, query id 24900 ec2-*-*-*-*.compute-1.amazonaws.com *.*.*.* sminq cleaning up
  ---TRANSACTION 10631403, not started
  MySQL thread id 321, OS thread handle 0x2aff7b359700, query id 24901 115.112.140.139 sminq init
  show engine innodb status
  ---TRANSACTION 10632661, not started
  MySQL thread id 13, OS thread handle 0x2aff4e39a700, query id 24817 localhost 127.0.0.1 rdsadmin cleaning up
  ---TRANSACTION 10632664, not started
  MySQL thread id 6, OS thread handle 0x2aff396c5700, query id 24873 ec2-*-*-*-*.ap-southeast-1.compute.amazonaws.com *.*.*.* sminq cleaning up
  ---TRANSACTION 10632655, not started
  MySQL thread id 7, OS thread handle 0x2aff39706700, query id 24783 ec2-*-*-*-*.ap-southeast-1.compute.amazonaws.com *.*.*.* sminq cleaning up
  ---TRANSACTION 10632652, not started
  MySQL thread id 3, OS thread handle 0x2aff37d28700, query id 24745 ec2-*-*-*-*.ap-southeast-1.compute.amazonaws.com *.*.*.* sminq cleaning up
  ---TRANSACTION 10627075, not started
  MySQL thread id 1, OS thread handle 0x2aff37ca6700, query id 0 Waiting for background binlog tasks
  ---TRANSACTION 10632663, ACTIVE 7 sec
  mysql tables in use 1, locked 1
  MySQL thread id 431, OS thread handle 0x2aff37daa700, query id 24863  172.31.3.120 sminq Waiting for table level lock
  insert into `sminq`.`Queue_token` (`token_queue_id`,    `total_process_time`, `token_user`, `created_on`, `join_date`, `join_time`, `app_type`, `token_user_group`, `uuid`) values (13, 10, 87, '2016-07-21 04:47:04.157000', '2016-07-21 10:17:04', '10:10:00', 1, NULL, 'D&lt;??BY??7?gk?Uo')
  Trx #rec lock waits 0 #table lock waits 0
  Trx total rec lock wait time 0 SEC
  Trx total table lock wait time 0 SEC
  ---TRANSACTION 10632646, ACTIVE 45 sec
  </code></pre>
  
  <p>These keep occurring for Inserts only, we have never faced the issue with Updates or deletes.</p>
  
  <p>I have an Isolation level READ-COMMITTED, along with innodb_autoinc_lock_mode = 2</p>
  
  <p>mysqltuner output for the same server</p>
  
  <pre><code>[--] Up for: 2h 11m 55s (25K q [3.230 qps], 478 conn, TX: 3M, RX: 1M)
  [--] Reads / Writes: 82% / 18%
  [--] Binary logging is enabled (GTID MODE: OFF)
  [--] Total buffers: 1.5G global + 17.0M per thread (100 max threads)
  [!!] Maximum reached memory usage: 3.0G (152.35% of installed RAM)
  [!!] Maximum possible memory usage: 3.1G (156.50% of installed RAM)
  [OK] Slow queries: 0% (0/25K)
  [!!] Highest connection usage: 95%  (95/100)
  [OK] Aborted connections: 0.00%  (0/478)
  [!!] Query cache is disabled
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
  [OK] Temporary tables created on disk: 24% (424 on disk / 1K total)
  [OK] Thread cache hit rate: 80% (95 created / 478 connections)
  [OK] Table cache hit rate: 129% (291 open / 224 opened)
  [OK] Open file limit used: 0% (64/65K)
  [OK] Table locks acquired immediately: 99% (6K immediate / 6K locks)
  [OK] Binlog cache memory access: 100.00% ( 1618 Memory / 1618 Total)
  
  -------- MyISAM Metrics -----------------------------------------------------
  [!!] Key buffer used: 18.5% (1M used / 8M cache)
  [OK] Key buffer size / total MyISAM indexes: 8.0M/2.4M
  [!!] Read Key buffer hit rate: 82.2% (90 cached / 16 reads)
  
  -------- InnoDB Metrics -----------------------------------------------------
  [--] InnoDB is enabled.
  [OK] InnoDB buffer pool / data size: 1.3G/29.0M
  [!!] InnoDB buffer pool instances: 8
  [!!] InnoDB Used buffer: 1.32% (1139 used/ 86584 total)
  [OK] InnoDB Read buffer efficiency: 99.86% (713109 hits/ 714137 total)
  [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
  [OK] InnoDB log waits: 0.00% (0 waits / 4915 writes)
  </code></pre>
  
  <p>Since this is a test server we are running the tests on a t2.small</p>