mysql metadata locks persist after thread is terminated

dll

1/24/24, 11:08 AM

this is tested on mysql 8.0.28, but other versions show it too

we get occasions where a metadata lock persists after the thread that created it has terminated. For example for these locks in performance_schema.metadata_locks

| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE      | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+-------------+---------------+-------------+-------------+-----------------+----------------+
| TABLE       | s1            | o1          | NULL        |        23079043765136 | SHARED_READ | TRANSACTION   | GRANTED     | mdl.cc:3693 |       128925773 |           5925 |
| TABLE       | s1            | o2          | NULL        |        23079043765296 | SHARED_READ | TRANSACTION   | GRANTED     | mdl.cc:3693 |       128925773 |           5926 |
| TABLE       | s1            | o3          | NULL        |        23079043765616 | SHARED_READ | TRANSACTION   | GRANTED     | mdl.cc:3693 |       128925773 |           5927 |
| TABLE       | s1            | o4          | NULL        |        23079043765936 | EXCLUSIVE   | TRANSACTION   | GRANTED     | mdl.cc:3693 |       128925773 |           5928 |

the thread 128925773 does not exist in SHOW PROCESSLIST - these locks never go away until the mysqld gets restarted.

So, some of the locks prevent new locks from being created, which then causes the client application to fail.

We think the problem is caused by the client process being killed unexpectedly. The client is running in a docker container, which can be terminated by the orchestration. We're working on that, but can anything be done on the mysql side to clear the locks?

0 + 1

mysql

database

docker

mysql8

Rick James

1/30/24, 8:03 PM

Was some long-running transaction busy when the connection was terminated? Perhaps this is "undoing" that transaction.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: mysql metadata locks persist after thread is terminated

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.