Using Transactions

For database environments that support transactions, the Database class provides helpful methods for starting, committing and rolling back database transactions.

A Simple Transaction

<?php
    $oDatabse->Begin();
 
    $sSQL = "UPDATE cars SET color = 'black' WHERE car_id = 14";
 
    if( $oDatabase->Query( $sSQL ) === null )
    {
        echo "Update failed: " . $oDatabase->GetLastError() . "<br />";
        $oDatabase->Rollback();
        exit;
    }
 
    $oDatabase->Commit();
?>

We start by beginning a transaction using the Begin() method. We then run an update query. If there was an error, we output a message and roll the transaction back using the Rollback()

method. And if the query updates successfully, we commit the transaction using the Commit() method.

A Word of Caution

Since our Database class employs a singleton implementation, each database object returned by Database::GetConnection() will be a reference to the same object when using the same database profile.

What this means for transactions is that if you request two database objects for the same profile, and invoke the Begin() method on one object, then the second object is also now in the same transaction.

To get around this, you could create two profiles with the same connection data and different names and use the optional argument to GetConnection() to get two unique objects to the same physical database.