Insert a line and avoid race conditions (PHP / MySQL)

Question

I am working on a multiplayer game in which there is a lobby where players select "sectors" to enter. The lobby gateway runs on PHP, and the actual gameplay is handled by one or more Java servers. MySQL data warehouse.

Happy Way: The player chooses a sector and tells the lobby that he would like to enter. The lobby checks if everything is in order, including checking if there are too many players in this sector (compares the number of entries in the industry assignments of this sector against the max_players value in the sector). The player is added to the Sector_assignments table, connecting it to the sector. Client-client receives an access key that will allow him to connect to the corresponding game server.

Race Status: If two players request access to the same sector at the same time, I can imagine the case when they were both added because there was one free space when their check was started and the maximum number of players was exceeded.

Is the best LOCK TABLE solution for sectors? Is there any other option?

Answer 1

Typically, the solution to such concurrency problems includes transactions and optimistic locking : when updating the counter, add a sentence whereto check the old value and count the number of updated rows.

v = select value from counter where id=x.
update counter set value = v+1 where value = v and id=x

, - , .

, , , , , .

, , . .

, .

Max authorized = 50. Current value = 49.

T1: start tx, read value --> 49
T2: start tx, read value --> 49
T1: update value --> 50, acquire a row lock
T1: commits --> release the lock
T2: update value --> 50, acquire a row lock
T2: commits --> release the lock

, 50, .

Answer 2

INNODB , db .

, . , , .