prevent two users editing same record

hobbyman

I have a <FORM> that will allow users to
pull up existing database records.

Does anybody have a trick/scheme that will
prevent two users from editing the same
database record (opening the same record
in the <FORM>)?

I tried to come up with a "locking" scheme
of my own but have ran into several
problems.

( My most recent thought was to do a SELECT
query. Then do a mssql_fetch_object call
(yes, I am using MS SQL SERVER). Then store
the serialize'd object into a session
variable. Then when the user submits their
data, do a SELECT query again and compare
that to the saved result, if the two do not
match, the record was changed by someone
else whilst they were on their page.

Does anybody have an opinion on this idea? )

djg_uk

Hi HobbyMan,

Wow, that MS SQL Server is fun to connect to, isn't it? :-P

I presume that each user is registered. If this is the case, create another table to link the user id and the record id together (sort of like a logging book). If someone is logged into a particular record, it should come up as unavailable on the search form.

If the user submits (changes) information, unlock the file. It might be a good idea to have sort of time field, so that the user can only edit the file for ten minutes to prevent people logging in and forgetting to log out.

Therefore, the new table would look like this:

ID | USERID | RECORDID | TIMESTAMP | STATUS

This way you can also track records..

Regards,

David

kparker

The direct approach gives poor results under the best circumstances, and http with its inherent statelessness is far from the best circumstance. Actually, this came up here recently, and a search for 'optimistic locking' might turn it up, but basically you need to record the state of the row before any changes are made, and then when updating the row abandon the update if anything has changed (implying that someone else updated the record.) If you can arrange for a serial-number column that gets incremented with each insert/update, you can rely on that rather than having to literally save the entire contents. Also, it's a good idea to store it in a session variable, rather than as hidden field(s) in the form, since that's harder to subvert.

hobbyman

I had thought of that.

Do you use something like this?

I feel that the users would be very upset
to know that they had just worked x number
of minutes only to find out that it won't
be saved.

How do you feel about that?

thanks.

kparker

Do you use something like this?

Yes, except for cases where the "sociology" indicates only one person will be working at a time (e.g. administrative stuff in certain cases.)

the users would be very upset

That's actually an argument for doing the full column-by-column before-image: you can present the user with a list of which columns changed, and in what way. And furthermore, if you're at all persuasive, you can console your users with the explanation that, if you hadn't done it carefully like that, someone's update woutl have been lost without a trace.

Make no mistake: you can lock a row whenever anyone might change it (with a horrific impact on concurrency), or you can implement some kind of optimistic-locking protocol, OR you can ignore the problem and just pretend it didn't happen when your users complain about occasional missing updates.