1

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.

Mohan
  • 3,893
  • 9
  • 33
  • 42
  • Are you using Spring? I know there is transaction support in Spring data: https://www.baeldung.com/transaction-configuration-with-jpa-and-spring – Fermi-4 Sep 13 '21 at 00:21
  • See https://stackoverflow.com/questions/58586687/mongodb-and-mysql-transaction-in-a-distributed-transaction – tgdavies Sep 13 '21 at 00:59
  • MongoDB "distributed" transactions aren't distributed in the XA sense, only in the "special Mongo-only" sense. – chrylis -cautiouslyoptimistic- Sep 13 '21 at 01:30
  • Thanks Fermi, tgdavies and Chrylis for taking time and checking. Am also exploring a way to solve it via programmatic way. – Mohan Sep 13 '21 at 13:41

1 Answers1

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.

yedf
  • 176
  • 5