Saving Models Using Transaction

Yii 1.1 General Discussion
1

I’m having troubles using transactions. I want to use transactions for saving all or none record to the database but if one model can be saved and the other fails then the successful model stays in database. It seems no rollback is done.

Here is an example causing the error:




$model1 = new User();

$model1->username = "user1@localhost.com";


$model2 = new User();

$model2->username = NULL;


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

try{


	if(!$model1->save()){ // can be saved normally

		throw new CHttpException(null, "model1 could not be saved");

	}


	if(!$model2->save()){ //should fail on validation and rollback transaction

		throw new CHttpException(null, "model2 could not be saved");

	}

	$transaction->commit();

}

catch(Exception $e){

	$transaction->rollBack();

	throw new CHttpException(null,"catch transaction, ".$e->getMessage());

}

I’m using a MySQL database and InnoDB tables.

2

Hi sandro

did you check if the exception message "catch transaction…" is displayed in this case?

3

Hey KonApaz, yes. I’m actually getting the following exception:


catch transactionmodel, 2 could not be saved
4

Try this code


$model1 = new User();

$model1->username = "user1@localhost.com";


$model2 = new User();

$model2->username = NULL;


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

$isok = true;


        if(!$model1->save()){ // can be saved normally

               $isok = false;

        }


        if(!$model2->save()){ //should fail on validation and rollback transaction

               $isok = false;

        }

        if ($isok == true) {

            $transaction->commit();

            echo 'successful!';

        } else {

        $transaction->rollBack();

           echo 'something wrong!';

        }
5

I tried it but unfortunately the result is the same. $model1 is saved to the database…

May be the problem is related to some database configuration which prevents transactions. But I have no idea what could be wrong…

6

Are you certain that the table is InnoDB? It’s worth double checking.

7

I just checked it again and would have been happy if I would have been wrong ;) … BUT actually it is InnoDB.

8

See if the information on this page helps. PDO might be auto committing.

9

Also, this page suggests that it might be possible for the MySQL installation to become corrupt in some way. Reinstalling MySQL might fix your problem.

10

Thanks for your hints Keith but I still could not identify the problem. I read about your first post and tried the example from the second post… I tried to query the database directly in phpmyadmin and it worked. I used exactly the same example given in the second post and it worked. So there must be something wrong in the way the transaction is done from the application side. Do you have another hint what could be the problem there?

11

Your code is OK and should work, are you sure your model is using the "db" connection ?

12

Hi Maurizio. What do you mean with using this db connection? The table I’m creating records in is located in this database I’m using for my transaction. Or is it possible that something is wrong with my connection?

13

You can have multiple database connections configured in the config like




'db' => array(

   'connectionString' => 'mysql:host=localhost;dbname=test',

   ...

,

'db1' => array(

   'class'=>'CDbConnection',

   'connectionString' => 'mysql:host=localhost;dbname=anothertest',

   ...

),

So my idea was that you are starting the stransaction on "db" but the model uses another connection like "db1"… but from your answer I guess this is not the problem here…

14

Ah okay, no this is not the problem in my case. Actually I’m using five databases but I checked whether I’m using the right connection.