Record lock/unlocking in PHP

[Mr. M.J. Lush]

I'm writing a postgres database with a PHP front end
for a small work-group , and I want to prevent two editors
working on the same record at the same time overwriting each
others entry's.

The standard solution is to add a lock field to the
database and toggle it from 0 (unlocked) to 1 (locked) when the
record is selected for editing, however I cannot guarantee that a
record will get unlocked when an editor has finished with it (a browser
crash would force visiting an unlock page to allow a record to be edited).
I also do not want to place any particular limitations on how long a record
can be checked out for editing.

What I have in mind is saving the original data in hidden fields
in the form then diff the data with the database when the form data is
saved, if there is a difference (ie something has been changed between
checking out the record and saving it) the form is put into a 'diff mode'
which highlights what has been done.
Another option would be to generate an MD5 checksums for each
row on the table store it in a checksum column and use that to spot
alterations.

Is there any other better options I'm missing?

--

Michael
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
NPC rights activist | Nameless Abominations are people too.

[Jochen Daum]

Re: Record lock/unlocking in PHP

Hi !

On Sun, 8 Feb 2004 23:43:16 +0000 (UTC), mlush@hgmp.mrc. ac.uk (Mr.
M.J. Lush) wrote:
[color=blue]
> I'm writing a postgres database with a PHP front end
>for a small work-group , and I want to prevent two editors
>working on the same record at the same time overwriting each
>others entry's.
>
> The standard solution is to add a lock field to the
>database and toggle it from 0 (unlocked) to 1 (locked) when the
>record is selected for editing, however I cannot guarantee that a
>record will get unlocked when an editor has finished with it (a browser
>crash would force visiting an unlock page to allow a record to be edited).
>I also do not want to place any particular limitations on how long a record
>can be checked out for editing.
>
> What I have in mind is saving the original data in hidden fields
>in the form then diff the data with the database when the form data is
>saved, if there is a difference (ie something has been changed between
>checking out the record and saving it) the form is put into a 'diff mode'
>which highlights what has been done.
> Another option would be to generate an MD5 checksums for each
>row on the table store it in a checksum column and use that to spot
>alterations.
>
> Is there any other better options I'm missing?[/color]

You should look at the other "standard" solution, which is optimistic
locking. Read a timestamp when you start editing the record. When you
save it, read the timestamp first. If it has changed, do your diff.
you need your old field values for that as well. People might have
changed different fields.
A lot of programs (eg. MS Access) just tell you that someone else
changed it and if you want to overwrite. I don't find that sufficient,
as most people will press "overwrite" (what do I care about other
peoples work?)

I have implemented the part with the old values in the system in the
signature. But not the optimistic locking yet.

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/