Do I need to use transaction?


I’m just working on the delete functionality for questions in my app.

A question will have one or more answers associated so I want to ensure

that I delete all associated answers before deleting the question.

Now this seems like an obvious case for a transaction but as I’m new to yii i’m not too clear on the best way to handle it.

I’m thinking of implementing my own beforeDelete and then either have a transaction to just delete the answers then if it succeeds just return true and if it fails rollback and return false. Does that sound right or is their a more ‘correct’ way to handle it in yii?



It is not YII. It is software development. It does not matter what u are using (Yii, CI). DO not use beforeDelete. It does not work for transaction. Transaction should be atomic, but with beforeDelete you can not make it atomic. when you call delete, yii calls before delete so you deleted all answers so now you committed transaction, it is time to delete question itself. But there is not guarantee that it gets deleted correctly. So this question becomes orphan without answers(none can answer LOL). you may it like that:

in model class:

function safeDelete(){

               //begin transaction

               //delete all items in loop


               //commit transaction 


There is another way. It is ultimate way for you i think. i would go with this way. On db level just put cascade = delete for foreign key(for answers table) so when you delete question then db itself deletes it is answers according to relationship. This is most secure way.

Thanks for the response.

Isn’t the cascade on delete only for inodb database? Mine isn’t inodb.

I like you’re idea about having a safe delete function. I guess that would

just hold the transaction for question and associated answers. Then instead

of calling the delete() function as it does now in the generated actionDelete

I’d call the safeDelete() function instead.





	public function safeDelete($id){

	    // delete transaction for 

	    // question and associated answers

        $connection = yii::app()->db;




            $sql1 = "DELETE FROM answers WHERE question_id = ".$id;

            $sql2 = "DELETE FROM questions WHERE id = ".$id;



            //.... other SQL executions



        catch(Exception $e) // an exception is raised if a query fails





IF it is not innodb engine then even transaction will not work. For checking just just fail by throwing and exception then u will see that rollback does not work. but u can change myIsam to innodb then it supports transaction

I haven’t done much with transactions so bear with me.

So you’re saying that even though the code I have appears to work that if the commit did fail the rollback would not actually kick in because it’s not innodb. So I have to change all the tables to innodb?

UPDATE: Just answered my own question by reading this overview of the pros and cons of each engine

I think since the application will be more search orientated and there will be very few updates/inserts I should stick with myisam. I guess I’ll just have to figure out how to handle data integrity on delete operations myself.

If I implement something like this in my safeDelete function and wrap it in a transaction will that suffice?



no need to convert all tables to innodb. just the tables that u are going to use with transaction