Doctrine DBAL Transactions

Question

I don't really understand how to do transactions in DBAL

I have the following script going which updates a column depending on the id of the row. I put a fake id which doesn't exists in the table, (therefore making an update unable to occur) but the first updates are committed despite the fact that it's a transaction. I expect all the transactions to fail if one of them fails.

 $conn -> beginTransaction();
   try{
       $try = $conn->prepare("update table set column = '123' where id = 0"); //column exists
       $try->execute();
       $try = $conn->prepare("update table set column = '123' where id = 1"); //column exists
       $try->execute();
       $try = $conn->prepare("update table set column = '123' where id = 120"); //column does not exists
       $try->execute();

       $try = $conn->commit();
   }
   catch(Exception $e) {
       $try = $conn->rollback();
       throw $e;
   }

Expected results, no updates since row with id = 120 does not exist Real Results, All rows are updated except for the non existant row.

I apologize in advance but object oriented programming is still Antarctica to me.

Answer 1

I know this question is quite old, so i will explain things a little if someone encounters a similar problem in the future, because this behaviour is not an error.

$try = $conn->prepare("update table set column = '123' where id = 120"); //column does not exists
$try->execute();

here the update condition references a nonexistent column, so the query will not fail, it will update 0 (zero) rows; in Doctrine the number of affected rows is returned by the execute() method.

You can throw an exeception to trigger the rollback.

$try = $conn->prepare("update table set column = '123' where id = 120"); //column does not exists
$affected = $try->execute();
if ($affected == 0) {
  throw new Exception('Update failed');
}

Answer 2

This code rollbacks transaction only when Exception is thrown.

When update is not successful, it returns false, not the Exception.

You may try without Exceptions:

$try = $conn->commit();
if (!$try) {
   $conn->rollback();
}

or throw an Exception when result is false.