Another way is to keep another unique value inside the table to be updated
and remember it.
When needed, I add a second unique column to the table (in my case a
char(64)) which is filled with the current timestamp and some md5 checksum.
I select this value before the update, pass it along with the HTML form,
and, before updating, I re-select the row to be updated and compare the
keys. If the comparision fails, the user is presented a warning message,
else I do the update (the user's data and a new generated stamp-value) with
the primary key _and_ the original stamp in the where clause. Then I check
if my new stamp made it to the table, or present another warning.
A sample:
Table atable:
id int not null primary key
stamp char(64) not null unique
avalue int
select id,stamp,avalue from atable where id=1 (select data for update)
build html form with "id" and "stamp" as hidden values
select stamp from atable where id=<the_id>
if stamp(form) != stamp(db)
error (e.g. start from beginning)
else
construct new_stamp
update atable set avalue=<new>,stamp=<new_stamp> where id=<the_id> AND
stamp=<old_stamp>
select stamp from atable where id=<the_id>
if stamp(db) != <new_stamp>
error
endif
endif
It's a bit of work, but it had never let me down.
Thomas
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
Is what I use in the WHERE clause to update data
ts CHAR(32) NOT NULL UNIQUE
I
> -----Ursprüngliche Nachricht-----
> Von: Doug Semig [mailto:[EMAIL PROTECTED]]
> Gesendet: Mittwoch, 18. April 2001 20:48
> An: [EMAIL PROTECTED]
> Betreff: Re: [PHP-DB] Concurrent update to database (PostgreSQL or
> MySQL) ??
>
>
> As others have mentioned, this is a tricky thing to do.
>
> What others haven't mentioned is a way to do it.
>
> Let's say you just showed the update form to two users. It
> contains the
> fields that they are allowed to update and the values in
> those fields had
> been retreived from the database.
>
> Both users see a form like this:
>
> Today's High Temperature
> ------------------------
> City: Detroit
> Temp (degrees F): 47
>
> ( Submit )
>
> The city field is not available for update (presumably the editor/user
> selected it off of a previous menu), and their task is to
> update today's
> high temperature for Detroit. One of the editors has
> information that the
> high temperature reached 49 degrees F, and the other editor
> has information
> that the high temperature has reached 50. (Perhaps one of
> the editors is
> overworked and hadn't gotten to enter the new high, or the
> temperature is
> changing very quickly.)
>
> So one of the people enter 50 and the other enters 49. Both
> hit submit.
>
> What needs to be done right at that moment to protect it from
> changing the
> value to 50 degrees (which is the correct, most recent, data) and then
> immediately changing the value to 49 degrees (which is now
> out of date)?
>
> Basically, you have to pass the original value to the script
> that is the
> ACTION of the HTML form. This way, you have the original
> value that may
> have been updated and the new value.
>
> The first thing the HTML form's ACTION script has to do is
> get the record
> from the database again. (Use SELECT ... FOR UPDATE if
> available, so the
> RDBMS might lock the row.) If the value you get from the
> SELECT is the
> same as the original value, go ahead and run the UPDATE to
> change the value
> to the new value. If not, then generate and display a screen
> telling the
> editor that the value has been updated by someone else...you
> can show the
> value and ask if they want to proceed with the update, but
> that's all up to
> you and/or your interface designers.
>
> This is all similar to how folks design old fashioned screen-oriented
> database systems (like with CICS).
>
> Good luck,
> Doug
>
> At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote:
> >Hi,
> >
> >How can I avoid two or more users to update the same record
> at the same time
> >?
> >
> >Ideally, I would like to lock the record when a user open it for
> >modification. So if another user try to open the same record
> he'll be warned
> >or get record values as read only. Is this possible and how
> to do it with
> >PHP ?? How can I know that the user that has locked the
> record has finished
> >with it, if he never commits his changes ? Is there an FAQ
> about this ?
> >
> >Regards,
> >
> >Nicolas
> >
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail:
> [EMAIL PROTECTED]
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
