MariaDB Locking – Troubleshooting Locked Table When SHOW PROCESSLIST Shows Nothing

innodblockingmariadb

We had some slowness in our application. When I checked I figure out that one particular table was locked so all transactions were rejected by Lock wait timeout exceeded. When I did SHOW PROCESSLIST; I was expecting to see some processes with Waiting for table level lock state but it shows nothing !? only some queries INSERT/UPDATE on the same table taking exactly 120 seconds before time-out and others processes on sleeping state (see picture):

enter image description here

Best Answer

The reason in my case was that one transaction is making a read-lock on a particular table leading to a "Lock wait timeout exceeded" because its not committed. So all other transactions are waiting to that particular tranx to do commit.

Now, why SHOW PROCESSLIST; show nothing ? In fact, it does. you'll find it (the process) with a "Sleep" state and with no query shown in the info column like if it's just a regular connection to the database.

Let's check my example in the image :enter image description here

In Blue, those are some very simple queries blocked (taking 120 second before a time-out) and the red one is the "ghost" or non-committed transaction.

To see those kind of transactions execute :

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX

And you may kill the process kill <id>; in which TRX_QUERY is null to free other transactions. In my case, KILL 33267342;

Make sure to check the non-committed transaction to see if a commit; or a Rollback clause is missing in order to ovoid such issues.

Related Question