Multiple AR transactions

Yii 1.1 General Discussion
1

I can’t create transactions on multiple Active Records.

Example 1. Don’t execute catch block:




$transaction = Yii::app()->db->beginTransaction();

$model = User::model()->findAll();

try {

     $i=0;

     foreach (model  as $row){

                        if($i++==1){

                            $row->status = 'A';//can be only integer, must rollback!!!

                        } else {

                            $row->status = '1';

                        }                                                

                        $row->save();                        

                    }

                    $transaction->commit();

                } catch (Exception $ex) {

                    $transaction->rollback();

                    die('FAILED!!!');

                }

Example 2. Execute catch block, but save all rows in DB, execpt when $i==1:




$transaction = Yii::app()->db->beginTransaction();

$model = User::model()->findAll();

try {

     $i=0;

     foreach (model  as $row){

                        if($i++==1){

                            $row->status = 'A';//can be only integer, must rollback!!!

                        } else {

                            $row->status = '1';

                        }                                                

                        if(!$row->save()){

                           $transaction->rollback();                          

                        }                     

                    }

                    $transaction->commit();

                } catch (Exception $ex) {

                    die('FAILED!!!');

                }

I tried also some other solutions, but without any success. What I am doing wrong and how it is possible to rollback rows that is already saved if one of rows can’t save?

2

Do you use InnoDB or BDB transactional tables? With MyISAM is not possible… Also your logic should be this way:




$transaction = Yii::app()->db->beginTransaction();

$model = User::model()->findAll();

      try {

        $i=0;

             foreach (model  as $row){

                        if($i++==1){

                            $row->status = 'A';//can be only integer, must rollback!!!

                        } else {

                            $row->status = '1';

                        }                                                

                        $row->save();                        

                    }

                   // commit transactions now

                   $transaction->commit();

           } catch (Exception $ex) {

                    $transaction->rollback();

                    die('FAILED!!!');

                }

if you wish to rolloback if save() validation isn’t successful, then just include your second but throw an error




if(!$row->save()){

      // by throwing the exception we will go directly to catch

      // we can even get the errors of the model that wasn't saved

      throw new CException('Transaction failed: ');

}
3

I have MyISAM. Is there any other possibilities to use transactions if I have MyISAM?

4

No, as far as I know…

5

I’m facing the same problem here on Yii2.

I’m using MyISAM, but I should be able to change it.

Should I change it to InnoDB?

The options available for me are:

  • MEMORY

  • CSV

  • BLACKHOLE

  • MyISAM

  • MRG_MyISAM

  • ARCHIVE

  • FEDERATED

  • InnoDB

  • Aria

Does changing to InnoDB the only thing I need to solve this problem?

Edit:

Solved it by changing all my table to use InnoDB engine.