InnoDB Concurrent writes being ignored Sorry if this isn't the right place, this is my first time posting anything here. Anyway, I know I'm not supposed to but I can think of no other way.

I need to use a small MySQL table (70 rows x 6 columns) as a queue. 

I'm writing a python application that requires a work queue to be shared between process windows (not sure what the proper name for them is). 

Each job is repeatable, each use must be recorded and cleared at regular intervals (so each job is "weighted" so usage is evenly and fairly distributed). I attempted to base it on another work queue where each job is NOT repeatable, but it seems that multiple writes to the database (up to 19 at once per 12 seconds) are not properly being counted up. 

Is there an alternative to doing something like this? 

Perhaps some kind of cache sitting between python and MySQL that would convert many single "job + 1" updates into a singular "job + 19"? 

I assumed being on a shiny new NVMe drive and a more than sufficient buffer_pool_size would make it plenty fast enough to handle that, but instead of counting up properly, over the course of 60 seconds it may reach 9, instead of 100+ where it should be.
"Don't queue it, just do it".  -- This is my advice on using queuing mechanisms in MySQL.  The effort to enqueue and dequeue small tasks is often more than simply doing the task.  However there are exceptions...

Is this a correct understanding of your "queue"?  You have exactly 70 tasks and they need to be done at times based on various external criteria.  That is, it is not really a typical queue, which can be thought of as "first in, first out".  However, sometimes you may have several of the 70 ready to go, but not enough resources to do all at once. ??

In that case, let's call it a "task status" table.  Dozens of updates per second feed status into the table.  Worker(s) issue a SELECT to see what to do now.  If the computation is too complex to do in a WHERE...ORDER BY...LIMIT 1, then simply readk all 70 and make the decision in a 'real' programming language.  (Yes, I have implemented such.)  Keep in mind that the focus of a database table is to hold raw data.  If you can process the data in a SELECT, that is just a bonus.

"Transactions"...  Keep in mind that transactions in InnoDB are not really designed to last more than a few seconds.  So, if the tasks take longer than that, you need to 'assign' a task to a 'worker'.  Better yet, have the 'worker' assign the task to itself.  Here's one efficient way:

-- with autocommit=1; that is, no need for transactional semantics
UPDATE tasks
    SET worker = $me
    WHERE worker IS NULL
    ORDER BY ...
    LIMIT 1;
if Rows_affected() &gt; 0, then I got a task, else none to get.
SELECT task_id FROM tasks
    WHERE worker = $me;   -- find out which task I got.


When finished:

UPDATE tasks
    SET worker = NULL
    WHERE worker = $me;


Beneficial:

INDEX(worker)


Sorry if I have rambled off in the wrong direction; perhaps there are useful clues anyway.