Skip to end of metadata
Go to start of metadata

<p>Proposers:<br />
<ac:link><ri:user ri:username="gearhead" /></ac:link><br />
<a href="mailto:darby@zend.com">Darby Felton</a>, Zend liaison</p>

<h1>Overview</h1>

<p>I propose to add a sort-of pseudo-nested transactions feature to Zend_Db_Abstract. It would just automatically merge together all the "nested" transactions within the outermost transaction, forming one large normal transaction. It would be fairly easy to implement, and would make it easier to compose together higher- and lower-level DB code that needed to rely on transactions.</p>

<ac:macro ac:name="warning"><ac:rich-text-body><p>This feature would <strong>not</strong> allow rolling back or committing of nested transactions separately from the outermost transaction. <strong>Such true nested transactions are out of scope for this proposal.</strong> Needless to say, trying to implement all that in PHP would be infeasible.</p></ac:rich-text-body></ac:macro>

<h1>Theory of Operation</h1>

<p>Currently, any call to <code>beginTransaction()</code> after the first, but before a matching call to <code>rollback()</code> or <code>commit()</code>, will result in a PDO exception. Instead, it would be trivial for <code>beginTransaction()</code>, <code>commit()</code> and <code>rollback()</code> to simply keep track of a counter. This counter would represent the current depth of the nested transactions. <code>beginTransaction()</code> would increment the counter, <code>commit()</code> would decrement it, and <code>rollback()</code> would always set it to zero. By only making a real begin or commit when the counter is set to 1, all the "nested" transactions would be transparently merged into the first, outermost transaction. However, if <code>rollback()</code> is called anywhere, at any nesting depth, it should immediately rollback (which would cancel the entire outermost transaction), and set the counter to zero. Thus, instead of producing an error on subsequent calls, <code>beginTransaction()</code> and company would simply return true, as if they were successful.</p>

<h1>Use Cases</h1>

<h3>Composing DB code</h3>

<p>Suppose you have a method, called <code>singleTableSave()</code>, that takes a table name and a data array, and stores the given data in the given table. Now, this method does a few things that you'd like to be done inside a transaction for atomicity, etc.</p>

<p>Example pseudo-code for <code>singleTableSave()</code>:</p>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
public function singleTableSave($tableName, $dataArray, $pk = 'id')
{
$this->_db->beginTransaction();
try

Unknown macro: { // A SELECT to decide whether to do an Insert or Update ... // Another SELECT, maybe get a value used for optimistic locking, for example ... // Do the INSERT or UPDATE // If INSERT, return the lastInsertId() ... $this->_db->commit(); }

catch (Exception $e)

Unknown macro: { $this->_db->rollback(); throw $e; }

}
]]></ac:plain-text-body></ac:macro>

<p>Note that this code begins and ends its own transaction. Since this method knows how to save to a table, you might want call it from somewhere else; to save to several tables with foreign key relationships, for example. In this example, you'd want the entire operation to be in one transaction. But that isn't possible with the current implementation, because the call to <code>beginTransaction()</code> in <code>singleTableSave()</code> would cause an error if you have already started a transaction. Also, in this example, if some exception is thrown in <code>singleTableSave()</code>, you would want to roll back any outer transaction that depended on this one.</p>

<h3>Other Use Cases</h3>

<p>I haven't thought of any, since the previous one is what applies to me, but I'm sure there are other cases when this might be useful. <ac:emoticon ac:name="smile" /> Importantly, the changes proposed here should not break any existing code, since the semantics of non-nested transactions aren't changed.</p>

<h1>Class Skeletons</h1>

<p>This proposal modifies existing classes, rather than adding any new ones. The patch below implements a working proof-of-concept. It could no doubt use some more robust checking for error conditions and inconsistencies.</p>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
Index: Zend/Db/Adapter/Abstract.php
===================================================================
— Abstract.php (revision 3815)
+++ Abstract.php (working copy)
@@ -69,6 +69,14 @@
protected $_connection = null;

/**
+ * Count of active "nested" transactions
+ *
+ * @var int
+ */
+ protected $_transDepth;
+
+
+ /**

  • Constructor.
    *
  • $config is an array of key/value pairs containing configuration
    @@ -105,6 +113,9 @@
    unset($this->_config['profiler']);
    }

+ // "0" means no active transactions
+ $this->_transDepth = 0;
+
$this->_profiler = new Zend_Db_Profiler($enabled);
}

@@ -165,10 +176,15 @@
*/
public function beginTransaction()
{

  • $this->_connect();
  • $q = $this->_profiler->queryStart('begin', Zend_Db_Profiler::TRANSACTION);
  • $this->_beginTransaction();
  • $this->_profiler->queryEnd($q);
    + $this->_transDepth++;
    +
    + if (1 == $this->_transDepth)
    Unknown macro: {+ $this->_connect();+ $q = $this->_profiler->queryStart('begin', Zend_Db_Profiler}

    +
    return true;
    }

@@ -179,10 +195,14 @@
*/
public function commit()
{

  • $this->_connect();
  • $q = $this->_profiler->queryStart('commit', Zend_Db_Profiler::TRANSACTION);
  • $this->_commit();
  • $this->_profiler->queryEnd($q);
    + if (1 == $this->_transDepth)
    Unknown macro: {+ $this->_connect();+ $q = $this->_profiler->queryStart('commit', Zend_Db_Profiler}

    +
    + $this->_transDepth--;
    return true;
    }

@@ -193,10 +213,15 @@
*/
public function rollBack()
{

  • $this->_connect();
  • $q = $this->_profiler->queryStart('rollback', Zend_Db_Profiler::TRANSACTION);
  • $this->_rollBack();
  • $this->_profiler->queryEnd($q);
    + if ($this->_transDepth > 0)
    Unknown macro: {+ $this->_connect();+ $q = $this->_profiler->queryStart('rollback', Zend_Db_Profiler}

    +
    return true;
    }

]]></ac:plain-text-body></ac:macro>

<p>This patch can also be found in the file attached to this wiki page.</p>

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Jun 14, 2007

    <p>Yes, I've already implemented this in my app to support multi-table update. Having this built-in in the framework would be elegant.</p>

    <p>Thanks!</p>

  2. Sep 11, 2007

    <p>I'm very uncomfortable with this proposal. Like it or not, transactions are "global" and they do not obey object-oriented encapsulation. </p>

    <p>Well, except in InterBase/Firebird, which permits multiple active transactions that can be committed or rolled back independently. But this is the only RDBMS I've ever used that supported this concept. Usually a transaction is 1:1 with the database connection.</p>

    <p><span style="text-decoration: underline;">Problem scenario #1</span></p>

    <p>I call <code>commit()</code>, are my changes committed? If I'm running inside an "inner transaction" they are not. The code that manages the outer transaction could choose to roll back, and my changes would be discarded without my knowledge or control.</p>

    <p><span style="text-decoration: underline;">Problem scenario #2</span></p>

    <p>An inner transaction rolls back, it could discard legitimate changes made by an outer transaction. When control is returned to the outer code, it believes its transaction is still active and available to be committed. With your patch, they could call <code>commit()</code>, and side the transDepth is now 0, it would silently set transDepth to -1 and return true, after <strong>not</strong> committing anything.</p>

    <p>This starts to sound like discussions I have had years ago with people who wanted to do transaction commit/rollback inside triggers. Yeesh! The can of worms that opens up is scary indeed.</p>

    1. Sep 11, 2007

      <p>Woops, I made this typo:</p>

      <p>...and side the transDepth is now 0...</p>

      <p>should be</p>

      <p>...and since the transDepth is now 0...</p>

    2. Sep 11, 2007

      <p><span style="text-decoration: underline;">Problem scenario #3</span></p>

      <p>With your patch, if I call <code>commit()</code> when there is no transaction active, it sets the transDepth to -1. The next begin transaction increments the depth to 0, which means the transaction can neither be rolled back nor committed. Subsequent calls to <code>commit()</code> will just decrement the transaction to -1 or further, and you'll never be able to use a transaction again.</p>

      <p>This is fixable – it's a coding issue instead of a conceptual issue. But I still feel that the concept is flawed. Trying to emulate transaction savepoints in application logic without allowing the database to do the bookkeeping is a doomed idea.</p>

      1. Oct 05, 2007

        <p>I acknowledge there are legitimate implementation issues with the code above, but it's about the concept, not the implementation. I suppose I was not clear enough in my intention about what this is trying to do: I <strong>in no way</strong> want to try to emulate or recreate the functionality of actual nested transactions. All those issues you mentioned are very valid and serious things you have to deal with when doing actual nested transactions, and I just want to avoid all of that outright. That's what I meant in the very first paragraph of the proposal when I said "true nested transactions are out of scope". I would not want anyone using this to go into it expecting things to work that way; they would need to know that it just merges everything into one big transaction, with only one rollback possible for the whole thing.</p>

        <p>All I wanted to do is just automatically have transactions (that I started and ended farther down the call stack) automatically get merged together with the one I may have already started. The bugs in this should definitely be fixed; I just hacked this code straight into the Abstract adapter class to accommodate my needs. Since I was throwing exceptions whenever I needed to roll back a transaction, this suited my use case well. I just figured such functionality might be useful to other people, since it makes organizing some kinds of database access code easier (IMHO).</p>

        <p>A while after I wrote this proposal, it occurred to me that it would be better if there was some kind of extensible transaction management mechanism I could just replace with my own code. How about separating the existing transaction methods (beginTransaction, commit, rollback) and separating them out to their own object? That way I can just set my own instance of the interface, and no one else has to deal with my bugs (conceptual or otherwise <ac:emoticon ac:name="smile" /> )?</p>

        <p>Something this:</p>
        <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
        // $adapter->tx would be an instance of Zend_Db_TransactionInterface (or whatever you wanted to call it)
        $adapter->tx->beginTransaction();
        // or use compatible proxy methods: $adapter->beginTransaction();

        // Write records
        if ($success) {
        $adapter->tx->commit(); // or $adapter->commit() proxies to tx manager
        } else {
        $adapter->tx->rollBack(); // same here: $adapter->rollBack();
        throw new TransactionFailedException;
        }

        // Then you could replace the default implementation with your own:
        $myTxMgr = new My_TransactionManager;
        $adapter->setTransactionManager($myTxMgr);
        ]]></ac:plain-text-body></ac:macro>

        <p>This would be completely backwards compatible, and that way one would not have to override the abstract adapter, and all classes that inherit from it, just to modify how transactions work.</p>

  3. Oct 30, 2007

    <p>This proposal does not follow the proposal template, and we are missing the "References", "Component Requirements, Constraints, and Acceptance Criteria", "Dependencies on Other Framework Components", "Milestones / Tasks", and "Class Index" sections. We also do not have the nice header, table of contents, and other features provided by the template. I suggest copying the wiki markup from another proposal, pasting it, followed by replacing the sections' data with the content here, and then filling out the sections that are presently missing.</p>

    1. Oct 31, 2007

      <p>Way back when I first created this proposal, the new proposal template didn't work correctly, so I just created the page from scratch.</p>

      <p>Actually, this whole idea was really just something I quickly hacked into the framework, back when it was much younger. I threw this proposal up, thinking it might be a useful feature to other people, and easy to implement. But honestly, this code has no business in the framework at all. If anything, the transaction management code should be factored out to user-replaceable objects, like I mentioned in a previous comment, but that belongs in a separate proposal (that correctly follows the template <ac:emoticon ac:name="wink" /> ).</p>

      <p>I'd like to just rescind this proposal altogether. </p>

  4. Jan 29, 2008

    <p>I implemented nested transactions using two private vars (one static + one dynamic) and one private method in the db-adapter class and it does not use transaction depth. It limits the number of transactions to just one, tracks which model can commit/rollback the transaction and makes sure that only the model that began the transaction is the only model that can ever commit/rollback the transaction. This overcomes the bug pointed out in the above proposal's comments and works fine:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    private static $_transaction = 0; // Tracks the transaction (global flag for all model classes)
    //
    // Flags the model that began transaction. Determines which model can commit/rollback transaction.
    // Only the model that BEGAN the transaction can commit/rollback the transaction.
    private $_flushable = 0; // Default: This model cannot commit/rollback transaction.
    //
    //////////////////////////////////////////////////////////////////////////////////////
    /**

    • Tracks which model can commit/rollback the transaction.
    • Only the model that began the transaction can commit/rollback (flush) the transaction.
      */
      final private function _setTransaction( $value )
      //////////////////////////////////////////////////////////////////////////////////////
      Unknown macro: { $value = (int)(boolean) $value; // self}

      //
      //////////////////////////////////////////////////////////////////////////////////////
      /**

    • Ancestor-script extension.
    • Begins and tracks a transaction.
    • Limits number of transactions to just one.
      */
      //////////////////////////////////////////////////////////////////////////////////////
      final function beginTransaction()
      //////////////////////////////////////////////////////////////////////////////////////
      Unknown macro: { if ( self}

      //
      //////////////////////////////////////////////////////////////////////////////////////
      /**

    • Ancestor-script extension.
    • Tracks and commits a transaction.
      */
      //////////////////////////////////////////////////////////////////////////////////////
      final function commit()
      //////////////////////////////////////////////////////////////////////////////////////
      Unknown macro: { if ( ! $this->_flushable ) // This model cannot commit/rollback transaction return 0; // Do nothing - silent failure // // Reset tracking flags $this->_setTransaction( 0 ); // Transaction ended. This model cannot commit/rollback transaction anymore // return parent}

      //
      //////////////////////////////////////////////////////////////////////////////////////
      /**

    • Ancestor-script extension.
    • Tracks and rolls-back a transaction.
      */
      //////////////////////////////////////////////////////////////////////////////////////
      final protected function _rollBack()
      //////////////////////////////////////////////////////////////////////////////////////
      Unknown macro: { if ( ! $this->_flushable ) // This model cannot commit/rollback transaction return 0; // Do nothing - silent failure // // Reset tracking flags $this->_setTransaction( 0 ); // Transaction ended. This model cannot commit/rollback transaction anymore // return parent}

      ]]></ac:plain-text-body></ac:macro>

    <p>HTH.</p>

    <p>Shekar</p>

    1. Jan 29, 2008

      <p>This suffers from some of the same problems I saw in the original proposal.</p>

      <p>That is, I can call <code>commit()</code> or <code>rollback()</code> in Model B (assuming Model A has already initiated a transaction) but this request is silently a no-op. The transaction may be resolved in the opposite way later, and this happens without notifying Model B. In other words:</p>

      <p>Model A: begin transaction<br />
      Model A: execute some changes<br />
      Model B: begin transaction (silent no-op)<br />
      Model B: execute some changes<br />
      Model B: commit (silent no-op)<br />
      Model A: rollback (discards both model A changes and model B changes)<br />
      Model B: WTF!? What happened to my changes?</p>

      <p>If you have a requirement for two models to use explicit transaction control in one application request, then you must open two DB connections, one for each model. Then each model can have its own active transaction, which can be committed or rolled back independently from one another.</p>

  5. Jan 29, 2008

    <p>Oops!</p>

    <p>The commit/rollback methods should be commit()/rollback() and not _commit()/_rollback().</p>

  6. Feb 19, 2008

    <p>In many scenarios, I'd like to have nested transactions where either all of them succeed or all of them fail, i.e. the first failing nested transaction (whatever depth) causes a global rollback, while the last succeeding nested transaction (the outmost) causes a global commit.</p>

    <p>I think the solution could be based on a simple template like this:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    try {

    if ($commit_now = db_autocommit())

    Unknown macro: { $this->_db->beginTransaction(); }

    ... //change objects

    if ($commit_now)

    Unknown macro: { $this->_db->commit(); }

    }
    catch ( Exception $e ) {
    $this->_db->rollback();

    ... //fix other things

    throw $e;
    }
    ]]></ac:plain-text-body></ac:macro>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    function db_autocommit( $db = null ) {
    if (is_null( $db ))

    Unknown macro: { $db = Zend_Db_Table_Abstract}

    $result = $db->fetchOne("SELECT @@autocommit");
    return $result;
    }
    ]]></ac:plain-text-body></ac:macro>

  7. Sep 24, 2010

    <p>The reason we need this is not so you can nest transactions in production code. Its pretty obvious that wouldn't work. The reason is unit testing.</p>

    <p><a class="external-link" href="http://framework.zend.com/issues/browse/ZF-8500">http://framework.zend.com/issues/browse/ZF-8500</a><br />
    See my patch & comments here. Pasting:</p>

    <p>--------------</p>

    <p>I should also note, this specific behavior (how it works now) is detrimental to unit testing. If you have a piece of code that uses the database, the standard unit testing practice is to wrap the unit test method in a transaction itself. However if the SUT tries to start it's own transaction, or commit it's "inner" transaction - you either crash your tests - or worse yet the "inner" transaction would be committed, leaving global state for the next unit test method.</p>

    <p>As you can see, the way it works now is completely contrary to best practices. (in regards to this one issue - the rest of the component is terrific)</p>