TransWikia.com

Why does MySQL wait for a table lock for a validation query?

Database Administrators Asked on January 10, 2021

Why are simple validation queries waiting for a table lock?

In our Hibernate setup we are using a validation query to check that a connection is still usable within the pool. We use a simple SELECT validation-query; statement for this purpose.
Recently during a busy day we noticed that our Aurora MySQL 5.6 cluster shows quite a few of these validation queries in the process list, which were waiting for a table lock. A similar thing also happened for isolation level settings queries, as shown below.

I did not expect that queries like those require a table at all, so why lock it? I was surprised that we have seen locks for this kind of queries very frequently. While there were longer running SELECT statements on other tables, I was wondering what the mechanism in the background is and if there is anything we could do to avoid waits. There was a very high load and I assume that the validation queries are sent before each query, effectively doubling the number of queries. Should we remove the validation query?

We only use InnoDB tables, no MyISAM or other engines are used.

Locking queries

Below is an example from the InnoDB monitor output. I did not expect the validation query and the isolation level query to require table locks.

`---TRANSACTION 3273445961, not started
MySQL thread id 338466, OS thread handle 0x2ab1bbf86700, query id 610723618 10.12.2.123 db-user Waiting for table level lock
SELECT 'validation-query'`

I was also wondering how this query could possibly wait for a table level lock:

---TRANSACTION 3273446385, not started
MySQL thread id 338823, OS thread handle 0x2ab1e2617700, query id 610723620 10.12.2.123 db-user Waiting for table level lock
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Profiling the validation query

I was profiling the validation query and it does spend time opening and closing tables. I suppose this is normal, also for string constants? But why are they waiting for a table lock when there is only a simple SELECT and no UPDATE, INSERT or DELETE involved?

root@local> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 0.00036050 | SELECT 'validation-query' |
+----------+------------+--------------------------------------+
1 row in set, 1 warning (0.000 sec)
root@local> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000081 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000012 |
| init                 | 0.000019 |
| optimizing           | 0.000012 |
| executing            | 0.000015 |
| end                  | 0.000008 |
| query end            | 0.000007 |
| closing tables       | 0.000007 |
| freeing items        | 0.000111 |
| cleaning up          | 0.000080 |
+----------------------+----------+
11 rows in set, 1 warning (0.001 sec)

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP