Issues

ZF-870: test if a transaction in use

Issue Type: New Feature Created: 2007-02-07T17:41:44.000+0000 Last Updated: 2013-02-10T16:16:41.000+0000 Status: Open Fix version(s): Reporter: Marc Bennewitz (GIATA mbH) (mben) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: - ZF-8063

Attachments: - pdo_transcount.diff

Description

I think it can usefull to test if a transaction already started and not commited or rolled back.

<pre class="highlight">
function update() {
    $db = WebD::Db::getInstance();
    $db->beginTransaction();
    try {
        $db->update( ... );
        // if this changed all linked data must change too
        updateAnother();
        $db->commit();
    } catch (Exception $e) {
        $db->rollBack();
        throw $e;
    }
}

function updateAnother() {
    $db = WebD::Db::getInstance();
    if ($db->transactionStarted()) {
        $db->update( [1] );
        $db->update( [2] );
    } else {
        $db->beginTransaction();
        try {
            $db->update( [1] );
            $db->update( [2] );
            $db->commit();
        } catch (Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }
}

Comments

Posted by Jan Pieper (jpieper) on 2007-03-04T03:11:07.000+0000

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)

Posted by Marc Bennewitz (GIATA mbH) (mben) on 2007-03-04T05:49:55.000+0000

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

Posted by Jan Pieper (jpieper) on 2007-03-04T06:20:03.000+0000

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.

Posted by Jan Pieper (jpieper) on 2007-03-30T18:58:26.000+0000

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.

<pre class="highlight">
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.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:58.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:12:04.000+0000

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

Posted by Wil Sinclair (wil) on 2008-12-04T12:53:17.000+0000

Reassigning as Ralph is the maintainer of Zend_Db

Posted by Remy Damour (remy215) on 2009-01-07T14:33:53.000+0000

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:

<pre class="highlight">
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()

Posted by Remy Damour (remy215) on 2009-01-08T14:51:40.000+0000

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.

<pre class="highlight">
/**
 * 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

Posted by Remy Damour (remy215) on 2009-01-09T07:26:11.000+0000

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

Posted by Remy Damour (remy215) on 2009-02-17T10:02:44.000+0000

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 :-(

Posted by Shaddy Zeineddine (shaddyz) on 2009-06-19T10:47:45.000+0000

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.

<pre class="highlight">
<?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);
        }
    }
}

?>

Posted by Shaddy Zeineddine (shaddyz) on 2009-07-31T09:44:04.000+0000

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

Posted by Dolf Schimmel (Freeaqingme) (freak) on 2009-08-11T19:07:58.000+0000

What's the status on this issue?

Posted by Marc Bennewitz (private) (mabe) on 2010-10-06T10:46:51.000+0000

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

Posted by Shaddy Zeineddine (shaddyz) on 2010-11-24T20:32:57.000+0000

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?

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts