MariaDB XA Distributed Transactions: Two-Phase Commit Protocols, Prepared State Recovery, and Coordinator Failover for Financial Systems in Pakistan

In distributed enterprise architectures across Pakistan—particularly digital banking cores, Raast payment settlement engines, e-wallet ledgers, and multi-tenant billing platforms—a single business transaction frequently spans multiple autonomous database instances. Maintaining strict ACID properties (Atomicity, Consistency, Isolation, Durability) across isolated MariaDB nodes requires distributed transaction coordination.

Standard MySQL/MariaDB transactions (START TRANSACTION / COMMIT) cannot guarantee multi-database atomicity: if node A commits successfully while node B crashes during commit, the ledger is left in an unrecoverable, inconsistent state.

MariaDB’s implementation of the X/Open XA standard provides a Two-Phase Commit (2PC) protocol that enables global distributed atomicity. Understanding the XA state machine, handling coordinator failures, and resolving orphaned PREPARED states are essential skills for mission-critical database engineering.

---

The Two-Phase Commit (2PC) State Machine

An XA transaction separates the commit operation into two distinct phases managed by an external Transaction Coordinator (such as an application server, Narayana, or Bitronix):

       +-------------------------------------------------------------+
       |             Transaction Coordinator (Fintech App)           |
       +-------------------------------------------------------------+
               |                                             |
     [Phase 1: XA PREPARE]                         [Phase 1: XA PREPARE]
               v                                             v
+-------------------------------+             +-------------------------------+
|  MariaDB Node 1 (Ledger DB)   |             |  MariaDB Node 2 (Wallet DB)   |
|                               |             |                               |
| - Writes changes to Redo Log  |             | - Writes changes to Redo Log  |
| - Locks affected rows         |             | - Locks affected rows         |
| - Responds: "XA_OK"           |             | - Responds: "XA_OK"           |
+-------------------------------+             +-------------------------------+
               |                                             |
               +----------------------+----------------------+
                                      |
                     [All Nodes Responded XA_OK?]
                                      |
                    +-----------------+-----------------+
                    | YES                               | NO (Any failure/timeout)
                    v                                   v
          [Phase 2: XA COMMIT]                 [Phase 2: XA ROLLBACK]

When operating on mission-critical Dedicated Servers in Pakistan, hosting independent MariaDB database shards on isolated bare-metal hardware guarantees that hardware failures on one shard do not cascade into cluster-wide distributed locks.

---

Anatomy of an XA Transaction in MariaDB

An XA transaction identifier (xid) consists of three components:

1. gtrid: Global Transaction ID (shared across all participating nodes).
2. bqual: Branch Qualifier (identifies this specific node’s work within the global transaction).
3. formatID: Format Identifier (identifies the coordinator implementation).

Step-by-Step SQL Execution Example

Execute the following sequence on MariaDB Node 1:

-- Step 1: Start XA transaction with formatID=1, gtrid='TXN_20261002_001', bqual='NODE1_LEDGER'
XA START 'TXN_20261002_001', 'NODE1_LEDGER', 1;

-- Step 2: Execute normal DML operations
UPDATE accounts 
SET balance = balance - 15000.00 
WHERE account_id = 'PK89MEZN0001429108';

-- Step 3: End the active state of the transaction
XA END 'TXN_20261002_001', 'NODE1_LEDGER', 1;

-- Step 4: Phase 1 - Prepare the transaction
XA PREPARE 'TXN_20261002_001', 'NODE1_LEDGER', 1;

At this moment, MariaDB writes all modified data pages to the InnoDB redo log and flushes it to NVMe disk. The transaction enters the PREPARED state. The locks on the accounts table are firmly held, but the changes are not yet visible to other sessions.

Once the coordinator receives XA_OK from Node 2 (which performed the credit operation), it issues the Phase 2 commit:

-- Step 5: Phase 2 - Final Commit
XA COMMIT 'TXN_20261002_001', 'NODE1_LEDGER', 1;

---

Diagnosing and Recovering Orphaned PREPARED Transactions

The primary vulnerability of Two-Phase Commit occurs when the coordinator crashes between Phase 1 and Phase 2. The MariaDB nodes remain indefinitely in the PREPARED state, locking rows and preventing any other transaction from accessing those records.

Step 1: Discovering Orphaned Transactions

Query active XA transactions on the MariaDB instance:

XA RECOVER;

Sample output:

+----------+--------------+--------------+-----------------------------+
| formatID | gtrid_length | bqual_length | data                        |
+----------+--------------+--------------+-----------------------------+
|        1 |           16 |           12 | TXN_20261002_001NODE1_LEDGER |
+----------+--------------+--------------+-----------------------------+

Step 2: Resolving the Orphaned State

If the coordinator cannot be recovered and the ledger audit confirms that the corresponding transaction failed on peer nodes, the database administrator must manually abort the transaction:

-- Manually rollback the orphaned transaction using the exact XID
XA ROLLBACK 'TXN_20261002_001', 'NODE1_LEDGER', 1;

Conversely, if the peer nodes committed, force the commit manually:

XA COMMIT 'TXN_20261002_001', 'NODE1_LEDGER', 1;

---

High-Throughput Engine Configuration for XA Transactions

Because XA transactions require multiple synchronous disk flushes during the PREPARE and COMMIT phases, storage latency is the primary throughput bottleneck.

Optimize /etc/my.cnf.d/server.cnf:

[mariadb]
# Enable strict XA support in InnoDB
innodb_support_xa = 1

# Synchronize binary log and InnoDB redo log
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# Dedicated undo log tablespaces for fast rollback resolution
innodb_undo_tablespaces = 4
innodb_undo_log_truncate = ON

# Lock wait timeout for distributed transactions (prevent infinite wait)
innodb_lock_wait_timeout = 30

Deploying distributed transaction clusters on bare-metal Dedicated Servers provides dedicated enterprise NVMe storage arrays with power-loss protection (PLP), high IOPS, and low-latency private interconnects essential for resilient 2PC financial ledgers.