MySQL PHP Transaction across databases

Question

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.

I would assume if they're on the same DB host machine it would work. — Ray
– Ray, Commented Feb 24, 2016 at 21:52
Well if they were not I could not use the same mysqli object for both statements. — nickdnk
– nickdnk, Commented Feb 24, 2016 at 21:52
yes, it'll work, in this particular case. transactions are done per-connection. if you hold open two connections, and start a transaction in each, then you've got two completely separate/different transactions, and actions on one will not affect the other. — Marc B
– Marc B, Commented Feb 24, 2016 at 21:53
@MarcB - I'm aware of that, and that's why the question specifically states that I'm using the same connection object. If you're sure and can back your claim up feel free to post an answer :) — nickdnk
– nickdnk, Commented Feb 24, 2016 at 21:55
It seems like this example already contains almost everything you'd need to verify it. — Don't Panic
– Don't Panic, Commented Feb 24, 2016 at 21:57

Marc B · Accepted Answer · 2016-02-24 21:58:56Z

4

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.

answered Feb 24, 2016 at 21:58

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nickdnk Over a year ago

I think I'll take your words and this example for it. Thanks :)

Barmar · Accepted Answer · 2016-02-24 22:05:28Z

2

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.

answered Feb 24, 2016 at 22:05

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

MySQL PHP Transaction across databases

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related