Skip to content

Instantly share code, notes, and snippets.

@sasasin
Last active August 11, 2023 10:55
Show Gist options
  • Save sasasin/8829158d4fe177e00094f86657023100 to your computer and use it in GitHub Desktop.
Save sasasin/8829158d4fe177e00094f86657023100 to your computer and use it in GitHub Desktop.
MySQL で Waiting for table metadata lock の原因となってるプロセスを特定するSQL
# MySQL で Waiting for table metadata lock の原因となってるプロセスを特定するSQL
select pl.*
, ml.*
from information_schema.processlist as pl
inner join performance_schema.threads as th
on pl.id = th.processlist_id
inner join performance_schema.metadata_locks as ml
on th.thread_id = ml.owner_thread_id
where
# 「GRANTED」が実際にロックを掴んでる。
# https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-metadata-locks-table.html
ml.lock_status = 'GRANTED'
# hogehoge の fugafuga を metadata lock してるのを調べる
and ml.object_schema = 'hogehoge'
and ml.object_name = 'fugafuga'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment