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;
}