Zend PHP 7 Certification – Databases – Transactions

This post covers the Transactions section of the Databases chapter when studying for the Zend PHP 7 Certification.

Transactions in PDO can be explained as: Any work carried out in a transaction is guaranteed to be applied to the database safely and without interference from other connections when it is committed. Transactional work can also be automatically undone at your request (provided you haven’t already committed it), which makes error handling in your scripts easier.

Here is a summarised list of points regarding transactions.

  • Combines individual SQL operations into one.
  • Using start with beginTransaction().
  • The transaction is executed using commit().
  • To cancel the transaction, you can use rollBack().

A code example of initiating a transaction using the beginTransaction() method can be seen below.

<?php
$dbh = new PDO("mysql:host=hostname;dbname=dbname", 'user', 'password');
$dbh->beginTransaction();

Database updates should then be enclosed between the beginTransaction() method and the commit() method. By doing it this way, we are guaranteeing that no one else will be able to see those changes until they are complete.

<?php
$dbh = new PDO("mysql:host=hostname;dbname=dbname", 'user', 'password');
$dbh->beginTransaction();

// Some queries
$dbh->exec("A query in here");
$dbh->exec("Another query in here");

// Commit
$dbh->commit();

Usually users will wrap transactions in a try-catch block, as if any code within the try block fails, a rollback can be performed using the rollBack() method.

The below represents a try-catch block where if something goes wrong, the catch block rolls back all changes made since the transaction was started and then prints out an error message.

<?php
$dbh = new PDO("mysql:host=hostname;dbname=dbname", 'user', 'password');
try {
    $dbh->beginTransaction();

    // Some queries
    $dbh->exec("A query in here");
    $dbh->exec("Another query in here");

    // Commit
    $dbh->commit();
} catch (Exception $e) {
    $dbh->rollBack();
    echo "Failed: " . $e->getMessage();
}

It is worth noting that MySQL MyISAM tables do not support transactions but will also not return an error when using them.

In addition, some databases such as MySQL automatically issue a COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction, so these cannot be used in a rollback scenario.

Of course, you’re not limited to updating data and so can perform queries to extract data should you need do. The beauty is that while the transaction is active, you are guaranteed that other connections to the database are not interfered with.

Note: This article is based on PHP version 7.1.