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.
Example 39-1. Doing a transaction
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.
Example 39-2. Using emulated nested transactions
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.
Example 39-3. Setting the transaction isolation level