ZF-8500: Mysql does not support nested transactions, code ignitor specifically supports this, zend does not (yet)

Description

I noticed if you nest transactions, if the inner transaction commits but the outer transaction rolls back, the changes take effect and ZF does not throw any exceptions or handle that nicely. It appears from this thread (http://codeigniter.com/forums/viewthread/82771/) that CI has a feature that makes it so only the outer commit/rollback takes effect.

This prevented me from testing code that uses Zend_Db & transactions, because my test itself needed to rollback any changes that happened during the test. But the code I was testing had it's own beginTransaction() and commit()

I guess in code ignitor that inner commit() would be ignored.

Comments

Possible patch is attached. The $_should_emulate_nesting flag which defaults to false will retain it's current behavior. Setting the flag to true mimics code ignitor's feature (which makes my green bar happy)

Fixed isNested() method (see patch2)

After trying it out in some more unit tests, it appears patch 3 would hold the correct logic.

It should be noted 1 caveat of using this patch, and enabling the flag, would be that extraneous calls to commit would put the transaction counter of sync. But this is good enough for my own development (I wrapped the adapter seeing as you may not desire to use this functionality in the zend core)

On PDO you get an exception when you call beginTransaction() more than once, so I augmented the code like this:

function beginTransaction() { $this->_transaction_depth++; if( $this->_transaction_depth > 0 ) { return; } return parent::beginTransaction(); }

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.

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)

One pitfall to this I discovered over the months, you'd have to make the 'nest' count a public property, and the user should remember to reset it to -1 when catching exceptions.