We have a query, which is trying to update a row, placing a user at the end of a queue.

To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+1

Due to the mysql restrictions on updating the same table you are reading from, we've designed this using a join

It's important this query is atomic, because it could be hit under heavy concurrency and we don't want the position to be duplicated

Here's the query:

UPDATE ListingQueue LQ
            JOIN (
                    (MAX(initialPosition) + 1) AS maxpos,
                    ListingQueue.listingid = 945877
                    FOR UPDATE
            ) AS LQ2 ON LQ2.listingid = LQ.listingid
            SET LQ.initialPosition = LQ2.maxpos,
                LQ.stage = 'waitingroom',
                LQ.stageLastUpdate = NOW()
                LQ.token = '6HMQ2JPcMTxUcJquw9SFH9ZQ76@CRbBf6p'
            AND LQ.listingid = 945877

This works fine, under normal conditions. When we hit it with concurrency, we get deadlocks:

**Deadlock found when trying to get lock; try restarting transaction**

Any suggestions on how to avoid this lock?

Table Structures:

  CREATE TABLE `ListingQueue` (
 `token` varchar(100) NOT NULL,
`listingid` int(11) unsigned NOT NULL,
`initialPosition` int(11) unsigned DEFAULT NULL,
 `stage` enum('waitingroom','inqueue','eventpage','frontofqueue') DEFAULT     'waitingroom',
 `stageLastUpdate` datetime DEFAULT NULL,
 `enteredQueue` datetime DEFAULT NULL,
 PRIMARY KEY (`token`,`listingid`),
 KEY `currentPos` (`listingid`,`initialPosition`,`stage`) USING BTREE
  • 1
    Easy -- don't use SELECT MAX(...)+1 to generate sequences. Use auto_increment instead.
    – mustaccio
    Mar 22, 2017 at 17:51
  • Can't use auto inc as you can't easily mix them up. And each event needs its own sequence. An auto increment column is for the whole table.
    – David
    Mar 22, 2017 at 21:09
  • @David - Do you need each "sequence" to be 'dense', as with 1,2,3...? Or would it suffice for them to be just "monotonically increasing" -- with possible gaps?
    – Rick James
    Mar 22, 2017 at 22:45
  • Live with it -- check for deadlock and rerun the query.
    – Rick James
    Mar 22, 2017 at 22:53

1 Answer 1


This is an ugly solution that may introduce other issues. It is offered as a means to eliminate the lock you are experiencing. Auto_increment is preferable to managing numbers in this manner.

The number could be managed in its own table incremented as needed. This table could be locked at no detriment to the main table. When write locked, other sessions are prevented from using the table, the locking session can then increment the number (and store it in a variable - something meaningful like @next_position) then releasing the lock immediately after the update. The number is then used in your main query - no join, simply "LQ.initialPosition = @next_position".

You may still have contention on the position number table - but these locks shouldn't be held too long - only during the incrementation to ensure only one process increments the number at a time.

The locking scheme could be eliminated, but this would require knowing the current number first which would then be tested in the update's where clause to eliminate race conditions with other sessions. A loop will be necessary to try again if the update doesn't work (another session made the update first). This complicates the use of the table but reduces the potential for trouble.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.