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

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.

javamongodboracle12ctwo-phase-commit


Solution 1:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions