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?
