元数据锁

说明:
MySQL版本: 8.0.25
事务隔离级别: READ-COMMITTED
表数据量: 2546669


表结构

          
CREATE TABLE foo (
   id bigint NOT NULL COMMENT '主键',
   batch_id bigint NOT NULL COMMENT '批次ID',
   PRIMARY KEY (id),
   KEY idx_batch_id(batch_id)
) ENGINE = InnoDB
          
        

某事务对foo表执行简单查询语句

          
SELECT * FROM foo WHERE id = 51942765248446464
          
        

事务还未结束
接下来, 在事务之外执行DDL语句

          
ALTER TABLE foo DROP index idx_batch_id
          
        

DDL语句发生阻塞(Waiting for table metadata lock)


1.使用 SHOW PROCESSLIST 命令查看, 显示DDL语句阻塞在元数据锁

          
| 452825 | root            | 192.168.10.26:61862 | local-2022 | Sleep       |      60 |                                                               | NULL
| 452826 | root            | 192.168.10.99:12763 | local-2022 | Query       |      30 | Waiting for table metadata lock                               | alter table foo drop index `idx_batch_id` |
| 452827 | root            | 192.168.10.32:53721 | local-2022 | Sleep       |      44 |                                                               | NULL
          
        

2.通过以下SQL语句可以找到阻塞源头

          
Version:0.9 StartHTML:00000156 EndHTML:00004129 StartFragment:00000190 EndFragment:00004093 SourceURL:https://www.infuq.com/zone/Metadata%20Lock.html
SELECT  locked_schema
       ,locked_table
       ,locked_type
       ,waiting_processlist_id
       ,waiting_age
       ,waiting_query
       ,waiting_state
       ,blocking_processlist_id
       ,blocking_age
       ,substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query
       ,sql_kill_blocking_connection
FROM
(
    SELECT  b.OWNER_THREAD_ID                AS granted_thread_id
           ,a.OBJECT_SCHEMA                  AS locked_schema
           ,a.OBJECT_NAME                    AS locked_table
           ,"Metadata Lock"                  AS locked_type
           ,c.PROCESSLIST_ID                 AS waiting_processlist_id
           ,c.PROCESSLIST_TIME               AS waiting_age
           ,c.PROCESSLIST_INFO               AS waiting_query
           ,c.PROCESSLIST_STATE              AS waiting_state
           ,d.PROCESSLIST_ID                 AS blocking_processlist_id
           ,d.PROCESSLIST_TIME               AS blocking_age
           ,d.PROCESSLIST_INFO               AS blocking_query
           ,concat('KILL ',d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    FROM performance_schema.metadata_locks a
    JOIN performance_schema.metadata_locks b
    ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE'
    JOIN performance_schema.threads c
    ON a.OWNER_THREAD_ID = c.THREAD_ID
    JOIN performance_schema.threads d
    ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1, (
SELECT  thread_id
       ,group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM performance_schema.events_statements_history
GROUP BY  thread_id ) t2
WHERE t1.granted_thread_id = t2.thread_id
          
        

另一种情况
如果DDL语句先被执行

          
ALTER TABLE foo DROP index idx_batch_id
          
        

在DDL语句运行的过程中(该DDL语句运行时间比较长), 某事务对foo表执行简单查询语句

          
SELECT * FROM foo WHERE id = 51942765248446464
          
        

问题: 该SELECT语句会被阻塞吗?



参考外链
一步步搞懂MySQL元数据锁(MDL)