is $transaction->rollBack() required ?

I am new to transaction and have some fundamental question on how transaction should work.

Here is the sample transaction code in Yii2

$transaction = $connection->beginTransaction();

try {

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

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

    //.... other SQL executions

    $transaction->commit();

} catch (Exception $e) {

    $transaction->rollBack();

}

My question is what happened if I remove $transaction->rollBack() ? Will the database got stuck ?

I often use ActiveRecord with transaction and of course, an active record instance may not save due to validation fail. In that case, should I call $transaction->rollBack() before return ?

here is my code

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


try {

    $valid = TRUE;


    //some update query

    Yii::$app->db->createCommand('UPDATE sql')

        ->execute();


    // create new pay rate

    $new = new PayRate();

    $new->load(Yii::$app->request->post());

    $new->active_from = time();

    $new->created_by = Yii::$app->user->id;

    $valid = $valid && $new->save();

    

    if ($valid) {

        $transaction->commit();

        echo 1;

    } else {

        # do I require to have $transaction->rollBack() ?

        # Assume I have successfully execute the update sql above

        # PayRate save may fail if PayRate validation fails

    	$transaction->rollBack(); 

        echo 0;

    }

} catch (Exception $e) {

    $transaction->rollBack();

    echo 0;

}

Hi,

I’m also no expert on transactions I always do a rollback when a transaction failed.

… but I think you could optimize following in your code.

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


try {

    $valid = TRUE;


    ....


    $valid = $valid && $new->save();

    

    if ($valid) {

        $transaction->commit();

        echo 1;

    } else {

    	$transaction->rollBack(); 

        echo 0;

    }

} catch (Exception $e) {

    $transaction->rollBack();

    echo 0;

}

$new->save(); should be enough.

If it returns something else than "true" catch will be triggered and the commit will not happen.

I do follwing:

1. I validate before transaction starts.

2. Just try to save and link everything.

$valid = $modelA->validate(); 

$valid = $modelB->validate() && $valid; 

$valid = $modelC->validate() && $valid; 


if($valid){

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

    try{

        $modelA->save(); 

        $modelB->link('modelA', $modelA); 

        $modelC->link('modelB', $modelB); 

        $transaction->commit();

    }

   catch(\Exception $e){

        $transaction->rollBack();

   }

}


// form output with validation errors. 

return $this->render('create', [

  'modelA' => $modelA, 

  'modelB' => $modelB, 

  'modelC' => $modelC

]);

Not sure if this is best practise but for me this works without any problems.

When any of the above fails (for example the linking between models, or the saving…) the rollback does happen.

Best Regards

People say it’s not necessary but it’s a good practice - it’s clear what’s going on in your code and you are releasing potential locks.