Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I perform a transaction across two MySQL databases?

Can I do?

Start transaction;

insert into db1.table1 (field1) values (100),(100);

insert into db2.table2 (field2) values (100),(100);

commit;

Or will that not work?

like image 445
Johan Avatar asked Apr 21 '11 01:04

Johan


People also ask

How do you achieve transactions when dealing with two different databases?

If your data is distributed across multiple databases, you may wish to update one database while reading from one or more other databases. This type of access can be performed within a single unit of work (transaction). This type of database access is called multisite update or two-phase commit.

Is it possible to connect to multiple databases?

Many database management and development tools support multiple connections to homogeneous databases, i.e., where they are all of the same type, ALL MySQL, ALL SQL Server, ALL Oracle, etc. On the other hand, very few support heterogeneous database servers, i.e. MySQL AND SQL Server AND Oracle, etc.

Can you have multiple MySQL databases?

Within an Azure Database for MySQL server, you can create one or multiple databases. You can opt to create a single database per server to use all the resources or to create multiple databases to share the resources.


1 Answers

Yes you can:

MySQL 5.0.3 and up provides server-side support for XA transactions. Currently, this support is available for the InnoDB storage engine. The MySQL XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at
http://www.opengroup.org/public/pubs/catalog/c193.htm. Ref: XA Transactions

like image 75
Mitch Wheat Avatar answered Sep 21 '22 07:09

Mitch Wheat