PHP PDO Transactions and Auto-Commit
Now that a connection has been established via PDO, it is essential to understand how PDO manages transactions before starting any queries.
Transaction support is characterized by four key properties (ACID):
Atomicity
Consistency
Isolation
Durability
In simple terms, any operations performed within a transaction, even if executed in stages, are safely applied to the database and are protected from interference by other connections upon commit.
Transaction operations can also be automatically rolled back (assuming they have not been committed yet), making error handling easier in scripts.
Transactions are typically achieved by "accumulating" a batch of changes and then applying them simultaneously; this can significantly improve the efficiency of these changes.
In other words, transactions can make scripts faster and potentially more robust (though this benefit is contingent on the correct use of transactions).
Unfortunately, not all databases support transactions, so PDO needs to operate in a so-called "auto-commit" mode when the connection is first opened.
Auto-commit mode means that each query, if the database supports it, runs within its own implicit transaction, or none if the database does not support transactions.
If a transaction is needed, it must be initiated with the PDO::beginTransaction() method. If the underlying driver does not support transactions, a PDOException is thrown (regardless of the error handling settings, this is a severe error state).
Once a transaction has started, it can be completed with PDO::commit() or PDO::rollBack(), depending on whether the code within the transaction executed successfully.
Note: PDO only checks for transaction capabilities at the driver level. If certain runtime conditions mean that a transaction is not available, and the database server accepts the request to start a transaction, PDO::beginTransaction() will still return TRUE without error. An example of this is trying to use transactions with a MyISAM table in a MySQL database.
When the script ends or the connection is about to be closed, if there is an unfinished transaction, PDO will automatically roll back that transaction. This safety measure helps avoid inconsistencies in the event of an unexpected script termination—if a transaction is not explicitly committed, it is assumed that something went wrong, so a rollback is performed to ensure data safety.
Note: Automatic rollback can only occur if a transaction is started via PDO::beginTransaction(). If a transaction is initiated manually with a query, PDO cannot be aware of it and thus cannot perform a rollback if necessary.
Executing batch operations within a transaction:
In the following example, assume a set of entries is being created for a new employee, assigned an ID of 23. In addition to recording basic data, their salary also needs to be logged.
While the two updates are simple to perform individually, enclosing them within PDO::beginTransaction() and PDO::commit() calls ensures that no one can see these changes until they are completed.
If an error occurs, the catch block rolls back all changes made since the transaction started and outputs an error message.
<?php
try {
$dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2',
array(PDO::ATTR_PERSISTENT => true));
echo "Connected\n";
} catch (Exception $e) {
die("Unable to connect: " . $e->getMessage());
}
try {
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
$dbh->exec("insert into salarychange (id, amount, changedate)
values (23, 50000, NOW())");
$dbh->commit();
} catch (Exception $e) {
$dbh->rollBack();
echo "Failed: " . $e->getMessage();
}
?>
It is not limited to making changes within a transaction; complex queries can also be issued to extract data, and this information can be used to construct further changes and queries. While a transaction is active, it ensures that no other operations can make changes in the meantime.