Using transactions

Archives Yii 1.0.x Discussions
1

I use MySQL with InnoDB tables.

I have a DB structure (see attached).

I want to delete comment record. So I should:

  • delete CommentRating records

  • delete Rating corresondent record

  • delete ProductComment record

  • delete LookComment record

  • finally delete Comment record

Here is what I wanted to do:



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


			try


			{


				$sql = "SELECT ratingID FROM CommentRating cr WHERE cr.commentID=:cID";


				$cmd = Yii::app()->db->createCommand($sql);


				$cmd->bindValue(':cID', $comment->commentID, PDO::PARAM_INT);


				$ids = $cmd->queryColumn();


				Yii::app()->db->createCommand('DELETE FROM CommentRating WHERE commentID='.$comment->commentID)->execute();


				Yii::app()->db->createCommand('DELETE FROM ProductComment WHERE commentID='.$comment->commentID)->execute();


				Yii::app()->db->createCommand('DELETE FROM LookComment WHERE commentID='.$comment->commentID)->execute();


				Yii::app()->db->createCommand('DELETE FROM Rating WHERE ratingID IN('.implode(',',$ids).')')->execute();


				$comment->delete();


			}


			catch (CException $e)


			{


				$txn->rollBack();


			}

But it doesn't work! I guess, because when using transaction it doesn't actually remove rows and so when I delete Rating or Comment records, it fails with constraint check.

When I remove transaction handling, it works OK.

When I was using MSSQL, such things worked fine. Is it MySQL’s bugs or Yii’s or mine? :)

2

From Class reference:

The following code is a common scenario of using transactions:

try


{


   $transaction=$connection->beginTransaction();


   $connection->createCommand($sql1)->execute();


   $connection->createCommand($sql2)->execute();


   //.... other SQL executions


   $transaction->commit();


}


catch(Exception $e)


{


   $transaction->rollBack();


}
3

No, mine is more correct because if you have an exception in

  $transaction=$connection->beginTransaction();

then the line

  $transaction->rollBack();

will fail also, because there will not be correct $transaction instance.

Hower, I really forgot abut $txn->commit() :)

4

Yeah, there was a problem in $txn->commit():)

How could I be so inattentive!

Thanks a lot for help!

5

Quote

...if you have an exception in

  $transaction=$connection->beginTransaction();…

So the problem is with the catchall exception.

6

Yes, normally you should enclose everything into try…catch.

However, doing like you showed is really incorrect. I printed docs from PDF, transaction starts before the first try

7

From http://www.yiiframew…i/CDbConnection



$transaction=$connection->beginTransaction();


try


{


   $connection->createCommand($sql1)->execute();


   $connection->createCommand($sql2)->execute();


   //.... other SQL executions


   $transaction->commit();


}


catch(Exception $e)


{


   $transaction->rollBack();


}

and from http://www.yiiframew…/CDbTransaction



try


{


   $transaction=$connection->beginTransaction();


   $connection->createCommand($sql1)->execute();


   $connection->createCommand($sql2)->execute();


   //.... other SQL executions


   $transaction->commit();


}


catch(Exception $e)


{


   $transaction->rollBack();


}