[태그:] Lock Wait Timeout Exceeded

MySQL에서 UPDATE나 DELETE 쿼리를 실행했는데 응답이 오랫동안 없거나, 일정 시간이 지난 뒤 Lock wait timeout exceeded; try restarting transaction 오류가 발생하는 경우가 있습니다. 특히 주문 정보 수정, 재고 차감, 회원 데이터 업데이트처럼 여러 사용자가 같은 데이터를 변경하는 환경에서 발생하기 쉽습니다.

이 오류가 발생하면 데이터베이스 성능이 부족하거나 SQL 문법에 문제가 있다고 생각하기 쉽습니다. 하지만 대부분은 다른 트랜잭션이 보유한 잠금이 해제되기를 기다리다가 설정된 대기 시간을 초과했기 때문입니다.

예를 들어 첫 번째 트랜잭션이 특정 주문 데이터를 수정한 뒤 COMMIT을 실행하지 않았다면, 다른 트랜잭션이 같은 데이터를 변경하려 할 때 잠금 대기 상태에 들어갈 수 있습니다. 이 상태가 오래 지속되면 MySQL은 오류 코드 1205를 반환합니다.

이때 무조건 MySQL 서비스를 재시작하거나 innodb_lock_wait_timeout 값을 크게 늘리면 문제가 일시적으로 사라져도 실제 잠금 충돌은 계속될 수 있습니다.

이번 글에서는 잠금 대기 확인 → 차단 트랜잭션 식별 → 원인 SQL 분석 → 안전한 트랜잭션 정리 → 재발 방지 순서로 Lock Wait Timeout Exceeded 오류를 해결하는 방법을 살펴봅니다.

Lock Wait Timeout Exceeded 핵심 SQL 한눈에 보기

MySQL에서 잠금 대기 오류가 발생했다면 먼저 현재 트랜잭션과 잠금 대기 관계를 확인해야 합니다. 아래 명령어는 주로 MySQL 8.0 환경을 기준으로 작성했습니다.

가장 중요한 것은 잠금을 기다리는 세션과 실제로 잠금을 보유한 세션을 구분하는 것입니다. 오류가 발생한 쿼리만 확인하면 다른 트랜잭션이 잠금을 장시간 유지하고 있다는 근본 원인을 놓칠 수 있습니다.

1. Lock Wait Timeout Exceeded 오류가 발생하는 이유

MySQL InnoDB 스토리지 엔진은 여러 트랜잭션이 동시에 데이터를 변경할 때 데이터 일관성을 유지하기 위해 잠금 기능을 사용합니다.

이 과정에서 다른 트랜잭션이 보유한 잠금과 충돌하면 해당 잠금이 해제될 때까지 기다리게 됩니다.

대기 시간이 설정된 한도를 초과하면 다음 오류가 발생합니다.

ERROR 1205 (HY000):
Lock wait timeout exceeded; try restarting transaction

대표적인 원인은 다음과 같습니다.

• COMMIT 누락: 데이터 수정 후 트랜잭션을 종료하지 않은 경우
• 장시간 트랜잭션: 하나의 트랜잭션에서 너무 많은 작업을 수행하는 경우
• 동일 행 동시 수정: 여러 세션이 같은 레코드를 UPDATE하는 경우
• 인덱스 부족: 비효율적인 검색으로 더 많은 레코드와 범위에 잠금이 발생하는 경우
• 잠금 범위 확대: 격리 수준과 쿼리 조건에 따라 갭 락 또는 넥스트 키 락이 발생하는 경우
• 애플리케이션 오류: 예외 발생 후 트랜잭션이 정리되지 않는 경우
• DDL과의 충돌: ALTER TABLE 등에서 메타데이터 잠금을 기다리는 경우

단, innodb_lock_wait_timeout은 일반적인 InnoDB 행 잠금 대기에 적용되며, 메타데이터 잠금 대기에는 별도의 lock_wait_timeout 설정이 사용됩니다.

2. 두 트랜잭션이 충돌하는 실제 상황

예를 들어 주문 테이블에서 같은 주문번호를 두 세션이 동시에 수정한다고 가정하겠습니다.

먼저 첫 번째 세션에서 트랜잭션을 시작합니다.

-- 세션 A
START TRANSACTION;

UPDATE orders
SET status = 'processing'
WHERE id = 1001;

여기서 COMMIT을 실행하지 않고 트랜잭션을 유지합니다.

이 상태에서 두 번째 세션이 동일한 행을 수정합니다.

-- 세션 B
START TRANSACTION;

UPDATE orders
SET status = 'completed'
WHERE id = 1001;

세션 B는 세션 A가 보유한 잠금이 해제되기를 기다립니다. 대기 시간이 초과되면 다음과 같은 오류가 발생할 수 있습니다.

ERROR 1205 (HY000):
Lock wait timeout exceeded; try restarting transaction

세션 A에서 COMMIT 또는 ROLLBACK을 실행해 잠금을 해제하면 다른 세션이 해당 행에 접근할 수 있게 됩니다.

중요한 점은 잠금 대기 시간이 초과됐다고 해서 기본 설정에서 트랜잭션 전체가 자동으로 롤백되는 것은 아니라는 사실입니다.

기본적으로 InnoDB는 시간 초과가 발생한 명령문만 롤백합니다. 따라서 세션 B에서 이전에 실행한 변경 작업이 있다면 트랜잭션이 계속 열린 상태로 남을 수 있습니다.

실패한 작업을 처음부터 다시 시도해야 한다면 애플리케이션에서 트랜잭션 전체를 ROLLBACK하고 새 트랜잭션으로 재시도하는 것이 일반적으로 안전합니다.

3. SHOW FULL PROCESSLIST로 실행 중인 세션 확인하기

잠금 대기가 발생했다면 먼저 MySQL에서 어떤 세션이 실행 중인지 확인합니다.

SHOW FULL PROCESSLIST;

출력에는 세션 ID, 사용자, 접속 호스트, 실행 상태와 SQL 정보 등이 표시됩니다.

주요 항목은 다음과 같습니다.

• Id: MySQL 연결 세션 ID
• User: 연결 사용자
• Host: 클라이언트 접속 정보
• Command: Query 또는 Sleep 등 현재 명령 상태
• Time: 현재 상태가 지속된 시간
• State: 서버 내부 처리 상태
• Info: 현재 실행 중인 SQL 문

특히 Sleep 상태인 세션이라도 트랜잭션을 종료하지 않았다면 잠금을 유지할 수 있습니다.

따라서 Sleep 세션을 모두 정상 상태로 판단하거나, 반대로 오래된 Sleep 세션을 무조건 종료하는 것은 적절하지 않습니다.

4. INNODB_TRX로 장시간 열린 트랜잭션 찾기

현재 실행 중인 InnoDB 트랜잭션은 다음 SQL로 조회할 수 있습니다.

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

여기서 trx_started는 트랜잭션 시작 시각, trx_mysql_thread_id는 연결 세션 ID를 나타냅니다.

trx_state가 LOCK WAIT라면 해당 트랜잭션이 잠금을 기다리는 상태일 수 있습니다.

다만 오래된 트랜잭션이라고 해서 반드시 현재 발생한 잠금 충돌의 원인인 것은 아닙니다. 실제 차단 관계는 다음 단계에서 확인해야 합니다.

5. Performance Schema로 잠금 대기 관계 확인하기

MySQL 8.0에서는 performance_schema.data_lock_waits와 data_locks를 사용해 잠금 대기 관계를 확인할 수 있습니다.

다음 SQL은 잠금을 기다리는 트랜잭션과 잠금을 차단하는 트랜잭션을 함께 조회하는 예시입니다.

SELECT
    w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
    w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
    r.OBJECT_SCHEMA,
    r.OBJECT_NAME,
    r.INDEX_NAME,
    r.LOCK_TYPE AS waiting_lock_type,
    r.LOCK_MODE AS waiting_lock_mode,
    b.LOCK_MODE AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON w.REQUESTING_ENGINE_LOCK_ID = r.ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON w.BLOCKING_ENGINE_LOCK_ID = b.ENGINE_LOCK_ID;

이 결과를 통해 어느 테이블과 인덱스에서 잠금 충돌이 발생하고 있는지 확인할 수 있습니다.

다만 이 SQL은 트랜잭션 ID와 잠금 정보를 보여주므로 실제 연결 세션을 종료하려면 세션 ID까지 확인해야 합니다.

MySQL의 sys.innodb_lock_waits 뷰를 사용할 수 있는 환경이라면 더 간단하게 대기 세션과 차단 세션을 조회할 수 있습니다.

SELECT
    waiting_pid,
    blocking_pid,
    locked_table,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits;

여기서 blocking_pid는 차단 세션의 연결 ID입니다. 단, 해당 뷰가 설치되어 있고 필요한 조회 권한이 있어야 합니다.

차단 트랜잭션이 현재 아무 SQL도 실행하지 않는 상태라면 blocking_query가 NULL일 수 있습니다. 이는 해당 트랜잭션이 잠금을 보유하지 않는다는 의미가 아닙니다.

6. 차단 세션을 안전하게 종료하는 방법

잠금 충돌의 원인이 되는 세션을 확인했다면 먼저 애플리케이션에서 해당 트랜잭션을 정상적으로 종료할 수 있는지 확인해야 합니다.

정상적인 트랜잭션 종료 방법은 다음 두 가지입니다.

COMMIT;

ROLLBACK;

단, 위 명령어는 현재 연결된 세션의 트랜잭션에 적용됩니다. 다른 세션에서 실행한다고 해서 차단 세션의 트랜잭션이 종료되는 것은 아닙니다.

차단 세션이 응답하지 않고 운영상 종료가 필요하다고 판단된다면 관리 권한을 가진 계정에서 해당 연결을 종료할 수 있습니다.

KILL CONNECTION 123;

여기서 123은 실제 MySQL 연결 ID입니다. 트랜잭션 ID나 운영체제 PID를 입력하는 것이 아닙니다.

연결이 종료되면 미완료 트랜잭션의 롤백이 진행될 수 있습니다. 대량의 변경 작업을 수행한 트랜잭션은 롤백에 시간이 걸릴 수 있습니다.

또한 KILL QUERY는 현재 실행 중인 SQL을 중단하는 명령이며, 연결이나 트랜잭션 전체를 종료하는 명령과는 다릅니다.

따라서 차단 세션을 종료하기 전에 실제 서비스와 연결된 작업인지, 데이터 변경 중인지, 종료 후 롤백 영향이 어느 정도인지 반드시 확인해야 합니다.

7. innodb_lock_wait_timeout 값을 늘리면 해결될까?

MySQL에서는 innodb_lock_wait_timeout으로 InnoDB 행 잠금 대기 시간을 설정할 수 있습니다.

현재 값을 확인합니다.

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

일반적인 기본값은 50초입니다. 다만 서버 버전과 운영 설정에 따라 실제 적용값은 다를 수 있습니다.

특정 세션에서 임시로 값을 변경하려면 다음과 같이 실행할 수 있습니다.

SET SESSION innodb_lock_wait_timeout = 30;

이 설정은 현재 세션에 적용됩니다.

하지만 잠금 대기 시간을 늘리는 것은 잠금 충돌 자체를 제거하는 해결 방법이 아닙니다.

잠금을 보유한 트랜잭션이 오래 유지되는 상황이라면 대기 시간을 늘려도 응답 지연과 연결 누적이 심해질 수 있습니다.

따라서 먼저 잠금 충돌 원인을 해결하고, 서비스의 정상적인 트랜잭션 처리 시간에 맞춰 적절한 대기 시간을 설정해야 합니다.

8. 인덱스와 트랜잭션 구조 개선하기

잠금 대기 오류가 반복된다면 SQL 실행 계획과 트랜잭션 범위를 점검해야 합니다.

예를 들어 특정 주문번호를 수정하는 쿼리가 있다면 해당 조건에 적절한 인덱스가 있는지 확인합니다.

EXPLAIN
UPDATE orders
SET status = 'completed'
WHERE id = 1001;

적절한 인덱스가 없다면 많은 레코드를 검사하면서 불필요하게 넓은 범위에 잠금이 발생할 수 있습니다.

특히 InnoDB에서는 인덱스 검색 조건과 트랜잭션 격리 수준에 따라 레코드 락, 갭 락, 넥스트 키 락의 범위가 달라질 수 있습니다.

다음 사항을 함께 점검하는 것이 좋습니다.

• UPDATE와 DELETE 조건에 적절한 인덱스가 있는지 확인
• 트랜잭션 안에서 외부 API 호출이나 장시간 작업을 수행하지 않도록 개선
• 필요한 데이터만 수정하고 가능한 한 빠르게 COMMIT 실행
• 여러 테이블을 수정할 때 일관된 잠금 획득 순서 유지
• 대량 변경 작업을 적절한 크기의 배치로 분할
• 잠금 대기 오류 발생 시 ROLLBACK 후 제한된 횟수로 재시도

특히 자동 재시도 로직에서는 동일한 작업이 중복 처리되지 않도록 트랜잭션 경계와 멱등성을 고려해야 합니다.

9. Lock Wait Timeout과 Deadlock의 차이

잠금 대기 오류와 함께 자주 언급되는 문제가 Deadlock입니다. 하지만 두 오류는 발생 원리가 다릅니다.

Deadlock은 트랜잭션들이 서로 상대방의 잠금 해제를 기다리는 순환 대기 상태입니다.

반면 Lock Wait Timeout은 반드시 순환 대기가 발생한 것은 아니며, 다른 트랜잭션이 잠금을 너무 오래 유지해도 발생할 수 있습니다.

따라서 두 오류를 구분해 로그와 트랜잭션 구조를 분석해야 합니다.

실전 점검 순서

MySQL에서 Lock Wait Timeout Exceeded 오류가 반복된다면 다음 순서로 확인할 수 있습니다.

-- 1. 잠금 대기 시간 확인
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 2. 현재 세션 확인
SHOW FULL PROCESSLIST;

-- 3. 실행 중인 트랜잭션 확인
SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 4. 잠금 대기 관계 확인
SELECT
    waiting_pid,
    blocking_pid,
    locked_table,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits;

-- 5. InnoDB 상태 확인
SHOW ENGINE INNODB STATUS\G

-- 6. 실제 차단 세션과 영향 확인 후
-- 필요한 경우에만 연결 종료
-- KILL CONNECTION 123;

위 명령어는 MySQL 8.0에서 사용할 수 있는 대표적인 점검 예시입니다. 버전과 권한, Performance Schema 설정에 따라 조회 가능한 정보가 달라질 수 있습니다.

특히 차단 세션을 발견했다고 해서 즉시 종료하지 말고 트랜잭션이 수행 중인 작업과 서비스 영향을 먼저 확인해야 합니다.

정리

MySQL에서 Lock Wait Timeout Exceeded 오류가 발생하는 이유는 대부분 다른 트랜잭션이 보유한 잠금이 해제되지 않아 설정된 대기 시간을 초과했기 때문입니다.

먼저 SHOW FULL PROCESSLIST와 INNODB_TRX로 세션과 트랜잭션 상태를 확인하고, sys.innodb_lock_waits 또는 Performance Schema로 실제 차단 관계를 분석해야 합니다.

잠금 대기 시간을 늘리거나 MySQL을 재시작하기보다 장시간 열린 트랜잭션, COMMIT 누락, 비효율적인 인덱스, 과도한 트랜잭션 범위를 개선하는 것이 근본적인 해결 방법입니다.

핵심 점검 순서는 잠금 대기 확인 → 차단 세션 식별 → 원인 SQL 분석 → 안전한 트랜잭션 정리 → 인덱스와 애플리케이션 개선입니다.