How nested transactions are handled by Phalcon in MySQL?
As far as I know MySQL don't support nested transactions. However MySQL 5.0.3 and up provides server-side support for XA transactions when using InnoDB engine.
I have MySQL 5.5 and am using InnoDB engine. Does Phalcon in such situation automatically use XA transactions?
If not, how nested transactions are handled then? By save points?
I believe Phalcon supports anything PDO supports. I don't know the answer - but you may be able to find information on whether or not PDO supports nested transactions.
I have analyzed the code of the following Phalcon 2.0 classes:
Phalcon\Db\Adapter\Pdo
Phalcon\Db\Adapter\Pdo\Mysql
Phalcon\Db\Adapter\Pdo\Oracle
Phalcon\Db\Adapter\Pdo\Postgresql
Phalcon\Db\Adapter\Pdo\Sqlite
Phalcon\Db\Dialect
Phalcon\Db\Dialect\Mysql
Phalcon\Db\Dialect\Oracle
Phalcon\Db\Dialect\Postgresql
Phalcon\Db\Dialect\Sqlite
I wanted to find out how transactions works.
My founds:
(1) Phalcon supports nested transactions via savepoints only. It does not matter which database (dialect) we are using.
(2) In order the nesting works properly without any errors we need to switch the nesting ON in the bootstrap file in the following way:
$di->set('db', function () {
$connection = new DbAdapter([
//array with params here
]);
//switch the nesting ON
$connection->setNestedTransactionsWithSavepoints(true);
return $connection;
});
If you don't do this, then nesting will not work. I think this is very important issue and it should be highlighted in the docs.
So the answer to my question is: the only way Phalcon supports nested transactions is via savepoint only (for all adapters). Phalcon don't support XA transactions in MySQL.