[MySQL] 메타데이터 락 때문에 멈춘 DDL 진단과 안전한 해소 절차

728x90

데이터베이스를 다루다 보면 분명 무거운 쿼리도 없고 InnoDB 락 경합도 안 보이는데, ALTER TABLE 같은 DDL 문이 실행조차 되지 못하고 그대로 멈춰 있는 상황을 만나게 됩니다. SHOW PROCESSLIST를 봐도 딱히 원인이 될 만한 것이 없어 보이고, 담당자는 배포 창구 시간이 흘러가는데도 손을 못 대는 경우가 많습니다. 이때 가장 흔하게 의심해야 할 것이 바로 메타데이터 락(Metadata Lock, 이하 MDL)입니다. 이 글에서는 MDL이 무엇이고 왜 걸리는지부터, performance_schema를 이용해 원인이 되는 세션을 정확히 찾아내는 방법, 그리고 서비스에 영향을 최소화하면서 안전하게 대기를 해소하는 절차까지 실무 순서대로 깊이 있게 다루어 보겠습니다.

1. 메타데이터 락(MDL)이란 무엇인가

MDL은 테이블의 구조, 즉 스키마 정보를 보호하기 위한 잠금입니다. InnoDB의 row lock이 '데이터 행'을 보호하는 것과 달리, MDL은 '테이블이 지금 어떤 구조인지'에 대한 일관성을 보호합니다. 세션이 트랜잭션 안에서 특정 테이블에 접근하면, 단순 SELECT라 하더라도 그 트랜잭션이 COMMIT 또는 ROLLBACK으로 끝날 때까지 해당 테이블에 대해 SHARED 계열의 MDL을 계속 보유하게 됩니다. 반면 ALTER TABLE, DROP TABLE, RENAME TABLE 같은 DDL은 EXCLUSIVE MDL을 획득해야 실행할 수 있습니다. SHARED 락이 하나라도 살아있으면 EXCLUSIVE 락은 그 세션이 끝날 때까지 대기할 수밖에 없다는 점이 이 문제의 핵심입니다.

MDL은 MySQL 5.5.3부터 명시적으로 관리되기 시작했으며, 문장 종류에 따라 요청하는 락의 강도가 다릅니다. 대표적인 유형은 다음과 같습니다.

문장 유형	요청하는 MDL 타입	특징
SELECT (트랜잭션 내)	SHARED_READ	트랜잭션 종료까지 유지, 다른 SHARED_READ와는 공존 가능
INSERT / UPDATE / DELETE	SHARED_WRITE	역시 트랜잭션 종료까지 유지
LOCK TABLES ... READ	SHARED_READ_ONLY	명시적 잠금, UNLOCK TABLES까지 유지
ALTER TABLE / DROP TABLE / RENAME TABLE	EXCLUSIVE	모든 SHARED 락이 해제되어야 획득, FIFO 큐 특성을 가짐

여기서 주니어 개발자가 흔히 하는 실수는 'DDL이 멈췄으니 뭔가 무거운 쿼리가 돌고 있을 것'이라고 짐작하는 것입니다. 하지만 실제로는 이미 종료된 것처럼 보이는, 즉 화면상으로는 아무 일도 하지 않는 idle 세션이 원인인 경우가 훨씬 많습니다.

2. DDL이 멈추는 전형적인 시나리오

실무에서 MDL 대기가 발생하는 패턴은 대체로 다음 세 가지로 압축됩니다.

방치된 트랜잭션: 애플리케이션 커넥션 풀에서 가져온 커넥션으로 SELECT를 한 번 실행한 뒤, 명시적 COMMIT이나 ROLLBACK 없이 커넥션을 풀에 반납하거나 그대로 idle 상태로 방치하는 경우입니다. 이 세션은 겉보기에는 아무 일도 하지 않지만, 트랜잭션이 열려 있는 한 SHARED MDL을 계속 붙잡고 있습니다.
장시간 실행 중인 조회/배치: 리포트용 대용량 집계 쿼리나 배치 작업이 해당 테이블을 스캔하는 동안에는 정상적으로 SHARED MDL을 보유합니다. 이 경우는 실행 계획이 비효율적이어서 스캔 자체가 오래 걸리는 것이 근본 원인일 수 있습니다.
연쇄 대기(큐잉) 현상: 가장 위험한 패턴입니다. MDL은 요청이 들어온 순서대로 처리되는 FIFO 큐 특성을 가지므로, ALTER TABLE이 EXCLUSIVE 락을 기다리며 큐에 들어가 있으면 그 뒤에 도착한 일반 SELECT/INSERT 문들까지도 ALTER TABLE보다 먼저 실행되지 못하고 줄줄이 대기하게 됩니다. 결과적으로 해당 테이블에 접근하는 모든 커넥션이 순식간에 고갈되며, 애플리케이션 전체가 응답 불가 상태처럼 보일 수 있습니다.

3. 첫 신호 포착: SHOW PROCESSLIST

가장 먼저 확인할 곳은 SHOW PROCESSLIST입니다. ALTER TABLE을 실행한 세션의 State 컬럼이 Waiting for table metadata lock으로 표시된다면 MDL 대기가 거의 확실합니다.

SHOW FULL PROCESSLIST;

다만 이 명령만으로는 '누가' 그 락을 쥐고 있는지까지는 알 수 없습니다. MySQL 8.0에서는 WHERE 절 완전 정복 글에서 다룬 것처럼 information_schema.PROCESSLIST 대신 performance_schema.threads 기반 조회를 권장하며, 실제로 8.0.3부터는 information_schema.PROCESSLIST가 내부적으로 성능 저하를 유발할 수 있어 개선된 경로로 대체되었습니다. 원인 세션까지 정확히 찾으려면 다음 단계인 performance_schema.metadata_locks를 조회해야 합니다.

4. 정밀 진단: performance_schema.metadata_locks

이 테이블이 바로 MDL 진단의 핵심입니다. 다만 버전별 함정이 있습니다. MySQL 8.0에서는 관련 계측(instrument)이 기본적으로 활성화되어 있지만, 5.7 이하 버전에서는 기본 비활성 상태이므로 아래 설정을 먼저 확인해야 합니다.

-- 5.7 이하에서만 필요, 8.0은 기본 ON
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

활성화가 확인되었다면, threads 테이블과 조인해서 실제 프로세스 정보를 얻습니다.

SELECT
  ml.OBJECT_TYPE,
  ml.OBJECT_SCHEMA,
  ml.OBJECT_NAME,
  ml.LOCK_TYPE,
  ml.LOCK_STATUS,
  ml.OWNER_THREAD_ID,
  t.PROCESSLIST_ID,
  t.PROCESSLIST_USER,
  t.PROCESSLIST_HOST,
  t.PROCESSLIST_TIME,
  t.PROCESSLIST_STATE,
  t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t
  ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE ml.OBJECT_SCHEMA = 'your_schema'
  AND ml.OBJECT_NAME = 'your_table'
ORDER BY ml.LOCK_STATUS DESC;

여기서 진단 쿼리를 작성할 때도 습관적으로 쓴 SELECT *는 지양하는 것이 좋습니다. metadata_locks와 threads는 컬럼 수가 많은 시스템 테이블이라 필요한 컬럼만 명시하는 편이 결과를 읽기에도, 반복 조회 시 부담을 줄이는 데도 유리합니다.

결과를 해석하는 기준은 LOCK_STATUS 컬럼입니다.

GRANTED: 이미 락을 쥐고 있는 세션입니다. 여러 개가 있을 수 있습니다.
PENDING: 락을 기다리는 중인 세션입니다. ALTER TABLE을 실행한 세션이 바로 여기에 EXCLUSIVE 상태로 나타납니다.

범인을 찾는 방법은 명확합니다. GRANTED 상태이면서 LOCK_TYPE이 SHARED_READ 또는 SHARED_WRITE이고, PROCESSLIST_INFO가 NULL(현재 실행 중인 쿼리 없음)이면서 PROCESSLIST_TIME 값이 비정상적으로 큰 세션이 바로 방치된 트랜잭션입니다.

5. 더 쉬운 길: sys 스키마 활용

MySQL 8.0에는 sys 스키마가 기본 설치되어 있어, 위 조인 쿼리를 매번 직접 작성할 필요가 없습니다.

SELECT * FROM sys.schema_table_lock_waits\G

이 뷰는 waiting_pid, waiting_query, blocking_pid, blocking_lock_type, blocking_lock_duration 등을 한 번에 보여주므로, 장애 상황에서 시간을 아낄 수 있는 가장 빠른 진단 경로입니다. 단, sys.innodb_lock_waits는 InnoDB row lock 대기를 보여주는 별개의 뷰이므로 MDL 문제를 볼 때는 반드시 schema_table_lock_waits를 사용해야 한다는 점에 주의해야 합니다. 이름이 비슷해 혼동하는 경우가 실무에서 의외로 자주 발생합니다.

6. 근본 원인 확인: information_schema.innodb_trx

범인 세션의 PROCESSLIST_ID(또는 blocking_pid)를 찾았다면, 그 세션이 열어둔 트랜잭션이 언제 시작되었는지 확인합니다.

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = ;

trx_started 값과 현재 시각의 차이가 곧 트랜잭션이 방치된 시간입니다. trx_query가 NULL이면 현재 실행 중인 쿼리는 없이 idle in transaction 상태라는 뜻이며, 이것이 가장 흔한 원인입니다. 반대로 trx_query에 실제로 무거운 쿼리가 들어있다면, 그 세션은 여전히 일을 하고 있는 것이므로 무작정 종료하기보다 EXPLAIN ANALYZE 해석법을 참고해 그 쿼리의 실행 계획부터 점검하고, 추정 행 수와 실제 처리 행 수의 오차가 크지 않은지 확인한 뒤 대응 방향을 정하는 것이 더 안전합니다.

7. 안전하게 해소하는 절차

범인 세션을 찾았다고 바로 KILL부터 하는 것은 위험합니다. 아래 순서를 반드시 지켜야 합니다.

세션 식별: PROCESSLIST_USER, PROCESSLIST_HOST로 이 세션이 어떤 애플리케이션 서버, 어떤 배치 잡, 혹은 사람이 직접 접속한 세션인지 먼저 파악합니다.
진행 중인 작업 확인: trx_query가 비어 있지 않고 실제로 INSERT/UPDATE/DELETE가 진행 중이라면, 강제 종료 시 InnoDB가 해당 트랜잭션을 롤백해야 합니다. 대용량 쓰기 작업의 롤백은 원래 쿼리 실행 시간보다 훨씬 오래 걸릴 수 있다는 점이 흔히 놓치는 함정입니다. 이 경우 KILL이 오히려 장애를 연장시킬 수 있습니다.
KILL QUERY와 KILL CONNECTION 구분: 아래 표를 참고해 상황에 맞는 명령을 선택해야 합니다.

명령	동작	MDL 해소 여부
KILL QUERY <id>	실행 중인 쿼리만 중단, 세션과 트랜잭션은 유지	해소되지 않음 (idle 트랜잭션이면 무의미)
KILL CONNECTION <id> (또는 KILL <id>)	세션 자체를 종료, 열린 트랜잭션은 롤백 후 커넥션 해제	해소됨

idle in transaction 상태가 원인이라면 KILL QUERY는 아무 효과가 없습니다. 세션 자체를 끊는 KILL CONNECTION이 필요하다는 점을 기억해야 합니다.

재확인: KILL 이후 performance_schema.metadata_locks 또는 sys.schema_table_lock_waits를 다시 조회해 대기가 실제로 해소되었는지, ALTER TABLE이 정상적으로 진행되는지 확인합니다.
기록: 어떤 세션이, 어떤 애플리케이션 코드 경로에서, 얼마 동안 트랜잭션을 방치했는지 기록해 재발 방지 대책의 근거로 남깁니다.

8. 흔히 하는 실수: lock_wait_timeout과 innodb_lock_wait_timeout 혼동

DDL이 MDL을 기다리는 시간을 제어하는 변수는 innodb_lock_wait_timeout이 아니라 lock_wait_timeout입니다. innodb_lock_wait_timeout(기본 50초)은 InnoDB row lock 대기에만 적용되는 반면, lock_wait_timeout(기본 31536000초, 약 1년)은 MDL을 포함한 메타데이터·스키마 락 대기에 적용됩니다. 이 둘을 혼동해 innodb_lock_wait_timeout만 짧게 설정해두고 'DDL 대기는 알아서 끊기겠지'라고 생각하면 실제로는 1년 가까이 대기가 이어질 수 있습니다. 세션 단위로 다음과 같이 조정할 수 있습니다.

SET SESSION lock_wait_timeout = 10;
ALTER TABLE your_table ADD COLUMN new_col INT;

이렇게 설정하면 10초 안에 EXCLUSIVE MDL을 얻지 못할 경우 에러(1205, Lock wait timeout exceeded)를 내고 빠르게 실패합니다. 무한정 대기하며 뒤따르는 커넥션까지 줄줄이 막는 연쇄 대기 현상을 예방할 수 있다는 점에서, 운영 환경의 스키마 변경 스크립트에는 짧은 lock_wait_timeout과 재시도 로직을 함께 넣는 패턴이 훨씬 안전합니다.

9. 예방 전략

애플리케이션 커넥션 풀에 idle transaction timeout을 설정해, 커밋되지 않은 채 방치된 트랜잭션이 일정 시간 후 자동으로 종료되도록 합니다.
코드 리뷰 단계에서 명시적으로 트랜잭션을 시작한 경우 반드시 COMMIT 또는 ROLLBACK으로 짝을 맞췄는지 점검합니다. 예외 처리 경로에서 COMMIT이 누락되는 경우가 특히 많습니다.
배포·마이그레이션 스크립트에서는 ALTER TABLE 직전에 performance_schema.metadata_locks를 사전 점검해 EXCLUSIVE 락 획득에 방해가 될 세션이 있는지 미리 확인합니다.
pt-kill 같은 도구나 자체 모니터링 스크립트로 State가 'Waiting for table metadata lock'인 세션이 임계 시간을 넘기면 알림을 보내거나 자동으로 조치하도록 구성합니다.

10. 버전별 동작 차이 정리

항목	5.6 이하	5.7	8.0
performance_schema.metadata_locks	미지원	지원하나 계측 기본 비활성	지원, 계측 기본 활성
sys 스키마	별도 설치 필요	별도 설치 필요	기본 제공
INFORMATION_SCHEMA.PROCESSLIST	주 진단 경로	주 진단 경로	성능상 비권장, performance_schema.threads 권장

정리해 드리면, ALTER TABLE이 멈췄을 때는 무거운 쿼리부터 의심하기보다 먼저 performance_schema.metadata_locks(또는 sys.schema_table_lock_waits)로 GRANTED 상태의 SHARED 락 보유 세션을 찾고, information_schema.innodb_trx로 그 세션의 트랜잭션이 idle 상태로 방치되었는지를 확인하는 순서가 가장 효율적입니다. KILL은 반드시 KILL CONNECTION으로, 그리고 진행 중인 쓰기 작업의 롤백 비용을 먼저 따져본 뒤에 실행해야 한다는 점을 잊지 말아야 합니다.