Announcement Module
No announcement yet.

Building a job queue with MySQL and InnoDB

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.


  • #2

    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: html


    • #3

      I've noticed a bit strange behaviour of next-key locking feature. Please see for details. Maybe it's not a bug, but anyway please keep in mind this behavior when working with innodb row-based locks.