Mysql transactions within transactions

Question

In a PHP script working with a mysql database, I recently had the need to use a transaction at a point that happened to be inside another transaction. All my tests seem to indicate this is working out fine, but I can't find any documentation on this usage.

I want to be sure - are transactions within transactions valid in mysql? If so, is there a way to find out how many levels deep you are in nested transactions? (ie. how many rollbacks it would take to return to normal)

Thanks in advance, Brian

Answer 1

Hi,

This page of the manual might interest you : 12.4.3. Statements That Cause an Implicit Commit ; quoting a few sentences :

The statements listed in this section (and any synonyms for them) implicitly end a transaction, as if you had done a COMMIT before executing the statement.

And, a bit farther in the page :

Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.

Also :

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

Hope this helps !

Answer 2

I want to be sure - are transactions within transactions valid in mysql?

No.

Answer 3

You might want to check your testing methadology. Outside of MaxDB, MySQL doesn't support anything remotely like nested transactions.

Answer 4

MySql doesn't support nested transactions. There are a few ways that you can emulate it though. First, you can use savepoints as a form of transaction, so that gives you two levels of transactions; I've used this for testing, but I'm not sure about the limitations, if you use it in production code. A simpler solution is to ignore the second begin transaction and instead increase a counter. For each commit, you decrease it. Once you hit zero, you do an actual commit. There are obvious limitations of this; Eg. a rollback will roll all transactions back, but for a case where you only use transactions for error-handling, that may be acceptable.

Answer 5

It does: http://dev.mysql.com/doc/refman/5.0/en/xa.html

Answer 6

Postgresql does?