Transaction in PHP: from Mysqli to PDO and Doctrine transaction handling

Starting from ACID properties, in the previous post, we have seen how transactions are handled by JDBC and by the abstracted layers written using JAVA as a programming language. What does it change if we are going to use PHP? How many layers and methodologies are provided? Let’s figure it out together!

Mysqli: vendor specific extension for MySQL DBMS

<?php

$mysqlConnection = new mysqli("localhost", "user", "password", "world");

/* Start transaction */
$mysqli->begin_transaction(); 

try {
    /* Insert some values */
    $mysqlConnection->query("INSERT INTO language(Code, Speakers) VALUES ('EN', 42000123)");
    $mysqlConnection->query("INSERT INTO language(Code, Speakers) VALUES ('DE', 42000124)");
    $mysqlConnection->query("INSERT INTO language(Code, Speakers) VALUES ('IT', 42000000)");

    /* If code reaches this point without errors then commit the data in the database */
    $mysqlConnection->commit();

} catch (mysqli_sql_exception $exception) {
    $mysqlConnection->rollback();
    throw $exception;
}

?>

In the code above, we can read a transaction example written using the mysqli extension. With the instruction:

$mysqlConnection->begin_transaction();

We are going to start a new and explicit transaction with MySQL DBMS. With the instruction:

$mysqlConnection->commit();

We are going to commit the current transaction for the database connection defined with the instruction:

new mysqli("localhost", "user", "password", "world");

The block try-catch is going to handle any exception related to mysqli_sql_exception: if we encounter this exception, we’re going to roll back all the operations submitted using the instruction:

$mysqlConnection->rollback();

But what happens behind the scenes? How are these operations translated on the DBMS side? Let’s see how the above code, written in PHP, is going to be solved on the DBMS side:

START TRANSACTION;
INSERT INTO language(Code, Speakers) VALUES ('EN', 42000123)
INSERT INTO language(Code, Speakers) VALUES ('DE', 42000124)
INSERT INTO language(Code, Speakers) VALUES ('IT', 42000000)
COMMIT/ROLLBACK;

With START TRANSACTION, autocommit remains disabled until the end of the transaction obtained with COMMIT or ROLLBACK. The autocommit mode then returns to the previous state. So, note that we can perform transactions without disabling autocommit explicitly.

Note that some statements cannot be rolled back. These include data definition languages (DDL) statements, such as those that create or drop databases and those that create, drop, or alter tables or stored routines.

If we use the following function on the PHP side:

public mysqli::autocommit(bool $enable): bool

We can switch on/off the autocommit explicitly. On the DBMS side, this is equivalent to this command:

SET autocommit=0/1.

The example that we have seen together is about MySQL DBMS. PHP is offering different extensions for other specific vendors. Here is the official and complete list.

PHP Data Objects (PDO) extension

With the following example, we are going to write the same transaction, already seen before, using the PDO extension:

<?php

try {
    $dbConnection = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    echo "Connected\n";
} catch (Exception $e) {
    die("Unable to connect: " . $e->getMessage());
}

try {
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbConnection->beginTransaction();
} catch (PDOException $e){
    die("Transactions are not supported by this driver: " . $e->getMessage());
}

try {  

    $dbConnection->exec("INSERT INTO language(Code, Speakers) VALUES ('EN', 42000123)");
    $dbConnection->exec("INSERT INTO language(Code, Speakers) VALUES ('DE', 42000124)");
    $dbConnection->exec("INSERT INTO language(Code, Speakers) VALUES ('IT', 42000000)");
  
    $dbConnection->commit();
  
} catch (Exception $e) {
    $dbConnection->rollBack();
    echo "Failed: " . $e->getMessage();
}

?>

With the PDO::beginTransaction() method, we will initiate a transaction. If the underlying driver does not support transactions, a PDOException will be thrown. Once in a transaction, we may use PDO::commit() or PDO::rollBack() to finish it, depending on the success of the code you run during the transaction.

Doctrine ORM transaction demarcation

Doctrine is an Object Relational Mapper for PHP 7.1+ that provides transparent persistence for PHP objects. It uses the Data Mapper pattern, aiming for a complete separation of your domain/business logic from the persistence in an RDBMS.

Doctrine ORM already takes care of the proper transaction demarcation for you: all write operations (INSERT / UPDATE / DELETE ) are queued until EntityManager#flush() is called, which wraps all these changes into one single transaction.

However, Doctrine ORM also allows (and encourages) you to assume and control transaction demarcation yourself.

Implicit handling

<?php
$item = new Item;
$item->setName('Phone');
$entityManagerInstance->persist($item);
$entityManagerInstance->flush();

The transaction will be committed or rollbacked when the EntityManager#flush() method is called. It is possible to have this behaviour if all the DML operations are made through the ORM and the domain model.

When using implicit transaction demarcation and an exception occurs when the method EntityManager#flush() is called, the transaction is automatically rolled back, and the EntityManager is closed.

Explicit handling

<?php
$entityManagerInstance->getConnection()->beginTransaction(); // suspend auto-commit
try {
    $item = new Item;
    $item->setName('Phone');
    $entityManagerInstance->persist($item);
    $entityManagerInstance->flush();
    $entityManagerInstance->getConnection()->commit();
} catch (Exception $e) {
    $entityManagerInstance->getConnection()->rollBack();
    throw $e;
}

Using the Doctrine\DBAL\Connection API is possible to create an explicit transaction. We can suspend the auto-commit with the method EntityManager#getConnection#beginTransaction() and start a new explicit transaction.

With the EntityManager#getConnection#commit(), the transactions’ operations can be explicitly flushed in the database. Otherwise, processes can be rollbacked in case of errors with the method EntityManager#getConnection#rollback().

Thanks for reading. Stay tuned!

Learn. Grow. Teach.