How do detect that transaction has already been started?

Question

I am using Zend_Db to insert some data inside a transaction. My function starts a transaction and then calls another method that also attempts to start a transaction and of course fails(I am using MySQL5). So, the question is - how do I detect that transaction has already been started? Here is a sample bit of code:

       try {
                    Zend_Registry::get('database')->beginTransaction();

                    $totals = self::calculateTotals($Cart);
                    $PaymentInstrument = new PaymentInstrument;
                    $PaymentInstrument->create();
                    $PaymentInstrument->validate();
                    $PaymentInstrument->save();

                    Zend_Registry::get('database')->commit();
                    return true;

            } catch(Zend_Exception $e) {
                    Bootstrap::$Log->err($e->getMessage());
                    Zend_Registry::get('database')->rollBack();
                    return false;
            }

Inside PaymentInstrument::create there is another beginTransaction statement that produces the exception that says that transaction has already been started.

Answer 1

Store the return value of beginTransaction() in Zend_Registry, and check it later.

Answer 2

Looking at the Zend_Db as well as the adapters (both mysqli and PDO versions) I'm not really seeing any nice way to check transaction state. There appears to be a ZF issue regarding this - fortunately with a patch slated to come out soon.

For the time being, if you'd rather not run unofficial ZF code, the mysqli documentation says you can SELECT @@autocommit to find out if you're currently in a transaction (err... not in autocommit mode).

Answer 3

Do a try/catch: if the exception is that a transaction has already started (based on error code or the message of the string, whatever), carry on. Otherwise, throw the exception again.

Answer 4

The framework has no way of knowing if you started a transaction. You can even use $db->query('START TRANSACTION') which the framework would not know about because it doesn't parse SQL statements you execute.

The point is that it's an application responsibility to track whether you've started a transaction or not. It's not something the framework can do.

I know some frameworks try to do it, and do cockamamie things like count how many times you've begun a transaction, only resolving it when you've done commit or rollback a matching number of times. But this is totally bogus because none of your functions can know if commit or rollback will actually do it, or if they're in another layer of nesting.

(Can you tell I've had this discussion a few times? :-)

edit: Propel is a PHP database access library that supports the concept of the "inner transaction" that doesn't commit when you tell it to. Beginning a transaction only increments a counter, and commit/rollback decrements the counter. Below is an excerpt from a mailing list thread where I describe a few scenarios where it fails.

---

Like it or not, transactions are "global" and they do not obey object-oriented encapsulation.

Problem scenario #1

I call commit(), 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.

For example:

1. Model A: begin transaction
2. Model A: execute some changes
3. Model B: begin transaction (silent no-op)
4. Model B: execute some changes
5. Model B: commit (silent no-op)
6. Model A: rollback (discards both model A changes and model B changes)
7. Model B: WTF!? What happened to my changes?

Problem scenario #2

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 commit(), and since the transDepth is now 0, it would silently set $transDepth to -1 and return true, after not committing anything.

Problem scenario #3

If I call commit() or rollback() when there is no transaction active, it sets the $transDepth to -1. The next beginTransaction() increments the level to 0, which means the transaction can neither be rolled back nor committed. Subsequent calls to commit() will just decrement the transaction to -1 or further, and you'll never be able to commit until you do another superfluous beginTransaction() to increment the level again.

Basically, trying to manage transactions in application logic without allowing the database to do the bookkeeping is a doomed idea. 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.

(see http://www.nabble.com/Zend-Framework-Db-Table-ORM-td19691776.html)

Answer 5

I have a MySQL stored function that works in 5.1 (but not 5.0) that can tell you if you're in a transaction or not. Essentially the try/catch suggestion from above, but inside of MySQL. Still working on getting it to work for 5.0.