How to put begin-commit transaction in controller: cakephp?

Question

I'm working on a controller that will update a few tables. I am able to call my model from my controller and inside the model function I can make a begin and commit my query, it can rollback should an error happen.

Here is my sample:

Controller:

//update table when update button is clicked
    if (!empty($this->data)) {
        if ($this->Item->update($this->data)) {
            $this->Item->create();
            $this->redirect('/sample');
            return;
        } else {
            $this->set('data', $this->data);
        }
    }

Model:

function update($data)
{
    $this->begin($this);
    if(!parent::save($data)) {
        $this->rollback($this);
        return false;
    }

    $this->commit();
    return true;
}

Now this works fine. But what I need to do is to call another model in my controller like "$this->"ANOTHER MODEL HERE"->update()". I need to have rollback should a problem occur with either model transaction. What I'm thinking is to put a commit in my controller after both model call succeeds.

Much like this:

CONTROLLER PHP:
  BEGIN TRANSACTION
    ->CALLS MODEL1
        IF(MODEL1 == ERROR){
             ROLLBACK
        }
    ->CALLS MODEL2
        IF(MODEL2 == ERROR){
             ROLLBACK
        }
   COMMIT WHEN NO PROBLEM IS ENCOUNTERED

So is it possible to perform commit in controller? I am only able to do it in model. Thanks in advance!

Answer 1

So is it possible to perform commit in controller? I am only able to do it in model.

Yes, you can perform commit or rollback from within the controller. You need to get the datasource from one of your models first. In the controller code, simply reference one of the models you are using (assuming they are all in the same database):

$ds = $this->MyModelName->getdatasource();

Then you can begin, commit, and rollback to that datasource from within the controller.

$ds->begin();

// do stuff and save data to models

if($success)
{
    $ds->commit();
}
else
{
    $ds->rollback();
}

I actually have a rollback or commit in more than one place if I am bailing on the action and redirecting or finalizing in some step and redirecting. I just illustrate a simple case here.

Handling transactions in the controller makes the most sense to me since the controller action is where the transaction boundaries really reside conceptually. The idea of a transaction naturally spans updates to multiple models. I have been doing this using postgres as the back end database with Cake 2.2 and 2.3 and it works fine here. YMMV with other db engines though I suspect.

Answer 2

Trasactions are to be enhanced in futures versions of CakePHP, as you can see in this CakePHP Lighthouse ticket.

There are two possible solutions proposed there, and I am showing you a third one. You could create a custom method to save it, and manually commit the transactions:

public function saveAndUpdate($data) {
    $ds = $this->getDataSource();
    $ds->begin();
    if ($this->save($data)) {
        foreach(Array('Model1', 'Model2') as $model) {
            if (!ClassRegistry::init($model)->update()) {
                $db->rollback();
                return false;
             }
         }
         return $db->commit() !== false;
     }
     return false;
 }

I wrote this code to illustrate how I though about your problem, although I didn't test.

More useful links:

• Transactions at CakePHP Book
• About CakePHP Behaviors
• How to create Behaviors

Answer 3

I used commit within my if statements and rollback in my else statements. Since I was using two different models from within a controller, I created two different datasources

        $transactiondatasource = $this->Transaction->getDataSource();
        $creditcarddatasource = $this->Creditcard->getDataSource();
        $transactiondatasource->begin();
        $creditcarddatasource->begin();

        if (CONDITION){
                $creditcarddatasource->commit();
                $transactiondatasource->commit();
                CakeSession::delete('Cart');
            } else {
                $this->Session->setFlash(__('MESSAGE'));
                $creditcarddatasource->rollback();
                $transactiondatasource->rollback();
            }