Description

PEAR MDB2 defaults to auto-committing all queries. However using the beginTransaction() method one can open a new transaction and with the commit() and rollback() methods, a transaction is finished. These three methods optionally accept a string name of a savepoint to set, release or rollback to respectively. The method inTransaction() may be used to check if a transaction is currently open.

Exemple 39-1. Doing a transaction <?php // Create a valid MDB2 object named $mdb2 // at the beginning of your program... require_once 'MDB2.php';     $mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');   if (PEAR::isError($mdb2)) {     die($mdb2->getMessage());   }     // check if transaction are supported by this driver if (!$mdb2->supports('transactions')) {     exit();   }     // Open a transaction $res = $mdb2->beginTransaction();     ..     // check if we are inside a transaction and if savepoints are supported if ($mdb2->inTransaction() && $mdb2->supports('savepoints')) {     // Set a savepoint    $savepoint = 'MYSAVEPOINT';     $res = $mdb2->beginTransaction($savepoint);       ..       // determine if the savepoint should be released or to rollback to the savepoint    if ($error_condition) {        $res = $mdb2->rollback($savepoint);     } else {        $res = $mdb2->commit($savepoint);     }   }     ..     // determine if the commit or rollback if ($error_condition) {     $res = $mdb2->rollback();   } else {     $res = $mdb2->commit();   }       ?>  Récupérer le code

MDB2 also supports "nested" transactions using the beginNestedTransaction() method. Actually these are not true nested transactions as they are natively supported in Interbase for example. MDB2 maintains a counter of opened nested transactions. The transaction is finished once that counter is decremented back to 1 with completeNestedTransaction() calls. If the RDBMS supports savepoints then MDB2 will automatically set a savepoint on every call of the beginNestedTransaction() method after the initial call and will return the name. These savepoints are automatically released by the completeNestedTransaction() method. The name of these automatic savepoints are determined by the "savepoint_format" option and the nested transaction counter. The "savepoint_format" defaults to 'MDB2_SAVEPOINT_%s'.

If, after initially opening a nested transaction, an unexpected PEAR error is raised on the MDB2 instance the transaction is rolled back, otherwise it is commited at this point. Using the getNestedTransactionError() method it is possible to check if there has been an error inside the transaction. Alternatively a rollback can be forced using the failNestedTransaction(). This method optionally accepts a mixed parameter which will set the error to return if the getNestedTransactionError() method is called, as well as a second boolean parameter that optionally forces an immidiate rollback.

Exemple 39-2. Using emulated nested transactions <?php   $mdb2->beginNestedTransaction(); # open transaction   $query = "INSERT INTO autoinc (id) VALUES (?)";   $stmt = $mdb2->prepare($query);     $stmt->execute(array(1));     $savepoint = $mdb2->beginNestedTransaction(); # ignored / sets savepoint   ..     // never true for this example if (false) {     // raise an error    $error = $mdb2->raiseError(MDB2_ERROR, null, null, 'kaboom');     $mdb2->failNestedTransaction();   }     if(($error = $mdb2->getNestedTransactionError())) {     die($error->getUserinfo());   }     $mdb2->completeNestedTransaction(); # ignored / releases savepoint   $stmt->execute(array(2));     $mdb2->completeNestedTransaction(); # commit     ?>  Récupérer le code

Finally MDB2 supports setting the transaction isolation level as per the SQL 92 standard using the setTransactionIsolation() method. If a given RDBMS does not support a given isolation level but supports a higher more strict isolation level, then MDB2 silently uses that higher transaction level. Some RDBMS systems support additional options which are silently ignored if they are not supported.

Exemple 39-3. Setting the transaction isolation level <?php   $options = array('wait' => 'WAIT', 'rw' => 'READ WRITE');   $options = array('wait' => 'NO WAIT', 'rw' => 'READ ONLY');     $isolation_level = READ UNCOMMITTED # (allows dirty reads) $isolation_level = READ COMMITTED # (prevents dirty reads) $isolation_level = REPEATABLE READ # (prevents nonrepeatable reads) $isolation_level = SERIALIZABLE # (prevents phantom reads) $mdb2->setTransactionIsolation($isolation_level, $options);       ?>  Récupérer le code