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

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? :-)

Update 1: 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.

Update 2: Doctrine DBAL also has this feature. They call it Transaction Nesting.

---

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.