MySQL 表上 Metadata lock 的产生和处理
--MySQL 5.6select
concat('kill ', a.owner_thread_id, ';')
from
information_schema.metadata_locks a
left join
(select
b.owner_thread_id
from
information_schema.metadata_locks b, information_schema.metadata_locks c
where
b.owner_thread_id = c.owner_thread_id
and b.lock_status = 'granted'
and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id
where
a.lock_status = 'granted'
and d.owner_thread_id is null;
--MySQL 5.5
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);
页:
[1]