The preferred solution depends on the version of MySQL. If you are using MySQL 5.7 or later with metadata lock instrumentation enabled in the Performance Schema (available in MySQL 5.7 and later), it is possible to get the information directly from the performance_schema.metadata_locks table. Otherwise, there is no direct way to get the information. To determine whether metadata lock instrumentation is enabled, check the performance_schema.setup_instruments table like:
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | YES | YES | +----------------------------+---------+-------+ 1 row in set (0.00 sec)
For the instrumentation to be enabled, the ENABLED column must be set to YES. Metadata lock instrumentation is enabled by default in 8.0.2 and later. The following sections will discuss how to determine who holds the lock in the case of metadata lock instrumentation being enabled and not.
Metadata Lock Instrumentation Enabled
Metadata lock information can be obtained directly from the performance_schema.metadata_locks table:
mysql> SELECT * FROM performance_schema.metadata_locks; +-------------+--------------------+--------------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+--------------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | 140288530502736 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5646 | 41 | 48 | | SCHEMA | employees | NULL | 140288531339744 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5631 | 41 | 48 | | TABLE | employees | salaries | 140288531278528 | SHARED_NO_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6041 | 41 | 48 | | TABLESPACE | NULL | innodb_file_per_table.12 | 140288531288032 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:931 | 41 | 50 | | TABLE | employees | #sql-1f43_d | 140288530333616 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:11414 | 41 | 50 | | TABLESPACE | NULL | innodb_file_per_table.12 | 140288531553072 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:348 | 41 | 53 | | TABLESPACE | NULL | innodb_file_per_table.13 | 140288531658048 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:348 | 41 | 53 | | GLOBAL | NULL | NULL | 140289143166560 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5646 | 43 | 14 | | SCHEMA | employees | NULL | 140289144298480 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5631 | 43 | 14 | | TABLE | employees | salaries | 140289145227296 | SHARED_UPGRADABLE | TRANSACTION | PENDING | sql_parse.cc:6041 | 43 | 14 | | TABLE | performance_schema | metadata_locks | 140288406572000 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6041 | 35 | 67 | +-------------+--------------------+--------------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ 11 rows in set (0.00 sec)
In the example note that OWNER_THREAD_ID = 43 has a PENDING metadata lock on employees.salaries (OBJECT_TYPE = TABLE) – this is the second row from the bottom. The corresponding GRANTED metadata lock is the third row from the top with the same OBJECT_TYPE, OBJECT_SCHEMA, and OBJECT_NAME, and LOCK_STATUS = GRANTED.
The performance_schema.metadata_locks table will include information about the metadata locks even if there is no other connection waiting for the lock.
The value of OWNER_THREAD_ID can be used to get more information about the blocking and/or waiting connections for example from the performance_schema.threads table or the sys.session view. For example to get information about the connection holding the metadata lock in the above example (OWNER_THREAD_ID = 41):
mysql> SELECT * FROM performance_schema.threads WHERE THREAD_ID = 41\G
*************************** 1. row ***************************
THREAD_ID: 41
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 13
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: employees
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 1
PROCESSLIST_STATE: alter table (read PK and internal sort)
PROCESSLIST_INFO: OPTIMIZE TABLE salaries
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 11403
RESOURCE_GROUP: NULL
1 row in set (0.00 sec)
mysql> SELECT * FROM sys.session WHERE thd_id = 41\G
*************************** 1. row ***************************
thd_id: 41
conn_id: 13
user: root@localhost
db: employees
command: Query
state: alter table (read PK and internal sort)
time: 2
current_statement: OPTIMIZE TABLE salaries
statement_latency: 2.31 s
progress: NULL
lock_latency: 45.47 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 24.72 MiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 50.95 us
trx_state: COMMITTED
trx_autocommit: YES
pid: 12497
program_name: mysql
1 row in set (0.13 sec)
If you are specifically looking for metadata locks where there is another connection waiting for a lock, you can also use the sys.schema_table_lock_waits view which combines the information discussed above:
mysql> SELECT * FROM sys.schema_table_lock_waits\G
*************************** 1. row ***************************
object_schema: employees
object_name: salaries
waiting_thread_id: 43
waiting_pid: 15
waiting_account: root@localhost
waiting_lock_type: SHARED_UPGRADABLE
waiting_lock_duration: TRANSACTION
waiting_query: ALTER TABLE salaries ADD INDEX (salary)
waiting_query_secs: 8
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 41
blocking_pid: 13
blocking_account: root@localhost
blocking_lock_type: SHARED_NO_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 13
sql_kill_blocking_connection: KILL 13
1 row in set (0.36 sec)
Metadata Lock Instrumentation Not Enabled
Unfortunately you cannot easily trace from a thread that you see is waiting on a metadata lock back to see what thread holds that lock. In many cases, a “stale” transaction holds a row lock within a table, blocking any DDL operations on that table. You can see which old/idle/stale transactions are holding on to row locks this way:
mysql> select trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL order by trx_started; +---------------------+---------------------+ | trx_mysql_thread_id | trx_started | +---------------------+---------------------+ | 1 | 2012-06-15 13:48:20 | | 5 | 2012-06-15 13:58:26 | +---------------------+---------------------+ 2 rows in set (0.00 sec)
One of the returned threads holds the problematic lock. We can narrow the search to those that have been idle for > 10 minutes:
mysql> select now() - trx_started as active_period, trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL and (now() - trx_started > 600) order by trx_started; +---------------+---------------------+---------------------+ | active_period | trx_mysql_thread_id | trx_started | +---------------+---------------------+---------------------+ | 5689.000000 | 1 | 2012-06-15 13:48:20 | | 4683.000000 | 5 | 2012-06-15 13:58:26 | +---------------+---------------------+---------------------+ 2 rows in set (0.01 sec)
Any transaction that has been open and idle for 20 minutes AND is holding on to row locks is bound to cause some problems. We can find such transactions like this:
mysql> select now() - trx_started as active_period, trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL and (now() - trx_started > 1200) order by trx_started; +---------------+---------------------+---------------------+ | active_period | trx_mysql_thread_id | trx_started | +---------------+---------------------+---------------------+ | 5819.000000 | 1 | 2012-06-15 13:48:20 | | 4813.000000 | 5 | 2012-06-15 13:58:26 | +---------------+---------------------+---------------------+ 2 rows in set (0.00 sec)
We can kill the thread that started that transaction, and then check to make sure it has been killed:
mysql> kill 1; Query OK, 0 rows affected (0.01 sec)
mysql> select now() - trx_started as active_period, trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL and (now() - trx_started > 1200) order by trx_started; +---------------+---------------------+---------------------+ | active_period | trx_mysql_thread_id | trx_started | +---------------+---------------------+---------------------+ | 4823.000000 | 5 | 2012-06-15 13:58:26 | +---------------+---------------------+---------------------+ 1 row in set (0.00 sec)
Final Thoughts
It is important to note that Meta Data Locks do not care about storage engines, so InnoDB will not even know about a transaction that causes a DROP to hang.
session 1:
drop table if exists t1; create table t1(a int)engine=myisam;
session2:
set autocommit=0; set transaction isolation level read uncommitted; delete from t1;
session 1:
drop table if exists t1; #hangs until session 2 commits.