In some situations, multiple workers may need to claim rows in a database, indicating that they are currently working on them and others should not interfere. This can be especially common with sets of unprocessed rows, such as imports or emails. While MySQL may not be the best queue driver for this type of scenario, it is certainly possible to use it to claim rows in a lightweight way.
Let's take a look at how to claim rows in a MySQL database and ensure that each row is only claimed once.
We'll start by creating an imports table that will hold unprocessed rows. This table will have columns for a file name, owner, available, started at, and finished at timestamps.
CREATE TABLE imports (
id INT NOT NULL AUTO_INCREMENT,
filename VARCHAR(255),
owner INT DEFAULT 0,
available TINYINT DEFAULT 1,
started_at TIMESTAMP,
finished_at TIMESTAMP,
PRIMARY KEY (id),
INDEX available_owner (available, owner)
);
Note that we have included a composite index on the available and owner columns in order to make our queries more efficient.
Our first step is to select any available rows for our workers to claim. We can do this by running the following query:
SELECT
*
FROM
imports
WHERE
available = 1
LIMIT 1;
This query retrieves the most recent unclaimed row from the table. However, it is important to note that this method is flawed, as it does not guarantee that another worker has not already claimed this row by the time our worker updates it.
Instead of selecting a row and then updating it, we can issue a blind update that claims the row as soon as it is found. We can do this using the following query:
UPDATE imports
SET
owner = 32, -- unique worker id
available = 0
WHERE
owner = 0
AND
available = 1
LIMIT 1;
In this example, we are claiming the row for worker #32 by setting the owner column to 32 and the available column to 0. On your application side, you would need to make sure each worker process has a unique id. We are also only claiming one row at a time with the LIMIT 1 clause.
Once a row has been claimed, we can ensure that only the owner of the row is able to modify or process it. We can do this by selecting rows where the owner is equal to the worker ID.
SELECT
*
FROM
imports
WHERE
owner = 32;
This query will only return rows where the owner is equal to 32, indicating that worker #32 is currently working on that row.
Although MySQL may not be the best option for larger queues, it is possible to use it to claim rows in a lightweight way. By claiming rows via an update, we can ensure that we are immune to race conditions and each row will only be processed once.
If you need a more robust queue driver, consider using Redis instead. Redis is specifically designed for queueing and can handle much larger workloads than MySQL. However, for smaller queues or lightweight processes, MySQL can be a viable option.