# Building a job queue with MySQL and InnoDB

**URL:** <https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674>\
**Category:** Other MySQL® Questions\
**Created:** [March 14, 2008, 9:17am UTC](https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674 "2008-03-14T09:17:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mperham](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@mperham](https://forums.percona.com/u/mperham)\
**Post date:** [March 14, 2008, 9:17am UTC](https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674/1 "2008-03-14T09:17:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\
**Post date:** [March 15, 2008, 12:23am UTC](https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674/2 "2008-03-15T00:23:10Z")

</div>

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](http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads). html

---

<div class="post-metadata">

**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\
**Post date:** [March 25, 2008, 9:36pm UTC](https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674/3 "2008-03-25T21:36:47Z")

</div>

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](http://bugs.mysql.com/bug.php?id=35472%5B/URL%5D) for details. Maybe it’s not a bug, but anyway please keep in mind this behavior when working with innodb row-based locks.

---

<div class="post-metadata">

**Author:** ![govtjobs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/govtjobs/32/2532_2.png) [@govtjobs](https://forums.percona.com/u/govtjobs)\
**Post date:** [January 13, 2021, 5:09pm UTC](https://forums.percona.com/t/building-a-job-queue-with-mysql-and-innodb/674/4 "2021-01-13T17:09:43Z")

</div>

Hey, this is [Jagran Josh](https://www.indijobs4u.in) SELECT FOR UPDATE only those rows, which were chosen. So that, if you have LIMIT 1 statement - then only one row will be locked.