I cannot find a definitive answer to whether or not it's allowed to make transactions in PHP span multiple databases.
I am using the same connection object (mysqli) and performing the transaction(s) in this manner:
$mysqli->autocommit(false);
try {
// Assume here that some code would throw an exception
$stmt = $mysqli->prepare("INSERT INTO db1.x (column) VALUES (y)");
$stmt->execute();
$stmt->close();
$stmt = $mysqli->prepare("INSERT INTO db2.x (column) VALUES (y)");
$stmt->execute();
$stmt->close();
$mysqli->commit();
// Great success
} catch (Exception $e) {
$mysqli->rollback();
throw $e; // handle elsewhere
}
Is this allowed and will it properly commit and roll back? According to the manual (http://dev.mysql.com/doc/refman/5.6/en/xa.html) and other threads on SO, it seems I need to use XA statements, but it's not really clear (to me) if this is when using multiple database servers, multiple connections or just in general when executing above code.