最后的想法
需要注意的是，元数据锁不关心存储引擎，因此 InnoDB 甚至不会知道导致 DROP 挂起的事务。
第 1 节：
drop table if exists t1; create table t1(a int)engine=myisam;
会议2：
set autocommit=0; set transaction isolation level read uncommitted; delete from t1;
第 1 节：
drop table if exists t1; #hangs until session 2 commits.
元数据锁定检测已启用
元数据锁信息可以直接从performance_schema.metadata_locks表中获取：
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)
在示例中，请注意 OWNER_THREAD_ID = 43 在雇员.salaries (OBJECT_TYPE = TABLE) 上有一个 PENDING 元数据锁，这是从底部算起的第二行。
对应的 GRANTED 元数据锁是从顶部数第三行，具有相同的 OBJECT_TYPE、OBJECT_SCHEMA 和 OBJECT_NAME，并且 LOCK_STATUS = GRANTED。
performance_schema.metadata_locks 表将包含有关元数据锁的信息，即使没有其他连接等待锁。
OWNER_THREAD_ID 的值可用于获取有关阻塞和/或者等待连接的更多信息，例如从 performance_schema.threads 表或者 sys.session 视图中获取。
例如，要获取上例中持有元数据锁的连接的信息 (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)
如果我们正在寻找另一个等待锁定的连接的元数据锁定，我们还可以使用 sys.schema_table_lock_waits 视图，它结合了上面讨论的信息：
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)
更多： zhilu jiaocheng
未启用元数据锁定检测
不幸的是，我们无法轻松跟踪我们看到的正在等待元数据锁的线程，以查看哪个线程持有该锁。
在许多情况下，“陈旧”事务在表中持有行锁，阻止对该表的任何 DDL 操作。
我们可以通过这种方式查看哪些旧的/空闲的/陈旧的事务持有行锁：
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)
返回的线程之一持有有问题的锁。
我们可以将搜索范围缩小到空闲时间超过 10 分钟的那些：
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)
任何已打开并空闲 20 分钟并持有行锁的事务都必然会导致一些问题。
我们可以找到这样的交易：
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)
我们可以杀死启动该事务的线程，然后检查以确保它已被杀死：
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)
首选解决方案取决于 MySQL 的版本。
如果我们使用 MySQL 5.7 或者更高版本并在 Performance Schema 中启用元数据锁检测（在 MySQL 5.7 和更高版本中可用），则可以直接从 performance_schema.metadata_locks 表中获取信息。
否则，没有直接的方法来获取信息。
要确定是否启用了元数据锁定检测，请检查 performance_schema.setup_instruments 表，如：
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)
要启用检测，必须将 ENABLED 列设置为 YES 。
在 8.0.2 及更高版本中默认启用元数据锁定检测。
以下部分将讨论在启用元数据锁检测的情况下如何确定谁持有锁。
日期：2020-09-17 00:11:12 来源：oir作者：oir