Multiple MySQL connections + transactions

[Dani]

I ran into a bit of a snag in my code, and I'm trying to make heads or tails as to whether this will work.

From within my PHP script, can I write to a database using a MySQL connection in the middle of a transaction with a different connection?

e.g.

// Open Connection 1 and connect to Database A
// Start transaction with Connection 1
// Write some stuff with Connection 1
// Open Connection 2 and connect to Database B
// Write some stuff with Connection 2
// Close the transaction associated with Connection 1

This is probably the wrong terminology, but I hope you get the idea of what I'm trying to do here.

[Dani]

A slightly related question:

Is it okay to use PHP's mysqli::select_db() function to switch databases in the middle of a transaction? This is what got me into trouble in the first place, when I started a transaction, performed some writes, switched databases, and then wrote to that second database, and then switched back, and performed some more writes, and then committed the transaction. I discovered that all of the writes that were performed after switching back to the original database were rolled back, but the initial set, and the set to the second database, were not. But what if I switch, just perform reads, and then switch back? Should that be safe?

[Salem]

https://www.php.net/manual/en/mysqli.select-db.php

I think the key word in all of this is "default".

You can probably do what you want, but you have to refer to each DB by it's own handle.

Using select_db is fine, if you only have one DB and you want to be lazy about referring to it.
So you make it the default DB.

But as soon as you have more than one DB, then forget about select_db and just be explicit about which DB you really want at each transaction.

[Reverend Jim]

As far as I know you can connect to more than one database at a time but you require a separate connection object for each one. Since queries go through the connection object you can't run a query on more than one db at a time. It seems to me that transactions are also connection based so you would have to manually roll back a transaction on A if the commit on B fails.

[Dani]

I am already using multiple MySQL connections throughout the project, as well as select_db() in some other places.

My question specifically revolved around if anyone can explain why select_db() implicitly committed the transaction, and then when done again to return back to the initial database, it did a rollback instead of commit.

[Reverend Jim]

In that case I can't suggest anything. I've never had to update multiple databases for any of the systems I set up.