0

I have a table A in database db1 in server A and table B in database db2 in server B, both of which are MySQL.

Is it possible to make a transaction, where updates in both tables are involved?

                      +-> Server A | db1 | table A -+
Transaction:  Update -+                             + -> COMMIT / ROLLBACK
                      +-> Server B | db2 | table B -+

If so, kindly provide step by step instruction on how to achieve this.

5
  • You want an application to connect to 2 different servers or one server connects to another? if first - it seems possible (assume the connection will not break), if second - the answer is "no".
    – Akina
    Commented Aug 9, 2018 at 5:30
  • federated engines Commented Aug 9, 2018 at 5:45
  • @AnkitKapoor Transactions are not supported for FEDERATED Engine. proof
    – Akina
    Commented Aug 9, 2018 at 7:07
  • blog on XA by a MySQL guru. (Caveat: It was written in 2008.)
    – Rick James
    Commented Aug 24, 2018 at 0:09
  • @Akina Correct . Commented Aug 26, 2018 at 7:18

1 Answer 1

3

This is a duplicate question. It seems like this is a possibility with InnoDB as your engine, but other than that, unfortunately, it does not seem like there is a way. This also seems dependent on the version of MySQL, like 5.6 and above, the thread I linked to points to a documentation here.

A side note: just because this seems to a possibility, it may not be the best solution. It seems that it would be best to write the transaction, and use a script to run the transaction on both servers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.