2. Metadata lock wait 的含義

3. 導致 Metadata lock wait 等待的活動事務

4. 解決方案

5. 如何避免出現長時間 Metadata lock wait 導致表上相關查詢阻塞，影響業務

---

1. Metadata lock wait 出現的場景

• 創建、刪除索引

• 修改表結構

• 表維護操作（optimize table、repair table 等）

• 刪除表

• 獲取表上表級寫鎖 (lock table tab_name write)

注：

• 支持事務的 InnoDB 引擎表和 不支持事務的 MyISAM 引擎表，都會出現 Metadata Lock Wait 等待現象。
• 一旦出現 Metadata Lock Wait 等待現象，後續所有對該表的訪問都會阻塞在該等待上，導致連接堆積，業務受影響。

 

2. Metadata lock wait 的含義

為了在並發環境下維護表元數據的數據一致性，在表上有活動事務（顯式或隱式）的時候，不可以對元數據進行寫入操作。因此 MySQL 引入了 metadata lock ，來保護表的元數據信息。

因此在對表進行上述操作時，如果表上有活動事務（未提交或回滾），請求寫入的會話會等待在 Metadata lock wait 。

3. 導致 Metadata lock wait 等待的活動事務

• 當前有對表的長時間查詢

• 顯示或者隱式開啟事務後未提交或回滾，比如查詢完成後未提交或者回滾。

• 表上有失敗的查詢事務

4. 解決方案

• show processlist 查看會話有長時間未完成的查詢，使用kill 命令終止該查詢。

• 查詢 information_schema.innodb_trx 看到有長時間未完成的事務， 使用 kill 命令終止該查詢。

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

-- 請根據具體的情景修改查詢語句
-- 如果導致阻塞的語句的用戶與當前用戶不同，請使用導致阻塞的語句的用戶登錄來終止會話

• 如果上麵兩個檢查沒有發現，或者事務過多，建議使用下麵的查詢將相關庫上的會話終止

select 
    concat('kill ', p1.id, ';')
from
    information_schema.processlist p1,
    (select 
        id, time
    from
        information_schema.processlist
    where
        time = (select 
                max(time)
            from
                information_schema.processlist
            where
                state = 'Waiting for table metadata lock'
                    and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2
where
    p1.time >= p2.time
        and p1.command in ('Sleep' , 'Query')
        and p1.id not in (connection_id() , p2.id);

-- RDS for MySQL 5.5 語句請根據具體的 DDL 語句情況修改查詢的條件；
-- 如果導致阻塞的語句的用戶與當前用戶不同，請使用導致阻塞的語句的用戶登錄來終止會話

 

5. 如何避免出現長時間 metadata lock wait 導致表上相關查詢阻塞，影響業務

• 在業務低峰期執行上述操作，比如創建刪除索引。

• 在到RDS的數據庫連接建立後，設置會話變量 autocommit 為 1 或者 on，比如 set autocommit=1; 或 set autocommit=on; 。

• 考慮使用事件來終止長時間運行的事務，比如下麵的例子中會終止執行時間超過60分鍾的事務。

  create event my_long_running_trx_monitor
  on schedule every 60 minute
  starts '2015-09-15 11:00:00'
  on completion preserve enable do
  begin
    declare v_sql varchar(500);
    declare no_more_long_running_trx integer default 0; 
    declare c_tid cursor for
      select concat ('kill ',trx_mysql_thread_id,';') 
      from information_schema.innodb_trx 
      where timestampdiff(minute,trx_started,now()) >= 60;
    declare continue handler for not found
      set no_more_long_running_trx=1;
   
    open c_tid;
    repeat
      fetch c_tid into v_sql;
   set @v_sql=v_sql;
   prepare stmt from @v_sql;
   execute stmt;
   deallocate prepare stmt;
    until no_more_long_running_trx end repeat;
    close c_tid;
  end;

  
  注：請根據您自身情況，自行修改運行間隔和事務執行時長。

• 執行上述1中操作前，設置會話變量 lock_wait_timeout 為較小值，比如 set lock_wait_timeout=30; 命令可以設置 metadata lock wait 的最長時間為 30 秒；避免長時間等待元數據鎖影響表上其他業務查詢。