Help troubleshooting mysql deadlock

Question

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 (
                SELECT
                    (MAX(initialPosition) + 1) AS maxpos,
                    listingid
                FROM
                    ListingQueue
                WHERE
                    ListingQueue.listingid = 945877
                    FOR UPDATE
            ) AS LQ2 ON LQ2.listingid = LQ.listingid
            SET LQ.initialPosition = LQ2.maxpos,
                LQ.stage = 'waitingroom',
                LQ.stageLastUpdate = NOW()
            WHERE
                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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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.