A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

Zend Framework: How to use nested transactions with Zend_Db and MySQL

Steve Hollis first blog post about practical nested database transactions using Zend_Db. He writes really well and his blog is very readable. I love reading long text in Serif :D Thanks Steve!

His solution:

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:

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:

  1. 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.
  2. 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.
  3. 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);

And of course, the source can be found here: http://www.stevehollis.com/2010/03/practical-nested-transactions-with-zend_db-and-mysql/

This entry was posted in General and tagged , , , , . Bookmark the permalink.

4 Responses to Zend Framework: How to use nested transactions with Zend_Db and MySQL

  1. Pingback: Super-Skinny your Zend Framework Action Controllers (Part One) | stevehollis.com

  2. Wenbert says:

    Hi Jony,

    Although I posted something about Zend_Form, I am no expert. I haven’t used Zend_Form in over 6 months now :(. Try to ask at IRC, connect to freenode and join #zftalk

    Thanks,
    Wenbert

  3. Patrik Lermon says:

    As I understand it, MySQL (5 and less at least) does not support nested transactions. If a second transaction is begun, the first will be committed automatically.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>