Consistent database with concurrent reads and writes

Question

I've written a REST API for a simple game including creating, joining and submitting game's matches. I've encountered a problem while testing server. By sending join requests by a single player (lots of them at once) the server would join the player to the lobby multiply times.

My design looks like this:

in players' table check if player's field 'playing' is set to true, if yes discard the request
if player is not currently playing create new player entry for the lobby and set player's playing status to true

I know that the problem is when checking if player is playing at the moment. Every request is run by separate goroutine in Go so it can happen that every goroutine gets the information from database that player's 'playing' field equals false. Then every gouroutine would add the same player to the lobby multiply times which I want to avoid.

Is there a way to avoid this problem, or the problems is in my design?

This is a standard race condition. There are many ways to solve it. — Jonathan Hall, Mar 28 '19 at 10:37

score 0 · Answer 1 · answered Mar 28 '19 at 10:33

There are a few options - it's worth reading the docs on Postgres for concurrency handling.

The short version is: make sure your insert/updates only fire when they find data in the state they expect, and use transactions.

So, something like:

begin;

insert into lobby
select player_id, ...
from players
where player_id = $current_player
and player_id not in (select player_id from lobby);

update player
set playing = 1
where playing = 0
and player_id = $player_id;

commit;

Those two SQL statements won't do anything if some other process already marked the player as active, but they will fire both together. This should mean that if you have multiple concurrent attempts to update the player's status, only one of them will do anything.

This conditional insert is probably what I was looking for as the ORM will do the transaction for me. — Qizot, Mar 28 '19 at 10:54

score 0 · Answer 2 · answered Mar 28 '19 at 10:44

Your question is not related to Go, but to database structure.

To simplify the solution:

To make sure you have only one player playing at the same time, you could create a dedicated table for this with a unique index/constraint.

How to add a uniqueness on 2 columns see this question: In Postgresql, Force unique on combination of two columns In your case the columns will be player_id and playing

On API level you would need to handle the errors you get from the database in case a player is already playing.

score 0 · Answer 3 · answered Mar 28 '19 at 11:20

If you want to ensure you can never have the same user in the same lobby twice, you should add a unique constraint on the combination of those two fields in the players table (assuming players is what links users to lobbies).

Once you have that, you can use an upsert:

INSERT INTO players (user_id, lobby_id, playing)  VALUES (12, 41, 1) ON CONFLICT name_of_your_constraint UPDATE players.playing = 1;

This will create the record - but, if that isn't possible because that combination of user and lobby already exists, it will just mark the user as playing.

Consistent database with concurrent reads and writes

3 Answers3