MySQL transaction over two databases on different servers
mysql, transactions
Solution
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.
Problem
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