4

I have a use case where we need to use both MySQL and MongoDB in a distributed transaction i.e. if either of MySQL or MongoDB fails, both MySQL and MongoDB must fail i.e. all the operations done by MongDB and MySQL should get reverted if any of the database fails.

I know about two phase commit protocol or XA transaction and have successfully implemented it with MySQL and zope transaction. But, now I want it to implement with both MongoDB and MySQL.

I have also read about, this question on stackoverflow, but it seems outdated.

With MongoDB 4.2, MongoDB supports distributed transaction between different shards, but is there a way I can use it to implement distributed transaction in MySQL and MongoDb.

I do not want eventual consistency. I am looking for real distributed transaction i.e. unless and until I commit the changes, user should not be able to see the changed data in both MySQL and MongoDB

Preferred language is Python, but if there is way it cannot be implemented in Python but available in other languages it's fine.

Any kind of help will be great. Thanks!

Rupesh Mishra
  • 65
  • 5

2 Answers2

2

Not possible. The distributed transaction capability you cite in MongoDB 4.2 is for transactional integrity across shards in MongoDB, not across MongoDB and other databases, XA or otherwise.

Buzz Moschetti
  • 7,057
  • 3
  • 23
  • 33
  • Hi Buzz Moschetti, thanks for the answer. I know that MongoDB 4.2 transactions is for shading. I just wanted to know if there are any other mechanism using which I can achieve distributed transaction in MongoDB and MySQL combined. Like in MySQL, we can achieve it using Zope.sqlalchemy. – Rupesh Mishra Oct 29 '19 at 05:56
  • Although it's not quite what you'll want to hear, I asked for feedback from some senior techs in Percona after you tweeted to us. They have said that the only thing that they would want to advise in this scenario is that you shouldn't do it. In their opinion, you'll be storing up trouble for yourself. You maybe need to think around the challenge in some different way. (Disclosure: I work for Percona) – greenweeds Oct 29 '19 at 14:00
  • Hi ercona-lorraine, thanks for the comment. I will check if there is any other way that I can follow. Thanks again! – Rupesh Mishra Nov 04 '19 at 10:16
  • Hi Rupesh, Any luck with this use case? I do have a similar use case where am try to implement distributed transactions between Oracle and MongoDB. Here is my use case https://stackoverflow.com/questions/69156072/achieve-all-or-nothing-transaction-between-oracle-and-mongodb?noredirect=1#comment122229385_69156072 Please advise if you see any breakthrough. Thanks. – Mohan Sep 13 '21 at 13:37
2

It is impossible to achieve ACID across multiple databases, because they don't share a version number.

The TCC pattern is described in detail here: https://dev.to/yedf2/best-practice-for-tcc-distributed-transaction-in-go-402m

In order to solve the problem, you can use TCC transaction, which provide you the ability to control data visibility. TCC has three parts:

  • Try part: attempts to execute, completes all business checks, reserves necessary business resources.
  • Confirm part: if all branches succeed in the Try phase, then we move to the Confirm phase, where Confirm actually executes the business without any business checks, using only the business resources reserved in the Try phase
  • Cancel part: If one of the Tries in any branches fails, we go to the Cancel phase, which releases the business resources reserved in the Try phase.

The Try-Confirm-Cancel are very likely to XA's Prepare-Commit-Rollback, but implemented by applications, not database.

Suppose you are doing a cross-bank transfer, then you can freeze the transfer amount in Try, and adjust the amount in Confirm, and unfreeze the amount in Cancel. The result is quite similar to XA distributed transaction.

This project provide a way to do a distribution across mongo, mysql and redis: https://github.com/dtm-labs/dtm

An example of distributed transaction across mongo, mysql, and redis can be found here: https://github.com/dtm-labs/dtm-examples

yedf
  • 176
  • 5
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/31578820) – Srikrushna Apr 25 '22 at 06:50
  • @Srikrushna essential parts has been added to the answer – yedf Apr 25 '22 at 13:27