XA transactions: two-phase commit
XA provides a resource-manager interface for externally coordinated transactions. The MySQL 8.4 manual states: "The MySQL implementation of XA enables a MySQL server to act as a Resource Manager". A Transaction Manager must own the global decision and recovery. This lesson executes one MySQL branch, not a complete distributed commit.
A prepared transaction outlives its session
The scenario explicitly enables xa_detach_on_prepare. The XA-state manual states: "MySQL 8.4 supports detached XA transactions". With that setting ON, PREPARE detaches the branch, and another connection can resolve it. With it OFF, the branch remains associated with the original connection. The setting is part of this demonstration.
A> SET SESSION xa_detach_on_prepare = ON;
Query OK
A> SELECT @@session.xa_detach_on_prepare AS detached;
detached
----------
1
(1 row)A is one participant in a distributed transfer. It does its work, then PREPARES — phase one.
A> XA START 'transfer-42';
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
A> XA END 'transfer-42';
Query OK
A> XA PREPARE 'transfer-42';
Query OKXA PREPARE detached the transaction from the session: A itself no longer sees its own change.
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
M> XA RECOVER;
formatID | gtrid_length | bqual_length | data
----------+--------------+--------------+-------------
1 | 11 | 0 | transfer-42
(1 row)The prepared transaction still holds its row locks — with no session attached.
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- ER_LOCK_NOWAIT
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.M kills participant session A. No Transaction Manager process is modeled.
M> CALL kill_session('A');
Query OK
A> SELECT 1;
ERROR ERR_MYSQL_CONNECTION_CLOSED (HY000): Connection closedA's session is gone. XA RECOVER still lists the prepared branch and B's NOWAIT read still fails. Server restart is not tested here.
M> XA RECOVER;
formatID | gtrid_length | bqual_length | data
----------+--------------+--------------+-------------
1 | 11 | 0 | transfer-42
(1 row)
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.Phase two: B, another permitted connection, commits this detached branch by XID.
B> XA COMMIT 'transfer-42';
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
What PREPARE actually buys, and costs
After PREPARE, A reads the old balance 100; the prepared change has not committed. XA RECOVER lists the branch. B's NOWAIT read fails with 3572 before and after M kills A's connection. B then commits the prepared branch and reads 200. The scenario does not kill a coordinator process or restart the server.
The XA-state contract documents resolution of a PREPARED branch by XA COMMIT or XA ROLLBACK. The demonstrated branch retains its conflicting row lock until resolution. This does not establish a database-wide lock, retention of every lock type, undo-history growth, or survival of every possible failure.
Should you use it?
Use XA only with a recovery design that can reconcile prepared branches with the Transaction Manager's decision. A stranded branch can block conflicting work, as the NOWAIT probes show. XA RECOVER lists prepared branches, not only orphans. Seeing an XID is not sufficient reason to choose COMMIT or ROLLBACK; that choice must follow the coordinated outcome.
The outbox and saga lessons describe different failure boundaries. They are not equivalent guarantees or evidence of simpler production recovery. This transcript establishes session termination and cross-session branch completion only, with detachment enabled.