Databases Reference
In-Depth Information
In general, it's a good idea to avoid
SELECT FOR UPDATE
. And not just for a queue table—
it's a good idea to avoid it for any purpose. There is almost always a better way to
achieve your desired purpose. In the case of a queue, you can use a simple
UPDATE
to
claim rows, and then check whether you claimed anything. Here's how. Let's start with
the schema:
CREATE TABLE unsent_emails (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- columns for the message, from, to, subject, etc.
status ENUM('unsent', 'claimed', 'sent'),
owner INT UNSIGNED NOT NULL DEFAULT 0,
ts TIMESTAMP,
KEY (owner, status, ts)
);
The
owner
column is used to store the connection ID of the worker process that owns
the row. This is the same value returned by the
CONNECTION_ID()
function in MySQL.
If it's
0
, then the row is unclaimed.
We frequently see a technique like the following to claim 10 rows:
BEGIN;
SELECT id FROM unsent_emails
WHERE owner = 0 AND status = 'unsent'
LIMIT 10 FOR UPDATE;
-- result: 123, 456, 789
UPDATE unsent_emails
SET status = 'claimed', owner = CONNECTION_ID()
WHERE id IN(123, 456, 789);
COMMIT;
That will use the first two columns of the index, so in theory it looks rather efficient.
The problem is that between the two queries, the application has some “think time,”
and that causes the locks on the rows to block other clients who are running the same
queries. All of the queries will use the same index, so they'll begin scanning right at the
front of the index and will probably block instantly.
It's much more efficient to perform the queries as follows:
SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
SET status = 'claimed', owner = CONNECTION_ID()
WHERE owner = 0 AND status = 'unsent'
LIMIT 10;
SET AUTOCOMMIT = 0;
SELECT id FROM unsent_emails
WHERE owner = CONNECTION_ID() AND status = 'claimed';
-- result: 123, 456, 789
You don't even have to run the
SELECT
query to check for rows that you claimed. The
client protocol will tell you how many rows were updated, so you know whether there
were unsent rows to claim.
