MySQL 잠금 장애 대응: 15분 롤백과 진단 순서
기존 쿼리가 운영 동시성을 만나 만든 MySQL 잠금 경합을 15분 안에 롤백한 경험과 대기·차단 관계를 찾는 진단 순서를 설명한다.
이 글의 목차느린 쿼리와 잠금 쿼리는 같은 문제가 아니었다10
운영 데이터베이스가 갑자기 멈췄다는 연락을 받았다.
애플리케이션 요청은 쌓였고, 평소라면 금방 끝나던 데이터 변경도 기다리기 시작했다.
최근에 반영된 기존 쿼리와 로직은 개발 환경에서 테스트를 통과한 상태였다.
그런데 운영에서는 같은 변경이 여러 요청과 큰 데이터량을 동시에 만나면서 잠금 경합을 만들었다.
잠금 경합은 여러 트랜잭션이 같은 데이터를 바꾸려 할 때, 먼저 잠금을 잡은 작업 때문에 뒤의 작업들이 줄지어 기다리는 상태다.
트랜잭션은 여러 데이터 변경을 하나의 작업 단위로 묶어 모두 성공시키거나 모두 취소하는 경계다.
당시에는 원인을 완벽하게 설명한 뒤 움직일 시간이 없었다.
나는 장애 시점과 가장 가까운 배포 변경을 먼저 되돌렸고, 데이터베이스가 다시 요청을 처리하는지 확인했다.
업무 기록에는 Major 등급 장애와 0.25시간의 대응시간이 남아 있다.
0.25시간은 15분이다.
다만 초 단위 타임라인과 당시 잠금 화면은 보존되지 않았기 때문에, 이 글에서는 “기록상 15분”이라는 범위를 넘겨 복구 속도를 과장하지 않는다.
느린 쿼리와 잠금 쿼리는 같은 문제가 아니었다
처음 마주한 증상만 보면 쿼리 성능 문제처럼 보인다.
응답이 늦어지고 DB 연결이 쌓이기 때문이다.
하지만 실행 시간이 긴 쿼리와 다른 작업을 막고 있는 쿼리는 구분해야 한다.
오래 걸리더라도 혼자 실행되는 조회는 다른 쓰기를 막지 않을 수 있다.
반대로 짧아 보이는 수정문도 트랜잭션이 끝나지 않으면 잠금을 오래 유지할 수 있다.
이번 기록에서 확인되는 직접 원인은 운영에 반영된 쿼리·로직이 잠금 경합을 만들었다는 점이다.
어떤 테이블의 어떤 SQL이었는지, 인덱스가 부족했는지, 두 트랜잭션이 서로 다른 순서로 행을 잡았는지는 남아 있지 않다.
그래서 특정 원인을 새로 지어내는 대신 당시 판단의 순서를 남기는 편을 택했다.
- 장애가 시작된 시각과 최근 배포 시각을 맞췄다.
- 가장 가까운 변경을 원인 후보로 좁혔다.
- 검증된 이전 버전으로 롤백했다.
- DB가 다시 요청을 처리하는지 확인했다.
- 개발 환경과 운영 환경의 데이터량·동시성 차이를 재발 방지 항목으로 남겼다.
당시 확인된 조치는 롤백까지다
이 구분이 중요한 이유가 있다.
장애 기록에는 “롤백으로 서비스가 복구됐다”는 결과가 남아 있지만, 당시 실행한 잠금 조회 명령과 쿼리 실행계획은 남아 있지 않다.
실행계획은 데이터베이스가 어떤 인덱스와 조인 순서로 쿼리를 처리할지 보여 주는 계획이다.
따라서 아래 진단 절차는 그날 그대로 실행했다고 주장하는 회고가 아니다.
같은 증상을 다시 만났을 때 내가 사용할 현재의 점검 순서다.
확인된 과거와 지금의 권장을 섞지 않으면, 다음 장애에서 그대로 재사용할 수 있는 문서가 된다.
| 구분 | 남아 있는 근거 |
|---|---|
| 당시 관찰 | 운영 배포 뒤 잠금 경합으로 DB가 요청을 처리하지 못함 |
| 당시 조치 | 장애 시점의 변경을 원인 후보로 좁히고 이전 버전으로 롤백 |
| 당시 결과 | Major 장애가 복구됐고 대응시간은 기록상 0.25시간 |
| 확인되지 않은 것 | 실제 SQL, 잠금 대상 행, 실행계획, 잠금 대기 수치 |
| 현재 권장 | 잠금 보유자·대기자 확인, 안전한 실행계획 검증, 작은 트랜잭션 단위 |
롤백·대기·세션 종료는 같은 해결책이 아니다
잠금 장애에서 선택지는 하나가 아니다.
최근 배포를 되돌리는 롤백은 새 요청이 문제 쿼리를 더 만들지 않게 할 수 있지만, 이미 열린 데이터베이스 트랜잭션을 자동으로 끝내지는 않는다.
반대로 차단 세션을 종료하면 현재 잠금은 풀릴 수 있지만, 애플리케이션이 같은 쿼리를 계속 만들면 곧 다시 막힌다.
주문 하나의 상태를 바꾸는 중이었다고 가정하면, 트랜잭션을 무작정 끊는 순간 해당 변경은 롤백될 수 있다.
결제 승인 같은 외부 작업은 이미 끝났는데 DB 기록만 취소되는 상황도 생길 수 있으므로, 세션 종료 전에는 업무 영향과 재처리 방법을 확인해야 한다.
| 관찰한 상황 | 우선 검토할 조치 | 선택 전에 확인할 것 |
|---|---|---|
| 최근 배포 뒤 같은 쿼리가 계속 유입된다 | 배포 롤백 또는 기능 플래그로 새 유입을 막는다 | 이전 버전이 현재 데이터 형식과 호환되는가 |
| 중요한 트랜잭션이 곧 끝날 가능성이 높다 | 새 쓰기를 줄이고 짧게 관찰한다 | 대기열과 사용자 오류가 허용 범위인가 |
| 한 세션이 장시간 다수를 막고 회복 조짐이 없다 | 책임자와 영향 범위를 확인한 뒤 세션 종료를 검토한다 | 외부 부작용, 자동 롤백 시간, 재처리 방법이 있는가 |
| 교착 상태 오류가 반복된다 | 잠금 획득 순서를 통일하고 안전한 작업만 제한 재시도한다 | 요청이 멱등한가, 재시도가 부하를 키우지 않는가 |
이 표는 자동 실행 규칙이 아니라 사고 중 판단을 빠르게 맞추기 위한 기준이다.
내 사례에서는 장애 시점과 가까운 변경이 있었고 검증된 이전 버전으로 되돌릴 수 있었기 때문에, 새 문제 요청을 끊는 배포 롤백을 먼저 택했다.
지금 다시 본다면 잠금 관계부터 찾는다
MySQL의 기본 스토리지 엔진인 InnoDB는 트랜잭션과 행 단위 잠금을 지원한다.
MySQL 8 계열에서는 Performance Schema의 data_locks와 data_lock_waits를 이용해 누가 잠금을 보유하고 누가 기다리는지 볼 수 있다.
Performance Schema는 서버 내부의 실행 상태를 관찰할 수 있게 제공하는 진단 데이터 모음이다.
아래 조회는 이해를 위한 단순화 예시다.
실제 컬럼 구성은 사용하는 MySQL 버전에서 공식 문서를 다시 확인해야 한다.
SELECT
waiting_engine_transaction_id,
blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;data_lock_waits는 데이터를 변경하지 않는 조회용 테이블이다.
waiting_engine_transaction_id는 잠금을 기다리는 트랜잭션을, blocking_engine_transaction_id는 그 앞을 막고 있는 트랜잭션을 가리킨다.
대기자가 많다는 사실만 보고 가장 오래된 연결을 바로 종료하지 않는다.
먼저 차단 트랜잭션이 결제나 정산 같은 중요한 변경을 수행 중인지, 커밋 또는 롤백이 진행 중인지, 새 쓰기를 잠시 줄일 수 있는지 확인한다.
교착 상태가 의심되면 다음 명령도 함께 본다.
SHOW ENGINE INNODB STATUS;SHOW ENGINE INNODB STATUS는 InnoDB의 최근 교착 상태와 트랜잭션 정보를 텍스트로 보여 준다.
교착 상태는 두 트랜잭션이 서로 상대가 가진 잠금을 기다려 어느 쪽도 진행하지 못하는 상태다.
InnoDB는 기본적으로 교착 상태를 감지하면 한 트랜잭션을 희생자로 선택해 롤백한다.
따라서 애플리케이션도 교착 상태 오류가 발생할 수 있음을 받아들이고, 안전하게 다시 실행할 수 있는 작업인지 판단해 제한된 횟수로 재시도해야 한다.
무조건 재시도하면 이미 일부 외부 작업이 끝난 요청을 중복 처리할 수 있다.
실행계획 확인도 안전한 장소가 필요하다
원인 후보 쿼리를 찾은 다음에는 실행계획을 본다.
일반 EXPLAIN은 쿼리를 실제로 실행하지 않고 옵티마이저가 선택할 계획을 보여 준다.
옵티마이저는 여러 실행 방법의 예상 비용을 비교해 처리 순서를 고르는 MySQL 내부 기능이다.
EXPLAIN
SELECT order_id
FROM order_item
WHERE order_status = 'READY';테이블과 컬럼 이름은 공개용 예시다.
EXPLAIN에서는 사용할 수 있는 인덱스와 실제 선택한 인덱스, 예상해서 읽을 행 수를 확인한다.
반면 EXPLAIN ANALYZE는 이름과 달리 계획만 계산하지 않는다.
쿼리를 실제로 실행한 뒤 예상 행 수와 실제 행 수, 걸린 시간을 보여 준다.
읽기 쿼리도 운영 데이터가 크면 부하를 만들 수 있고, 변경 쿼리에 사용하면 데이터까지 바뀔 수 있다.
그래서 운영 장애 중 무심코 실행하지 않고 복제된 데이터나 안전한 읽기 쿼리에서 먼저 사용한다.
MAX_EXECUTION_TIME(5000) 같은 힌트도 범위를 알고 써야 한다.
숫자 5000은 5,000밀리초, 곧 5초를 뜻한다.
이 제한은 읽기 전용 SELECT에 적용되며 UPDATE나 DELETE를 보호하는 만능 타임아웃이 아니다.
SELECT /*+ MAX_EXECUTION_TIME(5000) */
order_id
FROM order_item
WHERE order_status = 'READY';이 코드는 당시 복구에 사용한 SQL이 아니라, 조사 쿼리 자체가 오래 실행되는 일을 줄이기 위한 현재의 예시다.
롤백 버튼을 눌렀다고 바로 끝나지는 않는다
애플리케이션 버전을 이전 상태로 되돌리는 롤백과 DB 트랜잭션을 취소하는 ROLLBACK은 같은 단어를 쓰지만 대상이 다르다.
전자는 코드와 배포 상태를 되돌리고, 후자는 아직 커밋하지 않은 데이터 변경을 취소한다.
MySQL에서 트랜잭션 전체가 롤백되면 그 트랜잭션이 잡고 있던 잠금도 해제된다.
하지만 SQL 문 하나가 오류로 취소됐더라도 바깥 트랜잭션이 계속 열려 있으면 잠금이 남을 수 있다.
대규모 변경을 취소할 때는 원래 변경보다 롤백이 더 오래 걸릴 수도 있다.
그래서 복구 절차를 다음처럼 나눈다.
- 새 쓰기 유입을 줄일 수 있는지 확인한다.
- 잠금 보유자와 대기자, 열린 트랜잭션을 확인한다.
- 배포 변경과 DB 트랜잭션 중 무엇을 되돌릴지 구분한다.
- 롤백 진행 중 DB CPU와 잠금 대기, 오류율을 관찰한다.
- 명령 성공이 아니라 사용자 요청 회복을 복구 완료 기준으로 삼는다.
기록상 당시에는 배포 변경을 신속하게 되돌려 복구했다.
다만 잠금 대기 건수와 쓰기 처리량의 전후 수치가 남아 있지 않으므로, “몇 퍼센트 개선됐다”는 숫자는 만들지 않았다.
운영 규모를 흉내 내지 못한 테스트의 빈틈
개발 환경에서 통과한 쿼리가 운영에서 잠금을 만든 사실은 확인된다.
데이터가 적으면 한 트랜잭션이 잠금을 보유하는 시간이 짧고, 동시에 같은 행을 바꾸는 요청도 적다.
운영에서는 데이터 분포와 요청 동시성이 달라진다.
여러 요청이 같은 인기 상품이나 같은 사용자 상태를 수정하면 개발 환경에서 보이지 않던 대기열이 생길 수 있다.
다음 배포부터는 정상 결과만 확인하는 테스트에 세 가지를 더 붙인다.
- 운영과 비슷한 데이터량과 분포에서 실행 시간을 잰다.
- 여러 요청이 같은 범위를 동시에 수정하는 상황을 재현한다.
- 트랜잭션마다 행을 잡는 순서와 커밋 경계를 비교한다.
큰 변경을 한 트랜잭션으로 묶는 대신 작은 배치로 나누는 것도 검토한다.
배치는 처리 대상을 일정한 개수로 나눠 차례로 실행하는 방식이다.
배치가 작으면 한 번에 잡는 잠금 범위와 실패했을 때 되돌릴 범위를 줄일 수 있다.
다만 중간에 일부만 반영될 수 있으므로 재실행해도 중복되지 않는지, 완료 지점을 어디에 기록할지 함께 설계해야 한다.
다음 장애에서 남길 최소 증거
이번 기록에는 복구 판단은 남았지만 직접적인 잠금 화면은 남지 않았다.
다음에는 민감정보를 가린 뒤 아래 정보를 같은 시간축에 보관하려 한다.
- 장애 시작과 배포 시각
- 잠금 보유 트랜잭션과 대기 트랜잭션의 관계
- 원인 후보 SQL의 지문과 실행계획
- 롤백 시작·완료 시각
- DB 연결 수, 잠금 대기, 애플리케이션 오류율의 회복 시점
- 재발 방지 변경과 검증 결과
SQL 지문은 값 자체를 남기지 않고 쿼리 구조가 같은 요청을 묶어 식별하는 형태다.
고객 데이터와 실제 조건값을 노출하지 않으면서 같은 쿼리가 얼마나 반복됐는지 비교할 수 있다.
결론
이번 장애에서 내가 먼저 한 일은 범인을 찾는 일이 아니었다.
운영에서 이미 발생한 문제와 가장 가까운 변경을 좁히고, 되돌릴 수 있을 때 이전 상태로 복구하는 일이었다.
기록상 롤백에는 15분이 걸렸고 서비스는 다시 요청을 처리했다.
그 뒤에 남은 교훈은 “느린 쿼리를 조심하자”보다 구체적이다.
운영 잠금 장애에서는 실행시간만 보지 말고 누가 누구를 기다리는지 확인하고, 복구와 원인 분석의 순서를 분리해야 한다.
새 컬럼의 인덱스 누락으로 DB가 멈춘 다른 사례는 개인정보 암호화 후 MySQL이 멈췄던 과정에 정리했다.
두 사례 모두 롤백부터 시작했지만, 하나는 조회 경로의 인덱스가 문제였고 이번 사례는 운영 동시성에서 나타난 잠금 경합이 중심이었다.