MariaDB/InnoDB: What to do on "Locks in Status-Log but no locked table found"

Question

We migrated lately from MySQL to MariaDB.

SELECT VERSION()

10.0.13-MariaDB-log

When executing this:

SHOW ENGINE INNODB STATUS

I get (shortened):

...
---TRANSACTION 279257007, ACTIVE 23981 sec
10 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 903
MySQL thread id 119634, OS thread handle 0x7f62c96e0700, query id 48830504 XXX.XXX.XXX.X admin cleaning up
Trx read view will not see trx with id >= 279257008, sees < 279256916
TABLE LOCK table `db1`.`table_a` trx id 279257007 lock mode IX
TABLE LOCK table `db1`.`table_b` trx id 279257007 lock mode IX
...

I guess this means that this transaction was running for several hours. However, when executing SHOW OPEN TABLES, all tables show no-lock. When doing SHOW FULL PROCESSLIST, the result of the thread 119634 is:

Command: SLEEP, State: '', Info: Null, Progress: 0

I have no idea what to do. When running a script I get lock-timeouts. But just increasing the timeout, I suspect will not help either.

Question: How can I get resolve this issue? Or, how should I tackle this?

Answer 1

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open.

If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE INNODB STATUS output

The lock status in SHOW OPEN TABLES, assuming you meant 'name_locked', is only for whether the name is locked for operations such as dropping or renaming the tables src

I am curious if SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS returns anything, especially if db1.table_a and db1.table_b are InnoDB tables. If they are MyISAM I would urge not using BEGIN TRANSACTION or SET autocommit=0, as you can run into some serious issues with potentially mixing InnoDB and MyISAM tables in a transaction.

Unfortunately, getting access to what that transaction has done is fairly impossible unless you're running the PERFROMANCE_SCHEMA to capture statemen. See: http://www.markleith.co.uk/2012/07/13/monitoring-processes-with-performance-schema-in-mysql-5-6/ Even still, it might not be possible for a transaction active that long depending on your server's activity and performance_schema settings.

So, you're really only left with the choice to kill the thread, which will rollback that transaction (again, assuming it's InnoDB tables being modified). Letting it run until it hits a timeout will result in the same rollback. And rollbacks might take a really long time, depending on what it's done!

I would scour the application's code-base to make sure you are closing/committing every transaction you are creating to prevent this in the future, as long-active transactions and an unruly history list length (undo space) can have a severe impact on the performance of your queries.

Answer 2

In addition to what Derek says,

• kill 119634; -- Since it is in Sleep mode, this will disconnect the client. That, in turn, terminates the runaway transaction.
• look in your code for autocommit=0. Don't do transactions that way; it leads to the problem you are experiencing. (No, I can't be sure this is the cause this time.)