Record Locking

Anon

Is there a good way of preventing a user from updating records in a databse when another user may already have retrieved them, and be about to update them himself. I take it database locking is insufficient due to the fact the script, and thus database connection, ends once the page is produced.

In case I am not being clear, what I am trying to ask is exactly what is put in the following articles:

http://www.geocrawler.com/mail/thread.php3?subject=%5BPHP3%5D+Record+locking&list=1

Are there any better/other methods than the two participants of the thread discus?

Thanks,
Tom.

Anon

I think that both people on the thread had good ideas as far as using either another table or field within the same table to lock the record and to also atleast give read-only access instead of denial of access.

One addition that I would add to the page once someone has successfully locked the record is a timer. You can advise the user that the record is locked for X amount of time and to prevent inactivity the user must either complete changes or click a timer restart periodically. Make the timer sensible for the user to accomplish their task within the given timeframe but not too long to prevent others from using the record.

You can have a countdown timer that even pops up an alert at a certain point, maybe one minute from the end of the timer. If not clicked the page redirects to close the record and release the lock. That helps to eliminate those users who may inadvertently leave the browser window open or take too long to modify a record.

If the user simply closes the browser without completely closing the record then the timestamp on the lock can be overcome by the next requestor of the record. When requested if the stamp is lets say 60 minutes old (seems long to me) then the lock is deleted and the user is granted rights to modify the record with their own lock being placed and timestamped.

Hope this helps,

Brian

Anon

The schemes they discuss all involve a "pessemistic" scenario. You should also consider an "optimistic" protocol:

When someone wants to edit a record, retrieve the desired fields with SELECT
and use this to initialize the fields on the form, but also create a corresponding
set of hidden fields that contain the original values.
When the form is POSTed, begin a transaction and then reissue the original
SELECT, but this time FOR UPDATE. Fetch the row into a new set of
variables and compare the original fields with the newly-retrieved data. If
anything has changed, it means that someone else updated the record in
the meantime, and you can cancel the update.

No locks other than the transient one provided by FOR UDATE, no extra fields, no delay waiting for a stale lock to go away.

Anon

Agreed, that is a nice addition, but then what you are doing is turning the tables. A user who feels secure about updating data and invests his time in updating the record has to cancel the update due to another user being able to enter data before him. I think I would feel more comfortable not being able to update the account from the beginning than wasting my time in trying to update a record but then having to cancel due to others updating. The time issue can be reduced greatly. 5 minutes seems pretty reasonable.

Have a timer set for 5 minutes, which can be extended manually by the editor. If he does not respond within 5 minutes, the account is released. 60 minutes on my earlier post was way too long hence the (seems long to me) addition.

False hope is worse than locking out potential problems.

Brian

Anon

I definitely agree that a 5-minute lockout is better than a 60-minute one, but
what happens to the original, slow updater when he goes up save his
changes but it's past the 5-minute timeout? Throw it away unconditionally?
No matter what timeout period you choose, there will be the problem of
updates coming just a second after the timeout expires. With the optimistic
solution, you can be as intelligent as you care to be about exactly how
you update the record if there has been an intervening update. This can
be on a field-by-field basis if you like.

Anon

No. When a user has an account locked and currently updating the record, the user has a timer on the browser in front of him. When it reachs 1 minute a popup or alert is called he has the choice to continue or close out of the record. If the page reachs 5 minutes it will redirect to a cancel page and clear the lock. It gives the reponsibility to the editor to keep his connection active. A little intrusive but depending on the timeframe on it takes to edit a record, 5 minutes is a long time.

Anon

I too am designing a system to deal with this problem. So far so great ideas have been discussed. But I still see problems. For one, this timeout thing can only be executed on the client. This means client dependent code, and regardless of how well you code it, it screws portability in the end. (ever browsed with lynx)?

So in my opinion javascript is just not the way to go. And for that matter, neither is CRON. If you're developing a highly portable system (ideally) then it should work on both IIS and unix/apache... CRON would allow for scheduled timeout checks independent of the client. And sure, there is CRON like functionality for windows, but what about users on a hosted site on IIS that dont have it? You're screwed again here. So at least CRON and javascript are out of the question.

Next we deal with how to represent this group data representation of locks then. Have a locks table, or a locks field, a locks file, etc. These are all valid representations. I would choose one which best fits your data size and access frequency.

Next, I think the solution lies completely within the interface areas defined by the application (in PHP). I suggest changing the entire metaphor of how data is updated. I suggest mirroring the metaphor of a library, where people check in/out books. This is much like how CSV/VSS work.

Each user of your 'portal' consciencely checks out records to edit. These records remain checked out to that user. Other users get the message the record is checked out for editing, and cant. They can see the time elapsed since the check out. You could even allow them a force/override checkout, which transfers control of the record to them. You could also have some god-mode functionality that provides an interface to who has what checked out and basic managerial functionality.

This would side step the problem of the connection being lost and the browser closed while the record is checked out.

On a related note, I think its smart to minimize the processing required, and thats why i would minimize the amount that needs to be locked. Records can always be selected for view. Even though some user is editing, you can still view the original record. Likewise, even though some user has the view of the data sitting on their client, you can begin editing anyways. So I would not lock read-only operations. Nor would I lock insert operations. I would only lock edit operations. And I would only allow delete operations from within the edit status.

You might say this is a cumbersome metaphor, but it successfully solves the problems, independent of the client.

Hope this helps. There is obviously more you could add to this concept, but I think you can do the rest.