MySQL: Correctness in the face of heavy concurrency (SELECT ... FOR UPDATE)

Question

Hi everybody,

I'm running a website where users can adopt virtual pets. There is a per-user adoption limit on each pet. So, for example, you can adopt one of our pets a maximum of 10 times. At the moment we do something like this:

CREATE TABLE `num_adopted` (
  `petid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `total` int(11) unsigned NOT NULL,
  PRIMARY KEY (`petid`,`userid`),
) ENGINE=InnoDB

Then when somebody adopts a pet we:

START TRANSACTION;

SELECT total
FROM num_adopted
WHERE petid=? AND userid=?
FOR UPDATE

We check the total against our limit. If they've already adopted up to the limit, we ROLLBACK and tell the user. Otherwise we:

INSERT INTO num_adopted (petid, userid, total)
VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE total=total+1

Then we add a row in a different table to record the new pet for them, and finally:

COMMIT

This has to work flawlessly under a very high level of concurrency.

Now, if the limit is 10 and the user has already adopted limit-1 pets, I can see that the FOR UPDATE annotation in the first SELECT guarantees that the total will be locked so that multiple concurrent adoptions won't end up seeing the total as limit-1 (which would allow the user to surpass their limit). The first adoption will see total=limit-1 and complete successfully, the rest will block. Eventually, the rest will see total=limit and refuse to adopt another pet.

But what if the limit=1 and the total=0? Could multiple adoption transactions see no row in the num_adopted table (so total=0) at the same time, allowing the user to adopt more than one pet? It's not clear that FOR UPDATE can lock a row that doesn't exist. If so, would this altered scheme solve the problem?

START TRANSACTION;

INSERT INTO num_adopted (petid, userid, total)
VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE total=total+1;

SELECT total
FROM num_adopted
WHERE petid=? AND userid=?

Check if total>limit, if so, ROLLBACK. Otherwise record the adopted pet and COMMIT.

Answer 1

I tested out both approaches and simulated high concurrency by adding a big sleep() between checking the current total and updating it. The first approach I described is checked like so:

<?php

$limit=1;

mysql_query("START TRANSACTION") or die("!");

echo "Checking the user's adoption count...\n";

$result=mysql_query("
    SELECT total
    FROM num_adopted
    WHERE petid=1 AND userid=1
    FOR UPDATE") or die(mysql_error());

$row = mysql_fetch_row($result);

$count = $row ? $row[0] : 0;

echo "Adoption count before adoption is: $count\n";

if ($count>=$limit) {
    echo "Limit reached, not allowing adoption\n";
    mysql_query("ROLLBACK");
    exit;
}

echo "Wait for 5 seconds...\n";
sleep(5);
echo "Recording the new adoption...\n";

mysql_query("
    INSERT INTO num_adopted (petid, userid, total)
    VALUES (1, 1, 1)
    ON DUPLICATE KEY UPDATE total=total+1") or die(mysql_error());

mysql_query("COMMIT") or die(mysql_error());

echo "Pet has been adopted.\n";

?>

If I start one adoption with an empty database, it checks the total (and finds it equal to zero), then it sleeps for 5 seconds. While it's sleeping, I start another adoption. The second adoption checks the total (also finds it equal to zero) and sleeps. The first adoption blocks on its INSERT waiting for the second adoption to release its lock. The second adoption blocks on its INSERT waiting for the first adoption to release its lock. Luckily, it notices that it is deadlocked with the first transaction, and aborts with an error message. So the SELECT ... FOR UPDATE approach does provide the security I need. But it'd be nice not to abort due to deadlock. With the second approach:

<?php

$limit=1;

mysql_query("START TRANSACTION") or die("!");

echo "Updating the user's adoption count...\n";

mysql_query("
    INSERT INTO num_adopted (petid, userid, total)
    VALUES (1, 1, 1)
    ON DUPLICATE KEY UPDATE total=total+1") or die(mysql_error());

echo "Checking the user's adoption count...\n";

$result=mysql_query("
    SELECT total
    FROM num_adopted
    WHERE petid=1 AND userid=1") or die(mysql_error());

$row=mysql_fetch_row($result);

if (!$row) die("Something's hinky...");

$count=$row[0];

echo "Adoption count after adoption will be: $count\n";

if ($count>$limit) {
    echo "Limit reached, not allowing adoption\n";
    mysql_query("ROLLBACK");
    exit;
}

echo "Wait for 5 seconds...\n";
sleep(5);
echo "Recording the new adoption...\n";

mysql_query("COMMIT") or die(mysql_error());

echo "Pet has been adopted.\n";

?>

The second adoption blocks when it makes its INSERT INTO until the first adoption is complete. After the first transaction completes, the second transaction can continue. It correctly detects that the limit has been reached without needing to give an error message about deadlock.

Answer 2

@Lamah Thanks. That is exactly what I have been looking for. Thanks a lot!