Support XA distributed transactions in CockroachDB

[knz]

X/Open XA is a standard for distributed transaction processing across multiple database engines. This is used in enterprise scenarios where data is spread across different technologies but applications need to preserve ACID semantics for transactions that span multiple database engines.

PostgreSQL supports XA transactions by a combination of two mechanisms:

• internally it supports the PREPARE TRANSACTION statement which enables a SQL transaction manager (TM) to "take over" transaction management
• the official pg ODBC driver contains an implementation of the standard MSDTP API (Microsoft's view on what XA should look like), so that pg becomes interoperable with other MSDTP-aware databases.
• ditto JDBC

For enterprise users CockroachDB should provide a path towards XA compatibility:

• provide a mechanism / API so that an external TM can take over transaction control in CockroachDB
• provide one or more driver extension that interoperates with one or more industry standard API (perhaps MSDTP or the java equivalent)

Strict compatibility with pg's PREPARE TRANSACTION would enable providing a XA API via pg's own ODBC driver.

cc @awoods187 for prioritization. cc @bdarnell for technical insights.

Also mentioned here: https://forum.cockroachlabs.com/t/xa-distributed-transactions/1336

Jira issue: CRDB-5863

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 5 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[SebTardif]

While looking if I 'could' use CockroachDB instead of PostgreSQL, I found out this was not yet implemented. It's a big deal. Without it you end-up with convoluted solution like -> https://senoritadeveloper.medium.com/microservices-transaction-management-patterns-46ef2df9a9c4

This would probably help thinking about how to test it -> https://docs.hazelcast.com/hazelcast/5.2/pipelines/xa