YouTip

# PHP PDO Transactions and Auto-commit [PHP PDO Reference](#) Now that you're connected via PDO, before you start querying, you must first understand how PDO manages transactions. Transactions support four key properties (ACID): * Atomicity * Consistency * Isolation * Durability In simple terms, any operations performed within a transaction, even if executed in stages, are guaranteed to be applied safely to the database and will not be interfered with by other connections upon commit. Transaction operations can also be automatically rolled back upon request (assuming they haven't been committed yet), making error handling in scripts easier. Transactions are typically implemented by "accumulating" a batch of changes and then applying them all at once; the benefit of doing this is that it can greatly improve the efficiency of these changes. In other words, transactions can make scripts faster and potentially more robust (though you need to use transactions correctly to gain these benefits). Unfortunately, not all databases support transactions, so when you first open a connection, PDO needs to operate in what is called "auto-commit" mode. Auto-commit mode means that, if the database supports it, every query you run has its own implicit transaction; if the database does not support transactions, there is none. If you need a transaction, you must start it using the `PDO::beginTransaction()` method. If the underlying driver does not support transactions, a `PDOException` is thrown (regardless of error handling settings, this is a severe error state). Once a transaction has started, you can complete it using `PDO::commit()` or `PDO::rollBack()`, depending on whether the code within the transaction executed successfully. **Note:** PDO only checks for transaction capability at the driver level. If certain runtime conditions mean a transaction is unavailable, but the database service accepts a request to start a transaction, `PDO::beginTransaction()` will still return `TRUE` without error. Trying to use transactions on a MyISAM table in a MySQL database is a good example. When a 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 inconsistent states when a script terminates unexpectedly—if a transaction hasn't been explicitly committed, it's assumed something went wrong, so a rollback is performed to ensure data safety. **Note:** Automatic rollback can only occur after a transaction has been started via `PDO::beginTransaction()`. If you manually issue a query that starts a transaction, PDO is unaware of it and therefore cannot roll it back if necessary. Executing a batch operation within a transaction: In the following example, assume you are creating a set of entries for a new employee, assigning an ID of 23. Besides registering this person's basic data, you also need to record their salary. The two updates are simple to complete individually, but by enclosing them within `PDO::beginTransaction()` and `PDO::commit()` calls, you ensure that no one else can see these changes until they are complete. If an error occurs, the catch block rolls back all changes made since the transaction started and outputs an error message. true)); echo "Connectedn";} 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();}?> You are not limited to making changes within a transaction; you can also issue complex queries to retrieve data and use that information to construct more changes and queries. While a transaction is active, you can be assured that others cannot make changes while your operations are in progress. * * PHP PDO Reference](#)