I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add a new 'SELECT' query to the same table as a part of another script , which is causing the 'INSERT' query to be locked. I used to 'DELETE' some of the old data to keep the table size from increasing beyond a limit. From MySQL documentation I saw that deletion can cause holes in data which will cause insert to lock.
But now the concurrent_insert is set to 'ALWAYS' so that it will work even if there are holes.
MySQL documentation says
Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.
But still the insert queries seems to be blocked with 'waiting for table level lock' status when SELECT is going on. After 'OPTIMIZE TABLES' is executed it will work without issues but locking happens again after holes are created. I couldn't find any reason for the locking. Nobody seems to have reported such an issue anywhere.
Any help is appreciated.
Best Answer
Face it, MyISAM survives only because it uses table locks for almost every kind of write. Even a
SELECTcan get caught up in the middle.The obscure exception has to do with multiple
INSERTthat are inserting into a table withAUTO_INCREMENTPRIMARY KEY(and, I suspect, no otherUNIQUEkeys). In that case, they can play around at the "end" of the table with less locking.Switch to InnoDB.
If your goal is to ingest lots of data from lots of sources, here's a technique: http://mysql.rjweb.org/doc.php/staging_table