온라인 DDL 4

온라인 DDL 3에서 프로세스 목록(process list)과 innodb_trx로 오래 열린 트랜잭션(transaction)을 찾는 법을 봤다. 이 문서는 누가 누구를 막고 있는지를 한 번에 보는 잠금 대기 조회, 막는 쪽을 끊는 KILL, 오래 걸리는 ALTER의 진행률 확인을 다룬다. DDL(Data Definition Language, 데이터 정의어)을 막는 메타데이터 락(metadata lock)도 행 잠금과 같은 방식으로 조회한다.

누가 누구를 막나 — 잠금 대기 조회

잠금은 두 층에 있고 조회하는 곳도 다르다.

무엇을 기다리나	원본 테이블	요약 조회
InnoDB 행 잠금	performance_schema.data_locks·data_lock_waits	sys.innodb_lock_waits
메타데이터 락	performance_schema.metadata_locks	sys.schema_table_lock_waits

-- 행 잠금: 기다리는 쿼리와 막고 있는 접속
SELECT wait_age, waiting_query, blocking_pid, blocking_query, sql_kill_blocking_connection
FROM sys.innodb_lock_waits;

-- 메타데이터 락: ALTER를 막고 있는 접속
SELECT object_name, waiting_pid, waiting_query, blocking_pid, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits;

performance_schema의 원본 테이블은 잠금마다 한 행이라 읽기 어렵다. sys 스키마(sys schema)는 그 원본을 사람이 읽기 좋게 이어 붙인 뷰(view) 모음이다. sys.innodb_lock_waits는 행 잠금을 기다리는 쪽과 막는 쪽을 한 행에 놓고, sys.schema_table_lock_waits는 같은 일을 메타데이터 락에 대해 한다. 두 뷰 모두 막는 쪽을 끊는 KILL 문장(sql_kill_blocking_connection)까지 만들어 준다. 다만 메타데이터 락 대기에서는 대기 중인 ALTER 자신도 뒤 쿼리를 막는 쪽으로 나올 수 있다. ALTER가 배타 메타데이터 락을 얻으려고 줄 앞에서 기다리는 동안, 그 뒤에 온 SELECT 같은 쿼리는 ALTER가 끝나기를 기다리기 때문이다. 이때 ALTER를 끊으면 작업만 날아가고 진짜 원인인 열린 트랜잭션은 그대로 남는다. 그래서 생성된 KILL을 그대로 돌리기 전에 blocking_pid가 ALTER인지, 열린 트랜잭션인지 확인한다. 메타데이터 락 조회에 필요한 인스트루먼트(instrument) wait/lock/metadata/sql/mdl은 MySQL 8.4에서 기본으로 켜져 있다. 행 잠금이 어떤 모양으로 걸리는지는 InnoDB 잠금에서 본다.

KILL — 막는 쪽 끊기

KILL QUERY 1234;   -- 실행 중인 문장만 중단, 접속은 유지
KILL 1234;         -- 접속을 끊는다 (KILL CONNECTION과 같다)

• 무엇을 끊을지 고른다. KILL QUERY는 지금 실행 중인 문장만 멈추고 접속은 남긴다. Sleep 상태로 트랜잭션만 열어 둔 세션에는 멈출 문장이 없으므로, 그 트랜잭션이 쥔 잠금을 풀려면 KILL(접속 끊기)을 써야 한다. 접속이 끊기면 커밋(commit)하지 않은 변경은 롤백(rollback)된다.
• 즉시 끝나지 않는다. KILL은 대상 스레드에 "멈추라"는 표시만 하고 바로 돌아온다. 스레드는 정해진 지점에서 이 표시를 확인하고 멈춘다. 많이 바꾼 트랜잭션은 그때까지의 변경을 되돌려야 하므로 ROLLING BACK 상태로 오래 남을 수 있다. 큰 배치를 끊기 전에 information_schema.innodb_trx의 trx_rows_modified로 되돌릴 양을 본다.
• 필요한 권한(privilege). 남의 스레드를 끊으려면 CONNECTION_ADMIN 권한이 필요하다. 예전의 SUPER 권한도 되지만 폐기 예정(deprecated)이다.

ALTER 진행률 보기

테이블을 복사하지 않고 제자리에서 바꾸는 INPLACE ALTER도 인덱스를 만들거나 테이블을 다시 쓰면 오래 걸린다. 얼마나 남았는지는 Performance 스키마(Performance Schema)의 단계 이벤트(stage event)로 본다. Performance 스키마는 서버 내부 동작을 계측해 테이블로 보여 주는 MySQL의 기능이고, 단계 이벤트는 ALTER처럼 긴 문장을 행 읽기·정렬·인덱스에 넣기 같은 단계로 나눠 지금 어느 단계를 얼마나 했는지 기록한 것이다. 매뉴얼 절차대로 두 가지를 켠 뒤 조회한다. 첫 UPDATE는 ALTER 단계를 재는 인스트루먼트를 켜고, 둘째 UPDATE는 잰 값을 단계 이벤트 테이블에 저장하는 컨슈머(consumer)를 켠다.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%stages%';

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
FROM performance_schema.events_stages_current;

WORK_COMPLETED/WORK_ESTIMATED가 대략의 진행률이다. 이 값은 행 수가 아니라 작업량을 어림한 단위이고, 단계가 바뀌면서 추정치도 바뀐다. 정확한 남은 시간이 아니라 "멈췄는지, 나아가는지"를 보는 용도로 쓴다.

면접 포인트

• "누가 막는지 한 번에 보려면?" → 행 잠금은 sys.innodb_lock_waits, 메타데이터 락은 sys.schema_table_lock_waits. 생성된 KILL을 돌리기 전에 blocking_pid가 ALTER 자신인지 본다.
• "KILL QUERY와 KILL의 차이?" → 문장만 멈춤 / 접속까지 끊음. 트랜잭션을 연 채 쉬는 세션의 잠금은 접속을 끊어야 풀린다.
• "KILL했는데 안 끝나요" → 큰 트랜잭션을 롤백하는 중(ROLLING BACK). 되돌릴 양만큼 걸린다.
• "ALTER가 얼마나 남았나?" → events_stages_current의 WORK_COMPLETED/WORK_ESTIMATED. 어림값이라 멈췄는지 나아가는지를 보는 용도.