Strange "nested Transactions" Exception


I’m running multiple instances of the same Yii command (yiic), but with slightly different parameter (for scraping purposes, if it matters).

I’m using in a single location in my code transactions for synchronization purposes. The actual synchronization is achieved ‘SELECT… FOR UPDATE’ but as you might (and should) know, with AUTOCOMMIT=1 there’s no effect for this lock so starting a transaction is needed in order to put it into effect. See last section of the page here for more information on this.

While developing, I’ve stopped the code and from command line MySQL attempted to run the same SELECT… FOR UPDATE locking query. It worked - and my command line client query was blocked until the code performed a ‘commit’ or ‘rollback’.

Now, the problem is that during real world usage of this code I started getting exceptions on the ‘beginTransaction()’ calls. Digging some more, it appears that somehow ‘nested transactions’ are attempted. That’s my puzzle - why are nested transactions are performed if each yii command is executed in a separate Yii console application?

My only guess so far is that this nested transaction problem is caused due to some shared connections or other resources, in the MySQL php extension level.

  • I’m curious - can someone verify this assumption?
  • Solutions: I can think of removing the ‘beginTransaction’ to add ‘set autocommit=0’ manually instead. You have a better solution? Remember that I don’t actually need a transaction here. It was only meant to get ‘SELECT…FOR UPDATE’ block and thus achieve synchronization (so articles such as this one are of little use to me).