PDO Transaction syntax with try catch

Question

What is the syntax preferred while using PDO transaction and try catch and Why?

$dbh->beginTransaction();
try {

} catch (Exception $e) {


}

OR

try {

$dbh->beginTransaction();

} catch (Exception $e) {

}

Answer 1

The existent answers seem to suggest that since $dbh->beginTransaction() could throw a PDOException it should be in the same try block of the actual transaction code, but this means that the rollBack() code itself will be wrong, because it could invoke a rollBack() without there being a transaction, which could also throw another PDOException.

The right logical ordering of this is that you put the code you want executed in one transaction in one catch block after the transaction has been created. You could also check that the return of beginTransaction() is true before proceeding. You could even check that the database session is in a transaction before calling rollback().

if ($dbh->beginTransaction()) 
{
  try 
  {
    //your db code
    $dbh->commit();
  } 
  catch (Exception $ex) 
  {
    if ($dbh->inTransaction())
    {
       $dbh->rollBack();
    }        
  }
}

Keep in mind that you could still, at least in theory, get an exception from beginTransaction() and rollBack() so I would put this in a separate function and enclose the invocation in another try-catch block.

You could also bubble the exception you get up to catch it and log all Exceptions in one place. But remember that some exceptions could be data integrity errors such as duplicate keys or invalid foreign keys, which would not be a database fault as such, but most probably a bug in your code.

With this approach, the main thing to keep in mind here is that the two try-catch blocks have a slightly different purpose. The inner one is purely to ensure that multiple queries are executed and committed atomically in one transaction and if something happens they are rolled back. The external try-catch would be to detect erroneous situations and log it, or whatever you would want to do if you have a problem with your database.