커밋하지 않은 SELECT 하나가 전 API를 멈춘 이유. MySQL 메타데이터 락

운영 데이터를 보정하다 트랜잭션을 24분간 열어뒀는데, 앞의 15분은 아무 일도 없었습니다. 서비스가 멈춘 건 그 사이 배포가 던진 ALTER TABLE 한 줄 때문이었고, 정작 InnoDB 행 락 지표는 끝까지 0이었습니다.

TL;DR

운영 데이터를 손보다가 COMMIT을 빼먹었다. 트랜잭션은 24분간 열려 있었지만, 앞의 15분 동안은 커넥션 풀 대기도 CPU도 처리량도 전부 평시였다.

서비스가 멈춘 건 그 사이 진행되던 배포에서 새로 뜬 인스턴스가 Hibernate 자동 DDL로 ALTER TABLE을 실행한 순간이다. 이 DDL이 요청한 배타 락이 대기 상태로 걸리자 뒤이어 들어온 조회가 전부 그 뒤에 줄을 섰고, 커넥션 21개가 모두 점유되면서 약 9분간 전 API가 응답하지 못했다.

정작 InnoDB 지표는 끝까지 조용했다. 행 잠금 대기도 데드락도 0이었는데, 락이 걸린 층이 흔히 말하는 그 락과 달랐기 때문이다.

1. 커밋을 빼먹은 SELECT 하나

승인 건 하나를 완료 처리해달라는 운영 요청을 받았다. 데이터를 한 행 넣고, 제대로 들어갔는지 눈으로 확인한 다음 커밋하는 흔한 모양의 쿼리다.

START TRANSACTION;

INSERT INTO approval_result (group_id, approver_id, completed)
SELECT ...;

-- 검증 A: 방금 넣은 행이 맞는지
SELECT id, approver_id, completed
FROM approval_result
WHERE group_id = ? AND approver_id = ?;

-- 검증 B: 아직 완료되지 않은 승인자가 누구인지
SELECT DISTINCT a.approver_id, u.name, u.email
FROM approver a
JOIN review r  ON r.id = a.review_id
JOIN users  u  ON u.id = a.approver_id
LEFT JOIN approval_result ar ON ...
WHERE r.group_id = ?;

COMMIT;

INSERT는 36ms 만에 끝났고 결과도 예상대로 1행이었다. 그리고 마지막 COMMIT을 치지 않은 채 창을 그대로 뒀다.

2. 15분 동안은 아무 일도 없었다

커밋 안 된 트랜잭션을 방치했으니 곧바로 뭔가 밀리기 시작했을 것 같은데, 커넥션 풀 대기 수치는 그 뒤로 15분 내내 정확히 0이었다. CPU는 1%대였고 초당 처리량도 평소와 같아서, 그 시점에 지표만 봤다면 아무 문제가 없다고 판단했을 것이다.

그러다 갑자기 알림이 쏟아졌다.

HikariPool-1 - Connection is not available, request timed out after 30000ms
(total=21, active=21, idle=0, waiting=83)

커넥션 21개가 전부 잡혀 있고 83개 요청이 줄을 서 있다는 뜻이다. 30초를 기다린 요청부터 차례로 죽어 나갔는데, 특정 엔드포인트가 아니라 전부였다.

그 사이에 무엇이 있었냐면, 정기 배포가 돌고 있었다. 새로 올라온 인스턴스가 부팅하면서 Hibernate가 엔티티와 스키마를 맞추려고 DDL을 한 줄 실행했다.

ALTER TABLE approval_weight
  ADD CONSTRAINT fk_approval_weight_user_id
  FOREIGN KEY (user_id) REFERENCES users (id);

새로 생긴 테이블에 외래 키를 하나 거는, 겉보기에는 조용한 작업이다. 대상 테이블도 방금 만들어져서 쓰는 사람이 없었다.

3. 잠긴 행이 하나도 없었다

DB부터 열어보니 대기 세션이 338개였다. 상태는 전부 같았다.

STATE: Waiting for table metadata lock
INFO : select ... from `users` where ...

여기까지는 “아, 락 경합이구나” 싶었는데 InnoDB 지표를 보고 멈칫했다. 행 잠금 대기는 평시와 같았고 데드락은 0이었다. 대기 중인 세션들을 하나씩 까 보니 더 이상했다.

trx_rows_locked:   0
trx_rows_modified: 0

락 경합이라면 어딘가에는 잠긴 행이 있어야 하는데, 338개 세션이 행을 하나도 잠그지 못한 채 멈춰 있었다. 평소에 데드락이나 락 타임아웃을 이야기할 때 쓰는 그 락에 걸린 게 아니라는 뜻이다.

4. MySQL의 락은 두 층으로 나뉜다

MySQL은 SQL을 처리하는 서버 레이어와 실제 데이터를 다루는 스토리지 엔진(InnoDB)이 분리되어 있다. 락도 이 경계를 따라 두 종류로 나뉜다.

MySQL의 락은 두 층으로 나뉜다

아래층의 InnoDB 락은 익숙한 쪽이다. 레코드 락, 갭 락, 넥스트 키 락처럼 어떤 행을 남이 못 바꾸게 잠그는 것들이고, 데드락이 나면 여기서 난다. InnoDB 락 모드를 정리한 글에서 다룬 내용이 전부 이 층이다.

위층에 있는 게 메타데이터 락(Metadata Lock, 줄여서 MDL)이다. 이건 데이터가 아니라 테이블의 정의를 지킨다. 내가 SELECT * FROM users를 실행하는 도중에 누군가 users의 컬럼을 지워버리면 실행 계획도 결과 형태도 전부 무너진다. 그래서 MySQL은 테이블을 여는 순간 “이 트랜잭션이 끝날 때까지 이 테이블의 모양은 바뀌지 않는다”는 보장을 잡아둔다.

잡히는 순서는 위에서 아래다. MDL은 테이블을 열기 직전에 잡히고 InnoDB 락은 그다음이라, 위층에서 막히면 아래층은 실행되지도 않는다. trx_rows_locked가 0이었던 게 이 상태다. 쿼리는 DB까지 분명히 도착했는데 테이블을 열지 못해서, InnoDB에 “이 행 잠가줘”라는 요청 자체를 보내지 못한 것이다.

5. 공유 락은 아무도 막지 않는다

MDL에도 종류가 있다. 실무에서 만나는 건 사실상 세 개다.

모드	언제 잡히나
`SHARED_READ` (SR)	평범한 `SELECT`
`SHARED_WRITE` (SW)	`INSERT` · `UPDATE` · `DELETE`, 그리고 `SELECT ... FOR UPDATE`
`EXCLUSIVE` (X)	`ALTER` · `DROP` · `TRUNCATE` 같은 DDL

SR과 SW는 서로 호환된다. 읽는 쪽과 쓰는 쪽이 스키마를 바꾸지는 않으니 굳이 서로를 막을 이유가 없다. 그래서 평상시 트래픽은 MDL 층에서 절대 부딪히지 않고, 덕분에 대부분의 백엔드 개발자는 MDL이라는 게 있는 줄도 모르고 몇 년을 보낸다.

내 트랜잭션이 잡고 있던 것도 SR이었다. 검증 B 쿼리가 users를 조인하면서 users에 SR을 걸었고, 커밋을 안 했으니 계속 쥐고 있었다. 그런데 SR은 다른 SELECT도 INSERT도 막지 않는다. 15분 동안 서비스가 멀쩡했던 이유가 이거다.

그림 위쪽이 그 15분이고, 아래쪽이 DDL이 끼어든 뒤다.

평소와 DDL이 끼어든 뒤의 차이

DDL은 X를 요청하는데, X는 SR과 호환되지 않는다. 그래서 내 트랜잭션이 커밋될 때까지 기다리는 상태로 들어간다. 여기까지는 “ALTER 하나가 느려지는” 정도의 이야기다.

그런데 MySQL은 X가 대기열에 들어가는 순간, 뒤이어 도착한 SR과 SW도 함께 세운다. 이미 허용된 SR과 호환되는데도 그렇다. 읽기 요청이 끊임없이 들어오는 테이블에서 그걸 다 통과시키면 DDL은 영원히 자기 차례를 못 받기 때문이고, 공식 문서도 이 동작을 명시한다.

온라인 DDL이 요청한 대기 상태의 exclusive 메타데이터 락은 해당 테이블에 대한 이후 트랜잭션들을 차단한다. — MySQL 8.0 Reference Manual, Online DDL Performance and Concurrency

users는 인증과 권한 조회 때문에 거의 모든 API가 거쳐 가는 테이블이었다. 그 한 장의 대기표가 서비스 전체를 세운 셈이다.

6. 왜 신규 테이블의 ALTER가 users를 잠갔을까

ALTER의 대상은 approval_weight였고 그 테이블은 방금 만들어져서 아무도 안 쓰고 있었다. 그런데 왜 users가 잠겼을까.

외래 키를 만들면 부모 테이블의 메타데이터도 함께 바뀌기 때문이다. users 입장에서는 “나를 참조하는 자식 테이블이 하나 늘었다”를 기록해야 한다. 그래서 DDL은 자식 테이블뿐 아니라 부모 테이블에도 락을 잡고, 그 부모가 하필 서비스 전체가 읽는 테이블이었다.

메타데이터 락은 필요에 따라 외래 키 제약으로 연결된 테이블들로 확장되어, 관련 테이블에서 충돌하는 DML과 DDL 작업이 동시에 실행되는 것을 방지한다. — MySQL Reference Manual, Metadata Locking

실제로 락 목록을 뽑아보니 approval_weight에는 X가 곧바로 허용되어 있었고, users에서만 대기가 걸려 있었다.

[내 세션]
  users            SHARED_READ         GRANTED    <- 커밋 안 한 검증 SELECT

[ALTER 세션]
  approval_weight  EXCLUSIVE           GRANTED    <- 쓰는 사람이 없어 바로 허용
  users            SHARED_READ_ONLY    GRANTED
  users            SHARED_UPGRADABLE   GRANTED
  users            EXCLUSIVE           PENDING    <- 여기서 막힘

[나머지 338개 세션]
  users            SHARED_READ         PENDING    <- 대기표 뒤에 줄

DDL이 처음부터 X를 잡지 않는다는 것도 이 목록에서 드러난다. 먼저 승격 가능한 공유 락(SHARED_UPGRADABLE)을 받아두고, 인덱스를 만들거나 데이터를 검증하는 긴 작업을 하는 동안에는 일반 트래픽을 통과시킨다. 온라인 DDL이 “온라인”인 이유가 이 구간이다. 그리고 마지막에 테이블 정의를 실제로 교체할 때만 X로 승격을 시도한다. 스냅샷에 SHARED_UPGRADABLE과 EXCLUSIVE가 나란히 찍혀 있던 건 그 승격이 진행 중이던 순간을 잡은 것이다.

그래서 핫 테이블을 부모로 삼는 외래 키를 새로 추가하는 작업은, 규모가 아무리 작아도 그 핫 테이블에 직접 DDL을 치는 것과 같은 위험도를 가진다.

7. 대기는 두 겹이었다

로그를 다시 읽어보면 대기 수치가 두 개 나온다. 하나는 waiting=83이고 다른 하나는 MDL 대기 338이다. 둘은 다른 곳에서 세고 있던 숫자다.

앱과 DB에서 각각 벌어진 대기

waiting=83은 앱 JVM 안에서 커넥션을 빌리지 못한 스레드 수다. 338은 이미 커넥션을 빌려서 DB까지 간 세션 수고. 인과는 아래에서 위로 올라간다. DB에서 응답이 안 오니 커넥션 21개가 계속 점유되고, 그러니 새 요청이 앱 안에서 커넥션을 못 빌려 줄을 선다.

처음에는 커넥션 풀 크기를 의심하기 쉬운데, 풀을 키웠으면 DB에 줄 선 세션만 더 늘었을 것이다. 막힌 지점이 앱이 아니라 DB 안이었기 때문이다.

8. 안전장치가 한쪽만 없었다

가장 허탈했던 부분이다. 두 락 층은 각자 타임아웃을 갖는데, 값이 이렇게 다르다.

	변수	기본값
InnoDB 행 락	`innodb_lock_wait_timeout`	50초
메타데이터 락	`lock_wait_timeout`	31,536,000초 (1년)

MySQL 기본값 그대로다. 행 락은 50초 안에 실패해서 알아서 풀리는데, MDL은 사실상 무한정 기다린다. 그래서 ALTER가 스스로 물러나지 못한 채 428초를 버티며 뒤를 전부 막고 있었다.

lock_wait_timeout을 짧게 걸었다면 어떻게 됐을까. 먼저 대기를 시작한 쪽이 ALTER이므로 ALTER가 항상 먼저 타임아웃한다. 그게 대기열에서 빠지는 순간 뒤에 줄 서 있던 조회는 곧바로 통과한다. 10분짜리 전면 장애가 몇 초짜리 지연과 실패한 DDL 한 건으로 끝났을 상황이다.

그럼 일반 쿼리까지 5초 만에 죽는 건 아닐까. 설정만 보면 그렇다. 문서는 이 값이 “메타데이터 락을 사용하는 모든 문장”에 적용된다고 못 박고 있고, 여기엔 SELECT도 INSERT도 포함된다.

그런데 실제로 문제가 되지 않는 이유는 평상시에 MDL 대기라는 게 아예 생기지 않기 때문이다. SR과 SW는 서로 호환이라 일반 트래픽끼리는 부딪히지 않는다. 쿼리가 MDL을 5초씩 기다리는 상황은 이미 DDL이 끼어든 뒤뿐이고, 그때도 먼저 줄을 선 DDL이 먼저 죽기 때문에 뒤에 있던 조회는 대개 5초를 채우기 전에 풀린다.

9. 무엇을 바꾸기로 했나

타임아웃부터 손봤다. 커넥션이 만들어질 때 세션 값을 짧게 잡아두면, 같은 일이 또 생겨도 DDL만 실패하고 서비스는 유지된다.

spring.datasource.hikari:
  connection-init-sql: "SET SESSION lock_wait_timeout = 5"

이 설정에는 대가가 있다. 앞에 세션을 방치했거나 오래 걸리는 쿼리가 있으면 의도한 스키마 마이그레이션의 DDL도 똑같이 5초 만에 실패한다. 다만 마이그레이션 러너가 실패를 알리고 성공한 작업만 기록해 다음 기동에 다시 시도하는 구조라, 스키마가 조용히 누락되지는 않는다.

설정 쪽에서 더 근본적인 건 운영 환경에서 Hibernate가 DDL을 자동으로 실행하고 있다는 사실이다. generate-ddl: true만 있고 ddl-auto 지정이 없으면 Hibernate는 update 모드로 동작하고, 엔티티가 하나 추가될 때마다 배포할 때 운영 DB에 DDL이 나간다. 이번 ALTER도 그렇게 나갔다. 그렇다고 바로 끌 수는 없는데, 자동 반영에 기대어 쌓인 스키마 차이가 있으면 애플리케이션이 아예 뜨지 않기 때문이다. 개발 환경에서 validate로 차이를 전부 확인하고 마이그레이션으로 정리한 다음에야 끌 수 있다.

쿼리를 쓰는 습관도 바꿨는데, “검증을 트랜잭션 밖으로 빼라”는 아니다. 결과가 이상하면 그 자리에서 ROLLBACK으로 되돌릴 수 있다는 게 이 패턴의 존재 이유고, 커밋한 뒤에 확인하면 되돌리는 데 또 수동 DML이 필요해진다. 운영 데이터를 손보는 자리에서 그건 더 위험하다.

문제는 검증을 트랜잭션 안에서 한 게 아니라 그 쿼리가 무엇을 조인했느냐였다. users를 붙인 건 순전히 이름과 이메일을 사람이 읽기 좋게 보려던 것이고, “누가 아직 완료되지 않았나”라는 판정은 approver_id만으로 성립한다. 그래서 트랜잭션 안의 검증은 판정에 꼭 필요한 테이블만 건드리고, 사람이 눈으로 뜯어볼 조회는 커밋 뒤로 빼기로 했다. 이 패턴에서는 사람이 결과를 보고 판단하는 시간만큼 락을 쥐게 되니, 그 구간에 뜨거운 테이블이 끼어 있으면 안 된다.

탐지도 손봐야 한다. 트랜잭션이 열린 시각과 첫 알림 사이에 15분이 비어 있었는데, 그동안 아무 신호도 없었고 알림이 울렸을 때는 이미 전면 장애였다. 60초 넘게 열려 있는 트랜잭션이나 MDL 대기 세션 수를 보는 알림이 하나만 있었어도 배포가 시작되기 전에 잡혔을 것이다. 반대로 InnoDB 행 락 기준 알림은 이 사고를 영원히 못 잡는다. 그쪽 지표는 끝까지 0이었으니까.

정리

MySQL의 락은 데이터를 지키는 InnoDB 락과 스키마를 지키는 메타데이터 락으로 나뉘고, 둘은 층도 타임아웃도 감시 지표도 따로 논다. 행 락은 코드를 짤 때 걱정하고 메타데이터 락은 배포할 때 걱정하면 된다.

다만 이 사고를 “커밋을 빼먹어서 났다”로 정리하면 재발을 못 막는다. MDL을 오래 쥔 세션이 하나만 있으면 되고, 그건 느린 쿼리로도 긴 배치 트랜잭션으로도 생긴다. 사람이 한 일은 홀더를 하나 공급한 것뿐이고, 실제로 막아야 할 건 매 배포마다 DDL이 나가는 구조와 그 DDL이 무한정 기다릴 수 있다는 설정이다.