I need to lock a record while fetching and updating his fields. It's the first time I need this, so I made a test class to see how thing works(I promise I'll neve make queries in the controller again).
With a browser (chrome) i call testisolationread, with another(FF) testisolationwrite which updates all the times without problems.
I tried with select()->forUpdate() too with same results.
I tried adding "WITH RS USE AND KEEP EXCLUSIVE LOCKS" (because "WITH RS USE AND KEEP UPDATE LOCKS" throws error). So here I am.
Any advice? Thank you
class Admin_testController extends Myprj_Controller_Main{
public function init(){
parent::init();
}
/**
* @return Strategic_Db_Adapter_Db2
* @throws Zend_Exception
*/
private function _getDb(){
$db = Zend_Registry::get('conn');
return $db;
}
public function testisolationreadAction(){
$this->_helper->layout()->disableLayout();
$this->_helper->viewRenderer->setNoRender(true);
$db = $this->_getDb();
try{
$select = $db->select()
->from('MYLIB/MYFILE', array('trim(FIELD01) as FIELD01'))->where("FIELD02 = '002654015-002'");
$stmt = $select->__toString()." WITH RR";
$result = $db->fetchRow($stmt);
sleep(10); //gives me the time to call testisolationwrite
$bind = array('FIELD01' => 'A');
$db->update('MYLIB/MYFILE', $bind, array("FIELD02 = '002654015-002'"));
$db->commit();
} catch(Exception $e){
print $stmt.'<br>';
print $e->getMessage();
$db->rollBack();
}
print '<pre>'.$stmt;
print_r($result);
}
public function testisolationwriteAction(){
$this->_helper->layout()->disableLayout();
$this->_helper->viewRenderer->setNoRender(true);
$db = $this->_getDb();
$vals = array('X', 'Y');
for($a = 0; $a < 20; $a++){
try{
$i = $vals[$a % 2];
$bind = array('FIELD01' => $i);
$db->update('MYLIB/MYFILE', $bind, "FIELD02 = '002654015-002'");
print 'SET :'.$i.' '.microtime().'<br>';
$db->commit();
} catch(Exception $e){
$db->rollBack();
}
}
}
}