We face the table level lock issues, almost every day on our test servers.
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<??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
These keep occurring for Inserts only, we have never faced the issue with Updates or deletes.
I have an Isolation level READ-COMMITTED, along with innodb_autoinc_lock_mode = 2
mysqltuner output for the same server
[--] 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)
Since this is a test server we are running the tests on a t2.small
1.3G buffer_pool in 2GB of RAM? This probably leads to lots of swapping, which is really bad for performance.
For 2GB of RAM and only 29M of data, let's set innodb_buffer_pool_size = 100M. That should enough for now, and safe for later (as the data grows).
(The recommendation of 70% or 80% only applies for machines with at least 4GB of RAM.)
Fix that. If the problem continues, then update the question with newer values, plus SHOW CREATE TABLE for the table(s) involved.