[Mysql] Metadata_lock 원인 쿼리 확인 및 lock 해제

Mysql을 사용하면서 DB 작업 중에 Metadata lock은 비교적 흔하게 발생한다.

 

내가 주로 경험한 것은 DBeaver 등의 클라이언트 툴에서 autocommit이 꺼진 상태로 select 등의 작업이 수행된 후 해당 object(table)에 alter table등의 DDL 문을 수행한 경우에 발생하는 metadata lock이었다.

 

주로 이런 경우는 show full processlist;로 원인이 되는 쿼리를 찾아도 쿼리는 이미 종료된 이후이기 때문에 processlist의 info에 null로 나와 원인인 쿼리를 정확히 알 수 없는 경우가 많다.

원인이 되는 쿼리가 object에 대한 lock을 해제하지 않으면 ddl문을 재차 수행해도 metadata lock으로 인하여 제대로 수행되지 않았다.

이때 원인이 되는 lock을 잡고 있는 세션을 찾기 위한 쿼리는 다음과 같다.

select l.object_name, l.lock_type, t.thread_id, processlist_id as prcs_id, processlist_user as prcs_user, processlist_command as prcs_cmd, processlist_time as prcs_time, processlist_state as prcs_state, convert(processlist_info using utf8) as prcs_info, esc.sql_text as cur_sql_text, esh.sql_text as his_sql_text from performance_schema.threads t JOIN performance_schema.events_statements_history esc on t.thread_id = esc.thread_id JOIN performance_schema.events_statements_history esh on t.thread_id = esh.thread_id JOIN performance_schema.metadata_locks l where 1=1 and t.thread_id = l.owner_thread_id group by thread_id, prcs_id, prcs_user, prcs_time, his_sql_text order by prcs_id ;

 

 

해당 쿼리로 lock이 잡힌 table(object)를 찾아 해당 process_id 를 kill 시켜주고 ddl을 수행하면 성공적으로 수행 가능하다.

kill processid;

 

 

 

만약 kill processid;를 수행했을 때  SQL 오류(1095): You are not owner of thread  와같은 에러가 난다면 aws rds를 사용하는 겨웅 다음과 같이 processlist를 kill 시킬 수 있다.

call mysql.rds_kill(process_id);