Practical Nested Transactions with Zend_Db and MySQL

The need to save dependent or child models appears frequently in object oriented applications. In such cases, it’s usually desirable to group all the save operations for a parent and its children into a single atomic transaction. Many RDBMS brands support transactions, but few can handle these kind of “nested” transactions.

This article discusses how to extend Zend_Db_Adapter to give the illusion of nested transactions – a handy workaround for use in object oriented applications. Although I focus on MySQL, it should be possible to apply these concepts to any other database flavour which supports transactions. There are some limitations to this approach, but I’ve found it to be very useful in practice.

A Brief Introduction to Transactions

You can’t work with database backed web apps for very long without coming across the concept of database transactions.

Transactions (http://en.wikipedia.org/wiki/Database_transaction) are a way of encapsulating a group of database queries, typically INSERT and UPDATE statements, so that they all must complete successfully in order for the changes to be written (“committed”) to the database. If any single query fails, any changes to the database can be “rolled back” to how they were when the transaction began.

This has great practical value. Many operations consist of several steps where the changes should only be applied if every step is successful. The classic example is transferring funds between two bank accounts, which is a three stage process:

1. Checking that the sender’s account has a balance greater than the amount to be transferred.
2. Subtracting that amount from the sender’s account
3. And, finally, adding it to the recipient’s account.

The SQL for this transaction might go something like:

START TRANSACTION;
SELECT balance FROM account WHERE account_number = 123456789;
UPDATE account SET balance = balance - 100.00 WHERE account_number = 123456789;
UPDATE account SET balance = balance + 100.00 WHERE account_number = 987654321;
COMMIT;

If an error is thrown at any point in the process, the ROLLBACK statement can be used to revert the database to its state before the transaction began.

In an application that doesn’t use transactions, a typical approach might be to check the return value of each query or enclose it in a try…catch block:

try {
    $balance = $db->fetchOne( "SELECT `balance` FROM `account` WHERE `account_number` = 123456789" );
    $db->update( 'account', array( 'balance' => new Zend_Db_Expr( "`balance` + 100" ) ), "`account_number` = 123456789" );
    $db->update( 'account', array( 'balance' => new Zend_Db_Expr( "`balance` - 100" ) ), "`account_number` = 987654321" );
} catch ( Zend_Db_Exception $e ) {
    echo $e->getMessage();
}

The problem with this approach is that although we know the operation failed, we don’t know when it failed. Perhaps the first update statement was executed, but the second one wasn’t. Perhaps the database connection was lost before we even started. The end result is that we may have introduced inconsistencies into our data that, even with this simple example, are going to be hard to track down and correct.

Using transactions we can ensure that each step is carried out successfully or the whole operation fails.

try {
    $db->beginTransaction();
    $balance = $db->fetchOne( "SELECT `balance` FROM `account` WHERE `account_number` = 123456789" );
    $db->update( 'account', array( 'balance' => new Zend_Db_Expr( "`balance` + 100" ) ), "`account_number` = 123456789" );
    $db->update( 'account', array( 'balance' => new Zend_Db_Expr( "`balance` - 100" ) ), "`account_number` = 987654321" );
    $db->commit();
} catch ( Zend_Db_Exception $e ) {
    $db->rollBack();
    throw $e;
}

By calling the beginTransaction() method, we temporarily delay any actual modifications to the database. If each query is successful, the changes are committed using the commit() method. But if an exception is thrown, the changes are reverted using rollback().

The Problem

Although transactions are a useful tool, sadly they don’t always fit well into an object oriented application.

It’s common to delegate the actual persistence (inserting and updating) of models to a save method which, in turn, goes on to save child objects. Say we have a simple address book application which allows you to enter multiple notes for each contact. The save method for the Contact model might look something like this:

public function save()
{
    $db = $this->_db;
 
    try {
	$db->beginTransaction();
	if ( !$this->hasId() ) {
	    // new model
	    $db->insert( 'contact', $this->getData() );
	} else {
	    // existing model
	    $db->update( 'contact', $this->getData(), $db->quoteInto( '`id` = ?', $this->getId() ) );
	}
	// save the contact's note
	foreach ( $this->getNotes() as $note ) {
	    $note->save();
	}
	$db->commit();
    } catch ( Zend_Db_Exception $e ) {
	$db->rollBack();
         throw $e;
    }
}

The save method for the Note class is similar, starting a transaction and saving the individual note.

Herein lies the problem: attempting to start an new transaction implicitly commits the current transaction (http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html). Without ugly hacks which tie the models directly to the database operations, it becomes difficult to establish which object started the transaction and when it should be committed.

The Solution

Disclaimer: This solution is adapted from the extended Pdo_MySql adapter in Varien’s Magento e-commerce product. A similar approach is adopted by Bryce Lohr’s Nested Table Support for Zend_Db proposal. You should read Bill Karwin’s comments about that proposal and understand the limitations of this method before implementing it. That said, I still believe this is a useful and practical way of simulating nested transactions and I have used it a number of times.

A simple solution to the problem is to keep track of the “depth” of the transaction, that is, how many times the beginTransaction() method has been called. That way, we can hold off committing the changes to the database until we are certain that all the save operations have completed successfully.

Since transactions apply to the whole database connection, the most logical place to manage this process is in the DB adapter class. To do this, we extend our adapter class like so:

class App_Zend_Db_Adapter_Mysqli extends Zend_Db_Adapter_Mysqli
{
    /**
     * Current Transaction Level
     *
     * @var int
     */
    protected $_transactionLevel = 0;
 
    /**
     * Begin new DB transaction for connection
     *
     * @return App_Zend_Db_Adapter_Mysqli
     */
    public function beginTransaction()
    {
        if ( $this->_transactionLevel === 0 ) {
            parent::beginTransaction();
        }
        $this->_transactionLevel++;
 
	return $this;
    }
 
    /**
     * Commit DB transaction
     *
     * @return App_Zend_Db_Adapter_Mysqli
     */
    public function commit()
    {
        if ( $this->_transactionLevel === 1 ) {
            parent::commit();
        }
        $this->_transactionLevel--;
 
        return $this;
    }
 
    /**
     * Rollback DB transaction
     *
     * @return App_Zend_Db_Adapter_Mysqli
     */
    public function rollback()
    {
        if ( $this->_transactionLevel === 1 ) {
            parent::rollback();
        }
        $this->_transactionLevel--;
 
        return $this;
    }
 
    /**
     * Get adapter transaction level state. Return 0 if all transactions are complete
     *
     * @return int
     */
    public function getTransactionLevel()
    {
        return $this->_transactionLevel;
    }
}

Update your bootstrap to use the extended class et voila – a single START TRANSACTION and COMMIT or ROLLBACK is sent to MySQL, regardless of how many levels of nested pseudo-transactions have been created.

Please Note:

• It’s important that each child save() method re-throws the exception so that transaction depth is reduced by successive calls to rollBack(). The end result is that the originally called save() method then performs the actual rollback.
• All the tables used in the transaction must use a storage engine that supports transactions. For MySQL, this will most likely mean using InnoDB. To convert a MyISAM or other table type to InnoDB, use “ALTER TABLE table ENGINE = InnoDB”. It could take some time to rebuild the indexes on large tables. There are other considerations about the use of InnoDB – please consult the MySQL manual.
• If the tables used don’t support transactions, it’ll just fail silently. Bad times. I highly recommend using Firebug and Zend_Db_Profiler to monitor database queries during development (see http://framework.zend.com/manual/en/zend.db.profiler.html);

I hope you find the above useful – if so, please leave a comment. If not, please be gentle… it’s my first blog post!