PHP, MySQL, PDO Transactions - Does the code inside try block stop at commit()?

Question 1

I'm pretty new to transactions.

Before, what I was doing was something like:

Code Block 1

$db = new PDO(...);

$stmt = $db->prepare(...);

if($stmt->execute()){
    // success
    return true;
}else{
    // failed
    return false;
}

But in an attempt to group multiple queries into a single transaction, I'm now using something like:

Code Block 2

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->beginTransaction();

try{
    $stmt = $db->prepare(... 1 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 2 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 3 ...);
    $stmt->execute();

    $db->commit();

    return true;
}catch(Exception $e){
    // Failed, maybe write the error to a txt file or something
    $db->rollBack();
    return false;
}

My question is: If the transaction fails for whatever reason, does the code stop at $db->commit(); and jump to the catch block? Or would the return true; run first, and then it would try to go to the catch? 'Cause if that's the case, then I've already returned, and so it wouldn't go to the catch. AND it would have returned the wrong value.

Do I still need to include something like:

Code Block 3

if($stmt->commit()){
    return true;
}

or is it sufficient the way I have it written in Code Block 2?

Question 2

If the transaction fails for whatever reason, the code does stop at the very line where error occurred end then the execution jumps directly to the catch block. So it is sufficient the way you have it written in Code Block 2.

Note that you should always re-throw the Exception after rollback. Otherwise you will never have an idea what was a problem. So it should be

try{
    $stmt = $db->prepare(... 1 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 2 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 3 ...);
    $stmt->execute();

    $db->commit();

    return true;
}catch(Exception $e){
    $db->rollBack();
    throw $e;
}

Question 3

Your first question:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.

Also worth noting (emphasis mine):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

This can cause racing conditions.

Your second question:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

From the documentation it says:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.

However note the following from the same source as well:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

So be sure to check the compatibility beforehand!

A few notes

Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.

Another note from the documentation:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Question 4

https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html says:

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

So when you set autocommit=0 in a session (call it session 1), this implicitly opens a transaction, and leaves it open indefinitely.

The default transaction isolation level is REPEATABLE-READ. So your session will not see a refreshed view of committed changes from other sessions' work until session 1 explicitly commits or rolls back.

Your LOCK TABLES in another session 2 does cause an implicit commit, but session 1 doesn't see the result because it's still only able to see an isolated view of the data because of its own transaction snapshot.

Question 5

(Upgrading to an answer)

Looks like this bug, which is still open after almost five years; try instead:

while (true) {
  try {
    $row = $qry_bat->fetch(PDO::FETCH_ASSOC);
    if (!$row) break;
    $ins_db->execute(array(...));
    $newOnes++;
  }
  catch (PDOException $e) {
    if ($e->getCode() != 23000) {
      echo '<span class="msg-alert">'.$e->getMessage().'</span>';
    } else {
      $doublons++;
    }
  }
}

Question 6

See my notes inline with the code:

    try{  
         $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));

         // this never executes because an Exception halts it here
         /*if($req == 0){
               $prepared_insertQry_toDuplicateRefTable->execute(array(something));
         }*/
     }
    catch(PDOException $e){
       // this catch grabs the exception and executes the code within instead
       // that code might log an error, echo the error message, or perform
       // alternative logic. In your case you want to execute alterntative logic
       // ie. your query
       $prepared_insertQry_toDuplicateRefTable->execute(array(something));

   }

PHP, MySQL, PDO Transactions - Does the code inside try block stop at commit()?

Answers

Your first question:

Your second question:

A few notes

php,mysql,pdo,transactions