Mysql – Many transactions are getting stuck in the “cleaning up” state – what could cause that

lockingMySQLtransaction

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. So I looked at SHOW ENGINE INNODB STATUS, which showed that when the transactions in question are waiting on locks, the only other tansactions present that have started (there are many in thenot started state) are ones that have been running for 30 seconds or more, and are in the cleaning up state.

Why would a transaction get stuck in the cleaning up state? Is there any way to tell at that point, what any of the original queries were for that transaction? (The transaction does not appear to be present in the information_schema.innodb_trx table).

Example output:

---TRANSACTION 2670738352, ACTIVE 25 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158609, OS thread handle 0x2ae9a6bcf700, query id 487681086 10.0.1.246 ownlocal cleaning up
Trx read view will not see trx with id >= 2670738353, sees < 2670727358
---TRANSACTION 2670736929, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158413, OS thread handle 0x2ae9a5842700, query id 487683091 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670737972, sees < 2670727358
---TRANSACTION 2670736875, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158415, OS thread handle 0x2ae9a64b3700, query id 487678962 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670736876, sees < 2670724054
---TRANSACTION 2670727358, ACTIVE 136 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 158216, OS thread handle 0x2ae9a7679700, query id 487676960 10.0.1.110 ownlocal cleaning up
Trx read view will not see trx with id >= 2670735437, sees < 2670724054

Additional Info:

  • MySQL Version: 5.6
  • Storage Engine: InnoDB

Best Answer

I have the same issue on RDS. Some of "cleaning up" transactions blocked my ALTER TABLE query. And as I understood in MySQL 5.6 there is no way to find out which transaction should be killed.

So, my solution was to kill all "cleaning up" transactions. And it helped me. And more thing: according to AWS doc on RDS you should use CALL mysql.rds_kill(pid) instead of standard KILL.

Related Question