I have a use-case where I have two different databases -- one local (let's say Postgres) and one remote (let's say MySQL) -- and I need to guarantee that they have the same data. The databases don't matter, this is more a conceptual question. How can I perform what amounts to a 'transaction' across these two databases? In pseudo-code:
-- local db
BEGIN TRANSACTION 'local';
UPDATE table SET name='todd' WHERE id=1;
-- remote db
BEGIN TRANSACTION 'remote';
UPDATE table SET name='todd' WHERE id=1;
-- save
COMMIT TRANSACTION 'local';
COMMIT TRANSACTION 'remote'; -- or ROLLBACK
The 'second save' is easy -- because we either ROLLBACK if the first one failed or COMMIT if the first one succeeded. But how would we ROLLBACK the first one if for-example the first one succeeded and the second one failed (for whatever reason).
Is there a known algorithm to minimize this risk or properly deal with this?
Update: where a database supports distributed transactions, this can be done with the PREPAPRE keyword -- supported in both MySQL and Postgres. Having said that, many databases (if not most) databases will not support this, so it'd be interested to see how this might be accomplished across one of those databses as well, for example SQLite being used as the Local DB. Here's also an interesting article about using SQLite for something like this: http://www.scs.stanford.edu/20sp-cs244b/projects/Distributed%20SQLite.pdf.