MongoDB and MySQL transaction in a distributed transaction

Question

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!

Answer 1

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.

Answer 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