I'm learning and doing a course on php and mysql. I have this case and I am not sure how to handle this: my application allows users to book a place in a meeting. There are 3 fixed meetings (meeting 1, meeting 2, and meeting 3), each having a limit).
The user can ask to go to two meetings, having to send 2 choices: the preference 1 (highest priority) and preference 2 (lower priority).
In the form to require to assit the meetings, the user MUST put the 2 preferences. For example say a user wants to go to meeting 1 and meeting 2, the app will let know the user it he got to one meeting (the other is full), two meeting (the two meetings have a place) or none (both full).
My question relates with the way of assign a seat in a meeting. The booking is done sequentially in the server, starting with the first preference, but letting other users who are booking simultaneously to insert their booking attempt before the booking attempt for the second preference.
EXAMPLE CASE:
- meeting 1: 1 places available
- meeting 2: 1 place available
- meeting 3: 0 place available
So if the following case would've happend with 2 users (both users doing it simultaneously):
- userA wants to go to meeting 2 (1st pref) and meeting 1 (2nd pref)
- userB wants to go to meeting 1 (1st pref) and meeting 2 (2nd pref)
As result, both users would only have their first preference satisfied.
How would you approach this?. We learned a little about transactions, locks, prepared statements, but I can't seem to apply it to this case. Or maybe I need to do some sort of "structure" or logic checking timestamps of users?.
I'm not asking for somebody to solve the problem for me, just a little guidance would be nice, please. I really appreciate your help, thank you very much.
EDIT: In order to explain what I need to achieve, I made this:
