Repurpose MySQL as a mutex

Question

My application needs to perform an operation once every 10 minutes, but that operation is triggered many times more frequently. Occasionally it is triggered three or four times a second in different processes, and that's the main thing I want to avoid.

It's Django, and at first I used the django.core.cache feature, but I am still getting more than one worker performing the operation simultaneously.

I have Redis also, and know that WATCH and MULTI can be used to prevent race conditions, but the django-redis library doesn't support them.

This is my solution; please comment on how well (or not) it prevents multiple workers (processes) from performing the operation simultaneously:

1. Have a MySQL table with fields when and uuid and just a single row starting with both NULLs.
2. When a worker is told to start the operation, it quits early if when is less than 10 minutes ago.
3. Otherwise, generate a UUID and store it in the table, updating when to NOW() in the same query.
4. Then, select the row and quit early if uuid doesn't match the one just generated.
5. Otherwise continue with the operation

This relies on MySQL enforcing the order of reads and writes and enforcing that only one query access the row at once, which may be invalid assumptions.

Any alternative ways of keeping Celery processes from running a task simultaneously would be just as helpful.

Answer 1

So, my understanding is

• this operation is triggered by activity within the application
• the triggering activity may occur up to several times per second
• although the operation is triggered many times per second it should only execute once every ten minutes.

A one-column table would suffice:

create table Mutex (LastExecuted datetime NOT NULL);
insert Mutex(LastExecuted) values ('2010-01-01');

Initialise the column to an arbitrary value at least 10 minutes in the past. Initialising to NULL will introduce a special case for the first execution which will have to be coded for.

A worker can grab rights to perform work by trying to update this value

update Mutex
set LastExecuted = NOW()
where LastExecuted < DATE_SUB(NOW(), INTERVAL 10 MINUTE);

Capture the number of rows affected by this statement. If the statement affects zero rows then 10 minutes have not passed. If it affects 1 row, the current worker has acquired the mutex and should perform the operation. If it affects more than 1 row you have a problem with your application!

This will also provide protection from a worker grabbing the mutex then terminating. After 10 minutes another worker will be able to grab the right to do work.