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.