PHP: PDO transaction example.

This is a tutorial on how to use transactions with the PDO object in PHP.

For those who don’t know; a database transaction represents a “block” or a “unit” of work. In most cases, this “unit” of work will consist of multiple queries that are somehow related to one another.

In certain scenarios, you might want to make sure that all of your queries have executed successfully BEFORE you commit the changes to your database.

For this example, we’re going to create a mock PHP application that adds credit or money to a user’s account. In this case, we have two queries. The first one will insert a payment record into a table called “payments” and the second query will update the balance of the user’s account.

/**
 * Connect to MySQL and instantiate the PDO object.
 * Set the error mode to throw exceptions and disable emulated prepared statements.
 */
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
));


//We are going to assume that the user with ID #1 has paid 10.50.
$userId = 1;
$paymentAmount = 10.50;


//We will need to wrap our queries inside a TRY / CATCH block.
//That way, we can rollback the transaction if a query fails and a PDO exception occurs.
try{

    //We start our transaction.
    $pdo->beginTransaction();


    //Query 1: Attempt to insert the payment record into our database.
    $sql = "INSERT INTO payments (user_id, amount) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $userId, 
            $paymentAmount,
        )
    );
    
    //Query 2: Attempt to update the user's profile.
    $sql = "UPDATE users SET credit = credit + ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $paymentAmount, 
            $userId
        )
    );
    
    //We've got this far without an exception, so commit the changes.
    $pdo->commit();
    
} 
//Our catch block will handle any exceptions that are thrown.
catch(Exception $e){
    //An exception has occured, which means that one of our database queries
    //failed.
    //Print out the error message.
    echo $e->getMessage();
    //Rollback the transaction.
    $pdo->rollBack();
}

In this example, we want to make sure that all of our queries are successful before we commit the changes.

In other words, we do not want to insert a record into the payments table if the second query fails to update the user’s credit balance.

An explanation of the code.

A step-by-step explanation of the PHP code above.

  1. Firstly, we connect to MySQL using the PDO object. We set the error mode to PDO::ERRMODE_EXCEPTION so that PHP will throw a PDO Exception if an error occurs. We also disabled emulated prepared statements by setting PDO::ATTR_EMULATE_PREPARES to false.
  2. For the sake of this example, we set the User ID to 1 and the payment amount to 10.50.
  3. We start off by opening our Try / Catch block. All of our queries will occur inside of this “Try block” so that we can catch any exceptions that may occur.
  4. We begin our transaction inside the Try block by calling the beginTransaction function. As soon as we call this function, the autocommit mode for MySQL will be set to 0 (by default, this is set to 1). This means that MySQL will not commit any changes until we tell it to.
  5. We insert the payment into our payments table.
  6. We then update the user’s account with their new credit count.
  7. Finally, we commit the transaction by calling the commit function.

If one of our queries fail, a PDO exception will be thrown and the code inside of our catch block will be executed. This means that MySQL will abort the transaction and roll back any changes.

Transaction terms.

A quick explanation of some of the key terms.

  • Begin transaction: The transaction begins by disabling the default autocommit nature of MySQL. For example, if you run an INSERT query, MySQL will not commit the change. Instead, it will “hold” the query until you manually commit it.
  • Commit: When you commit a transaction, you are basically telling MySQL that it should go ahead and save any changes. It is another way of saying “Everything went fine. You can save those changes now.”
  • Rollback: When you perform a rollback, you are telling MySQL to abort the transaction and discard any changes. Typically speaking, a rollback will only occur if a query fails.