Transaction Processing

Because mySQLi defaults to error handling while PDO defaults to exception handling, the way in which we do transaction processing is somewhat different between the two. Also the PDO version has the begin_transaction method do a bit more for us in that it automatically turns off autocommit for us.

In each example the $db->... represents each database call within the transaction and with the mySQLi version each one needs to be wrapped as shown in order to handle any errors.

Here's the mySQLi version:

function myTransaction() {
 
if (!$db->... ) return false;
 
return true;
}
 
$db->autocommit(FALSE);
$db->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
myTransaction() ? $db->commit() : $db->rollback();

Here's the PDO version:

$db->beginTransaction();
try {
 
$db->...
 
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
}

In each case we begin a new transaction and then execute as many database calls as we need. We'll assume that at least one of these is going to try to write something to the database as otherwise there will not be anything to rollback if it fails. With the mySQLi version we need to put all of the calls inside of a separate function (which we can call whatever we like) so that the first database call to fail can easily exit without needing to even attempt the subsequent calls. With the PDO version try block takes care of this for us.

So what happens is that provided all of the database calls work the commit at the end makes all the changes to the database. If any of the calls fail and the rollback is run instead then none of the database updates get applied. This ensures that our database remains in a consistent state when all of the calls are interdependent.

Note that myisam does not support transaction processing so you need to convert all your tables to innodb if you want to use transactions.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate