DB Concurrency Questions
Posted: Wed Oct 29, 2003 3:19 am
So I'm reading up on concurrency, and I understand how LOCK/UNLOCK work and I know they have to be used in the same script. But I'm unsure how to deal with the following situation:
I'm writing an app where students will choose existing time slots with limited availability (say 3-5 students per slot). I want to show a list of existing time slots (query), have the user pick one, have them enter an identifier (name or email) and then save that selection (update).
Now imagine if user A comes and sees the available time slots from the query, and then pauses for coffee. In the mean time, user B comes, quickly queries and updates for the last available slot in a certain time. Now user A returns, and tries to take the slot he "thought" was open, and the DB tells him it's gone.
Is there a way to elegantly deal with this problem? I can't use LOCK/UNLOCK because the query and the update aren't in the same script. I can't artifically decrease the timeslot to "hold" it for user A because if he never completes the update (or closes browser), then that timeslot is one less than it should be. This seems like such a common DB problem that there must be a good way to deal with it. Someone please tell me
I'm writing an app where students will choose existing time slots with limited availability (say 3-5 students per slot). I want to show a list of existing time slots (query), have the user pick one, have them enter an identifier (name or email) and then save that selection (update).
Now imagine if user A comes and sees the available time slots from the query, and then pauses for coffee. In the mean time, user B comes, quickly queries and updates for the last available slot in a certain time. Now user A returns, and tries to take the slot he "thought" was open, and the DB tells him it's gone.
Is there a way to elegantly deal with this problem? I can't use LOCK/UNLOCK because the query and the update aren't in the same script. I can't artifically decrease the timeslot to "hold" it for user A because if he never completes the update (or closes browser), then that timeslot is one less than it should be. This seems like such a common DB problem that there must be a good way to deal with it. Someone please tell me