CODEWITHSUNDEEP
mysql
sveti petar
sveti petar

Reputation: 3787

Prevent simultaneous database updates in MySQL without locking the table

I have a multiplayer card game. Each game is played by 4 players. Each player is asigned a seat (1, 2, 3, 4), players click to join the game and when all 4 seats are filled the game can begin.

A game entry in the DB looks like:

id | status
13 | 3

The status is the number of filled seats. It is incremented every time a new player joins. Now I have a problem. Sometimes 2 players send a request simultaneously and they are both added to the same seat. In the example above, two requests are received and suddenly there are 5 players in the game, with 2 of them assigned to seat 4.

Obviously before I update, I check that there aren't already 4 players.

Something like:

if($row['status'] < 4){
    mysql_query("update games set status=status+1 where id=13");
}

Logically, this should prevent the status ever being more than 4, but if there are lots of players joining games, there's often a situation where status becomes 5 because multiple players join simultaneously.

I suppose I could prevent this by using table lock but I don't want to lock the whole table for the purpose of updating just one row (there could be 50 games in progress at the same time).

There's gotta be a reliable way to resolve this, right?

Upvotes: 2

Views: 141

Answers (2)

MTilsted
MTilsted

Reputation: 5530

You could also use "select for update."

Something like:

sql:
begin(); // Need to be in a transaction.
select status from $YOUR_TABLE where $SOMETHING for update
code:
if(status<4)
   Set new status in database.
commit();

Select for update lock the row(Not the database, only the single row which match the where).

Upvotes: 1

davek
davek

Reputation: 22895

You could incorporate the constraint into the update statement itself, so that it is regulated in the DB:

update games set status = coalesce(status, 0) + 1 
where id=13
and coalesce(status, 0) < 4

Upvotes: 2

Related Questions