php - PDO Exception for each statement VS. Once for transaction? -

i'm implement transactions in php scripts , i'm doing testing myself understand how work. have following code snippet:

try{     $db->begintransaction();      $update = "update persons set first_name = 'adam' person_id = 4";     $stmt = $db->exec($update);       $select = "select person_id, column_that_doesnt_exist persons";      try{         $stmt = $db->prepare($select);         $stmt->execute();         $rows = $stmt->fetchall(pdo::fetch_assoc);         echo json_encode('success');     } catch (pdoexception $e) {         echo 'execute failure';     }      echo ' code gets here';      $db->commit(); } catch(pdoexception $e){     $db->rollback();     echo json_encode('commit failure'); } 

which outputs:

execute failure code gets here

and person 4's first name updated adam.

now i'm pretty sure it's getting committed because second query never failed, because never executed since prepare point of failure.

it nice if pdoexception thrown last catch since 1 thrown in "inner" try can work around that.

now if take out "inner" try , have code:

try{         $db->begintransaction();          $update = "update persons set first_name = 'adam' person_id = 4";         $stmt = $db->exec($update);           $select = "select person_id, column_that_doesnt_exist persons";               $stmt = $db->prepare($select);             $stmt->execute();             $rows = $stmt->fetchall(pdo::fetch_assoc);           $db->commit();     } catch(pdoexception $e){         $db->rollback();         echo json_encode('commit failure');     } 

the commit fails, db rolled , outputs commit failure expected.

so in production, should not wrap each individual statement in try-catch, , instead throw of statements inside 1 big try block (transaction), catch commit exception @ end? doesn't seem right me....and likewise wouldn't give info statement failed....

you catch exception using inner try..catch block, without rethrowing it. means that exception handled, , code continues if nothing went wrong, committing transaction.

normally not desired solution, since transactions used make combination of multiple data modifying statements atomic. using transaction, can commit if went well, or rollback when went wrong.

also, since there nothing commit or rollback when starting transaction fail, should pull out of exception handling. proper structure be:

starttransaction; try {   modifydata;   committransaction; } catch {   rollbacktransaction;   // log/mail/show/ignore error } 

if want continue inserting records if inserting 1 of them fails, don't need transaction.

if want specific information item failed, can rethrow exception or throw new one:

starttransaction; try {    foreach ($persons $person) {     try {       modifyperson($person);     }     catch {       throw new exception("updating person {$person->name} failed");     }   }    committransaction; } catch {   rollbacktransaction;   // log/mail/show/ignore error } 

by (re) throwing exception inner exception handler, jump directly outer exception handler, terminating loop, , bypassing commit.