PEAR Manual
PrevNext

Introduction - Transaction

Introduction - Transaction -- Performing transactions

Description

PEAR MDB2 default to auto commiting all queries. However using the beginTransaction() one can open a new transaction and with the commit() and rollback() a transaction is finished. All of 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.

Example 33-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();
}

?>

Warning

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 (notably 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" (default: 'MDB2_SAVEPOINT_%s') option and the nested transaction counter.

If after initial opening of the 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 an rollback can be forced using the 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.

Example 33-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

?>

Finally MDB2 supports setting of 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 support additional options which are silently ignored if they are not supported.

Example 33-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);

?>
PrevHomeNext
Introduction - Prepare & ExecuteUpIntroduction - Modules