ActiveRecord and optimistic locking?

I am evaluating Yii. Your combination of "old fashioned" DAO with "modern" ActiveRecord looks great, but we always use optimistic locking in our web applications. Does your ActiveRecord support optimistic locking?

Thanks

Jozef

Don’t believe it does (never heard of it until now myself). You could probably implant it with a behavior.

DAO does support transactions and they can be used with ActiveRecord. PHP5 supports Try and Catch blocks. Transactions can be started in the Try block. If any one of the transaction queries fails after the transaction is started, an exception is triggered and you can rollback the transaction in the Catch block. This is documented in the Transaction sections in the DAO and ActiveRecord parts of the User Guide. You might also check the API docs for CDBTransaction.

I also searched for optimistic locking implementation for yii, but did not found anything.

Here is my implementation, see comments for how to use instructions. Implementation idea is taken from ruby on rails.




<?php

/**

  * OptimisticLockingActiveRecord class file.

  *

  * @author seb

  * @package app.models

  */


 /**

  * OptimisticLockingActiveRecord will automatically fill object version and do not

  * allow.to update / delete record if data has been changed by another user

  *

  * Usage:

  * 1. Add locking_version field to the database table

  * 2. Inherit model class from OptimisticLockingActiveRecord

  * 3. Add 'lock_version' hidden field to the edit form

  * 4. Handle StaleObjectError exception when saving record, for example

  *    try {

  *        $result = $model->save();

  *     } catch (StaleObjectError $e) {

  *        $model->addError('lock_version', $e->getMessage());

  *        return false;

  *     }

  *

  *

  * @author seb

  * @package app.models

  */

class OptimisticLockingActiveRecord extends CActiveRecord {


    /*

     * Can not implement this as behavior, because CActiveRecord::update() does not allow

     * to change criteria for SQL update

     * And optimistic locking requires this to implement safe locking

     */


    /**

	* Returns the name of the attribute to store object version number.

	* Defaults to 'lock_version'

     * @return string locking attibute name

     */

	public function getlockingAttribute() {

        return 'lock_version';

    }


    /**

     * Overrides parent implementation to add object version check during update

     * @param mixed $pk primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value).

	 * @param array $attributes list of attributes (name=>$value) to be updated

	 * @param mixed $condition query condition or criteria.

	 * @param array $params parameters to be bound to an SQL statement.

	 * @return integer the number of rows being updated

     */

    public function updateByPk($pk,$attributes,$condition='',$params=array()) {

        $this->applyLockingCondition($condition);


        //increment object version

        $lockingAttribute = $this->getlockingAttribute();

        $attributes[$lockingAttribute] = $this->$lockingAttribute + 1;


        $affectedRows = parent::updateByPk($pk, $attributes, $condition, $params);

        if ($affectedRows != 1) {

            throw new StaleObjectError(Yii::t('app', 'Data has been updated by another user'));

        }

        $this->$lockingAttribute = $this->$lockingAttribute + 1;

        return $affectedRows;

	}


    /**

     * Overrides parent implementation to add object version check during delete

     * @param mixed $pk primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value).

	 * @param mixed $condition query condition or criteria.

	 * @param array $params parameters to be bound to an SQL statement.

	 * @return integer the number of rows deleted

     */

	public function deleteByPk($pk,$condition='',$params=array())

	{

		$this->applyLockingCondition($condition);

        $affectedRows = parent::deleteByPk($pk, $condition, $params);

        if ($affectedRows != 1) {

            throw new StaleObjectError(Yii::t('app','Data has been updated by another user'));

        }

        return $affectedRows;

	}


    /**

     * Adds check for object version to the specified condition and increments version

     * @param string $condition initial condition

     */

    private function applyLockingCondition(&$condition) {

        $lockingAttribute = $this->getlockingAttribute();

        $lockingAttributeValue = $this->$lockingAttribute;


        if (!empty($condition)) $condition .= ' and ';

        $condition .= "$lockingAttribute = $lockingAttributeValue";

    }


}


/**

 * Exceprion class for optimistic locking exception

 */

class StaleObjectError extends CDbException {

}



How to use OptimisticLockingActiveRecord:

  1. Inherit your model from OptimisticLockingActiveRecord class



class MyModel extends OptimisticLockingActiveRecord

{

...

}



  1. Add optimistic locking field to the update form view file:



<?php $form=$this->beginWidget('CActiveForm'); ?>

    <?php echo $form->hiddenField($model, 'lock_version'); ?>

    <?php echo $form->error($model, 'lock_version'); ?>

    ...



  1. Handle StaleObjectError in the controller:



...

    if (isset($_POST['MyModel'])) {

        $model->attributes = $_POST['SiteSettingsData'];

	$model->lock_version = $_POST['MyModel']['lock_version'];


        try { 

            $model->save();

        } catch(StaleObjectError $e) {

	    $model->addError('lock_version', 

		Yii::t('app', 'Site settings have been updated by another user'));

	}

...



It works like a charm! Many thanks for this great addition :)

I think this is exactly what im looking for, but im having trouble with know where to save what… (im not quite the Noob i was a couple of weeks ago though :lol: )

Im using the default structure…

Do i save the Class in /protected/components? and does it have to be called OptimisticLockingActiveRecord.php ?

Also, in my Model, i already have class Leads extends CActiveRecord so not sure if im meant to replace that with class Leads extends OptimisticLockingActiveRecord

And then, where in my LeadsController do i put:




...

    if (isset($_POST['MyModel'])) {

        $model->attributes = $_POST['SiteSettingsData'];

        $model->lock_version = $_POST['MyModel']['lock_version'];


        try { 

            $model->save();

        } catch(StaleObjectError $e) {

            $model->addError('lock_version', 

                Yii::t('app', 'Site settings have been updated by another user'));

        }

...

I hate having to ask, but im getting the hang of Yii…

p

Yeah, all the answers are "yes" except for the last one.




...

    if (isset($_POST['MyModel'])) {

        $model->attributes = $_POST['MyModel'];

        $model->lock_version = $_POST['MyModel']['lock_version'];

        try { 

            $model->save();

        } catch(StaleObjectError $e) {

            $model->addError('lock_version', $e->getMessage());

        }

...



Replace ‘MyModel’ with your model name, ‘Leads’ I guess.

seb seems to have forgotten the last touch on his great example code … ::)

I forgot to mention that I want this feature included in the core, too.

With ‘lock_version’ hidden field automatically added in an active form, like ‘csrf’ hidden field. :)

I’m getting blank page on update… and nothing is appearing in the lock_version column…




public function actionUpdate($id)

  {

    $model=$this->loadModel($id);


    // Uncomment the following line if AJAX validation is needed

    // $this->performAjaxValidation($model);


    /*if(isset($_POST['Leads']))

      {

        $model->attributes=$_POST['Leads'];

        if($model->save())

        $this->redirect(array('view','id'=>$model->id));

      }


      $this->render('update',array(

        'model'=>$model,

      ));*/


    if (isset($_POST['Leads'])) {

      $model->attributes = $_POST['Leads'];

      $model->lock_version = $_POST['Leads']['lock_version'];

        try { 

            $model->save();

        } catch(StaleObjectError $e) {

            $model->addError('lock_version', $e->getMessage());

    }

}

Very nice. Thanks! I’ve used this code as a basis and altered it a bit.

One comment though, for the record - updateByPk() accepts a $condition that can be either a string or a CDbCriteria. Your applyLockingCondition() works only with string. Fine for me but better be noted. In fact, in my base AR class I’ve documented this limitation and added a @TODO comment for this… :)

Thanks again!

any progress in this matter? :)

As I see from the issue tracker this feature is implemented in Yii2.