MySQL transaction over two databases on different servers

Question

Is it possible to make a MySQL transaction that changes databases on different servers?

I would need to:

insert into the first database
select from the first database
insert into the second database
if any of these fails, revert to the original state

possible duplicate of [Can I to a transaction across two MySQL databases?](http://stackoverflow.com/questions/5738685/can-i-to-a-transaction-across-two-mysql-databases) — eggyal, Jul 11 '13 at 15:50
No this is not a duplicate as the question states on different servers not just different databases. — Anigel, Jul 11 '13 at 15:52

score 6 · Accepted Answer · edited May 23 '17 at 12:21

As observed above, this question is very similar to Can I to a transaction across two MySQL databases? Whilst that question referred to different databases on the same server, its accepted answer still applies.

Indeed, as documented under XA Transactions:

Support for XA transactions is available for the InnoDB storage engine.
[ deletia ]
XA supports distributed transactions, that is, the ability to permit multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources.
[ deletia ]
Some examples of distributed transactions:
[ deletia ]
An application performs actions that involve different database servers, such as a MySQL server and an Oracle server (or multiple MySQL servers), where actions that involve multiple servers must happen as part of a global transaction, rather than as separate transactions local to each server.

MySQL transaction over two databases on different servers

1 Answers1