Building a job queue with MySQL and InnoDB

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

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

Mike,

I’ve noticed a bit strange behaviour of next-key locking feature. Please see http://bugs.mysql.com/bug.php?id=35472 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

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