Building a job queue with MySQL and InnoDB

MySQL & MariaDB Other MySQL® Questions
1

I’m trying to create a simple queuing system using mysql 5.0.45 and innodb. Everything works but I do get occasional failures due to lock timeouts. I have 10 client processes pulling jobs from the database concurrently. My main query to pull jobs out of the queue table is this:

SELECT * FROM queue_entries WHERE (qtype = ‘load’ and completed_at is null and (leased_at is null OR leased_at < ‘2008-03-14 08:51:29’)) LIMIT 1 FOR UPDATE

The FOR UPDATE is so the job record is immediately locked and the process can set the leased_at value so no other processes grab it. The question I have is regarding LIMIT. Is this the best way to minimize contention between multiple queue readers? Obviously if there are 20 outstanding jobs waiting in the queue, I don’t want that query to lock all 20 rows. Will this result in only a single row lock?

Any optimization advice would be appreciated.

mike

1 Like
2

Mike,

SELECT FOR UPDATE locks only those rows, which were selected. So if you have LIMIT 1 statement - then only one row will be locked.
Selected rows will be unlocked after end of transaction.

This is described MySQL 5.0 reference manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads. html

1 Like
3

Mike,

I’ve noticed a bit strange behaviour of next-key locking feature. Please see [URL]MySQL Bugs: #35472: row-based InnoDB locks prevent block update queries from other client for details. Maybe it’s not a bug, but anyway please keep in mind this behavior when working with innodb row-based locks.

1 Like
4

Hey, this is Jagran Josh SELECT FOR UPDATE only those rows, which were chosen. So that, if you have LIMIT 1 statement - then only one row will be locked.

1 Like