For most people (including me until recently) database XA transactions are a fuzzy concept. In over eight years with Percona, I have never had to deal with XA transactions. Then a few weeks ago I got two customers having issues with XA transactions. That deserves a post.
XA 101
What are XA transactions? XA transactions are useful when you need to coordinate a transaction between different systems. The simplest example could be simply two storage engines within MySQL. Basically, it follows this sequence:
- XA START
- Some SQL statements
- XA END
- XA PREPARE
- XA COMMIT or ROLLBACK
Once prepared, the XA transaction survives a MySQL crash. Upon restart, you’ll see something like this in the MySQL error log:
2017-08-23T14:53:54.189068Z 0 [Note] …[Read more]