Home » Software Development » Mysql versions prior to 5.7 do not fully support two phase commit

About Ant Kutschera

Ant Kutschera
Ant is a freelance Java architect and developer. He has been writing a blog and white papers since 2004 and writes about anything he finds interesting, related to Java or software. Most recently he has been working on enterprise systems involving Eclipse RCP, Google GWT, Hibernate, Spring and J2ME. He believes very strongly in being involved in all parts of the software life cycle.

Mysql versions prior to 5.7 do not fully support two phase commit

While doing some tests for the recently released generic JCA adapter which is capable of binding remote calls to microservices (as well as other things) into JTA transactions, I discovered a bug in Mysql 5.6 which has been around for nearly ten years.

The test scenario was a crash after the “prepare” phase of the XA transaction, after both the database and the generic connector vote to commit the transaction. After crashing the database or the application server, the transaction manager will try to recover and commit the transaction in the database. But while testing the crashes with Mysql 5.6 rather than Mysql 5.7, I was having the problem that the database never actually committed the transaction, meaning that the system as a whole was in an inconsistent state. There were absolutely no problems with the generic connector, just the database. The application server continuously logged that there was an incomplete transaction but was unable to complete the transaction in the database. During the simulated database crash, the application returned a HeuristicMixedException to indicate to the user that something is not and will not ever be consistent. The error logged by JBoss was:

...WARN [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016037: Could not find new XAResource to use for recovering non-serializable XAResource XAResourceRecord < resource:null, txid: <...eis_name=unknown eis name >, heuristic: TwoPhaseOutcome.FINISH_OK ...> 

I spent time debugging in the Mysql driver code but eventually came across MySQL Bug #12161 which has only just been closed after being open for nearly 10 years! It is clearly a point where the database is not two phase commit compatible, because remember what Wikipedia states: “… Log records, which are typically slow to generate but survive failures, are used by the protocol’s recovery procedures.” In the case of Mysql 5.6 and previous versions, its log records do not survive failures, as documented in the bug report. Additionally, section 7.6.2.8 of the JCA spec 1.6 says we must not erase knowledge of the transaction branch until commit or rollback is called, again which Mysql is not adhering to.

Unfortunately the fix is not yet in the 5.6 GA version of Mysql, rather only available in the 5.7 DMR version. But that is due to become a GA release soon, and other databases like Postgres and Oracle do not have this issue. The problem is also clearly described in the JBoss manual (RTFM!), which also provides tips on getting XA recovery working with Oracle where special access needs to be granted to the appropriate user (which has caught us out in the past). More information on this problem is located here. Further tests with the H2 database sadly showed that it too does not support recovery of XA transactions.

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of