Multiple AR transactions

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?

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: ');

} 

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

No, as far as I know…

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.