Distributed, Multi-Database Transactions Involving MariaDB and PostgreSQL

In some situations, an application needs to run a single logical transaction that involves multiple database technologies: in our example, they’ll be MariaDB and PostgreSQL. This is not an optimal scenario and I’m not recommending to design systems in this way. But it’s simply a situation that you might have to deal with in real life, for various reasons that are outside of the scope of this article.

Multi-database work implies several problems, because a transaction must be atomic, but in a distributed architecture this is very hard to guarantee. This article explores how to run distributed transactions using similar built-in features of MariaDB and PostgreSQL: two-phase commit transactions (2pc transactions).

This is not the only solution and it’s not always the best. There are different patterns to deal with this situation, and there is software that implement these patterns for you. But 2pc transactions are relatively simple to use for developers, and they only imply sending some special SQL commands to the database.

We’ll also discuss which problems you will solve using 2pc transactions, and which problems will arise because of this solution.

See also the xa-utils repository that contains bonus material for this article.

Atomicity and Durability

Important characteristics of transactions are atomicity and durability. Atomicity means that multiple writes to multiple tables can be enclosed in a single transaction, and yet that transaction can only completely succeed or completely fail. You can issue a ROLLBACK command to revert the changes, or the changes might be reverted automatically because an error of some kind causes the transaction to fail. But in no case will the changes be partially reverted.

This includes the cases when the database crashes. Transactions that are not complete will never be applied. Transactions that ended with a successful COMMIT command and made some changes won’t be lost. This is called durability.

Single-database transactions are held by a single technology, that implements atomicity and durability entirely. MariaDB implements transactions in a classic way, by using transaction logs that can always be used (in conjunction with lazily updated tablespaces) to reconstruct an exact version of the data. PostgreSQL does this in a more simplistic way, by storing each physical version of each row in the files, periodically removing old unused versions, and trying to keep an index visibility map that prevents too many accesses to obsolete versions. But the way the DBMSs implement durability is important for the DBAs, not for developers: from an end-user perspective, all databases behave more or less in the same way.

But how to guarantee atomicity and durability when multiple technologies are involved? The solution is conceptually simple: the transaction has two commits, or if you prefer, it has a preparation phase that needs to precede the commit.

A Single-Database 2pc Transaction Workflow

For each involved database, the workflow will be the following:

1. The transaction starts.
2. Data changes are sent to the database.
3. The transaction is prepared. This means that the transaction itself is persisted, and will survive a connection drop or a database crash. But the data changes are not applied yet, and are not visible to other transactions. Importantly, rollback is still allowed.
4. The transaction is committed. The changes are applied to the data, and become visible to new transactions.

A Multi-Database Workflow

It’s important to understand the above workflow for a single-database 2pc transaction. But now, let’s see the overall workflow for a transaction that involves several databases.

1. The application starts to write data into the databases involved in the transaction.
2. As soon as possible, the application will run a prepare command. It’s entirely possible to prepare a transaction on a database, and then continue to write into other databases.
3. Did any prepare command fail? If so, the application will run a rollback command against all the involved databases.
4. Did all prepare commands succeed? If so, the application will run a commit against all the database.

Which Problems Still Hold

Database servers may crash or become unreachable. Application servers might crash too. In theory 2pc transactions solve these problems, because the preparation phase persists the transaction, but if something fails elsewhere, the application can still rollback the prepared transaction. In practice, though, a database crash at the wrong time can still cause headaches, or worse.

Let’s see what can go wrong.

A database crashes and won’t come back in a reasonable time

A prepared transaction still has active locks. This is by design. Row locks and metadata locks can’t be released before commit, because the application might never decide to apply those changes. But what if one of the involved databases has crashed and its data is corrupted? Bringing it up again might take a long time.

You’ll have to rollback the transaction immediately on the databases that are up and running. Once the failed database restarts, you must make sure that the transaction is rolled back.

A database permanently dies after other databases performed a commit

Your application verifies that all preparations succeeded. It starts to send commits to all the involved databases. But… after at least one commit succeeded, one commit fails because a server is gone for good. Maybe the data centre is on fire, maybe SSH accesses are gone and beyond repair. You’ll be able to restore data in the end, but you’ll have to use a backup, and this will take hours.

You might have to redo the transaction. In the best case, the data you need to write are the same data written in one of the other databases involved in the transaction. If some data is missing from the other databases, considering writing it to allow this type of recovery. For example, if only a state column is present in database B but absent from database A, consider adding it and keeping it updated. This won’t make recovery easy, but at least it should be possible.

An alternative is running a compensatory transaction in the databases that are still up and running. The application should have the logic to do this. Sometimes it’s relatively easy: if the application INSERTed a row, all it has to do is to DELETE it. But if the application DELETEd or UPDATEd rows, the application needs to somehow remember the old values, to be able to rewrite them.

With MariaDB, you can use system-verioned tables. The older versions of a DELETEd or UPDATEd row still exist. You can explicitly query old rows, and retrieve the values to restore. Unfortunately, PostgreSQL doesn’t have this feature.

Clearly, I’m simplifying things. When you decide to compensate a transaction that shouldn’t have been committed, the rows might have been further modified by other transactions that were correctly committed. This case is complex to handle, and the right thing to do depends on too many factors – I can’t provide a generic guidance on this.

The application server crashes and restarts while the DBA is taking action

To run 2pc transactions, you might want to use a transaction orchestrator, or transaction manager, like Apache ShardingSphere or Atomikos. They automatically handle preparations, commits and rollbacks for a multi-database transaction. Even if the application server crashes, they will take care of prepared transactions on restart.

But in the meanwhile, a DBA or some other automation take care of the prepared transacctions. And these actors (the transaction manager and the DBA) might disagree on what to do: the transaction manager might know that some transactions can be committed on all involved databases, but a DBA might decide to rollback everything everywhere “just to be sure”. This might lead to have a partially committed and partially rolled back transaction.

How to prevent this issue? Configure the transaction manager to always rollback all transactions on restart, if this is possible. If it’s not possible, the DBA (or any automation script) must somehow make sure that the transaction manager won’t resurrect at the wrong time. Or they might temporarily revoke all their permissions on the database.

MariaDB Syntax

MariaDB syntax for 2pc transactions adheres to the XA Open/X standard:

XA START '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;
-- read or write some data here ...
XA END '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;
XA PREPARE '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;
XA COMMIT '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;

'019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1 is the transaction id, which consists of three components. Only the first is mandatory. The second component is the branch id, and it could be a generical '.mariadb' or an id for the current MariaDB server or cluster. If the component has a meaning, it’s a good idea to start it with a separator, because when we list the prepared transactions it will appear concatenated to the first component. The last component is meant to be a version number of the format, usable to know how the first components should be interpreted. It’s 1 by default. MariaDB doesn’t interpret these components, but if you use a transaction manager, it might interpret them. The id of a committed or rollbacked transaction can be reused.

During the transaction, you might realise that you don’t need to write to other databases, so you might want to skip XA PREPARE and use a one-phase connection instead:

XA START '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;
-- read or write some data here ...
XA END '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;
XA COMMIT '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1 ONE PHASE;

To rollback:

XA ROLLBACK '019bad8c-2ea2-7080-9332-3274861c1969', '.maria', 1;

To list prepared transactions, and then decide what to do with them:

MariaDB [(none)]> XA RECOVER;
+----------+--------------+--------------+---------------+
| formatID | gtrid_length | bqual_length | data          |
+----------+--------------+--------------+---------------+
|        1 |           13 |            0 | Transaction 1 |
|        3 |            3 |            6 | t31.maria     |
|        1 |           11 |            0 | xxx-xxx-xxx   |
+----------+--------------+--------------+---------------+

MariaDB [(none)]> XA RECOVER FORMAT = 'SQL';
+----------+--------------+--------------+-----------------------------+
| formatID | gtrid_length | bqual_length | data                        |
+----------+--------------+--------------+-----------------------------+
|        1 |           13 |            0 | 'Transaction 1'             |
|        3 |            3 |            6 | X'743331',X'2e6d61726961',3 |
|        1 |           11 |            0 | 'xxx-xxx-xxx'               |
+----------+--------------+--------------+-----------------------------+

MariaDB [(none)]> XA ROLLBACK X'743331',X'2e6d61726961',3;
ERROR 1402 (XA100): XA_RBROLLBACK: Transaction branch was rolled back

MariaDB [(none)]> XA RECOVER FORMAT = 'SQL';
+----------+--------------+--------------+-----------------+
| formatID | gtrid_length | bqual_length | data            |
+----------+--------------+--------------+-----------------+
|        1 |           13 |            0 | 'Transaction 1' |
|        1 |           11 |            0 | 'xxx-xxx-xxx'   |
+----------+--------------+--------------+-----------------+

FORMAT = 'SQL' is very convenient, because it shows the transaction id exactly as it should appear in XA COMMIT or XA ROLLBACK.

PostgreSQL Syntax

PostgreSQL syntax is not based on a particular standard, so it might not work with some transaction managers.

In PostgreSQL you start a transaction normally. If you decide to commit in one phase, you will use no special syntax. If you decide to use a two-phase commit, you’ll need to use PREPARE TRANSACTION and COMMIT PREPARED.

START TRANSACTION;
PREPARE TRANSACTION '019badbc-bb6b-7eb0-b5ac-439ade362710';
COMMIT PREPARED '019badbc-bb6b-7eb0-b5ac-439ade362710';

To rollback:

ROLLBACK PREPARED '019badbc-bb6b-7eb0-b5ac-439ade362710';

To list the prepared transactions:

postgres=# SELECT * FROM pg_catalog.pg_prepared_xacts;
 transaction |                 gid                  |           prepared            |  owner   | database 
-------------+--------------------------------------+-------------------------------+----------+----------
         769 | 019badbc-bb6b-7eb0-b5ac-439ade362710 | 2026-01-15 22:50:16.098882+00 | postgres | postgres
         771 | trx2                                 | 2026-01-15 22:51:27.578579+00 | postgres | postgres
         772 | trx3                                 | 2026-01-15 22:51:48.320954+00 | postgres | postgres

postgres=# ROLLBACK PREPARED '019badbc-bb6b-7eb0-b5ac-439ade362710';
ROLLBACK PREPARED

postgres=# SELECT * FROM pg_catalog.pg_prepared_xacts;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
         772 | trx3 | 2026-01-15 22:51:48.320954+00 | postgres | postgres
         771 | trx2 | 2026-01-15 22:51:27.578579+00 | postgres | postgres

A Complete Scenario

Now that we discussed how XA transactions work and how to use them, let’s see a complete example in a realistic scenario.

The use case is the following: we have two applications working with inventory data. The ecommerce platform uses MariaDB and the procurement application uses PostgreSQL. Both read and write data. Importantly for this example, both can decrease a product’s availability (we’re a reseller, and the produrement app handles returns of flawed products to the vendor). This is hopefully a temporary situation, as one of the applications will be abandoned or ported to the other database. But in the meanwhile, the company must be able to operate.

Here’s what should happen when a customer buys a product:

1. We check for product availability in MariaDB. If it’s equal or higher than the desired quantity, the purchase can take place. But we need to make sure that concurrent transactions don’t read or modify the quantity before the purchase is completed or cancelled.
2. We check for product availability in Postgres, too. And again, we need to make sure that no one can read or modify the quantity before the purchase has completed. If we can’t acquire the lock immediately or if the quantities don’t match, we don’t have a reasonable guarantee of consistency. So the purchase will fail – but it may retry immediately, or after a short timeout.
   • Special case: if we don’t find the desided product in Postgres, the product is not handled by the procurement app. In this case, we’ll complete the MariaDB transaction with a one-phase commit. This is not strictly necessary, but it’s a performance optimisation we can use when we realise that a 2pc is not needed.
3. We modify the quantity in MariaDB (UPDATE)..
4. We PREPARE the XA transaction in MariaDB.
5. We modify the quantity in PostgreSQL (UPDATE).
6. We PREPARE the 2pc transaction in PostgreSQL.
7. If every former step succceeded, we COMMIT the XA transaction in MariaDB. Otherwise, we ROLLBACK.
8. If every former step succceeded, at least up to point 6, we also COMMIT the XA transaction in PostgreSQL. Otherwise, we ROLLBACK.
9. If a database crashed before the final COMMIT, we will COMMIT the transaction at restart.

The SQL syntax was explained above, so I’m not including the commands here for the sake of brevity.

It is important to have a log of these operations at application level, to be able to debug all sorts of problems – including applicaiton bugs or DBMSs bugs. To make a logical distributed transaction easier to follow across the log, it would be useful to use the same transaction id on MariaDB and PostgreSQL.

Locks and Performance Considerations

To achieve true global consistency, XA transactions should theoretically use the SERIALIZABLE isolation level. This is the same as REPEATABLE READ, except that reads acquire shared locks on the rows they examine. In this way, writes requested by other transactions will be delayed until commit or rollback. This is important because transactions are not committed at the same time in every database. So, without locks, data would be globally inconsistent.

SERIALIZABLE can come with serious scalability issues. It might result into transactions constantly waiting for each other. For this reason, REPEATABLE READ is normally the right choice. It is the default in MariaDB, but it should be set manually in PostgreSQL (the default is READ COMMITTED).

Note that 2pc transactions are not lightweight. Generally speaking, it is recommended that an application doesn’t do anything else in the middle of a transaction, to end the transaction as soon as possible. The reason is that long transactions tend to damage a database performance and they keep locks alive for more time. This recommendation can’t be applied to 2pc transactions because they’re meant exactly to allow you to run other transactions on other databases, while at least database is waiting. You should still try not to make 2pc transactions longer than necessary.

2pc transactions don’t release locks on preparation. They can’t, because the database doesn’t know yet if the transaction will be committed or not. So the applications should be prepared to wait for longer lock times. Deadlocks are also possible. While deadlocks that involve a single database can still be detected, deadlocks that involve multiple databases cannot. So the timeout should be long enough to allow the application to tolerate frequent normal locks, but short enough to avoid incidents in case of distributed deadlocks.

Limitations

MariaDB and PostgreSQL have similar limitations concerning 2pc transactions.

Common Limitations

The XA standard separates database connections from transaction. A connection should be able to detach from a transaction, and another connection should be able to take control of that connection and continue its work. This is supported by Oracle, DB2 and SQL Server. But MariaDB and PostgreSQL don’t support this feature. MariaDB supports the syntax to do this, but it will return an error, except for the case when a transaction is suspended by a connection, and the same connection later resumes the same transaction.

In both MariaDB and PostgreSQL, observability is limited. You can monitor transactions, regardless they have 1-phase or 2-phase commits. But in the case of 2-phase commits, it would be useful to know which transactions have been prepared. If nothing else, because you can’t kill them unless you’re sure that they are rolled back in other databases.

MariaDB Limitations

In MariaDB XA transactions are only supported by the InnoDB, MyRocks, and SPIDER storage engines. For InnoDB, before MariaDB 10.3, XA transactions could be disabled via the innodb_support_xa variable, but they were enabled by default and can’t be disabled anymore. Disabling them was a bad idea even when users weren’t supposed to use XA transactions, because MariaDB used them internally. Disabling them meant having no guarantee that the transactions appeared in the binary log in the correct order, which would have led to replication inconsistencies or failures.

PostgreSQL limitations

In PostgreSQL, 2pc transactions are disabled by default. To enable them, set max_prepared_transactions to a number > 0 in the configuration file.

PostgreSQL doesn’t follow a standard. As a consequence, some transaction coordinators might not support it.

In XA, transaction id’s have three components. In PostgreSQL, an id is just a string. While you can logically split this string into three substrings, most probably transaction managers that support PostgreSQL won’t support any particular logic involving these components.

PostgreSQL’s logic is more error-prone than the XA standard. With the standard, you start an XA transaction using a special syntax. You’ll still be able to commit in one phase if you realise that you don’t need to start transactions on other databases, but you’ll need to specify COMMIT ... ONE PHASE explicitly. With PostgreSQL, you start transactions without specifying if you wish a one-phase or a two-phase commit. As a consequence, if your code has a bug, you might improperly run a single commit when a two-phase commit is needed.

For XA transactions, MariaDB requires an isolation level of REPEATABLE READ (MariaDB’s default) or SERIALIZABLE. This is correct: even REPEATABLE READ doesn’t offer sufficient consistency guarantees for distributed transactions, but requiring locking reads might lead to big performance problems. PostgreSQL accepts READ COMMITTED (PostgreSQL’s default), which could improve scalability but at the expence of consistency. It also accepts READ UNCOMMITTED, but in PostgreSQL that is a synonym for READ COMMITTED.

Bonus Material

Some extra material can be found in the xa-utils repository. This includes a cheatsheet for MariaDB and PostgreSQL, and stored procedures to rollback all prepared transactions after a server restart (but before accepting client connections).

Conclusions

Two-phase transactions allow us to coordinate transactions across multiple databases, making sure that every transaction will completely succeed or completely fail.

Both MariaDB and PostgreSQL support two-phase commit transactions. MariaDB does it by following the XA standard, PostgreSQL implements non-standard statements. Their implementation is limited compared to XA support in databases like Oracle or DB2, but it’s sufficient for handling practical scenarios.

We discussed 2pc transactions, as well as the problems that are not resolved by 2pc transactions, or are caused by them. Some of these problems don’t have an easy solution in complex cases. These are edge cases that rarely occur, so they shouldn’t discourage the use of XA. But DBAs should be aware of these problems, and have plans to follow if things go wrong.

If you need to understand better which problems might affect your particular scenario, or if you need help in setting up recovery procedures, contact us for a consultation.

Federico Razzoli