1

I've created two databases on mysql, each containing a table called "test".

create table db1.test(
test_id int primary key auto_increment,
value int(5) not null);

create table db2.test(
test_id int primary key auto_increment,
value int(5) not null);

According to PDO transaction across multiple databases, is it possible?, PDO does not support transactions within multiple databases, but I am surprised by the results of my test. Here is my code.

$con = new PDO("mysql:host=localhost", "root", "", array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
try {
    $con->beginTransaction();
    $con->exec("use db1");
    $stmt = $con->prepare("insert into test (value) values(?)");
    $stmt->execute([1]);
    $con->exec("use db2");
    $stmt = $con->prepare("insert into test (value) values(?)");
    $stmt->execute([1]);
    $con->commit();
} catch (Exception $e) {
    $con->rollback();
    echo $e->getMessage();
}

The code successfully inserted with no problems. To test that the transaction I started will not work since I am using two databases, I've deleted db2.test and expect the insert on db1.test to still succeed. The insert on db2.test obviously failed, but db1.test also didn't commit.

I am confused. Why is PDO performing a transaction when I am clearly using two databases?

Also, the answer to this question: Multiple database and transactions

That won't do squat. Transactions are isolated within a single "database". In order to have transactions span across multiple databases, you need what's called "distributed transaction management".

Although the code on this question uses two connections.

lightning_missile
  • 2,821
  • 5
  • 30
  • 58

1 Answers1

3

You have misinterpreted the question you linked to.

You can certainly do a transaction that references tables in multiple databases, as long as the databases are hosted in the same instance of MySQL Server. You can even reference multiple databases in a single statement, by using qualified table names.

The question you linked to is about databases hosted in different MySQL Server instances on different hosts. PDO has no support for coordinating commits against multiple MySQL servers.

Re your comment:

Yes, a transaction is scoped to a single connection, and a connection is made to a single MySQL Server instance. You can't make a transaction that spans multiple MySQL connections.

But within one connection to one MySQL Server instance, you can reference multiple databases.

The concept of a distributed transaction that spans multiple servers is supported by MySQL. It's called an XA Transaction. PDO does not have any direct support for XA Transactions.

Most applications do not need XA transactions, because they work on just one MySQL instance at a time.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828