0

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.

Improve this question

1 Answer 1

Reset to default
1

Face it, MyISAM survives only because it uses table locks for almost every kind of write. Even a SELECT can get caught up in the middle.

The obscure exception has to do with multiple INSERT that are inserting into a table with AUTO_INCREMENT PRIMARY KEY (and, I suspect, no other UNIQUE keys). 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

Improve this answer
3
  • Does this mean that the concurrent_insert - ALWAYS may not work at times if multiple INSERTs are done during the SELECT query execution?
    – atm007
    Apr 1, 2021 at 3:00
  • @atm007 - (I don't know about "always".) Here is a typical case: (1) INSERT starts; (2) SELECT starts but is promptly blocked. (3) another INSERT is also blocked. Or: (1) SELECT is busy; (2) INSERT starts, but blocked by it; (3) everything blocked.
    – Rick James
    Apr 1, 2021 at 17:34
  • One insert query blocking another is expected regardless of the 'concurrent_insert 'value. But the issue I'm facing is that (1) SELECT starts (2) Two INSERT queries are executed at the same time. (3) Both the INSERT queries are blocked until SELECT is completed.
    – atm007
    Apr 6, 2021 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.