Set isolation on DB2 with Zend Framework

111 views Asked by At

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();
        }
    }
}

}

0

There are 0 answers