CODEWITHSUNDEEP
phpmysqlpdo
Ana
Ana

Reputation: 121

PDO transaction across multiple databases, is it possible?

I have the following problem with one of our users: they have two stores in two diferent locations, each place has its own database, however, they need to share the client base and the list of materials registered for sale. At the moment, what we do is when the client registers a new client, a copy is made on the other location's database. Problems quickly arise as their internet connection is unstable. If, at the time of the registration, the internet is down, it tries to make a copy, fails and carries with inconsistent databases.

I considered making the updates to the database via Pdo transactions that would manage the two databases, but it seems that you need a new instance of PDO $dbh1= new PDO('mysql:host=xxxx;dbname=test',$user,$pass); for each database, and I don't see a way to commit both updates. Looking at this related question what is the best way to do distributed transactions across multiple databases it seems that I need some for transation management. Can this be achieved by PDO?

Upvotes: 1

Views: 1422

Answers (2)

brokekidweb
brokekidweb

Reputation: 157

You can only instantiate a single PDO object. Therefore, you will need to switch databases using a query, then performing the same queries in the second DB.

Best bet is to do a transaction, then commit that transaction (if successful). Then do something like 

$dbh->query('USE otherdb');
$dbh->exec();

Then do a second transaction, and commit or rollback based on whether or not it worked.

I'm not sure if this really answers what you are asking though.

Upvotes: 0

Jon
Jon

Reputation: 437424

No, PDO cannot do anything remotely resembling distributed transactions (which in any case are a very thorny issue where no silver bullets exist).

In general, in the presence of network partitions (i.e. actors falling off the network) it can be proved that you cannot achieve consistency and availability (guaranteed response to your queries) at the same time -- see CAP theorem.

It seems that you need to re-evaluate your requirements and design a solution based on the results of this analysis; in order to scale data processing or storage horizontally you have to take the scaling into account from day one and plan accordingly.

Upvotes: 2

Related Questions