NYCPHP Meetup

[David Mintz]

Still having trouble understanding how to avoid the Lost Update Problem,
which was discussed here a couple months ago
(http://lists.nyphp.org/pipermail/talk/2003-December/006718.html etc).

An HTML form is populated with data from a db, including a field holding
my last_modified timestamp so I can compare it with the db before I write
the update.

How is using a transaction supposed to prevent another client from
sneaking in an update after I compare, but before I write?

I've read that you should either lock tables or use transactions. My
experiment (below) seems to suggest transactions alone is not enough and I
have to lock tables as well, or instead. But that doesn't sound right -- I
must be confused.

I've tried setting the isolation level to "read uncommitted" and it makes
no difference.

Here's the table.

CREATE TABLE test01 (
  id smallint(6) NOT NULL auto_increment,
  string varchar(75) default NULL,
  lastmod timestamp(14) NOT NULL,
  PRIMARY KEY  (id)
) TYPE=InnoDB;


INSERT INTO test01 VALUES (1,'bla bla bla', NOW());
--

Here's the experiment. When I load a copy into each of two windows and
submit both so they overlap in time, I expect the first to succeed and the
second to abort. Is there something stupid I'm doing wrong here? (btw I am
making sure to submit something in the 'string' field that's different
from what's in the db to ensure that my timestamp updates)

<?php

require('DB.php');
$db=DB::connect('mysql://joe_user@localhost/test');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

if($_SERVER['REQUEST_METHOD']=='POST') {

        $db->query("set transaction isolation level READ UNCOMMITTED");
        $db->query("start transaction");
        // compare our timestamp with db
        $theirs = $db->getOne("select lastmod from test01 where id=1");
        echo "sleeping..."; flush();
        sleep(4);
        if ($theirs != $_POST['lastmod']) {
                echo "record has been modified by someone else.
reloading...";
                $db->query("rollback");
        } else {
                $db->query('update test01 set string ='  .
$db->quote($_POST['string']) . ' where id=1');
                $db->query("commit");
                echo "ok, $theirs = {$_POST['lastmod']}, updating... ";
        }
}
$formData = $db->getRow("select * from test01 where id = 1");
?>

<form action="<?=$_SERVER['PHP_SELF']?>" method="POST">
        Id <input type="text" size="2" name="id"
value="<?=$formData['id']?>" /><br />
        Last Mod <input type="text" size="24" name="lastmod"
value="<?=$formData['lastmod']?>" /><br />
        String <input type="text" size="30" name="string"
value="<?=htmlspecialchars($formData['string'])?>" /><br />
        <input type="submit" />
</form>

FWIW I've tried this with both 4.0.13-standard and 4.0.16-max-nt-log, both
with PEAR DB 1.6.0RC6


Many many thanks,


---
David Mintz
http://davidmintz.org/

        "Anybody else got a problem with Webistics?" -- Sopranos 24:17