How to fix ERROR 1614: Transaction not found in MySQL

First, determine whether the XID is known to MySQL at all. For MySQL 8.0+, query the performance schema:

SELECT * FROM performance_schema.events_transactions_current
WHERE THREAD_ID = <your_thread_id>;

Or, check all prepared XA transactions globally:

XA RECOVER;

The XA RECOVER command returns a list of all transactions in PREPARED state. If your XID is not listed, it either never reached PREPARED state, was already committed/rolled back, or the session is different.

What this tells you: If the XID doesn't appear, error 1614 is inevitable because MySQL has no record of that transaction. You'll need to skip the error (in replication) or inject an empty transaction (see below).

In application code, ensure the same database connection is used for the entire XA workflow:

// WRONG - connection pooling recycled the connection
connection = getConnection();
connection.execute("XA START 'xid1'");
connection.close(); // Bad!

connection = getConnection(); // May be a different physical connection
connection.execute("XA COMMIT 'xid1'"); // Error 1614!

// CORRECT - pin the connection for the entire transaction
connection = getConnection();
try {
  connection.execute("XA START 'xid1'");
  connection.execute("INSERT INTO users VALUES (...)");
  connection.execute("XA END 'xid1'");
  connection.execute("XA PREPARE 'xid1'");
  connection.execute("XA COMMIT 'xid1'");
} finally {
  connection.close(); // Only after all XA operations complete
}

Connection pooling libraries like HikariCP or Druid must be configured to "pin" connections during distributed transactions—not recycle them between XA phases.

Why this matters: Each XA transaction is tied to the session that started it. If the connection is recycled, MySQL's XA list for that session is cleared, and the XID becomes invisible.

If replication is stuck because the replica received an XA COMMIT for an unknown XID, you can inject a placeholder transaction on the replica so it can catch up:

-- Example: replica received "XA COMMIT X'1a',X'a1',1" but XID was never prepared

-- 1. Start an empty XA transaction with the same XID
XA START X'1a',X'a1',1;

-- 2. End it (no actual work needed)
XA END X'1a',X'a1',1;

-- 3. Prepare it
XA PREPARE X'1a',X'a1',1;

-- 4. Commit it
XA COMMIT X'1a',X'a1',1;

-- Now replication can resume
START SLAVE;

This creates a "dummy" prepared transaction that replication can then commit naturally, unblocking the replica.

When to use: Only when replication is halted and you've verified the XID doesn't exist. This requires super privilege and careful coordination.

Starting in MySQL 8.0.17, the system variable xa_detach_on_prepare allows XA transactions to be completed by any session after PREPARE. This is enabled by default in MySQL 8.0.29+:

-- Check the current setting
SHOW VARIABLES LIKE 'xa_detach_on_prepare';

-- If you're on 8.0.17-8.0.28, enable it explicitly
SET GLOBAL xa_detach_on_prepare = ON;

With xa_detach_on_prepare = ON, after XA PREPARE, the transaction is detached from the originating session. Any session can then XA COMMIT or XA ROLLBACK it, which is safer for connection pooling scenarios.

Benefit: Reduces the risk of error 1614 caused by connection recycling, because the prepared XID is not tied to a specific session anymore.

If replication is blocked after a source crash, you may need to skip the problematic XA COMMIT event:

-- Stop the replica
STOP SLAVE;

-- Check the current position
SHOW SLAVE STATUS\G

-- Skip the next N events (usually 1 for the failing XA COMMIT)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-- Resume replication
START SLAVE;

-- Verify it's working
SHOW SLAVE STATUS\G

This tells the replica to skip the XA COMMIT event in the binlog. Use this only if:
- You've confirmed the XID doesn't exist and can't be recovered
- You're willing to accept potential data inconsistency
- You have a backup plan to resync if needed

Note: This is a last resort. Only use after exhausting other options.