PHP PDO Affairs and auto-commit

PHP PDO Reference Manual

Now connected via PDO went in before the start of the query, you must first understand how PDO is to manage affairs.

Transaction support four properties (ACID):

• Atomicity (Atomicity)
• Consistency (Consistency)
• Isolation (Isolation)
• Persistent (Durability)

More simply, any operations performed in a transaction, even a phased implementation, but also to guarantee the security applied to the database, and will not be submitted in time interference from other connections.

Transaction operations can also be automatically revoked upon request (assuming not yet submitted), which makes it easier to handle errors in a script.

The transaction is usually done by a number of changes "savings" up and then allowed to come into effect and implemented; benefits of doing so is to provide the efficiency of these can be greatly changed.

In other words, transactions can make your scripts faster and potentially more robust (you still need to use them correctly to reap that benefit).

Unfortunately, not every database supports transactions, so when you first open the connection, PDO requires so-called "auto-commit" operation mode.

Auto-commit mode means that if the database support, each query run has its own implicit transaction, if the database does not support transactions, no.

If you need a transaction, you must use the PDO :: beginTransaction () method to start. If the underlying driver does not support transactions, throw an exception PDOException (regardless of what kind of error handling settings, this is a serious error condition).

Once the transaction is available PDO :: commit () or PDO :: rollBack () to complete, depending on the transaction code is running successfully.

Note: PDO only whether the transaction processing capability in the driver layer inspection. If some runtime condition means that a transaction is not available, and the database service accepts a request to initiate the transaction, PDO :: beginTransaction () will still return TRUE and no errors. Try using the transaction is a good example in the MySQL database MyISAM tables.

When the script ended or the connection is about to be closed, if there is not a completed transaction, PDO will automatically roll back the transaction. This security measure helps to avoid inconsistencies in the script terminates unexpectedly - if not explicitly commit the transaction, then the assumption is wrong somewhere, so the rollback is performed to ensure data security.

Note: Only start a transaction PDO :: beginTransaction () later, it may happen automatically rolled back. If you manually issue a query to start a transaction, the PDO can not know, so that, if necessary, can not be rolled back.

In a transaction executing batch:

In the following example, suppose you create a set of entries for the new employees, assign a ID 23 is. In addition to the registration person's basic data, but also you need to record his salary.

Both updates are completed it is very simple, but by closing the PDO :: beginTransaction () and PDO :: commit () call, it is possible to ensure that before the changes, other people can not see these changes.

If an error occurs, the catch block all changes since the transaction was rolled back from the start, 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();
}
?>

Is not limited to changes in the transaction, it can issue complex queries to extract data, but also to build more changes and query using that information; when the transaction is active, you can ensure that other people can not make changes in the operation in progress.

PHP PDO Reference Manual