Experience with distributed transactions and MySQL?

Question

MySQL

I'm building a project using JMS + DB distributed transactions.
The database is MySQL Percona (innodb).
Can someone share their experience of using MySQL in XA mode?
Interested in practice bugs, glitches, rakes, subsidence in performance, bitter experiences, and so on.

Answer 1

There is a very unpleasant problem:
As soon as 2 different connections are connected to the same database, on the same server, XA transactions between them fall off, they are simply not supported. The version was 5.5 or 5.6.
After this annoying problem, I transferred to PostrgeSQL no problems have been found so far. I would not recommend using MySQL in conjunction with JTA (which is essentially the same as your case)

Answer 2

1. Strange, why is the author not interested in XA problems with his JMS implementation? After all, you want to combine two providers into one transaction: DBMS and JMS. Moreover, historically, DBMSs have been more advanced in terms of transactions, this is one of their main functions, while message delivery services have a slightly different bias. It seems to me that possible problems with XA at ​​your JMS provider should be taken care of in the first place.
2. From personal experience: I somehow encountered a similar situation, there was a desire to combine JMS (IBM WebSphere MQ) and DBMS (Oracle) into one XA transaction. It didn't work, there were some glitches. It was a very long time ago, maybe I didn’t figure it out to the end, maybe the XA technologies were still raw then, it doesn’t matter. I then went the other way. The project used ORM (Hibernate), respectively, all calls to the DBMS went through this layer. So, I thought that since everything is done through Hibernate, then why not see what is there in terms of combining various resources into one transaction. And it turned out that there is. At org.hibernate. Transaction has a registerSynchronization method that takes an object that implements the beforeCompletion andafterCompletion . In the beforeCompletion method, I implemented the completion of the JMS transaction. Of course, this approach can be considered less reliable than XA technology, but absolutely reliable transactions do not exist. I will only add that the system is highly loaded (about a million transactions per day, a third of them using the above-described combination of DBMS and JMS, and for 6 years of industrial operation there were no questions / problems associated with just such an implementation. So, in my opinion, a worthy option , which does not have glitches (at least in one system in 6 years).