앞선 포스팅에서 ALGORITHM=INSTANT를 활용한 Native Online DDL과 gh-ost를 통한 무중단 스키마 변경 전략을 살펴보았습니다. 하지만 "0.1초 만에 끝나는 INSTANT 컬럼 추가니까 낮에 바로 실행해도 되겠지?", 혹은 "gh-ost로 데이터 복사를 다 끝내고 마지막 Cut-over(테이블 교체)만 남았으니 안전하겠지?"라고 방심하는 순간, 예상치 못한 대형 장애가 터지곤 합니다.
모니터링 화면에 갑자기 Waiting for table metadata lock 상태의 세션이 수백 개씩 쌓이더니, 불과 몇 초 만에 애플리케이션의 DB 커넥션 풀(HikariCP 등)이 고갈되어 서비스 전체가 응답 불능(Hang)에 빠지는 현상입니다.
이번 글에서는 무중단 스키마 변경의 가장 마지막이자 치명적인 함정인 메타데이터 락(Metadata Lock, 이하 MDL)의 내부 동작 아키텍처를 파헤쳐 보고, 작업 전/중/후에 이를 사전에 감지하고 방어하는 실무 모니터링 쿼리 및 핵심 파라미터 설정 가이드를 정리해 보겠습니다.
1. Metadata Lock(MDL)의 내부 작동 원리와 도미노 장애 시나리오
InnoDB 스토리지 엔진이 레코드 단위의 락(Record Lock, Gap Lock)을 관리한다면, Metadata Lock(MDL)은 그보다 상위 계층인 MySQL Server 엔진(SQL Layer)에서 테이블의 구조(스키마 정의)를 보호하기 위해 관리하는 테이블 레벨의 락입니다.
트랜잭션이 특정 테이블을 조회하고 있는 도중에 다른 세션에서 그 테이블의 컬럼을 지우거나 이름을 바꿔버리면 데이터 정합성이 깨지기 때문에, MySQL은 모든 쿼리에 암묵적으로 MDL을 부여합니다.
① MDL 잠금 모드와 호환성 매트릭스
| 잠금 종류 | 획득하는 작업 (SQL) | Shared Read / Write와 호환 여부 | Exclusive와 호환 여부 |
| MDL_SHARED_READ (SRO) | 단순 SELECT (잠금 없는 일반 조회) | O (동시 실행 가능) | X (대기 발생) |
| MDL_SHARED_WRITE (SW) | INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE | O (동시 실행 가능) | X (대기 발생) |
| MDL_EXCLUSIVE (X) | ALTER TABLE (시작/종료 시점), DROP TABLE, RENAME TABLE | X (모든 쿼리와 충돌) | X (대기 발생) |
- 핵심 규칙 1: MDL은 단일 쿼리가 끝날 때 해제되는 것이 아니라, 트랜잭션이 완전히 종료(COMMIT 또는 ROLLBACK)될 때까지 유지됩니다.
- 핵심 규칙 2 (장애의 근본 원인): MDL 대기 큐(Wait Queue)는 단순한 선입선출(FIFO)이 아니라 잠금 요청의 우선순위(Priority)를 따릅니다. MDL_EXCLUSIVE는 MDL_SHARED_READ보다 우선순위가 높습니다.
② 공포의 도미노 현상 (Convoy Effect) 발생 과정
ALGORITHM=INSTANT로 컬럼을 하나 추가하는 데 왜 서버가 죽을까요? 시간순으로 재구성해 보겠습니다.
[정상 상태] ──> [Session A: Long Query/Tx 시작] ──> [Session B: ALTER/RENAME 실행] ──> [Session C, D, E...: 일반 SELECT 유입]
(MDL_SHARED_READ 점유 중) (MDL_EXCLUSIVE 요청 -> 대기!) (Session B에 막혀 도미노 대기 -> 장애!)
- 빌런 트랜잭션 등장 (Session A): 배치 서버나 어드민 페이지에서 orders 테이블을 조회하는 무거운 슬로우 쿼리가 실행되었거나, 애플리케이션 코드에서 @Transactional을 열어두고 외부 API를 호출하느라 커밋을 안 하고 버티고 있습니다. 이 세션은 orders 테이블에 대해 MDL_SHARED_READ를 쥐고 놓지 않는 상태입니다.
- 스키마 변경 시도 (Session B): DBA가 ALTER TABLE orders ADD COLUMN ... ALGORITHM=INSTANT를 실행(또는 gh-ost가 마지막 RENAME Cut-over를 시도)합니다. 이 작업을 완료하려면 단 1밀리초라도 MDL_EXCLUSIVE를 획득해야 합니다. 하지만 Session A가 Shared Lock을 쥐고 있으므로 Session B는 큐에서 대기(Waiting for table metadata lock)합니다.
- [14:00:06 ~] 일반 트래픽 전면 차단 (Session C, D, E...): 일반 유저들이 주문 내역을 보기 위해 0.001초짜리 가벼운 SELECT * FROM orders WHERE id = 100을 날립니다. 평소 같으면 바로 통과했겠지만, 앞에서 우선순위가 높은 Session B(MDL_EXCLUSIVE)가 대기 중이기 때문에, 뒤따라오는 모든 단순 SELECT 쿼리들까지 Session B 뒤로 줄을 서며 전부 블로킹됩니다.
- 서비스 장애: 초당 1,000개씩 들어오던 조회 쿼리가 단 5초 만에 5,000개의 대기 스레드를 만들고, DB의 max_connections가 꽉 차며 서비스가 완전히 다운됩니다.
2. 왜 gh-ost의 Cut-over는 일반 DDL보다 안전할까? (내부 알고리즘)
pt-osc나 일반 DDL은 마지막에 RENAME TABLE을 던져놓고 블로킹되면 사람이 수동으로 죽이기 전까지 마냥 기다리지만, gh-ost는 독자적인 Atomic Two-Step Cut-over 알고리즘을 통해 MDL 대기로 인한 장애를 방어하도록 설계되어 있습니다.
- Session 1 (가짜 파수꾼): gh-ost가 먼저 원본 테이블에 LOCK TABLES orders WRITE를 겁니다. 이때 만약 롱 트랜잭션이 있다면 이 요청은 대기하지만, 뒤이어 들어오는 일반 SELECT를 막기 시작합니다.
- Session 2 (교체 집행자): gh-ost의 다른 커넥션이 들어와 RENAME TABLE orders TO _orders_del, _orders_gho TO orders를 실행합니다. Session 1이 WRITE 락을 쥐고(또는 대기하고) 있으므로 Session 2도 대기 큐에 들어갑니다.
- 타임아웃 감지 및 즉각 롤백: gh-ost는 이 과정에 매우 짧은 타임아웃(--cut-over-lock-timeout-seconds, 기본값 3초)을 겁니다. 만약 롱 쿼리 때문에 3초 안에 RENAME이 성공하지 못하면, gh-ost는 즉시 Session 1과 2의 커넥션을 스스로 끊어버립니다.
- 자동 재시도: 락 요청이 사라졌으므로 막혀있던 일반 서비스 쿼리들은 즉시 정상 처리되어 장애로 번지지 않습니다. gh-ost는 다시 Binlog를 따라가며 잠시 후 Cut-over를 자동으로 재시도합니다.
주의: gh-ost가 아무리 안전하게 빠져나온다 해도, 재시도하는 3초 동안은 순간적인 쿼리 적체(Spike)가 발생할 수 있습니다. 따라서 어떤 도구를 쓰든 작업 직전 MDL을 유발할 수 있는 롱 트랜잭션을 찾아 제거하는 것이 정석입니다.
3. 실무 방어 가이드 1: 필수 안전핀 파라미터 설정 (lock_wait_timeout)
Native Online DDL이나 수동 RENAME, DROP을 실행할 때 도미노 장애를 막는 가장 확실하고 간단한 방법은 세션 레벨에서 lock_wait_timeout을 극단적으로 짧게 설정하는 것입니다.
많은 개발자가 헷갈려하는 두 가지 타임아웃 파라미터의 차이를 명확히 알아야 합니다.
| 파라미터명 | 기본값 (Default) | 적용 대상 및 역할 |
| innodb_lock_wait_timeout | 50초 | InnoDB 스토리지 엔진 내부의 Row-level Lock(레코드 락, 갭 락) 대기 시간 |
| lock_wait_timeout | 31536000초 (1년) | MySQL 서버 레이어의 Metadata Lock(MDL) 대기 시간 |
보시다시피 lock_wait_timeout의 기본값은 무려 1년입니다. 즉, 설정 없이 ALTER TABLE을 날렸다가 롱 쿼리에 걸리면 1년 동안 취소되지 않고 모든 트래픽을 막아버립니다.
실무 DDL 실행 표준 패턴
운영 DB에서 DDL을 직접 실행할 때는 반드시 아래와 같이 세트로 묶어서 실행하는 습관을 들여야 합니다.
-- 1. 현재 세션의 MDL 대기 타임아웃을 3초로 제한 (3초 내 락 획득 실패 시 즉시 에러 반환 후 포기)
SET SESSION lock_wait_timeout = 3;
-- 2. InnoDB 레코드 락 타임아웃도 함께 축소
SET SESSION innodb_lock_wait_timeout = 3;
-- 3. 안전하게 Online DDL 실행
ALTER TABLE orders
ADD COLUMN delivery_memo VARCHAR(255) DEFAULT NULL,
ALGORITHM=INSTANT, LOCK=NONE;
이렇게 하면 3초 안에 MDL을 얻지 못할 경우 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 에러를 뱉고 DDL만 깔끔하게 실패합니다. 서비스 장애는 전혀 발생하지 않으며, 잠시 후 다시 시도하면 됩니다.
4. 실무 방어 가이드 2: 사전 감지 및 범인 색출 모니터링 쿼리
스키마 변경 작업을 시작하기 직전, 그리고 gh-ost의 Cut-over 플래그를 해제하기 직전에는 반드시 아래 쿼리들을 통해 대상 테이블을 물고 있는 미커밋 트랜잭션이나 롱 쿼리가 없는지 확인해야 합니다.
① [사전 점검] 현재 실행 중인 모든 장기 트랜잭션(Long Transaction) 조회
단순 SHOW PROCESSLIST만으로는 트랜잭션이 열린 채 아무 쿼리도 안 날리고 대기 중인 Sleep 상태의 좀비 트랜잭션을 찾을 수 없습니다. 반드시 information_schema.innodb_trx를 조회해야 합니다.
SELECT
trx.trx_id,
trx.trx_mysql_thread_id AS thread_id,
trx.trx_state,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS duration_sec,
p.USER,
p.HOST,
p.DB,
p.COMMAND,
p.STATE,
trx.trx_query AS current_query
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist p
ON trx.trx_mysql_thread_id = p.ID
WHERE TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) > 5
ORDER BY duration_sec DESC;
- 해석: COMMAND가 Sleep인데 duration_sec이 수십 초 이상인 세션이 있다면, 애플리케이션에서 트랜잭션을 열고 COMMIT을 안 한 채 외부 통신 등을 하고 있는 상태입니다. 이 세션이 대상 테이블을 한 번이라도 조회했다면 보이지 않는 MDL_SHARED_READ를 쥐고 있는 상태이므로 끝날 때까지 기다리거나 강제 종료(KILL)해야 합니다.
② [장애 대응] MDL 대기를 유발하고 있는 '진짜 범인(Blocker)' 스레드 정확히 찾아내기
만약 이미 Waiting for table metadata lock 현상이 발생했다면, 수백 개의 대기 세션 중 누가 맨 앞에서 락을 쥐고 안 놔주는지 1초 만에 찾아내어 KILL 해야 합니다. (MySQL 8.0 기준 performance_schema.metadata_locks 활용)
참고: MySQL 8.0에서는 기본 활성화되어 있으나, 만약 조회되지 않는다면 performance_schema.setup_instruments에서 wait/lock/metadata/sql/mdl 항목이 ENABLED='YES'인지 확인해야 합니다.
SELECT
locked_Scroll.OBJECT_SCHEMA AS db_name,
locked_Scroll.OBJECT_NAME AS table_name,
waiting_trx.PROCESSLIST_ID AS waiting_thread_id,
waiting_trx.PROCESSLIST_INFO AS waiting_query,
blocking_trx.PROCESSLIST_ID AS blocking_thread_id,
blocking_trx.PROCESSLIST_COMMAND AS blocker_command,
blocking_trx.PROCESSLIST_TIME AS blocker_idle_time_sec,
blocking_trx.PROCESSLIST_INFO AS blocking_current_query,
CONCAT('KILL ', blocking_trx.PROCESSLIST_ID, ';') AS kill_command
FROM performance_schema.metadata_locks waiting_lock
JOIN performance_schema.metadata_locks locked_Scroll
ON waiting_lock.OBJECT_TYPE = locked_Scroll.OBJECT_TYPE
AND waiting_lock.OBJECT_SCHEMA = locked_Scroll.OBJECT_SCHEMA
AND waiting_lock.OBJECT_NAME = locked_Scroll.OBJECT_NAME
JOIN performance_schema.threads waiting_trx
ON waiting_lock.OWNER_THREAD_ID = waiting_trx.THREAD_ID
JOIN performance_schema.threads blocking_trx
ON locked_Scroll.OWNER_THREAD_ID = blocking_trx.THREAD_ID
WHERE waiting_lock.LOCK_STATUS = 'PENDING'
AND locked_Scroll.LOCK_STATUS = 'GRANTED'
AND waiting_lock.OWNER_THREAD_ID != locked_Scroll.OWNER_THREAD_ID;
쿼리 결과 활용법:
- 위 쿼리를 실행하면 현재 MDL을 기다리는 쿼리(waiting_query, 보통 ALTER나 RENAME)와 이를 가로막고 있는 범인 스레드 ID(blocking_thread_id), 그리고 즉시 실행할 수 있는 kill_command(KILL 12345;)가 출력됩니다.
- 긴급 상황에서는 고민할 것 없이 두 가지 중 하나를 즉시 선택해 실행합니다:
- 선택 A (권장): 대기 중인 DDL 세션(waiting_thread_id)을 KILL하여 스키마 변경을 취소하고 서비스 트래픽부터 즉시 정상화한다.
- 선택 B: 만약 범인(blocking_thread_id)이 단순 배치 조회나 어드민 슬로우 쿼리임이 확실하다면, 범인 세션을 KILL하여 DDL이 즉시 완료되게 만든다.
5. 마무리: 무중단 스키마 변경 최종 안전 수칙 4계명
- INSTANT도 MDL 앞에서는 평등하다: 아무리 0.1초 만에 끝나는 ALGORITHM=INSTANT라도 시작과 끝에는 MDL_EXCLUSIVE가 필요합니다. 방심하지 말고 작업 전 반드시 innodb_trx를 확인하세요.
- 타임아웃 없는 수동 DDL은 자살 행위다: 직접 콘솔에서 DDL을 실행할 때는 무조건 SET SESSION lock_wait_timeout = 3;을 먼저 선언하여 도미노 블로킹을 원천 차단하세요.
- gh-ost의 Cut-over는 눈으로 보며 수동으로 제어하라: 대용량 테이블 작업 시 --postpone-cut-over-flag-file 옵션을 사용해 복사는 밤새 돌려두고, 마지막 테이블 교체 순간에는 엔지니어가 직접 롱 쿼리가 없는 타이밍을 확인한 뒤 플래그를 풀어 마무리하세요.
- 애플리케이션의 트랜잭션 범위를 다이어트하라: 근본적으로 MDL 장애가 잦은 팀은 애플리케이션 코드 내에 @Transactional 범위가 너무 넓거나(외부 API 호출 포함 등), 인덱스 없는 슬로우 쿼리가 방치되어 있는 경우가 많습니다. DB 작업 전후로 슬로우 쿼리 튜닝이 병행되어야 진정한 무중단 아키텍처가 완성됩니다.