Insert a row and avoiding race condition (PHP/MySQL)

Question

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?

Answer 1

Usually, the solution to such concurrency issues involves transactions and optimistic locking: when you update the counter, add a where clause to check the old value and count the number of rows updated.

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

If the counter was updated in the meantime, the update won't change any row -- so you know you have to rollback and try one more time the transaction.

One problem is that it could lead to a high contention, with only a few transaction which succeeds and a lot that fail.

It then might be better to stick to pessimistic locking, where you lock the row first, then update it. But only a benchmark will tell you.

EDIT

If you use transaction without optimistic locking the following scenario could happen.

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

Both transactions succeed, the value is 50, but there is an inconsistency.

Answer 2

if you use INNODB as your storage engine, you can use transactions in the db and avoid the need to manually lock the table.

In a single transaction, check that the space is available and add the player to the sector. This guarantees that the result of the check query is still valid until after you commit your transaction.