Achieve "all-or-nothing" transaction between Oracle and MongoDB

Question

I have an usecase(migrate data from Oracle to MongoDB via Java app) where I required to implement transaction management to ensure "all-or-nothing" in the datastore.

1. Read the input data from Oracle Table (eg: sourcetable).
2. Execute two separate stored procedure with that input and process the output cursor and construct two different Mongo document (that get interested into two different collections in a same datasource[collection1 & collection2] respectively).
3. If the above steps completed successful then update the above Oracle table (eg: sourcetable) with the status as migration successful.
4. Perform commit(both MongoDB & Oracle) only if all the above steps completed successfully.
5. If any error at any step perform Rollback on the entire transaction.

I see Oracle offers Two Phase Commit and MongoDB offers Distributed Transactions to achieve these things separately but,I was looking for the way to achieve it together. Also, I have no clue on how to implement it or is that the right solution to my use case or not. I really appreciate for any guidance or pseudo code implementation.

Note: All these process will execute in a distributed environment.

Answer 1

Here is an article explaining how to do distributed transactions across different kinds of databases: https://betterprogramming.pub/how-to-implement-a-distributed-transaction-across-mysql-redis-and-mongo-9f6c7448b3b5

MongoDB do not support XA, so dtm-labs/dtm provide Saga pattern to solve the problem like this. If you want more control on data isolation, you can use TCC pattern instead of Saga pattern.