Transactions
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.
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();
}
?>
PEAR MDB2 does not emulate transactions or savepoints. This means that it depends on the underlying RDBMS (and in the case of MySQL the storage engines used) if transactions will be available in MDB2. Also note that some RDBMS implicitly commit transactions when executing DDL statements - notable exceptions are Oracle and PostgreSQL.
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.
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
?>
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.
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);
?>