KWPro.net

I just encountered a problem where I needed to lock a table row in Mysql and update a field to indicate that the table has been locked during a transaction.  Normally, you would use the "select for update" and proceed to update the lock field in the same DB statement.  If you're using a plain query or exec statement with the Zend Framework, that's fine, but you're not really taking advantage of the methods that the Zend Framework presents to enforce encapsulation.  However, when I went through the documentation, I found that you can use the forUpdate() method to lock the row.  However, the documentation did not specify a good methodology for employing handling the update section.

With a little research, I found that you need to wrap the entire update into a transaction.  The article I found was a little verbose and the code wasn't that generic.  So I wanted to write up a slightly cleaner version of the code:

$model = new MyModel();

$db = $model->getAdapter();

$db->beginTransaction();

$row = $model->fetchRow($model->select()->forUpdate()->where('table_id = ?', $tableKey));

if ($model->update(array('is_locked' => true), $db->quoteInto('table_id =?', $tableKey)))

{

  $db->commit();

}

else

{

  $db->rollback();

}

This bit of code only specifies how to lock the table row.  You still have to do your updates while the row is locked and add logic to ensure no others can update that row while you're doing your updates.  And you still need to unlock the roll.  But I think this piece of code is easy enough to understand and implement generically.