Added diff for small propsal for Zend_Db_Pdo_Abstract to get open transaction count.

Added Property:

$_openTransactions = 0

Changed method(s): + beginTransaction + commit + rollback

Added method(s): + int getOpenTransactionCount(void)

The diferences must created in the abstract class Zend_Db_Adapter_Abstract. (In your diff file it is correct)

Can a connection have more than 1 open transaction ? I think no

Hmm I saw such a implementation in a class am I using for a few month so I thought it is possible to open more than 1 transaction. I really never tested it.

Now i had a situation i needed more than one open transaction and at the end of all i commit/rollback them all. So it is possible and I think we should add this patch/improvement.

try {
    $oDb->startTransaction();
    $oDb->query(QUERY_A);

    foreach ($testArray as $key => $value)
    {
        [...]
        try {
            $oDb->startTransaction();
            $oDb->query(QUERY_B);
        }
        catch (Exception $e) {
            $oDb->rollback();
        }
        [...]
    }

    [...]

    while ($oDb->getTransactionCount() > 0) {
        $oDb->commit();
    }
}
catch (Exception $e) {
    while ($oDb->getTransactionCount() > 0) {
        $oDb->rollback();
    }
}

So QUERY_A is required, QUERY_B not and all queries will be commited at the same time.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Reassigning as Ralph is the maintainer of Zend_Db

It would be great to implement getTransactionCount() method at Zend_Db_Adapter_Abstract level.

MySQL does not allow nested transaction, there if we call $db->beginTransaction() when it has already been called, it results in a 'PDOException' (I'm using PDO adapter).

For now my quick fix is to use a function as follow:

function isDbTransactionRunning(Zend_Db_Adapter_Abstract $dba)
{
    try {
        $dba->beginTransaction();
    } catch (Exception $e) {
        return true;
    }
    $dba->rollBack();
    return false;
}

It would be so much better/cleaner to have Zend_Db_Adapter_Abstract::getTransactionCount()

I ran into the following error when saving a row object while a transaction was active: exception 'Zend_Db_Table_Row_Exception' with message 'Cannot refresh row as parent is missing' in /var/www/library/ZendFramework-1.7.1-minimal/library/Zend/Db/Table/Row/Abstract.php:755

This is because when Zend_Db_Table_Row::save() (_doInsert()/doUpdate()) automatically run ::_refresh(). Since the transaction is running, ::_refresh() query fails and raise above mentioned error.

I extended Zend_Db_Adapter as well as Zend_Db_Table_Row so that they handle transactions more natively & transparently, below are those classes, I hope it will be usefull for next versions. Basically when a transaction is running, ::_refresh() call is simply delayed up to when transaction is commited.

/**
 * Extends Zend db adapter to add extra functionalities to handle sql transactions
 */
class Library_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql
{
    /**
     * Array of instances waiting to be refreshed once sql transaction is commited
     * Mutli-dimensional array: 1 entry per nested running transaction
     * @var array [Zend_Db_Table_Row,]
     */
    protected $_rows_waiting_refresh = array();

    /**
     * Keep track of the number of currently running transactions
     * @var int
     */
    protected $_transaction_count = 0;

    /**
     * Add a row waiting for refresh once current transaction gets commited
     *
     * @param Zend_Db_Table_Row_Abstract $row
     */
    public function addRowWaitingRefresh(Zend_Db_Table_Row_Abstract $row)
    {
        if (!$this->isTransactionRunning()) {
            throw new Library_FenvException_DevError('cannot add row to wait refresh once transaction is commited, no transaction currently running');
        }
        $this->_rows_waiting_refresh[$this->_transaction_count][] = $row;
    }

    /**
     * Return true if a transaction is running, false otherwise
     *
     * @return bool
     */
    public function isTransactionRunning()
    {
        return !!$this->_transaction_count;
    }

    /**
     * Start a transaction
     *
     * @return parent::beginTransaction
     */
    public function beginTransaction()
    {
        if ($output = parent::beginTransaction()) {
            $this->_rows_waiting_refresh[$this->_transaction_count] = array();
            ++$this->_transaction_count;
        }
        return $ output;
    }

    /**
     * Rollback a transaction
     *
     * @return parent::rollBack
     */
    public function rollBack()
    {
        if ($output = parent::rollBack()) {
            --$this->_transaction_count;

            // do not refresh waiting instances and remove them from waiting list
            unset($this->_rows_waiting_refresh[$this->_transaction_count]);     }
        return $output;
    }

    /**
     * Commit a transaction
     *
     * @return parent::commit
     */
    public function commit()
    {
        if ($output = parent::commit()) {
            --$this->_transaction_count;

            //update instances waiting to be refreshed
            while ($row = array_shift($this->_rows_waiting_refresh[$this->_transaction_count])) {
                $row->refresh();
            }
        }
        return $output;
    }
}

class Library_Db_Row extends Zend_Db_Table_Row_Abstract
{

    /**
     * Upon insert/update, self::_refresh() is automatically called, generating an error if refresh returns no data.
     *
     * Problem is that if you're using sql transaction, refresh will fail and generate an exception
     * Therefore we prevent automatic refresh if a transaction is running, and store instance in an array to be refreshed as soon as transaction is commited.
     *
     */
    protected function _refresh()
    {
        $dba = $this->getTable()->getAdapter();
        if ($dba->isTransactionRunning()) {
            $dba->addRowWaitingRefresh($this);
        } else {
            return parent::_refresh();
        }
    }
}

You can find additional information here: http://www.qc4blog.com/?p=410

Just to correct a typo on class declared on my precedent comment. In method "public function addRowWaitingRefresh($row)" please read:```

Please ignore my comment on _refresh failing due to running sql transaction.

As William pointed it here (http://www.qc4blog.com/?p=410), ::_refresh error did not come from having a running sql transaction, but from a filtering mechanism I had on Zend_Db_Table::_fetch() that made my newly inserted row not be returned.

Too bad, shame on me :-(

I had a similar problem. I extended the Mysqli adapter to automatically handle "nested" transactions. I've been using it and it works as intended.

<?php

require_once 'Zend/Db/Adapter/Mysqli.php';

class Steam_Db_Adapter_Mysqli extends Zend_Db_Adapter_Mysqli
{
    private $_transactionCount = 0;
    
    /**
     * Begin a transaction.
     *
     * @return void
     */
    protected function _beginTransaction()
    {
        if ($this->_transactionCount === 0)
        {
            $this->_connect();
            $this->_connection->autocommit(false);
        }
        
        $this->_transactionCount++;
    }

    /**
     * Commit a transaction.
     *
     * @return void
     */
    protected function _commit()
    {
        $this->_transactionCount--;
        
        if ($this->_transactionCount === 0)
        {
            $this->_connect();
            $this->_connection->commit();
            $this->_connection->autocommit(true);
        }
    }
}

?>

hmm... i couldn't find the changes for this issue or any information related to how this was fixed.

What's the status on this issue?

I found a PHP-Bug-Report that it's already implemented in PDO but not documented and tested -> http://bugs.php.net/bug.php?id=52736

I have a patch for this issue, which I consider pretty significant. I have been using my own modified class in order to get the desired functionality. Is there a reason why this has not yet been fixed? Is there any way I can submit my patch in some official way? If so, can everyone describe the IDEAL operation. Should nested transactions be handled automatically, should it be an option? should it be done manually using the transaction count?