데이터베이스 락 해결 방법: 원인 분석부터 안전한 해제 및 재발 방지까지
데이터베이스 락 해결 방법: 원인 분석부터 안전한 해제 및 재발 방지까지
운영 중인 시스템에서 갑자기 데이터베이스 락이 걸려 모든 쿼리가 멈춰버리는 경험, 한 번쯤은 겪어보셨을 것입니다. 특히 야근을 하며 “도대체 왜 이 쿼리는 안 끝나는 거지?”라고 의심하다가 결국 락임을 확인하고 나면 막막해지기 마련입니다. 트랜잭션이 길어지고, 데드락이 발생하고, 대기 시간이 길어지면서 서비스 전체가 마비되는 상황은 모든 데이터베이스 운영자에게 가장 피하고 싶은 장애 중 하나입니다. 오늘은 제가 직접 겪은 데이터베이스 락의 원인 분석부터 실제 해제까지의 전 과정을 단계별로 공유합니다. 이 글을 통해 단순히 락을 푸는 임시방편이 아닌, 재발 방지까지 고려한 실질적인 해결책을 얻어가시길 바랍니다.
📌 핵심 요약 (3줄 정리)
- DB 락의 주범은 대부분 인덱스 미사용, 트랜잭션 내 불필요한 지연(SLEEP), 그리고 잘못된 락 범위 설정입니다.
- 락 해제의 핵심은
information_schema에서 대기 중인 프로세스 확인 후, 해당 세션을 안전하게 KILL 하거나 트랜잭션을 롤백하는 것입니다. - 예방이 최선이며, 모든 UPDATE/DELETE 쿼리는 반드시 실행 계획(EXPLAIN)을 확인하고, 트랜잭션은 짧고 굵게 가져가야 합니다.
1. 왜 DB 락이 발생하는가? (근본 원인 3가지)
대부분의 DB 락 문제는 단순히 동시성이 높아서가 아닙니다. 사고가 발생했던 시스템의 로그를 분석해보면 공통점이 발견됩니다. 첫 번째는 인덱스가 없는 컬럼을 기준으로 UPDATE를 수행한 경우입니다. 테이블 풀 스캔이 발생하면서 수많은 레코드에 락이 걸리고, 다른 트랜잭션은 모든 데이터가 풀릴 때까지 무한정 대기해야 합니다. 예를 들어, 100만 건의 테이블에서 인덱스가 없는 status 컬럼을 WHERE로 UPDATE하면 전체 행에 락이 걸릴 수 있습니다. 두 번째는 트랜잭션 내부에서 불필요한 외부 API 호출이나 SLEEP() 함수를 사용한 경우입니다. 데이터베이스 커넥션을 오랫동안 점유하면서 락을 해제하지 않아 대기 현상이 발생합니다. 실제로 트랜잭션 안에서 HTTP 호출로 결제 정보를 조회하다가 타임아웃이 나서 락이 30초 이상 유지된 사례도 있었습니다. 마지막으로 프로그래밍 레벨에서 락을 획득하는 순서를 보장하지 않은 경우입니다. 여러 테이블을 접근할 때 각기 다른 순서로 락을 획득하면 데드락이 발생할 확률이 비약적으로 상승합니다. 예를 들어, A 트랜잭션은 user → order 순서로, B 트랜잭션은 order → user 순서로 접근하면 서로가 가진 락을 기다리며 무한 대기에 빠질 수 있습니다.
2. DB 락 실시간 탐지: 누가 범인인지 찾아내는 법
장애가 발생하면 가장 먼저 현재 어떤 세션이 락을 점유 중인지, 누가 락을 기다리고 있는지 파악해야 합니다. MySQL 기준으로 아래 쿼리를 실행하면 즉시 범인을 특정할 수 있습니다. 이 과정에서 중요한 것은 운영 환경에서의 부하를 최소화하기 위해 가능하면 읽기 전용 인스턴스가 아닌 실제 세션 정보가 보이는 master DB에서 조회해야 한다는 점입니다.
-- 현재 락 대기 상태 및 점유 중인 트랜잭션 확인
SELECT * FROM information_schema.INNODB_TRX;
-- 락이 걸린 테이블과 인덱스 상태 확인
SELECT * FROM sys.innodb_lock_waits;
-- 락 대기 중인 프로세스 ID 목록
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits;
중요한 점은 위 쿼리 결과에서 blocking_pid와 waiting_pid가 동시에 나온다면, 해당 blocking_pid가 진정한 범인입니다. 이 PID의 트랜잭션 시간(trx_started)을 확인하여 오래된 트랜잭션이라면 즉시 종료하는 것이 합리적입니다. 또한 waiting_query가 비어 있다면 해당 세션이 대기 중인 쿼리가 없고 트랜잭션만 유지 중일 수 있으므로, 커넥션 풀의 점유 시간도 함께 확인하세요. 실제 운영 환경에서는 위 쿼리 하나로 문제의 80% 이상을 해결할 수 있습니다.
3. 락 해제 전 과정: 안전하게 죽이는 시나리오
락의 원인을 찾았다면 이제 해제 작업을 진행합니다. 무작정 KILL 명령어를 남발하면 데이터 정합성이 깨질 수 있으므로 아래 절차를 따르는 것을 권장합니다. 특히, 블로킹 세션이 수행 중이던 트랜잭션이 커밋 직전이었다면 부분 롤백이 발생할 수 있기 때문에 반드시 애플리케이션 로그와 함께 검토해야 합니다.
Step 1: 대기 시간 임계값 설정
락 대기 시간이 30초를 넘었다면 이는 심각한 상황입니다. MySQL 설정 파일에서 innodb_lock_wait_timeout 값을 기본 50초에서 5초~10초로 줄여 문제가 발생하면 즉시 타임아웃으로 오류를 반환하게 만듭니다. 이렇게 하면 시스템 전체가 마비되는 최악의 사태를 방지할 수 있습니다. 다만, 이 값을 너무 낮추면 장시간 실행되는 정상적인 보고용 쿼리도 실패할 수 있으니, 서비스 특성에 맞는 값을 선택해야 합니다.
Step 2: 안전한 트랜잭션 롤백 진행
락을 점유 중인 세션이 단순 SELECT가 아니라 UPDATE/DELETE 트랜잭션이라면 ROLLBACK을 수행합니다. 하지만 직접 커넥션을 종료해야 한다면 아래 명령어를 사용합니다. 이때 주의할 점은 KILL만으로는 커넥션이 정리되지 않는 경우가 있으므로, 이후에 SHOW PROCESSLIST로 세션이 완전히 제거되었는지 확인하는 습관이 중요합니다.
-- 블로킹 중인 스레드 ID 확인 후 종료
-- 12345는 확인된 blocking_pid 입니다.
KILL 12345;
-- 만약 KILL이 안된다면 CONNECTION으로 강제 종료
KILL CONNECTION 12345;
KILL 이후에는 즉시 SHOW ENGINE INNODB STATUS\G 명령어를 실행하여 데드락이 사라졌는지, 그리고 대기 세션이 모두 정상 처리되고 있는지 확인합니다. 특히, 위 결과에서 LATEST DETECTED DEADLOCK 섹션을 열어 어떤 쿼리가 서로의 락을 기다렸는지 기록해 두면 추후 재발 방지에 큰 도움이 됩니다. 여기서 중요한 것은 락을 푼 후에도 해당 쿼리가 재실행되면 동일한 문제가 반복된다는 점입니다.
4. 유형별 DB 락 비교 분석: 어떤 락이 가장 위험한가?
DB 락의 종류를 명확히 구분하면 대응 전략을 세우기가 훨씬 수월해집니다. 아래 표는 실제 장애 대응 시 가장 많이 마주치는 4가지 락 유형을 비교한 것입니다. 각 락의 특성을 이해하면 단순히 대응하는 것에서 나아가 사전에 위험도를 평가할 수 있습니다.
| 락 유형 | 발생 빈도 | 위험도 | 해결 난이도 | 주요 발생 원인 |
|---|---|---|---|---|
| Shared Lock (S Lock) | 보통 | 낮음 | 쉬움 | 읽기 트랜잭션 중 쓰기 차단 |
| Exclusive Lock (X Lock) | 높음 | 높음 | 중간 | INSERT/UPDATE/DELETE 시 발생 |
| Gap Lock | 보통 | 높음 | 어려움 | 범위 검색 시 인덱스 사이 간격 잠금 |
| Next-Key Lock | 낮음 | 매우 높음 | 매우 어려움 | 레코드 + 간격 동시 잠금 (데드락 주범) |
위 표에서 가장 위험한 것은 Next-Key Lock입니다. 이는 단순한 레코드 락과 달리, 해당 인덱스의 앞뒤 간격까지 잠그기 때문에 다른 트랜잭션이 데이터를 삽입조차 하지 못하게 만듭니다. 특히 REPEATABLE READ 격리 수준을 사용하는 InnoDB 환경에서 자주 발생하며, 만약 이러한 락이 대량 발생한다면 서비스는 사실상 정지 상태가 됩니다. 이런 상황에서는 격리 수준을 READ COMMITTED로 조정하는 것도 하나의 해결책이 될 수 있습니다.
5. 재발 방지 전략: 락 없는 설계로 가는 길
락 해제 후에는 동일한 문제가 발생하지 않도록 코드 레벨에서 근본적인 교정이 필요합니다. 제가 실제로 적용했던 세 가지 핵심 전략은 다음과 같습니다.
첫째, 인덱스 최적화입니다. 모든 UPDATE/DELETE 쿼리는 WHERE 절의 컬럼이 반드시 인덱스를 타는지 EXPLAIN으로 검증했습니다. 특히 복합 인덱스의 경우 컬럼 순서를 데이터 분포도에 맞게 재설계했습니다. 예를 들어, (status, created_at) 인덱스를 (created_at, status)보다 먼저 생성해야 하는 경우가 많았는데, 실제로는 status의 선택도가 높을 때만 유용했습니다. 이 간단한 작업만으로 락이 걸리는 건수가 현저하게 줄었습니다.
둘째, 트랜잭션 내에서 절대 외부 작업을 하지 않는 것입니다. 하나의 트랜잭션 안에서 Java나 Python 코드로 HTTP API를 호출하거나, 파일 I/O를 수행하면 트랜잭션 점유 시간이 길어집니다. 예를 들어, 주문 처리 중 이메일 발송을 트랜잭션 안에서 하면 수 초간 락이 유지될 수 있습니다. 이를 대폭 제거하고, 데이터베이스 커넥션을 최소한으로 유지하도록 코드를 리팩토링했습니다.
셋째, 데드락 감지 및 재시도 로직을 구축했습니다. 트랜잭션 실행 구문에 try-catch를 적용하여 락 타임아웃 오류(MySQL 기준 1213, 1205)가 발생하면 일정 시간(1~2초) 대기 후 자동으로 재시도하도록 구현했습니다. 이는 코드가 조금 복잡해지지만, 예상치 못한 동시성 상황에서 시스템이 죽지 않고 스스로 복구할 수 있는 최소한의 안전장치입니다. 재시도 횟수는 3회 정도로 제한하고, 초과 시 별도 알림을 발생시키는 것이 좋습니다.
6. 요약 및 실행 체크리스트
오늘 정리한 내용을 바탕으로, 다음에 DB 락이 걸리면 당황하지 말고 아래 체크리스트를 순서대로 수행하시기 바랍니다. 이 체크리스트를 팀 내 공유 문서로 만들어 두면 장애 대응이 훨씬 빨라집니다.
- 현황 파악:
information_schema에서 블로킹 PID 즉시 확인. - 타임아웃 조정:
innodb_lock_wait_timeout10초 이하로 하향 조정. - 세션 종료: 블로킹 트랜잭션과 관련된 앱 서버 인스턴스를 먼저 내린 후, 해당 세션 KILL 수행.
- 실행 계획 검토: 문제가 된 쿼리에 대해 인덱스가 미사용된 컬럼 확인.
- 코드 수정: 트랜잭션 범위 축소 및 재시도 로직 적용.
DB 락은 완벽하게 예방하는 것이 사실상 불가능한 영역입니다. 하지만 위 과정을 하나의 플레이북으로 만들어 두면 장애 대응 시간을 30분 이상 단축할 수 있습니다. 더 나아가, 이번 기회에 애플리케이션의 모든 트랜잭션 쿼리를 점검하여 조회 성능 최적화와 동시성 제어 설계를 동시에 잡으시길 권장합니다. 오늘 공유한 내용이 여러분의 소중한 야근 시간을 지키는 데 도움이 되기를 바랍니다.