DB Concurrency Questions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nyee
Forum Newbie
Posts: 3
Joined: Mon Oct 27, 2003 11:46 pm

DB Concurrency Questions

Post by nyee »

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 :)
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

If you arent using transactions you may use Lock to prevent others for writing or reading data while updating it, so in your case you likely want to use locks when finding and reserving a slot, then have a couple of columns indicating whether it is taken...

e.g. these two extra columns
slot_taken int
slot_taken_by int

When a user finds a lot he wants he clicks to reserve, your script could do something like
set a rw-lock
a select query to make sure it is still available, if not then bail
update set slot_taken_by to the user id of this user and slot_taken to current unix_time()
remove lock

The timout can now be based on the slot_taken value, whenever someone releases a lot just set the value to 0..

whenever a user does something in regards to this slot the script should check whether it still belongs to him or not, if the timout-value is shorter than the slot itself, each operation the user does should update the slot_taken to the current time...

makes sense?
nyee
Forum Newbie
Posts: 3
Joined: Mon Oct 27, 2003 11:46 pm

Post by nyee »

So the possibility of user A receiving an error "that timeslot is no longer available" (after user B takes the slot) is inevitable even though we can deal with the DB concurrency issue itself through locking? I just wanted to make sure that there is no easy way out of that problem.

Thanks for the reply.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

in a stateless system there is no other way that I know of than a timeout... Unless you have physical equipment in place to register if a person has moved from the chair :)
Post Reply