Fetching from table with concurrent connections

Question

We have a game where user can login and play a game, the game is for a group of 6 users only and when 6 users joins in a group the game starts.

we have a table in mysql named “temp_group” with user_id and a random_number Now, what we have to achieve is when a user enters the game we need to insert his user_id and check how many users are already in the group with same random_number, the scenario are as follows:

1. If no user are there in the group (first User to enter the game) - we generate a random_number and insert it against the new user
2. There are users in a group (2nd to 6th user joins the game) – fetch the random_number for that group and update it against this user
3. If 7th user join the game - treat him as a new user for a group and do as in point No 1.

We have created a stored procedure where we first check

SELECT count(random_number), random_number from temp_group group by random_number HAVING COUNT(random_number) < 6 order by id ASC limit 1

If we get the row, we fetch the random_number and update it for the new user (2th – 6th User)

If we do not get the row, we generate the random_number and update it against the new user.

Our Stored Procedure:

BEGIN
DECLARE ch_done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET ch_done = 1;

            START TRANSACTION;

            SELECT count(random_number), random_number  from `temp_group` where group by random_number HAVING COUNT(random_number) < players order by temp_id ASC limit 1 into cnt, randomnumber;

            IF(ch_done = 1) THEN 
                IF(randomnumber IS NULL) OR (cnt = 0) THEN    
                    SET randomnumber = MD5(NOW());
                    SET cnt = 0;
                END IF;
            END IF;

            INSERT INTO temp_group (`user_id`,`random_number`,`no_of_players`) VALUES(userid,,randomnumber,6) ON DUPLICATE KEY UPDATE `random_number` = randomnumber, `no_of_players` = 6;
            COMMIT;
END

Problem:

When we tested with a single user joining the game at a time, it works proper. But when we tested it with concurrent connections, the same random_number is assigned to more then 6 users.

The Problem is when concurrent connections are calling the stored procedure, all the stored procedure fetches the select query and gets random_number and so it just updates the same random_number for all the users. Can anyone help me on how to stop the stored procedure from reading the data till the previous stored procedure updates the table and commit

Answer 1

There are some problems in your procedure:

First, and probably your main issue, your select is non-locking, e.g. if two concurrent sessions read the table at the same time, they can get the same result and make their decision based on that same, current state. E.g. their might add their player to the same team as they both assume there are currently only 5 unassigned player, resulting in a team of 7. The fix would be a locking read, which you can get by adding for update:

SELECT count(random_number), random_number ... into cnt, randomnumber for update; 

Then a second query will have to wait until you commit the first session (with the exception of an empty table, which may or may not be a concern here), so, as you wanted, it "stops the stored procedure from reading the data till the previous stored procedure updates the table and commit".

The second problem is that MD5(NOW()) isn't unique. If you have two concurrent sessions, it implies that they run at the same time - so MD5(NOW()) will return the same value, even if your procedure actually intended to create a new group. It will probably only become an issue if more than 6 players join within the same second, but even rare issues are issues.

While you can probably find a better randomizer, I would strongly suggest to add a new table that contains information about the group, in its simplest form e.g. just team (id int primary key auto_increment). Your current table temp_group could then become something like team_user (team_id, user_id).

Instead of generating a random number to create a new team (and assigning a player to it at the same time), you create a new team by adding a row to the team table, and you assign the player to that team by inserting him to the team_user table. This way, you properly split up the two logical steps (creating a new team, adding a user to a team) into two actual steps.

A proper, normalized data model makes a lot of database tasks easier, e.g. the uniqueness of your teamid basically emerges naturally without doing any more work (and even if you would need a unique md5 value per team, you have a simple way to enforce it by adding a unique key to the team table). Also imagine you would want to add any additional information about the team (e.g. a team name, a game mode, an option to create a private/password protected lobby, or, as you currently seem to be storing, the max number of 6 players) - you would have to store it several times in the temp_group table, which is always a bad idea.

And a last issue:

SELECT ... group by random_number ... order by temp_id

This is, in general, invalid, as temp_id does not have a deterministic value (e.g. which of the up to 6 values for temp_id per random_number do you mean)? MySQL allows this only if you disable only_full_group_by (which you shouldn't). A fix could e.g. be to use order by min(temp_id) (depending on what you want to achieve). On the other hand, if you add the team-table, you could (correctly) use group by team_id having .... order by team_id.