MySQL for Developers

How to claim rows in a database using MySQL

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.

Setting up the imports table

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.

Selecting available rows

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.

Updating claimed rows

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.

Checking claimed rows

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.

Conclusion

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.

About this lesson

Sometimes your application needs to claim rows and process them. There are several ways to do this, but in this video we'll look at one that doesn't require locking.

5:53
Closed captioned

Feedback or questions? Reach out to our team at education@planetscale.com.