插入表格时的竞争条件

Question

I have a rounds mysql table (innodb) that is used to track all games that given user plays. It has the following columns: id (int), userId (int), gameId (int), status (int).

Id is table's primary key, userId and gameId represent foreign keys to other tables and in status the int values represent 3 different states if the game round - in progress, finished, error.

One user is not allowed to proceed with new game round until his previous round is still in progress.

My code does the following: SELECT id FROM rounds WHERE userId = <user> AND gameId = <game> AND status = <in progress> LIMIT 1

If the select returns result, an error is thrown. Otherwise I'm creating new round: INSERT INTO rounds (userId, gameId, status) VALUES (<user>, <game>, <in progress>);

There is no other code between the select and insert and most of the time everything seems to be working correctly and creating new game round, while the previous is still in progress, results in error. But when I test the code for concurrency and performance under big load, there are some rounds that manage to get inserted simultaneously. (I'm using simple node script that send 200 requests asynchronously.)

Do you have any ideas how should I alter the code so that in all cases I have maximum 1 active round?

I seem to be stuck on this problem and I know there must be simple solution buy I just can't see it. :(

Any help is greatly appreciated!

PS: I've tried using INSERT ... INTO rounds SELECT ... FROM ROUNDS WHERE NOT EXISTS (SELECT id FROM rounds WHERE userId=<user> AND gameId=<game> AND status=<in progress>) but this results in deadlocks..

EDIT: The code looks something like this:

In game:

public function play() {
    $gamesInProgress = $this->repo->getGamesInProgress($this->user->getId(), $this->id, self::STATUS_IN_PROGRESS);

    if($gamesInProgress) throw \Exception('Only one active game is allowed.');

    $this->createGame();

    // some other code
}

In Repo:

public function checkForGamesInProgress($userId, $gameId, $status) {
    $stmt = $this->dbh->prepare('SELECT `id` FROM `rounds` WHERE `userId`=:userId AND `gameId`=:gameId AND `status`=:status LIMIT 1');
    $stmt->prepare([
        'userId' => $userId, 
        'gameId' => $gameId,
        'status' => $status
    ]);

    return $stmt->fetchColumn();
}

Answer 1

It would be a good practice to use "composite key". This way the database ensured that no duplicated rows are inserted. But then you will have to handle the database error when (accidentally) trying to insert the duplicated entry.

I know that you have tried this, but what happens when you run this?

INSERT INTO rounds (userId, gameId, status) 
SELECT <user>, <game>, '<in progress>' FROM rounds WHERE NOT EXISTS(
SELECT id FROM rounds WHERE userId = <user> AND gameId = <game> AND status = '<in progress>' LIMIT 1)