Re: Locking views

[Luis Briones]

Thanks for replying.

That LIMIT clause is going to help me a lot. Also, you are absolutely correct. After I submitted the post, I thought of the problem of assuming that every open request on the second page will be completed, but I could not come up with a solution. Now based on your suggestions, I think I will create a row for being_viewed and a row for Completed. The row for being_viewed would be set to 1, for a set amount of time, as soon as the page opens so that another user will not be able to retrieve the same request. Once the request is completed and the user clicks on "Submit", for example, I would change the being_viewed row to 0 and the Completed row to 1.

would this work for another user trying to correctly access the database?:

SELECT productID
FROM procducts
WHERE NOT being_Viewed and NOT Completed
ORDER BY orig_time_stamp (this is not a the time stamp showing when the user pulled the record, this is the time stamp showing when the record was inserted)
LIMIT 1,1

The only problem that I see with the time limit to keep a record open is that, for purposes of this project, a user may need to keep the request open for a long time so that he/she can study what is being requested, for example. So if I set being_viewed to be changed after 10 minutes, the being_viewed column will be set to 0 if it's taking longer to complete the request.
Now, is there a way to perform any given function if the connection is lost. So let's say I set the allowed time to 30 minutes in case the user leaves the page open without working on it. After that time the record will become available to other users. But what if it the browser crashes after 5 minutes and the record is locked for everybody? how can I let MySQL know of such event?

I really thought MySQL would have a simpler way to accomplish all this, like "SELECT * FROM table FOR UPDATE" or something.

Thanks again and I hope to get more replies :)!