PHP PDO Transactions and Auto-commit

PHP PDO Reference Manual

Now that you are connected via PDO, before you begin querying, you must first understand how PDO manages transactions.

Transactions support four major characteristics (ACID):

• Atomicity
• Consistency
• Isolation
• Durability

In plain terms, any operation executed within a transaction, even if executed in stages, is guaranteed to be safely applied to the database and, upon commit, will not be interfered with by other connections.

Transaction operations can also be automatically undone upon request (assuming they have not yet been committed), which makes handling errors in scripts easier.

Transactions are typically implemented by "accumulating" a batch of changes and then making them take effect simultaneously; the benefit of this is that it can greatly improve the efficiency of these changes.

In other words, transactions can make scripts faster and possibly more robust (though you need to use transactions correctly to gain such benefits).

Unfortunately, not every database supports transactions, so when the connection is first opened, PDO needs to run in what is called "auto-commit" mode.

Auto-commit mode means that, if the database supports it, every query executed has its own implicit transaction; if the database does not support transactions, then there is none.

If a transaction is needed, you must start it 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 serious error state).

Once a transaction has been started, it can be completed with PDO::commit() or PDO::rollBack(), depending on whether the code within the transaction ran successfully.

Note:PDO only checks at the driver level whether it has transaction processing capability. If certain runtime conditions mean that transactions are unavailable, and the database service accepts the request to start a transaction, PDO::beginTransaction() will still return TRUE with no error. Trying to use transactions in a MyISAM table in a MySQL database is a good example.

When the script ends or the connection is about to be closed, if there is still an unfinished transaction, PDO will automatically roll back that transaction. This safety measure helps avoid inconsistencies when a script terminates unexpectedly — if the transaction was not explicitly committed, it is assumed that something went wrong somewhere, so a rollback is performed to ensure data safety.

Note:Automatic rollback can only occur after a transaction is started via PDO::beginTransaction(). If you manually issue a query to start a transaction, PDO cannot know about it, and therefore cannot perform a rollback when necessary.

Executing batch processing within a transaction:

In the example below, suppose we create a set of entries for a new employee, assigning an ID of 23. In addition to registering this person's basic data, we also need to record his salary.

The two updates are simple to complete separately, but by enclosing them within PDO::beginTransaction() and PDO::commit() calls, you can guarantee that others cannot see these changes before 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; you can also issue complex queries to extract data, and use that information to build more changes and queries. When a transaction is active, you can be assured that others cannot make changes while the operation is in progress.

PHP PDO Reference Manual