DB 락 대기 시간 초과(Lock Timeout)와 트랜잭션 중단

증상 진단: 데이터베이스가 응답하지 않습니다

애플리케이션 로그에 “Lock wait timeout exceeded; try restarting transaction” 또는 유사한 에러 메시지가 빈번하게 기록되고 있나요? 사용자는 화면에서 ‘처리 중’ 아이콘만 계속 보다가 결국 “요청 시간 초과” 오류를 마주하게 됩니다. 간헐적으로 특정 기능만 느려지다가, 심각한 경우 전체 데이터베이스 연결이 차단되어 서비스 장애로 이어집니다, 이는 데이터베이스 락(lock) 경합이 해결되지 않고 지정된 대기 시간을 초과했을 때 발생하는 전형적인 증상입니다.

원인 분석: 누가, 무엇을 잠그고 있는가

락 타임아웃의 근본 원인은 하나의 트랜잭션이 특정 데이터(행 또는 테이블)에 대한 배타적 잠금을 장시간 보유한 상태에서, 다른 트랜잭션이 동일한 데이터에 대한 잠금을 요청할 때 발생합니다. 후자의 트랜잭션은 전자의 트랜잭션이 잠금을 해제하기를 기다리게 되는데, 데이터베이스 시스템은 무한정 기다리지 않도록 innodb_lock_wait_timeout(MySQL InnoDB 기준) 같은 매개변수로 대기 시간을 제한합니다. 이 시간을 초과하면 후자의 트랜잭션은 강제 중단되고 에러가 발생합니다. 주요 원인은 장시간 실행되는 트랜잭션(예: 대량 데이터 업데이트), 잘못 설계된 트랜잭션 범위, 적절하지 않은 인덱스로 인한 풀 테이블 스캔 시 테이블 락 발생, 그리고 데드락(Deadlock) 상황입니다.

백업의 중요성: 데이터베이스 파라미터 변경, 특히 글로벌 변수 수정 전에는 반드시 현재 설정값을 문서화하여 백업하십시오. 프로덕션 환경에서는 변경 관리 절차를 따르고, 가능하면 스테이징 환경에서 먼저 테스트하는 것이 시스템 안정성을 보장하는 필수 절차입니다.

해결 방법 1: 즉각적인 현황 파악 및 응급 조치

서비스 장애가 진행 중이라면, 먼저 어떤 세션이 문제를 일으키는지 확인하고 최악의 경우 해당 세션을 종료해야 합니다. 이 단계는 시스템을 정상 상태로 빠르게 복귀시키는 ‘소방 활동’에 해당합니다.

현재 대기 중인 락과 보유 중인 락 조회

MySQL InnoDB 엔진을 기준으로, 락 정보를 확인하는 가장 효과적인 방법은 성능 스키마와 정보 스키마를 활용하는 것입니다.

1. 먼저, 어떤 트랜잭션이 락을 기다리고 있는지 확인합니다.
   SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT';
   이 쿼리는 대기 상태인 트랜잭션의 ID(trx_id), 시작 시간, 대기 중인 락의 ID(trx_requested_lock_id)를 보여줍니다.

2. 더 상세한 락 대기 관계를 확인하려면 다음 쿼리를 실행합니다.
   SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;
   이 결과는 ‘누가'(blocking_thread) ‘무엇을'(blocking_query) 실행 중이어서 ‘누구를'(waiting_thread) 막고 있는지 명확히 보여줍니다.

문제가 되는 세션 강제 종료

락을 보유 중인 트랜잭션의 쿼리가 비정상적이거나, 즉시 종료해도 무방한 작업(예: 개발자의 실수로 실행한 장시간 쿼리)이라면 해당 데이터베이스 연결 세션을 강제로 종료할 수 있습니다.

1. 위 쿼리에서 확인한 blocking_thread 값을 확인합니다. 이는 PROCESSLIST의 Id와 동일합니다.

2. 해당 스레드(세션)을 종료합니다.
   KILL [blocking_thread 값];
   예: KILL 42;
   이 명령어는 해당 연결에서 실행 중인 트랜잭션을 롤백(Rollback)하고 세션을 종료시킵니다. 대량 작업 중이었다면 롤백에도 시간이 소요될 수 있음을 유의하십시오.

해결 방법 2: 설정 최적화 및 모니터링 체계 구축

응급 조치만으로는 근본 문제가 해결되지 않습니다. 시스템이 지속적으로 안정적으로 운영되도록 설정을 조정하고 모니터링 툴을 구성해야 합니다.

락 타임아웃 시간 조정

기본 락 대기 시간(일반적으로 50초)이 애플리케이션의 특정 작업에는 너무 짧을 수 있습니다. 하지만 이 값을 무작정 늘리는 것은 데드락 상태의 트랜잭션이 더 오래 대기하게 만들어 전체 성능에 악영향을 줄 수 있으므로 신중해야 합니다.

1. 현재 설정값 확인:
   SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

2. 세션 단위로 조정(현재 연결에만 적용):
   SET SESSION innodb_lock_wait_timeout = 120; (값: 초 단위)

3. 글로벌로 조정(재시작 후에도 유지, 서버 재시작 필요):
   SET GLOBAL innodb_lock_wait_timeout = 120;
   영구적으로 적용하려면 MySQL 설정 파일(my.cnf 또는 my.ini)의 [mysqld] 섹션에 innodb_lock_wait_timeout=120을 추가하고 서비스를 재시작해야 합니다.

장시간 실행 트랜잭션 모니터링

정기적으로 장시간 실행 중인 트랜잭션을 찾아 사전에 예방하는 것이 최선입니다.

1. 실행 시간이 긴 트랜잭션을 찾는 쿼리를 주기적으로 실행하거나 모니터링 도구에 등록합니다.
   SELECT trx_id, trx_started, TIMEDIFF(NOW(), trx_started) AS running_time, trx_mysql_thread_id, trx_query FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING' ORDER BY trx_started ASC LIMIT 10;

2. 트랜잭션 격리 수준(Isolation Level) 검토: 기본값인 REPEATABLE READ는 락을 상대적으로 더 많이 보유할 수 있습니다. 읽기 일관성 요구사항이 낮은 작업에 대해 READ COMMITTED 수준을 사용하면 락 경합을 줄일 수 있습니다. 애플리케이션 코드에서 트랜잭션 시작 시 설정할 수 있습니다.
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

해결 방법 3: 애플리케이션 및 쿼리 구조 최적화

가장 근본적인 해결책은 락이 덜 발생하도록 애플리케이션 로직과 데이터 접근 방식을 변경하는 것입니다. 이는 개발팀과의 협업이 필수적입니다.

트랜잭션 범위 최소화

트랜잭션은 반드시 필요한 데이터베이스 작업만 포함해야 합니다. 네트워크 호출, 파일 I/O, 사용자 입력 대기와 같은 외부 연산은 트랜잭션 밖에서 처리하도록 로직을 재구성합니다. 트랜잭션 내에서 실행되는 쿼리의 수와 영향을 받는 행의 수를 최소화하는 것이 핵심 원칙입니다.

인덱스 전략 재검토 및 쿼리 튜닝

인덱스가 없거나 비효율적인 인덱스로 인해 UPDATE나 DELETE 문이 풀 테이블 스캔을 하게 되면, 조건에 맞는 행만 잠그는 것이 아니라 전체 테이블 또는 많은 페이지에 락이 걸릴 수 있습니다.

1. 문제가 되는 쿼리의 실행 계획(EXPLAIN)을 분석합니다.
   EXPLAIN SELECT * FROM your_table WHERE your_column = 'value';
type 컬럼이 ALL(풀 테이블 스캔)인지 확인합니다.

2. WHERE 절에 자주 사용되고 선택도가 높은 컬럼에 적절한 인덱스를 생성합니다. 인덱스 생성은 쓰기 성능에 영향을 미칠 수 있으므로 신중히 판단해야 합니다.

3. 쿼리를 작은 단위로 나누는 배치 처리(Batch Processing)를 고려합니다. 한 번에 100만 행을 업데이트하는 대신, 1만 행씩 100번 나누어 실행하면 각 배치 처리 시점의 락 보유 시간이 크게 줄어듭니다.

낙관적 락 활용 검토

동시 수정 충돌이 빈번하지 않은 경우, 낙관적 락(Optimistic Lock) 패턴을 적용할 수 있습니다. 이는 데이터베이스 수준의 배타적 락을 사용하지 않고, 애플리케이션에서 버전 번호나 타임스탬프 컬럼을 관리하여 데이터의 무결성을 보장하는 방법입니다, 업데이트 시 where 절에 현재 버전을 명시하고, 업데이트 후 버전을 증가시킵니다. 이때 영향을 받은 행 수가 0이면 다른 누군가가 먼저 데이터를 변경한 것이므로, 애플리케이션에서 적절히 재시도하거나 사용자에게 알립니다.

주의사항 및 예방 정책

락 타임아웃은 단순한 설정 문제가 아닌 시스템 아키텍처와 개발 패러다임의 문제를 드러내는 지표입니다. 지속적인 관찰과 개선이 필요합니다.

• 데드락 모니터링 활성화: InnoDB는 데드락을 자동으로 감지하여 한 트랜잭션을 롤백시킵니다. SHOW ENGINE INNODB STATUS 명령어의 LATEST DETECTED DEADLOCK 섹션을 주기적으로 확인하여 데드락의 원인이 되는 쿼리 패턴을 파악하고 수정해야 합니다.

• 연결 풀 설정 검토: 과도한 데이터베이스 연결 수는 동시에 실행 가능한 트랜잭션 수를 늘려 락 경합 가능성을 높입니다, 애플리케이션의 실제 동시 요청 수에 맞춰 연결 풀의 최대 크기를 합리적으로 설정하십시오.

• 비즈니스 로직 분리: 보고서 생성이나 대량 데이터 마이그레이션 같은 장시간 읽기/쓰기 작업은 가능한 경우 프로덕션 온라인 데이터베이스가 아닌, 별도의 리플리카(복제본) 서버에서 수행하도록 아키텍처를 설계합니다.

전문가 팁: “락 타임아웃 에러 로그가 발생하면, 단순히 타임아웃 시간만 늘리지 마십시오. 반드시 information_schema.INNODB_LOCK_WAITS 뷰를 활용해 ‘블로킹 체인(Blocking Chain)’의 정점을 찾아야 합니다. 하나의 느린 쿼리가 수십 개의 정상적인 트랜잭션을 대기 상태로 만들 수 있습니다. 문제의 근원이 되는 그 하나의 쿼리를 최적화하는 것이 시스템 전체의 락 경합을 90% 이상 해결하는 열쇠입니다.”