MySQL DDL이 끝나지 않는 이유: Metadata Lock 대기 재현과 진단
데이터가 3행뿐인 테이블에 컬럼 하나를 추가하는데 ALTER TABLE이 끝나지 않는다면, 데이터 처리량은 원인이 아닐 가능성이 큽니다. 3행을 처리하는 데 몇십 초가 걸릴 이유가 없기 때문입니다. 이럴 때는 DDL이 테이블의 메타데이터 잠금(Metadata Lock, MDL)을 얻지 못하고 기다리는지부터 확인해야 합니다.
ALGORITHM=INSTANT를 지정해도 마찬가지입니다. INSTANT는 데이터를 다시 쓰지 않는 방식일 뿐, 다른 트랜잭션과 충돌하지 않고 즉시 끝난다는 보장이 아닙니다. 그리고 이 충돌을 일으키는 세션은 UPDATE를 실행한 세션이 아니라 일반 SELECT만 실행하고 트랜잭션을 닫지 않은 세션일 수 있습니다.
이 글에서는 MySQL 8.4.11에서 이 상황을 재현했습니다. SELECT 한 번만 실행하고 Sleep 상태로 남은 50번 세션이 52번 세션의 INSTANT ALTER TABLE을 막는 상황을 만들고, performance_schema.metadata_locks와 sys.schema_table_lock_waits로 원인을 확정한 뒤 트랜잭션 종료 후 컬럼 추가까지 확인했습니다.
실습 환경과 테스트 데이터
테스트는 별도 스키마에서 독립된 연결 세 개로 진행했습니다. 연결 A는 SELECT 후 트랜잭션을 열어 두고, 연결 B는 DDL을 실행하며, 연결 C는 두 연결을 관찰합니다.
| 항목 | 실험 조건 |
|---|---|
| 환경 | Windows, MySQL Community 8.4.11, MySQL Workbench |
| 스키마와 테이블 | mdl_lab_26.ddl_target |
| 엔진과 데이터 | InnoDB, 3행 |
| 인덱스 | PRIMARY KEY(id) 하나 |
| 연결 A (50번) | REPEATABLE READ, 명시적 트랜잭션에서 일반 SELECT만 실행 |
| 연결 B (52번) | ALGORITHM=INSTANT로 note 컬럼 추가, lock_wait_timeout 600초 |
| 연결 C | autocommit이 켜진 관찰 연결 |
| 비교 기준 | MDL 보유·대기 관계, 트랜잭션 종료 후 DDL 완료 여부 |
테스트 테이블과 데이터는 다음 SQL로 만들었습니다. 같은 이름의 스키마가 이미 있다면 다른 이름으로 바꿔서 진행합니다.
CREATE DATABASE mdl_lab_26;
USE mdl_lab_26;
CREATE TABLE ddl_target (
id INT NOT NULL,
title VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO ddl_target (id, title, status)
VALUES
(1, 'task_1', 'READY'),
(2, 'task_2', 'READY'),
(3, 'task_3', 'DONE');
COMMIT;관찰 연결 C에서 버전, Performance Schema 활성화 여부, 테스트 행 수를 확인했습니다. 이후 진단에 쓰는 metadata_locks는 Performance Schema 테이블이므로 이 기능이 켜져 있어야 합니다. MySQL 8.0 이후에는 MDL 계측도 기본으로 활성화되어 있습니다.
SELECT
VERSION() AS mysql_version,
@@GLOBAL.performance_schema AS ps_enabled,
@@SESSION.autocommit AS observer_autocommit,
COUNT(*) AS test_rows
FROM mdl_lab_26.ddl_target;
여기서 autocommit 값은 조회를 실행한 연결 C의 값입니다. 연결 A는 START TRANSACTION으로 트랜잭션을 직접 열기 때문에, 이 값으로 A의 트랜잭션 종료 여부를 판단하지 않습니다.
SELECT *
FROM mdl_lab_26.ddl_target
ORDER BY id;
초기 테이블에는 id, title, status 세 컬럼만 있습니다. 실습의 목표는 여기에 note 컬럼을 INSTANT 방식으로 추가하는 것입니다.
재현: SELECT만 실행한 트랜잭션과 INSTANT ALTER TABLE
연결 A에서 트랜잭션을 열고 일반 SELECT 한 번만 실행합니다. UPDATE나 SELECT … FOR UPDATE는 사용하지 않습니다. SELECT가 결과를 돌려준 뒤 COMMIT과 ROLLBACK 모두 실행하지 않고 그대로 둡니다.
-- 연결 A (50번)
USE mdl_lab_26;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SELECT CONNECTION_ID();
START TRANSACTION;
SELECT *
FROM mdl_lab_26.ddl_target
WHERE id = 1;연결 B에서는 접속 번호가 A와 다른지 확인한 뒤, 기존 대기 제한 값을 저장하고 DDL을 실행합니다.
-- 연결 B (52번)
USE mdl_lab_26;
SELECT CONNECTION_ID();
SET @saved_mdl_timeout := @@SESSION.lock_wait_timeout;
SET SESSION lock_wait_timeout = 600;
ALTER TABLE mdl_lab_26.ddl_target
ADD COLUMN note VARCHAR(100) NULL,
ALGORITHM=INSTANT;여기서 600초는 대기 시간을 늘린 값이 아니라 줄인 값입니다. lock_wait_timeout의 기본값은 31536000초, 즉 1년입니다. 기본값 그대로 DDL을 실행하면 차단 세션이 끝나지 않는 한 사실상 무한히 기다립니다. 실습에서는 캡처할 시간을 확보하면서도 끝없이 기다리지 않도록 600초로 제한했습니다. 운영에서 이 값을 어떻게 잡아야 하는지는 뒤에서 다시 다룹니다.
ALTER TABLE을 실행하면 연결 B는 결과를 돌려주지 않고 멈춥니다. 3행짜리 테이블에 INSTANT로 컬럼을 추가하는 작업인데도 끝나지 않는 상황입니다.
1단계 증상 확인: threads에 드러나는 MDL 대기
DDL이 끝나지 않은 상태에서 연결 C로 실습 스키마에 붙은 세션을 조회했습니다.
SELECT
PROCESSLIST_ID AS conn_id,
PROCESSLIST_COMMAND AS command_name,
PROCESSLIST_TIME AS state_seconds,
PROCESSLIST_STATE AS state_name,
LEFT(PROCESSLIST_INFO, 160) AS current_sql
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
AND PROCESSLIST_DB = 'mdl_lab_26'
AND PROCESSLIST_ID <> CONNECTION_ID()
ORDER BY PROCESSLIST_ID;
| conn_id | command_name | state_seconds | state_name | current_sql |
|---|---|---|---|---|
| 50 | Sleep | 33 | NULL | NULL |
| 52 | Query | 18 | Waiting for table metadata lock | ALTER TABLE … ADD COLUMN note … ALGORITHM=INSTANT |
행 잠금 대기와 다른 점이 여기서 바로 보입니다. InnoDB 행 잠금을 기다릴 때는 상태가 updating처럼 모호하게 표시되지만, MDL 대기는 Waiting for table metadata lock이라는 명확한 상태로 나타납니다. 증상 확인 단계에서 원인의 종류를 좁힐 수 있는 셈입니다.
다만 이 화면은 누가 잠금을 쥐고 있는지 알려 주지 않습니다. 50번은 Sleep 상태에 SQL도 NULL이라 원인처럼 보이지 않습니다. 52번의 state_seconds 18도 현재 상태가 이어진 시간일 뿐, 컬럼 추가를 위해 데이터를 처리한 시간이 아닙니다. 차단 세션을 찾으려면 잠금 정보를 직접 봐야 합니다.
SELECT 하나가 DDL을 막는 이유: 행 잠금과 MDL은 다릅니다
이 상황에서 세운 가설은 다음과 같습니다. 50번은 SELECT를 끝냈지만 트랜잭션을 종료하지 않아 테이블의 공유 메타데이터 잠금을 계속 보유하고 있고, 52번의 DDL은 필요한 배타적 메타데이터 잠금을 얻지 못하고 있습니다.
일반 SELECT는 행을 배타적으로 잠그지 않습니다. 그래서 “SELECT는 다른 작업을 막지 않는다”고 생각하기 쉽지만, 이는 행 잠금에 한정된 이야기입니다. MDL은 테이블을 사용하는 동안 다른 연결이 그 구조를 바꾸지 못하게 막는 별도의 잠금입니다. 명시적 트랜잭션 안에서 테이블을 읽으면 이 MDL은 문장이 끝나도 풀리지 않고 트랜잭션이 끝날 때까지 유지됩니다. 트랜잭션 도중에 테이블 구조가 바뀌면 같은 트랜잭션 안에서 읽은 결과의 일관성이 깨지기 때문입니다.
| 구분 | InnoDB 행 잠금 | 메타데이터 잠금(MDL) |
|---|---|---|
| 보호 대상 | 행(레코드)과 갭 | 테이블 구조 등 객체 정의 |
| 일반 SELECT | 잡지 않음 (일관된 읽기) | SHARED_READ를 잡음 |
| 확인 테이블 | data_locks, data_lock_waits | metadata_locks |
| 대기 표시 | LOCK_STATUS = WAITING | LOCK_STATUS = PENDING |
| 대기 제한 변수 | innodb_lock_wait_timeout (기본 50초) | lock_wait_timeout (기본 1년) |
INSTANT도 같은 맥락으로 이해해야 합니다. INSTANT는 지원되는 변경에서 데이터를 다시 쓰지 않고 데이터 사전만 바꾸는 방식입니다. 데이터 재작성 비용은 사라지지만, 데이터 사전을 바꾸는 순간에는 테이블에 대한 배타적 MDL이 필요합니다. INSTANT는 실행 시간을 줄여 주는 기능이지 열린 트랜잭션을 건너뛰는 기능이 아닙니다.
2단계 잠금 확인: metadata_locks 세 행을 읽는 방법
연결 C에서 metadata_locks와 threads를 연결해 대상 테이블의 MDL을 조회했습니다.
SELECT
t.PROCESSLIST_ID AS conn_id,
t.PROCESSLIST_COMMAND AS command_name,
m.OBJECT_NAME AS table_name,
m.LOCK_TYPE AS lock_type,
m.LOCK_DURATION AS lock_duration,
m.LOCK_STATUS AS lock_status
FROM performance_schema.metadata_locks AS m
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = m.OWNER_THREAD_ID
WHERE m.OBJECT_TYPE = 'TABLE'
AND m.OBJECT_SCHEMA = 'mdl_lab_26'
AND m.OBJECT_NAME = 'ddl_target'
ORDER BY conn_id, lock_status, lock_type;
| conn_id | command_name | lock_type | lock_duration | lock_status |
|---|---|---|---|---|
| 50 | Sleep | SHARED_READ | TRANSACTION | GRANTED |
| 52 | Query | SHARED_UPGRADABLE | TRANSACTION | GRANTED |
| 52 | Query | EXCLUSIVE | TRANSACTION | PENDING |
세 행을 순서대로 읽으면 DDL이 멈춘 지점이 드러납니다.
- 50번 SHARED_READ / TRANSACTION / GRANTED: 일반 SELECT가 잡는 공유 읽기 잠금입니다. SELECT는 이미 끝났고 세션은 Sleep인데도 잠금이 남아 있습니다. LOCK_DURATION이 TRANSACTION이라는 것은 이 잠금이 트랜잭션 종료까지 유지된다는 뜻이며, 가설과 정확히 일치합니다.
- 52번 SHARED_UPGRADABLE / GRANTED: ALTER TABLE이 처음에 잡는 잠금입니다. 다른 연결의 읽기와 쓰기를 허용하면서, 같은 테이블에 대한 다른 DDL은 막습니다. 나중에 EXCLUSIVE로 올릴 수 있는 잠금이라는 의미에서 UPGRADABLE이라는 이름이 붙었습니다.
- 52번 EXCLUSIVE / PENDING: 데이터 사전을 실제로 바꾸는 순간에 필요한 배타적 잠금입니다. 50번의 SHARED_READ와 호환되지 않아 대기 중입니다.
52번에 GRANTED 행이 있다고 해서 ALTER TABLE이 진행 중인 것은 아닙니다. 첫 단계 잠금은 얻었지만 실제 변경에 필요한 EXCLUSIVE 단계에서 멈춰 있습니다. LOCK_DURATION은 초 단위 시간이 아니라 잠금이 유지되는 범위(STATEMENT, TRANSACTION, EXPLICIT)를 나타낸다는 점도 함께 기억해 둡니다.
3단계 관계 확정: sys.schema_table_lock_waits로 차단 세션 찾기
metadata_locks만으로도 이번 실습의 원인은 분명해 보입니다. 하지만 운영에서는 같은 테이블에 수십 개의 SHARED_READ가 걸려 있을 수 있으므로, 대기 세션과 차단 세션을 짝지은 결과로 확정하는 편이 안전합니다. MDL에는 data_lock_waits 같은 관계 테이블이 없어서, metadata_locks를 가공한 sys 스키마 뷰를 사용합니다.
SELECT
w.waiting_pid AS waiting_conn,
w.blocking_pid AS blocking_conn,
bt.PROCESSLIST_COMMAND AS blocker_cmd,
w.object_name AS table_name,
w.waiting_lock_type,
w.blocking_lock_type,
LEFT(w.waiting_query, 160) AS waiting_sql
FROM sys.schema_table_lock_waits AS w
LEFT JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = w.blocking_thread_id
WHERE w.object_schema = 'mdl_lab_26'
AND w.object_name = 'ddl_target'
AND w.waiting_pid <> w.blocking_pid
ORDER BY waiting_conn, blocking_conn;
| waiting_conn | blocking_conn | blocker_cmd | waiting_lock_type | blocking_lock_type | waiting_sql |
|---|---|---|---|---|---|
| 52 | 50 | Sleep | EXCLUSIVE | SHARED_READ | ALTER TABLE … ALGORITHM=INSTANT |
52번의 EXCLUSIVE 요청이 Sleep 상태인 50번의 SHARED_READ에 막혀 있다는 관계가 한 행으로 확정됩니다. 세션 상태(1단계), 원시 MDL 정보(2단계), 대기 관계(3단계)가 모두 같은 원인을 가리킵니다.
이 쿼리에서 w.waiting_pid <> w.blocking_pid 조건은 반드시 필요합니다. 이 뷰는 대상 객체의 PENDING 잠금과 GRANTED 잠금을 짝지어 보여 주는데, 같은 세션인지는 걸러내지 않습니다. 52번은 자신의 SHARED_UPGRADABLE을 GRANTED로 가지고 있으므로, 조건이 없으면 “52번이 52번을 막는다”는 의미 없는 행이 함께 나옵니다.
같은 이유로 이 뷰의 결과를 차단 세션 목록으로 바로 쓰면 안 됩니다. 뷰는 잠금 종류의 호환 여부를 엄격히 따지지 않고 같은 객체의 보유 잠금을 짝지으므로, 운영 환경에서는 실제로 충돌하지 않는 세션까지 목록에 오를 수 있습니다. sql_kill_blocking_connection 컬럼에 KILL 문장이 만들어져 있더라도 일괄 실행하지 말고, blocking_lock_type과 2단계의 원시 결과를 대조해서 판단합니다.
4단계 해소 검증: 트랜잭션 종료 후 컬럼 추가 확인
차단 원인을 확인했으므로 연결 A에서 트랜잭션을 종료합니다.
-- 연결 A (50번)
COMMIT;50번의 SHARED_READ가 풀리자 연결 B의 ALTER TABLE이 바로 완료되었습니다. DDL은 실행 전후에 자동으로 커밋되므로 B에서 따로 COMMIT할 필요가 없습니다. 연결 C에서 결과를 확인했습니다.
SELECT
(
SELECT COUNT(*)
FROM mdl_lab_26.ddl_target
) AS test_rows,
(
SELECT COUNT(*)
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'mdl_lab_26'
AND TABLE_NAME = 'ddl_target'
AND COLUMN_NAME = 'note'
) AS note_column_exists,
(
SELECT COUNT(*)
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'
AND OBJECT_SCHEMA = 'mdl_lab_26'
AND OBJECT_NAME = 'ddl_target'
AND LOCK_STATUS = 'PENDING'
) AS pending_mdl;
| 비교 항목 | 트랜잭션 종료 전 | 종료 후 |
|---|---|---|
| 52번 DDL의 MDL | EXCLUSIVE / PENDING | PENDING 0건 |
| note 컬럼 | 없음 | 존재 (조회값 1) |
| 데이터 행 수 | 3행 | 3행 |
마지막 조회에서 전체 MDL 건수가 아니라 PENDING 건수만 센 데는 이유가 있습니다. 이 조회 자체가 첫 번째 서브쿼리에서 ddl_target을 읽으므로, 실행되는 동안 연결 C도 이 테이블에 STATEMENT 범위의 SHARED_READ를 잡습니다. 전체 건수를 세면 관찰하는 쿼리 자신의 잠금이 섞일 수 있어, “대기 중인 잠금이 없다”는 것을 확인하는 PENDING 기준이 정확합니다.
이 결과가 검증한 것은 컬럼 추가, 행 수 유지, 대기 잠금 해소 세 가지입니다. 행 수가 같다는 것만으로 모든 데이터 값을 대조했다고 말할 수는 없으며, 잠금 해제 뒤 DDL 자체의 처리 시간은 따로 측정하지 않았습니다.
운영 DDL 전에 확인할 것: 대기열과 lock_wait_timeout
MDL 대기가 행 잠금 대기보다 위험한 이유는 대기열 효과에 있습니다. 배타적 MDL을 기다리는 DDL이 대기열에 들어가면, 그 뒤에 들어오는 일반 SELECT와 DML도 같은 테이블의 MDL을 얻지 못하고 Waiting for table metadata lock 상태로 줄을 서게 됩니다. 배타적 요청이 먼저 처리되도록 우선순위가 정해져 있기 때문입니다. 결과적으로 Sleep 세션 하나와 ALTER TABLE 하나 때문에 해당 테이블을 쓰는 모든 요청이 멈출 수 있습니다.
이번 실습에서는 후속 요청을 추가하지 않아 대기열이 늘어나는 모습은 재현하지 않았습니다. 운영에서 DDL이 멈췄다면 차단 세션뿐 아니라 같은 테이블에서 MDL을 기다리는 연결 수가 늘어나고 있는지도 함께 확인해야 합니다.
이 위험을 줄이는 가장 실용적인 방법은 DDL을 실행하는 세션의 lock_wait_timeout을 짧게 잡는 것입니다. 기본값 1년으로 두면 DDL이 대기열을 막은 채 계속 기다리지만, 몇 초로 제한하면 잠금을 얻지 못했을 때 오류(1205)로 빠져나오고 대기열도 풀립니다. 이후 차단 원인을 정리하고 다시 시도하면 됩니다.
-- DDL 세션에서만 적용
SET SESSION lock_wait_timeout = 5;
ALTER TABLE mdl_lab_26.ddl_target
ADD COLUMN memo VARCHAR(100) NULL,
ALGORITHM=INSTANT;5초는 예시 값입니다. 서비스가 그 테이블의 요청 지연을 얼마나 견딜 수 있는지를 기준으로 정합니다. lock_wait_timeout은 잠금을 한 번 얻으려고 기다리는 시간에 적용되며, DDL 전체 실행 시간을 제한하는 값이 아니라는 점도 구분해야 합니다.
DDL을 실행하기 전에 대상 테이블의 MDL을 쥔 세션이 있는지 먼저 확인하는 습관도 도움이 됩니다. 2단계 쿼리를 조금 바꾸면 사전 점검용으로 쓸 수 있습니다.
SELECT
t.PROCESSLIST_ID AS conn_id,
t.PROCESSLIST_COMMAND AS command_name,
t.PROCESSLIST_TIME AS state_seconds,
m.LOCK_TYPE AS lock_type,
m.LOCK_DURATION AS lock_duration
FROM performance_schema.metadata_locks AS m
JOIN performance_schema.threads AS t
ON t.THREAD_ID = m.OWNER_THREAD_ID
WHERE m.OBJECT_TYPE = 'TABLE'
AND m.OBJECT_SCHEMA = 'mdl_lab_26'
AND m.OBJECT_NAME = 'ddl_target'
AND m.LOCK_STATUS = 'GRANTED'
AND t.PROCESSLIST_ID <> CONNECTION_ID()
ORDER BY state_seconds DESC;여기에 command_name이 Sleep이고 lock_duration이 TRANSACTION인 세션이 보이면, 이번 실습의 50번과 같은 상태의 세션이 있다는 뜻입니다. 이 쿼리는 이번 글의 캡처에는 포함하지 않은 점검용 쿼리입니다.
읽기만 한 차단 세션을 정리하는 판단 기준
이번 차단 세션은 데이터를 바꾸지 않고 SELECT만 실행했습니다. 그래서 COMMIT, ROLLBACK, KILL 중 어느 것을 선택해도 테이블 데이터는 달라지지 않습니다. UPDATE를 쥔 채 멈춘 세션을 KILL하면 변경 내용이 롤백되는 것과 비교하면, 데이터 측면의 위험은 낮은 편입니다.
그렇다고 바로 종료해도 된다는 뜻은 아닙니다. 운영에서는 같은 트랜잭션 안에서 이미 다른 테이블을 변경했을 수 있고, 애플리케이션이 그 커넥션으로 요청을 처리하는 중일 수도 있습니다. 차단 세션을 발견하면 그 트랜잭션이 무엇을 했는지 확인한 뒤 종료 방법을 정해야 하며, 이번 실습의 COMMIT을 모든 차단 세션에 그대로 적용하지 않습니다.
반대 방향의 위험도 있습니다. 성공한 ALTER TABLE은 ROLLBACK으로 되돌릴 수 없습니다. DDL은 실행 시점에 자동 커밋되기 때문입니다. 컬럼 추가를 원복하려면 별도의 DROP COLUMN 같은 DDL이 필요하고, 그 원복 DDL도 똑같이 MDL을 기다릴 수 있습니다. 운영 DDL을 계획할 때 원복 절차까지 같은 기준으로 준비해야 하는 이유입니다.
같은 문제가 반복된다면 애플리케이션이 조회 후 외부 응답을 기다리는 동안 트랜잭션을 열어 두는지, 커넥션을 풀에 반환할 때 트랜잭션이 정리되는지를 확인해야 합니다. 이번 실습에서 확인한 원인이 아니라 운영에서 이어서 조사할 항목입니다. 실습 후에는 연결 B에서 저장해 둔 값을 확인하고 원래 설정으로 복구합니다.
SELECT @saved_mdl_timeout;
-- 저장값이 NULL이 아닐 때 실행
SET SESSION lock_wait_timeout = @saved_mdl_timeout;정리하면, 작은 테이블의 DDL이 끝나지 않을 때는 데이터량이나 알고리즘보다 먼저 threads에서 Waiting for table metadata lock 상태를 확인합니다. 그다음 metadata_locks에서 TRANSACTION 범위로 남은 공유 잠금을 찾고, sys.schema_table_lock_waits로 차단 관계를 확정합니다. 이 순서를 따르면 “ALTER가 안 끝난다”는 현상을 “Sleep 상태인 50번이 SELECT 후 닫지 않은 트랜잭션으로 SHARED_READ를 쥐고 52번의 EXCLUSIVE 요청을 막고 있다”는 구체적인 원인으로 바꿀 수 있습니다.
댓글 0
첫 댓글을 남겨보세요.