Re: MySQL Locking Question
| From: | Don Read | Date: | Sat, 06 Oct 2001 02:16:37 +0000 |
| Subject: | Re: MySQL Locking Question | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-70175@lists.php.net to get a copy of this message | ||
On 06-Oct-2001 Chip wrote:
>> Well, if you only update the fields that changed then who cares? The
>> fields that Jill changes are going to overwrites Jack's changes anyway.
>> Locking the record and having Jack finish before Jill can start isn't
>> going to change the end result which would be that Jill's changes are
>> going to overwrite Jack's.
>
> The reason for this is because the data that Jack enters could potentialy
> affect the data that Jill enters.
>
> Example: Jack is an administrator at a forum somewhere. Jack is editing a
> post that Jill entered to remove inapropriate content. Meanwhile Jill is
> editing the same post to revise what she said seeing that it could be
> mistaken for being inappropriate. Jack saves, Jill Saves...Jacks stuff is
> lost.
>
> OK that example is poor I admit...but do you see the reasoning behind
> locking? If a lock was in place Jill would't be able to edit her post
> because she would get a message stating that Admin Jack was allready editing
> it.
>
You can add a timestamp to the table
mysql> ALTER TABLEda_table ADD ts TIMESTAMPNOT NULL AFTER id;
Add the timestamp to the update criterion; Re-do the form if it
doesen't match:
if (isset($do_update) {
$qry= "updateda_table set foo='$foo' where id='$id' and
ts='$ts'";
$res=mysql_query($qry);
$cnt= mysql_affected_rows($res);
if ($cnt == 0 ) {
printf('<META HTTP-EQUIV="Refresh" CONTENT="5;
URL=%s?id=%s"',
$PHP_SELF, $id);
echo '<P>Record is stale<BR>';
} else {
echo '<P>Record updated<BR>';
}
}
$qry= "select ts,foo from da_table where id='$id'";
$res=mysql_query($qry);
$row = mysql_fetch_object($res);
printf('<FORM METHOD=POST ACTION="%s">', $PHP_SELF);
echo "<INPUT TYPE=HIDDEN NAME=id VALUE=$id>";
echo "<INPUT TYPE=HIDDEN NAME=ts VALUE=$row->ts>";
echo "<INPUT TYPE=TEXT NAME=foo VALUE=$row->foo>";
echo '</FORM>';
Regards,
--
Don Read dread@texas.net
-- It is necessary for me to learn from others' mistakes. I
will not live long enough to make them all by myself.