Locking Cases For Edit/Write where table lock won't do

[dlite922]

I need to do some sort of Locking mechanism at interface level, instead of DB Level.

I know how MySQL table locking works, but that won't work in my scenerio.

Requirements:
When someone is editing a case, for example changing the persons info, editing the violations/charges they have, etc... I don't want another user to have pulled the same case and one of them clicks Update or Save before the other.

So the case is locked for editing and should only be changed by that user.

My Proposed Solution:
Upon entering the case editing page, I could issue a table lock on ALL the tables for Write (there are several) and unlock it when the person leaves, closes the case and goes back to the list or elsewhere.

What happens if that user closes the browser or their computer turns off?

First of all when anybody else trying to access it, it will show what user is editing it. (Don't know where i should this save this info? another table? I already have way too many)

The same person that locked it however, should be able to go back in and finish and close, and hence Unlock it at that time.

If that person is no longer available (went home), admins can go in and unlock it by force so other users now can go in.

I could also set a timer? or check to see if its been an hour since that person locked it and allow if it is. No admin interaction needed with this.

And maybe even use my current Ajax functions to update the DB while that person is in the case editing page. So essentially a person can be in there for an hour (It's a big page)

I guess my original statement where locking tables won't do should be changed to, locking tables can do that, is there any other way in this unforsaking state-less web.

Your Ideas:
[Insert Here]

I'm not looking for code, but any other ideas, solutions or ways of doing this. I know this is a long shot in the dark and don't expect pbmods to show up out of mystical light in the sky http://www.cosmiclighthouse.com/file...feb08/tree.jpg

But you never know :) you numbnuts might know a thing or two.

Thanks for all your input guys,


Dan

[TheServant]

I have no idea. Someone asked a similar thing a while back and no solution.

Hopefully I will provide a new way of thinking, maybe leading to a work-around, as directly doing what you have asked is not possible to my knowledge.

What about setting a time (eg. 5 mins) to edit the document. You will need AJAX so make a countdown timer while the user is editting, and to submit the data/close the window when the timer is finished. If you don't like this idea what about asking the user to select a time (eg. minor changes - 5 mins OR major changes - 30 mins). This way if someone else tried to access it, they can have an exact time it will be available.

Alternatively, a little more extreme is record using AJAX the time of last character press or mouse click and if that is longer than 2mins, make the page available to other people and refresh the first users page to one saying that it has timed out and all changes have been lost.

Hope I have got your brain juices flowing or atleast amuzed you for a time.

**subscribing**

[coolsti]

This is a difficult one. For my application, I can get around this problem by having all such multiple-page-request database changes be configured such that the user making the changes is re-presented with the current state of the database each page request, so that any changes that have been made by another user at the same time are immediately noticed by him/her. This is only allowable in my situation because 1) it is rare in my application that two people will indeed work on the same thing simultaneously and 2) the implications of such simultaneous editing in my case is not so critical.

But in general, it is a problem. One thing you can consider doing is to script case locking instead of using table locking. Add an attribute to the main table of your case item, which normally is empty but which will be filled with the user's database ID when he/she starts editing the case. Program your scripts such that anyone who tries to initiate an editing procedure for a case will be denied if a database query indicates that this attribute is filled with someone else's ID. The person who is doing the editing is allowed to continue because it is his/her initials in this table column. Then when the edit procedure is finished, you clear out the user's initials from the table column for this case, thus making the case available for editing by others. You can also script to clear out all of the user's initials from such attributes when the user logs out.

What to do when the user does not log out but simply closes the browser? I would then have a program running in the background that would every xxx minutes clear out the user's initials from this column if a timeout period has been exceeded. In this case, you would then need to also add a datetime attribute as well, and set the date and time when the user initiates the editing and each time the user accesses a page that is part of the editing procedure. It is a rather simple query to clear out timed out edit operations this way.

This avoids table locking. The advantage is that you don't lock the entire table blocking other access, and you do not have to go to individual table row locking. You simply block the entrance to the edit operation to anyone else if one person has "locked" the case for editing in this manner.But it means a bit of database structure change (to add the user ID and date-time attributes) and a bit of script programming.

[dlite922]

....One thing you can consider doing is to script case locking instead of using table locking. Add an attribute to the main table of your case item, which normally is empty but which will be filled with the user's database ID when he/she starts editing the case. Program your scripts such that anyone who tries to initiate an editing procedure for a case will be denied if a database query indicates that this attribute is filled with someone else's ID.....

Thanks, i'll consider that.

This was interesting as you metion row locking: http://dev.mysql.com/doc/refman/5.1/...ock-modes.html

I'll let you guys know what I come up with. In my case two people editing the same case is rare, but i'm doing this just in case.

[Atli]

How about, before your user is allowed to update the record, you check if the record in the database has been updated since the user pulled it.

If it has, you prompt your user with this info, asking him if he wants to override these recent changes. Or even re-load the updated version for him to edit. Possibly side-by-side with his version so he can compare and edit before saving.

[coolsti]

How about, before your user is allowed to update the record, you check if the record in the database has been updated since the user pulled it.

If it has, you prompt your user with this info, asking him if he wants to override these recent changes. Or even re-load the updated version for him to edit. Possibly side-by-side with his version so he can compare and edit before saving.

Something like this may work if the edit operation is performed in one step. One way to implement this is to store in $_POST (or $_SESSION) the original state of the case being edited by user A when user A begins the edit procedure, for example when an edit page is requested. Then when user A submits his/her changes, the receiving script can compare the current state found in the database to the original state, and if different, present the edit page again with a warning to the user indicating how the state has been changed in the meantime.

[Atli]

Or, if we are talking about a lot of data that needs to be compared, the record could simply include a TimeStamp indicating when it was last updated, which you could fetch along with the data when you start editing. Then when you submit, check it that TimeStamp has been updated.

[dlite922]

Or, if we are talking about a lot of data that needs to be compared, the record could simply include a TimeStamp indicating when it was last updated, which you could fetch along with the data when you start editing. Then when you submit, check it that TimeStamp has been updated.

Yeah, i have this timestamp in place for all my tables (Since responsibility is very important, need to know who did what when)

I could use that to my advantage here exactly like you said.

I could have a hidden field when the user requests the edit page and give the timestamp. On submit, i'll take that and compare it to the lastUpdate timestamp in DB, if its Older than someone has updated the case and will warn the current user the case has been updated while in edit by him/her.

This also works because I'll save her changes that it won't get lost. I'll probably suggest to open another window and access the case to see any changes made (or if any she/he cares about), or just go a head and submit
the data anyway if they don't care.

These a really good idea and got my brain "juices" flowin'.

See ya!

-Dan