Insert a row and avoiding race condition (PHP/MySQL)
- by justkevin
I'm working on a multiplayer game which has a lobby-like area where players select "sectors" to enter. The lobby gateway is powered by PHP, while actual gameplay is handled by one or more Java servers. The datastore is MySQL.
The happy path:
A player chooses a sector and tells the lobby he'd like to enter.
The lobby checks whether this is okay, including checking whether there are too many players in the sector (compares the entry count in sector assignments for that sector against the sector's max_players value).
The player is added to the sector_assignments table pairing him with the sector. The player client receives a passkey that will let him connect to the appropriate game server.
The race condition:
If two players request access to the same sector at close to same time, I can envision a case where they are both added because there was one space free when their check was started and max players gets exceeded.
Is the best solution LOCK TABLE on sector_assignments? Is there another option?
