MySQL Queue – Best Way to Implement Concurrent Table-Based Queue

MySQLqueue

I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have multiple instances of the processing app, spread across several servers.

How can I ensure that each record is picked by one app only? Is there a way to flag/lock the record?

Right now, to avoid two or more picking up the same link, I'm allowing each instance only to pick up a certain set of records (based in the MOD of their ID), but this is not a transparent way to increase queue processing speed just by adding new instances.

Best Answer

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally close (those are set on memory). There is also not a good change notification system for MySQL- it has to be implemented as a polling. There are dozens of pieces of software more optimized for that task.

Having said that, I have seen successfully implement this kind of access if the performance/efficiency requirements are not very high. Many people cannot afford to introduce and maintain a complete separate piece of technology just for a small part of the business logic.

SELECT FOR UPDATE is what you are looking for- read serialization. While an UPDATE/DELETE will always lock the row during a running MYSQL transaction, you may want to avoid a large transaction while the process is going on, so:

START TRANSACTION;
SELECT * FROM your_table WHERE state != 'PROCESSING' 
  ORDER BY date_added ASC LIMIT 1 FOR UPDATE;
if (rows_selected = 0) { //finished processing the queue, abort}
else {
UPDATE your_table WHERE id = $row.id SET state = 'PROCESSING'
COMMIT;

// row is processed here, outside of the transaction, and it can take as much time as we want

// once we finish:
DELETE FROM your_table WHERE id = $row.id and state = 'PROCESSING' LIMIT 1;
}

MySQL will take care of locking all the concurrent selects except one when selecting rows. As this can lead to a lot of locked connections at the same time, keep the initial transaction as small as possible and try to process more that 1 row at a time.

Related Question