背景資訊
MySQL 5.5版本開始，引入了MDL鎖，用于解決或者保證DDL操作與DML操作之間的一緻性，但是在部分場景下會出現阻塞，例如執行DML操作時執行ALTER操作、存在長時間查詢時執行ALTER操作等。
出現場景
- 建立、删除索引。
- 修改表結構。
- 表維護操作（optimize table、repair table 等）。
- 删除表。
- 擷取表級寫鎖 。
原因
- 目前有對表的長時間查詢。
- 顯示或者隐式開啟事務後未送出或復原，比如查詢完成後未送出或者復原。
- 表上有失敗的查詢事務。
操作步驟
1、登入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'))) 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;
附錄
https://help.aliyun.com/document_detail/94566.htm?spm=a2c4g.11186623.0.0.712b1a601x66k8#concept-csn-5tt-4fb