Generation and Handling of Metadata Locks on RDS for MySQL Tables

Introduction

Metadata Lock on RDS for MySQL Tables

1. What does metadata lock wait mean?

2. When does metadata lock wait occur?

3. What active transactions will lead to metadata lock waits?

Resolving Metadata Locks

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);

Avoiding a long metadata lock wait that blocks related queries on the table or impacts business

create event my_long_running_trx_monitor on schedule every 60 minutes
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;

Conclusion:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com