ApsaraDB RDS：解決MDL鎖導致無法操作資料庫的問題

背景資訊

MySQL 5.5版本開始，引入了MDL鎖，用於解決或者保證DDL操作與DML操作之間的一致性，但是在部分情境下會出現阻塞，例如執行DML操作時執行ALTER操作、存在長時間查詢時執行ALTER操作等。

出現情境

• 建立、刪除索引。
• 修改表結構。
• 表維護操作（optimize table、repair table 等）。
• 刪除表。
• 擷取表級寫鎖 。

原因

• 當前有對錶的長時間查詢。
• 顯示或者隱式開啟事務後未提交或復原，比如查詢完成後未提交或者復原。
• 表上有失敗的查詢事務。

操作步驟

1. 通過DMS登入RDS資料庫。
2. 在SQL視窗執行show full processlist命令，查看資料庫所有線程狀態。
3. 查看State列是否存在大量Waiting for table metadata lock，出現Waiting for table metadata lock即表示出現阻塞。
4. 尋找造成阻塞的會話ID。
   1. 查看狀態為Waiting for table metadata lock會話的Info列，找到該會話操作的表，例如sbtest2。
   2. 查看其他會話的Info列，找到正在對錶sbtest2進行操作的會話，記錄會話Id。
      說明 這裡需要找到的是一直在佔用操作該表的會話，而不是正在等待MDL鎖（狀態為Waiting for table metadata lock）解除的會話，注意區分。可以根據State列的狀態和Info列的命令內容來進行分析判斷。
      例如，下圖中State為Waiting for table metadata lock的會話，從其Info列的命令判斷，此會話需要對錶sbtest2進行操作；其他需要動作表sbtest2的會話中，從Id為267會話的State列狀態可以判斷，此會話正在對錶sbtest2進行操作，造成了阻塞。

      說明 本文以狀態Sending data為例，請根據實際的工作階段狀態進行判斷。
      
      您也可以用如下命令查詢長時間未完成的事務，如果導致阻塞的語句的使用者與當前登入使用者不同，請使用導致阻塞的語句的使用者登入來終止會話。

      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', 'trunc'))) p
        where timestampdiff(second, i.trx_started, now()) > p.time
        and i.trx_mysql_thread_id  not in (connection_id(),p.id);

5. 在命令列輸入kill 會話ID，例如 kill 267，即可中斷會話，解除MDL鎖。

後續維護

• 在業務低峰期執行相關情境操作，例如建立索引、刪除索引等。
• 開啟事務自動認可autocommit。
• 設定參數lock_wait_timeout為較小值。
• 考慮使用事件來終止長時間啟動並執行事務，比如下面的例子中會終止執行時間超過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;