CODEWITHSUNDEEP
mysqlconcurrencytransactionslocking
BenMorel
BenMorel

Reputation: 36604

How to lock rows in a queue?

I have a use case similar to How to lock row in mysql without blocking?, but unfortunately the solution is not what I'm after.

I have a queue table that contains rows to be processed by several, simultaneous workers. Say for example:

CREATE TABLE queue (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  done TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id)
);

Every worker will find a few rows to process:

SELECT * FROM queue WHERE done = 0 LIMIT 10;

The problem is that several concurrent workers will get the same rows, which is something I have to avoid at all costs.

I can use a transaction and SELECT ... FOR UPDATE, which will prevent other workers from getting the same rows, but will make them block until the first transaction is completed, effectively killing all concurrency.

Is there a built-in way in MySQL to select only rows that are not currently locked by another connection?

I know I could tackle the problem by adding an extra column that every process would update with a unique ID to assign a row to itself, but if there's a built-in way, that's much better.

Upvotes: 1

Views: 500

Answers (1)

Dean Clark
Dean Clark

Reputation: 3868

You're looking for a concept similar to SELECT ... FOR UPDATE SKIP LOCKED. Unfortunately, that's not an option in MySQL. It is, however, in recent versions of Oracle and PostgreSQL.

Upvotes: 2

Related Questions