Why KILL QUERY didn't release the row lock

Checkout requests fail with ERROR 1205, Lock wait timeout exceeded; try restarting transaction. sys.innodb_lock_waits names the blocker: thread 24802. You run KILL QUERY 24802, the server answers Query OK, and the waits go on. Thread 24802 still sits in the processlist as Sleep, and the next checkout fails the same way.

The locks belong to the transaction

InnoDB row locks belong to transactions. A transaction takes them as its statements run and releases them at COMMIT or ROLLBACK. Between statements, the connection that owns the transaction goes back to Sleep and waits for the client’s next command, and its locks stay in place.

Thread 24802 is in that state. The application ran UPDATE orders SET status = 'paid' WHERE id = 81244 inside a transaction, then went to call a payment provider, or hit an error path that skipped the rollback. The processlist shows Sleep, an empty Info column and a Time that counts seconds since the last statement. The row lock on order 81244 stays with the transaction until something ends it.

KILL QUERY and KILL CONNECTION

KILL QUERY stops the statement a connection is executing and leaves the connection and its transaction in place. KILL CONNECTION, or plain KILL, stops any running statement and then closes the connection; InnoDB rolls back the open transaction and releases its locks.

A sleeping connection executes nothing, so KILL QUERY finds nothing to stop, and the transaction keeps each lock it took. A thread that runs a statement inside a longer transaction has a smaller version of the same problem: KILL QUERY undoes that one statement, and the locks from earlier statements stay.

Finding the transaction

information_schema.INNODB_TRX lists each open InnoDB transaction. Join it to the processlist to see which ones sleep:

SELECT p.ID      AS id,
       p.USER,
       p.HOST,
       p.COMMAND,
       p.TIME    AS idle_s,
       t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
       t.trx_rows_locked,
       t.trx_rows_modified,
       t.trx_state
FROM information_schema.INNODB_TRX t
JOIN information_schema.PROCESSLIST p ON p.ID = t.trx_mysql_thread_id
ORDER BY t.trx_started;

A row with COMMAND = 'Sleep', a transaction age in minutes and a non-zero trx_rows_locked is your suspect. trx_rows_modified tells you how much work a rollback will have to undo.

On MySQL 8.0 and 8.4, sys.innodb_lock_waits confirms that the suspect blocks others. A blocking_query of NULL means the blocker runs nothing at the moment:

SELECT waiting_pid, waiting_query, wait_age_secs,
       blocking_pid, blocking_query, blocking_trx_age
FROM sys.innodb_lock_waits;

performance_schema.data_locks shows the rows the transaction holds, with the index and the key values in LOCK_DATA:

SELECT l.OBJECT_SCHEMA, l.OBJECT_NAME, l.INDEX_NAME,
       l.LOCK_MODE, l.LOCK_STATUS, l.LOCK_DATA
FROM performance_schema.data_locks l
JOIN performance_schema.threads t ON t.THREAD_ID = l.THREAD_ID
WHERE t.PROCESSLIST_ID = 24802;

For a transaction that locked millions of rows, this table returns millions of rows. Filter it by thread as above, and don’t poll it in a loop.

MariaDB has no data_locks table. It keeps information_schema.INNODB_LOCK_WAITS and INNODB_LOCKS, and the same question looks like this:

SELECT r.trx_mysql_thread_id AS waiting_id,
       b.trx_mysql_thread_id AS blocking_id,
       b.trx_query           AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

Ending it

KILL 24802;

The application that owned the connection gets an error on its next use of it. InnoDB rolls back the transaction, and the rollback holds the locks until it finishes. A transaction that modified a handful of rows rolls back in milliseconds. One that modified millions can take as long as the original work, and sometimes longer. While it runs, the processlist shows the thread with the command Killed, INNODB_TRX shows trx_state = 'ROLLING BACK', and trx_rows_modified counts down as InnoDB undoes the rows. A second KILL doesn’t speed it up, and restarting mysqld doesn’t skip it: InnoDB finishes the rollback after crash recovery.

Managed services give you procedures for accounts that can’t kill other users’ threads:

• Amazon RDS and Aurora MySQL: CALL mysql.rds_kill(24802); closes the connection, and CALL mysql.rds_kill_query(24802); stops the statement.
• Azure Database for MySQL Flexible Server: CALL mysql.az_kill(24802); and CALL mysql.az_kill_query(24802); do the same.

The _query variants have the same blind spot as KILL QUERY. For a sleeping root, call the connection version.

MariaDB adds a trap. There, KILL QUERY against an idle session can leave the thread showing Killed in the processlist while its transaction stays open and keeps its locks until the connection ends. That label reads like a finished job. Look the thread up in INNODB_TRX before you move on, and if its transaction is still there, run KILL CONNECTION.

Keeping it from happening again

• innodb_lock_wait_timeout limits the waiter. After 50 seconds by default, the waiting statement fails with ERROR 1205. Only that statement rolls back (innodb_rollback_on_timeout is off by default), and the blocker keeps its locks. A lower value makes requests fail sooner and leaves the cause where it was.
• wait_timeout closes idle connections, and the server rolls back whatever transaction they hold. Its default is 28800 seconds, eight hours. Clients that connect as interactive, such as the mysql command-line client, get interactive_timeout instead, with the same default. SET GLOBAL wait_timeout = 600; applies to connections opened after the change. Keep the pool’s connection lifetime below it: HikariCP’s documentation asks for a maxLifetime several seconds shorter than any timeout the database imposes.
• MySQL 8.0 and 8.4 have no idle transaction timeout. MariaDB 10.3 added three, all off (0) by default: idle_transaction_timeout, idle_readonly_transaction_timeout and idle_write_transaction_timeout. Each closes a connection that sits idle inside that kind of transaction longer than the limit. SET GLOBAL idle_write_transaction_timeout = 60; ends forgotten writers after a minute and leaves long read-only sessions alone. On MySQL, teams cover the gap with a scheduled job that runs the INNODB_TRX query above and kills connections asleep in a transaction older than their limit.
• Fix the application. Keep network calls out of transactions, and make each error path roll back. HikariCP’s leakDetectionThreshold logs a connection that stays out of the pool too long, with the stack trace of the code that borrowed it.

A sleeping transaction can also hold a metadata lock and stall a migration; the post about the ALTER TABLE queue covers that version.

On the phone

RowDeck looks at what the root blocker is doing before it offers a kill. A root asleep in a transaction gets the pill Sleep · open trx, the note “Kill query won’t help: the connection is idle in an open transaction.”, and Kill connection as the main action with the number of sessions it unblocks. The confirmation (Pro) warns that the open transaction rolls back, and on RDS it names the call it makes, via CALL mysql.rds_kill(24802). On MariaDB, Activity warns that Kill query on an idle session leaves it in Killed with its transaction open. After the kill, RowDeck reads the lock tree again so you see what still waits.

More about the screen: killing a MySQL query from your iPhone.