Concurrency Problem

Question

Hello,

I'm having what seems to be a concurrency problem while using MySQL and PHP + Propel 1.3. Below is a small example of the "save" method of a Propel object.

public function save(PropelPDO $con = null) {
    $con = Propel::getConnection();
    try {
        $con->beginTransaction();
        sleep(3); // ignore this, used for testing only
        parent::save($con);
        $foo = $this->getFoo(); // Propel object, triggers a SELECT

        // stuff is happening here...

        $foo->save($con);
        $con->commit();
    } catch (Exception $e) {
        $con->rollBack();
        throw $e;
    }
}

The problem is the $foo object. Let's say we get two calls of the example method one after another in a very short time. In some cases, if the second transaction reads $foo...

$foo = $this->getFoo();

... before the first transaction has had the chance to save it...

$foo->save($con);

... $foo read by the second transaction will be outdated and bad things will happen.

How can I force the locking of the table Foo objects are stored in so that subsequent transactions can read from it only after the first one has finished its work?

EDIT: The context is a web application. In short, in some cases I want the very first request to do some data modification (which happens between fetching and saving of $foo). All subsequent requests should not be able to do the modification. Whether the modification will occur or not depends on the fetched $foo state (table row attribute). If two transactions fetch the same $foo, the modification will occur twice which causes a problem.

Answer 1

when you load this existing row to the screen/application, load the LastChgDate too. when you save it, use "AND LastChgDate=thevalue". check the affected row count of the update, if it is zero, return an error "someone else has already saved this record", and rollback and other changes. With this logic in place you can only save a row if it the same as when you loaded it. for new rows, INSERT, this is not necessary because they are new.

Answer 2

In MySQL, I think you can use SELECT FOR UPDATE to accomplish the lock.

Another option is to use the GET_LOCK and RELEASE_LOCK MySQL function calls to create named locks that you would use to control access to the resource.

There are some downsides to these approaches. I haven't used them myself very much and they are MySQL specific but they could work for you.