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.

Solution 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.

Solution 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).

Solution 3

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

Solution 4

For innoDB you should be able to use

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

Solution 5

You can also write your code as per following:

try {
    Zend_Registry::get('database')->beginTransaction();
} 
catch (Exception $e) { }

try {
    $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;
}

View more solutions

Comments

[Bill Karwin]

All ZF issues say "fix in next minor release" until they are actually fixed. I hope they had a good reason for doing that, because it's pretty misleading and causes confusion for a lot of people.

[xelurg]

true enough, though it would certainly be an extremely nice feature. I wonder if something like that exists in Hibernate or any similar more mature persistence layers...

[Bill Karwin]

Propel has this, but I still believe it's a bogus design. See my edit above.

[xelurg]

returning back to this topic... JPA for instance has concept of TransactionManager would similar concept be logical to have for ZF?

[Bill Karwin]

Well I can't speak to JPA, but Java is not PHP and vice versa. PHP assumes that the request establishes a unit of work, and so the scope of a transaction in a PHP app should be the same as the scope of a request. It's very simple. There are no PHP application servers, so no "conversation" scope for a transaction. So there's no need to make Domain Model classes or DAO classes manage transactions -- just do it at the Controller level.

[Bill Karwin]

@Pacerier, I don't know what "bdbaft" means.

[Pacerier]

@BillKarwin, Ah, typo there, I meant "bdbafh".

[Bill Karwin]

@Pacerier, ah, you're calling me a "bastard DBA from hell?" Well, that's rather rude. Don't shoot the messenger for explaining a logical flaw in a coding solution.

[Pacerier]

@BillKarwin, Lol I'd thought that was a praise term. Anyway, this is weird, my comment above is no longer there. Did you flag it or something? Seems like someone is deleting off my comments.

[Bill Karwin]

@Pacerier, I did not flag it. It's possible someone else did, and the moderators deleted it.

[ColinM]

In my testing via mysql client SELECT @@autocommit; still returns 1 during a transaction.

[emfi]

the key here is to only open one db connection per request. then it isn't bogus to check if a transaction is open. i am speaking of non-nested transactions!i think that is also the paradigm JPA follows. if there would be more connections open, then you would have to choose the correct connection to check for a transaction.

[Leandro Jacques]

Maybe the DB layer of the framework doesn't encapsulate this PDO method for checking transaction status. I came with the same answer as you, I almost posted mine before I saw your answer here.

[Timo Huovinen]

Related: stackoverflow.com/questions/15537950/…