AWS RDS MySQL kill transaction by ID (without thread_id)

Got a transaction ID from SHOW ENGINE INNODB STATUS or a lock-wait report, but no thread ID to pass to KILL? You can still end it. InnoDB already knows which connection owns every open transaction, so the job is one lookup and one procedure call. This is the usual AWS RDS MySQL kill transaction by ID (without thread_id) workflow, plus what to do when no connection owns the transaction at all.

MySQL can't kill a transaction directly, only the connection that owns it. Look up the transaction in information_schema.INNODB_TRX to get trx_mysql_thread_id, then run CALL mysql.rds_kill(<that id>);. If the thread ID is 0, it's an orphaned XA transaction, and you end it with XA ROLLBACK instead.

Why aws rds mysql kill transaction by id (without thread_id) needs a lookup first

Three different IDs get mixed up here:

• trx_id: the InnoDB transaction ID, shown as TRANSACTION 5678901 in SHOW ENGINE INNODB STATUS.
• Processlist ID: what SHOW PROCESSLIST shows in the Id column, and the only thing KILL accepts.
• THREAD_ID: performance_schema's internal thread number. It's not the same as the processlist ID, even though the names look alike.

KILL 5678901 with a trx_id either fails or, worse, kills an unrelated connection that happens to have that number. Always translate first.

RDS adds one more wrinkle. The master user doesn't have SUPER, so KILL on another user's connection usually returns:

ERROR 1095 (HY000): You are not owner of thread 1234

That's why RDS provides mysql.rds_kill. Aurora MySQL provides the same procedures.

For the rest of this guide, the running example is transaction 5678901, which has been holding row locks on orders for an hour.

Step 1: Find the connection that owns the trx_id

SELECT trx_id,
       trx_state,
       trx_started,
       trx_mysql_thread_id,
       trx_rows_modified,
       trx_query
FROM information_schema.INNODB_TRX
WHERE trx_id = '5678901';

What to expect:

• trx_mysql_thread_id is a normal number, for example 1234. That's your processlist ID. Go to Step 3.
• trx_mysql_thread_id is 0. No connection owns it. Skip to Step 5.
• The query returns no rows. The transaction already committed or rolled back.

trx_query is often NULL. That doesn't mean it's idle in a harmless way. It usually means the client opened a transaction, ran some statements, and is now sitting there without committing. In Laravel apps, a typical source is a DB::beginTransaction() whose commit got skipped because an exception was caught somewhere higher up.

Step 2: If you only have a performance_schema THREAD_ID

Sometimes you get a THREAD_ID from performance_schema.data_locks or a monitoring tool. Map it to the processlist ID:

SELECT THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_USER,
       PROCESSLIST_HOST,
       PROCESSLIST_TIME
FROM performance_schema.threads
WHERE THREAD_ID = 87;

PROCESSLIST_ID is what you'll kill. If you're investigating blocked queries and don't have any ID yet, the sys schema gives you the blocker directly:

SELECT waiting_pid,
       waiting_query,
       blocking_pid,
       blocking_trx_id,
       blocking_query
FROM sys.innodb_lock_waits;

blocking_pid is already a processlist ID. The view also has a sql_kill_blocking_connection column with a ready-made KILL statement, but on RDS swap it for mysql.rds_kill. If you need to trace which statements the blocker ran earlier, the approach in finding the query from a query_id in InnoDB helps.

Step 3: Check who you're about to kill

Don't skip this. Look at the connection first:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE ID = 1234;

Check that USER and HOST match something you expect, like your app user from a web server IP. If the user is rdsadmin, leave it alone. That's AWS's own management connection, and RDS won't let you kill it anyway.

COMMAND = Sleep with a large TIME confirms an idle connection holding an open transaction. That's the classic lock hog.

Step 4: Kill the connection with mysql.rds_kill

CALL mysql.rds_kill(1234);

The connection drops, and InnoDB rolls back its open transaction. The app sees a "server has gone away" or "lost connection" error on its next query, which is expected.

Gotcha: there's also mysql.rds_kill_query(1234). Skip it here. It stops only the current statement, the transaction stays open, and the locks stay held. For an idle transaction there's no running statement to stop, so it does nothing useful.

If you have several stale transactions, generate the calls instead of typing them:

SELECT CONCAT('CALL mysql.rds_kill(', trx_mysql_thread_id, ');') AS kill_cmd
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 10 MINUTE
  AND trx_mysql_thread_id <> 0;

Review the list before running it. Long-running reports or migrations can show up here legitimately.

Step 5: When trx_mysql_thread_id is 0

A transaction with no owner is almost always an XA transaction in the PREPARED state. The client called XA PREPARE and then disconnected or crashed before committing. It survives disconnects and even restarts, and it keeps its locks. There's no connection to kill.

List them:

XA RECOVER CONVERT XID;

You'll get formatID, gtrid_length, bqual_length and data. With CONVERT XID, data is shown in hex, which avoids problems with binary XIDs. Split the hex using the two lengths (each byte is two hex characters) and roll back:

-- gtrid_length = 6, bqual_length = 0, formatID = 1
-- data = 0x616263313233
XA ROLLBACK X'616263313233', '', 1;

If bqual_length is not 0, the second part of the hex goes into the second argument. Use XA COMMIT instead only if you know the distributed transaction was supposed to commit. Rolling back is the safe default when nobody knows.

If XA RECOVER returns an access error, your user lacks the XA_RECOVER_ADMIN privilege on newer MySQL versions. Grant it from the master user.

The other case with no owner is trx_state = 'ROLLING BACK', usually after a crash or a kill. Nothing kills a rollback. Wait for it. Rebooting the RDS instance doesn't help either, because crash recovery picks the rollback up again on startup.

Verify the transaction is gone

Run the Step 1 query again:

SELECT trx_id, trx_state, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE trx_id = '5678901';

You want an empty result. If the row is still there with trx_state = 'ROLLING BACK', it's undoing changes. A large trx_rows_modified means it can take a while, roughly in line with how much work the transaction did. Then confirm nothing is still waiting:

SELECT COUNT(*) FROM sys.innodb_lock_waits;

Next steps

Killing fixes the symptom. To stop idle transactions piling up, lower wait_timeout in your RDS parameter group so sleeping connections get closed (which rolls back their transactions), and make sure every beginTransaction() in your code has a matching commit or rollback in a finally path. If you also hit odd storage-level errors on self-hosted MySQL, this guide to error #1932 table doesn't exist in engine covers the InnoDB side. For help reviewing a production RDS setup, see HeadsNTail services.

FAQ

Can I kill a transaction without killing the connection in MySQL?

Not for a normal transaction. MySQL has no command that rolls back another session's transaction while keeping its connection open. You kill the connection, and InnoDB rolls the transaction back. XA transactions are the exception, since XA ROLLBACK works from any session.

Why does KILL say "You are not owner of thread" on RDS?

The RDS master user doesn't have SUPER, so it can only kill its own connections with plain KILL. Use CALL mysql.rds_kill(id) for connections owned by other users.

What is the difference between trx_id and thread_id in MySQL?

trx_id identifies an InnoDB transaction. The processlist ID (trx_mysql_thread_id) identifies the client connection that owns it, and that's what KILL and rds_kill take. performance_schema's THREAD_ID is a third, internal number that maps to the processlist ID through performance_schema.threads.

How long does rollback take after killing a long transaction?

It depends on how many rows the transaction changed. Rolling back a large write can take about as long as the original work did. Check progress with trx_rows_modified in INNODB_TRX.

Next time you have only an InnoDB ID, start with INNODB_TRX, and aws rds mysql kill transaction by id (without thread_id) becomes a two-query job.

Originally published at headsntail.com. Free tools for PHP, Laravel and cPanel work: headsntail.com/tools.