Now that you are connected through PDO, before you start querying, you must first understand how PDO manages transactions.
Transactions support four major characteristics (ACID):
Atomicity
Consistency
Isolation
Durability
In layman's terms, any operation performed within a transaction, even if performed in stages, is guaranteed to be safely applied to the database and committed without interference from other connections.
Transaction operations can also be automatically undone on request (assuming they haven't been committed yet), making it easier to handle errors in scripts.
Transactions are usually implemented by "accumulating" a batch of changes and making them effective at the same time; the advantage 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 (although transactions need to be used correctly to gain such benefits).
Unfortunately, not every database supports transactions, so PDO needs to run in so-called "autocommit" mode when the connection is first opened.
Autocommit mode means that each query run has its own implicit transaction if the database supports it, or none if the database does not support transactions.
If a transaction is required, it must be started with the PDO::beginTransaction() method. If the underlying driver does not support transactions, a PDOException is thrown (this is a serious error condition regardless of the error handling settings).
Once a transaction is started, it can be completed with PDO::commit() or PDO::rollBack(), depending on whether the code in the transaction ran successfully.
Note: PDO only checks whether it has transaction capabilities at the driver level. If some runtime condition means that a transaction is not available, and the database service accepts a request to start a transaction, PDO::beginTransaction() will still return TRUE without error. A good example is trying to use transactions in a MyISAM table in a MySQL database.
When the script ends or the connection is about to be closed, if there is an outstanding transaction, PDO will automatically roll back the transaction. This safety measure helps avoid inconsistencies if the script terminates unexpectedly - if the transaction is not committed explicitly, it is assumed that something went wrong, so a rollback is performed to keep the data safe.
Note: Automatic rollback may only occur after starting a transaction via PDO::beginTransaction(). If you manually issue a query to start a transaction, PDO has no way of knowing it and can't roll it back if necessary.
Execute batch processing in a transaction:
In the following example, assume that a set of entries is created for a new employee and assigned an ID of 23. In addition to registering the person's basic data, it is also necessary to record his salary.
It's simple to do both updates separately, but by enclosing them in PDO::beginTransaction() and PDO::commit() calls, you ensure that no one else can see the changes until they are complete.
If an error occurs, the catch block rolls back all changes that occurred since the transaction started and prints an error message.
<?phptry { $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', array(PDO::ATTR_PERSISTENT => 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 extract data, and you can use that information to build more changes and queries; when the transaction is active, you can guarantee that others cannot make changes while the operation is in progress.