Mysql transactions within transactions
mysql, php, transactions
Solution
This page of the manual might interest you : 12.3.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`.
See also this paragraph :
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.
Problem
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