Lock visitor until mysql query is complete

I have a bunch of pages on a site for which the actions that can be taken on one page are contingent upon the info in the database for the site visitor. So, let's say Visitor A comes to page B and updates a database to show that they have joined a certain group. Then the visitor goes to page C, the group page. If the user is a group member, they are shown member content. If they are not, they are shown non-member content. Here comes the issue (btw, pages are in php):

In an ideal world, the query run on page B would be completed instantaneously before the user goes to page C so the database is always filled with the most recent info on membership states. However, it can happen that the server is under a lot of load and the query does not complete by the time the user goes to page C. So, even though the user is a member of the group, that is not reflected on page C since the query has yet to complete.

Is there a way to make it so that if a user run a query on a page, until that query completes any other page they try to visit will just "hang" and load after there query is complete, or until a given amount of time has passed? I can make this system manually, but if mysql and php already have something built in, that would be preferable.

In case it matters, I am using a LAMP server.

Can't the visitor simply be not shown the link to page C until the confirmation happens in page B? So they join the group in page B which happens by submitting a form post to a separate intermediate form processor page F, and then once that's done (ie mysql insert completed successfully), it loads page B again with the proper new information and directions to page C.

If page C links have to appear always, or the user can have it bookmarked, then perhaps page C can also display the date of the record, offering the user a "Refresh" button in case his record doesn't match what he just did as a way of telling him to wait a bit and try again.

In any case, in mysql you're talking about the concept or row locking but you're trying to apply that to a multi-entry site. I don't know of any such built-in mechanism in PHP but you could fake it with semaphores per user if you'd like:

user initiates action
PHP sets a lock for that user either by creating a unique file, or setting a unique key in a memory store like memcache, or in the user session
that lock indicates that the action has not finished yet
PHP performs action (mysql insert, whatever)
once action is complete and validated, remove lock

Then you can use the existence of the lock to determine wether page C should load and display something descriptive to the user, such as my above suggestion of a Refresh button.

I can think of a number of ways page C could be requested before page B is done loading.

ysth 2010-09-26 10:04:13

@ysth not if the user have to do an explicit action on page B to open up page C. it doesn't make sense to try to work around a case where the user interrupts that action and jumps straight to page C .

nos 2010-09-26 10:23:40

The user can always have page C already open in another window then refresh it while the query in page B is still running. Situations like this is rare but it does happen often enough to merit proper countermeasures. Navigating to page C is not contingent upon the user first accessing page B; the user can always go to page C. The group content cannot be viewed unless the user is a member. If they are not a member, then different content is displayed. My apologies, should have made that clear.

2010-09-26 13:11:10

@ysth Agree with nos.

James 2010-09-26 13:13:46

@user396404 No, it very rarely happens and when users do do that kind of thing they are usually the type of person who understands how web pages works and will expect to see an error if they try something on one page before another has finished loading. They'll see one page saying "You are not a member of group X" and they'll see the button they clicked saying "join group x" is still loading and they will know to wait. (Of course, if you design your site to deliberately open multiple windows it gets more complex but then you've brought it on yourself)

James 2010-09-26 13:18:45

I can't rely on users to take action according to what's best for the software; they're going to do whatever is comfortable for them, and I don't want to run into errors because of that. I have had it happen enough times to realize the value of implementing a way of handling this situation properly.

2010-09-26 13:24:19

@James: so do I, "if the user ha[s] to do an explicit action". But I don't see what that has to do with there being a number of other possibilities.

ysth 2010-09-26 16:19:00

@ysth which in my experience (assuming the site doesn't open multiple windows itself) will never arise for almost all users and those who do know how to deal with it. I would like to hear about the problem situations @user396404 describes because for me the problem seems really small, and the locking solution seems really overblown and fraught with other difficulties. But let's just leave it; one of the few problems I think Stackoverflow has is sometimes you really want to hear more context about a problem, and this is a difference in approach we are arguing about.

James 2010-09-26 18:51:50

@James: I certainly know where you are coming from; in answering a question, you often have to assess not just the details of the question, but the person asking. My assessment was that s/he really had given it some thought and could actually use an answer to this particular question, not (as is often the case) to be told that it isn't in fact a problem almost all the time.

ysth 2010-09-27 01:04:18

@James: But then I also don't think the locking solution is all that bad. Sure, you have to keep the timeout high enough to help most of the time and low enough that it is manageable if a lock happens to not get released, but it can be very helpful to have such per-user single threading built in to your session logic in certain problem domains (say, shopping carts).

ysth 2010-09-27 01:08:25

Each user does have a unique session id and also a unique user id. However, how would mysql know to reference that?

2010-09-26 13:12:39

@user396404: You pass that session id as the first parameter to the functions.

ysth 2010-09-26 16:19:43

2010-09-26 13:13:34

@user396404 my manual locking mechanism should work to give them an indication to wait for their action from page B to complete then, should they open or have already opened page C.

Fanis 2010-09-26 17:14:05

Thanks. This is what I was planning on doing (using memcached), but just wanted to see if other solutions were readily available.

2010-09-26 17:36:06

Best of luck. It sounds like a challenging task. Consider how to mitigate the case where the user lock will get stuck, eg by a crashed mysql server. With memcache you can have it timeout on its own I reckon.

Fanis 2010-09-26 17:50:33

ansaurus

tags:

views:

answers:

Lock visitor until mysql query is complete

related questions