CODEWITHSUNDEEP
phpmysqltransactions
nickdnk
nickdnk

Reputation: 4319

MySQL PHP Transaction across databases

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.

Upvotes: 2

Views: 1043

Answers (2)

Barmar
Barmar

Reputation: 782488

Transactions apply to the server as a whole. Databases are just used for grouping tables for the purpose of granting permissions (e.g. you can grant permissions to database.*) and providing some defaults when creating new tables. And they're used to allow you to select a default database with the USE statement, so you don't have to specify the database names on every query.

Upvotes: 2

Marc B
Marc B

Reputation: 360862

As per comment request above, omitting the create/use feedback:

mysql> create database x;
mysql> use x;
mysql> create table x (x int);
mysql> create database y;
mysql> use y;
mysql> create table y (y int);
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x.x values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into y.y values (1);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from x.x;
Empty set (0.00 sec)

mysql> select * from y.y;
Empty set (0.00 sec)

Note that this is all on a single connection, to a single mysql instance. It will NOT work if the dbs are hosted on different servers, or you're doing each create/insert on different connections.

Upvotes: 4

Related Questions