在mysql中一次只能允许1个开放条目

Question

I have developed a game which works with php and jquery through ajax.

Essentially, when a request is made to the server the server will create a new game if there is not already one open. My database table for the games looks as follows:

id | closed | time

Where closed will be set to 1 once a game is over, and time is the unix timestamp of when the game was created.

Now the important thing here is that at any given time there can only be 1 game with closed = 0 in the table.

To do this I am using the following php code

$query = "SELECT id FROM games WHERE closed = '0' LIMIT 1";
$result = $this->database->query($query);
if ($result->num_rows == 0) {
    $query = "INSERT INTO games_roulette SET time = '".time()."'";
    $result = $this->database->query($query);
    return $this->database->insert_id;
} else {
    return false;
}

The thing is occasionally 2 games will be created, I believe due to 2 requests being sent at the exact same time, as the time values are always the exact same when there are 2 games open.

Is there any way I can have 100% certainty that there will never be 2 games with closed = 0 in the table?

Answer 1

The likely reason you end up with 2 records is, as you suspect, concurrency. If 2 processes separately run the SELECT query first, they'll both get the green light to proceed, and they'll both run the INSERT. So you end up with 2 (or more!) such rows.

Traditionally, you'd use transactions or locks to avoid this, but there's also an SQL "trick" that would allow a single atomic query to accomplish this:

INSERT INTO
  game_roulette
SELECT NULL, x.closed, NOW()
  FROM
    (SELECT '0' as closed) x
    LEFT JOIN game_roulette g ON x.closed = g.closed
  WHERE
    g.id IS NULL

Just to explain how this works: it's an INSERT ... SELECT syntax, where the SELECT query only yields a result if there's no row where game_roulette.closed is '0'. This, in turn, is done by creating a single row table x, then doing a LEFT JOIN against game_roulette on closed column and using the g.id IS NULL constraint.

After the completion of this query, if needed, the application can check the result of the INSERT, just as it would do for any other, to know if any row(s) were inserted as a result.

Note: if you're doing this on a large table, you should measure the query performance and/or make sure there's an index on closed column and it's getting used.