MySQL ERROR 1205: Lock wait timeout exceeded 원인, 해결 방법, 실전 트러블슈팅 가이드

MySQL ERROR 1205: Lock wait timeout exceeded 원인, 해결 방법, 실전 트러블슈팅 가이드

 

MySQL ERROR 1205 lock wait timeout exceeded 오류는 트랜잭션이 락을 기다리는 시간이 제한 시간을 초과했을 때 발생하는 일반적인 데이터베이스 문제로, 적절한 진단과 해결책을 통해 완전히 해결할 수 있습니다.

---

MySQL ERROR 1205란 무엇인가?

MySQL ERROR 1205는 InnoDB 스토리지 엔진에서 발생하는 가장 빈번한 트랜잭션 오류 중 하나입니다.

이 오류는 하나의 트랜잭션이 다른 트랜잭션에 의해 잠긴 리소스에 접근하려고 할 때, 설정된 대기 시간(innodb_lock_wait_timeout)을 초과했을 때 발생합니다.

오류 메시지 예시

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

 

일반적으로 다음과 같은 상황에서 이 오류가 발생합니다

• 장시간 실행되는 트랜잭션
• 데드락 상황
• 높은 동시성 환경에서의 리소스 경합
• 잘못된 인덱스 설계로 인한 테이블 락

---

lock wait timeout exceeded 발생 원인 분석

1. 트랜잭션 락 경합

가장 일반적인 원인은 여러 트랜잭션이 동일한 리소스에 대해 경합하는 상황입니다.

-- 트랜잭션 A
BEGIN;
UPDATE users SET name = 'John' WHERE id = 1;
-- 트랜잭션이 커밋되지 않은 상태

-- 트랜잭션 B (다른 세션에서)
BEGIN;
UPDATE users SET email = 'john@example.com' WHERE id = 1; -- ERROR 1205 발생 가능

2. 장시간 실행되는 쿼리

복잡한 조인이나 대용량 데이터 처리 쿼리가 트랜잭션을 오래 점유하는 경우:

-- 문제가 되는 쿼리 예시
UPDATE large_table lt 
JOIN another_large_table alt ON lt.id = alt.ref_id 
SET lt.status = 'processed' 
WHERE alt.created_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

3. 인덱스 부족으로 인한 테이블 락

적절한 인덱스가 없어 테이블 전체에 락이 걸리는 경우:

-- 인덱스가 없는 컬럼으로 업데이트
UPDATE orders SET status = 'shipped' WHERE order_date = '2025-01-15';

4. 애플리케이션 레벨 트랜잭션 관리 문제

애플리케이션에서 트랜잭션을 명시적으로 커밋하지 않거나, 예외 상황에서 롤백하지 않는 경우가 있습니다.

---

MySQL 트러블슈팅을 위한 진단 방법

1. 현재 실행 중인 트랜잭션 확인

-- 현재 실행 중인 프로세스 확인
SHOW PROCESSLIST;

-- 더 자세한 정보 확인
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

2. InnoDB 상태 확인

InnoDB 엔진의 상태를 확인하여 락 상황을 파악할 수 있습니다:

SHOW ENGINE INNODB STATUS;

 

이 명령어는 다음과 같은 정보를 제공합니다

섹션	정보 내용
LATEST DETECTED DEADLOCK	최근 데드락 정보
TRANSACTIONS	현재 활성 트랜잭션
FILE I/O	파일 I/O 통계
BUFFER POOL AND MEMORY	버퍼 풀 상태

3. 락 대기 상황 모니터링

-- 현재 락 대기 상황 확인
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;

---

lock wait timeout exceeded try restarting transaction 해결 방법

1. 즉시 해결 방법

트랜잭션 강제 종료

-- 문제가 되는 프로세스 ID 확인 후 종료
SHOW PROCESSLIST;
KILL [PROCESS_ID];

트랜잭션 재시도

애플리케이션 레벨에서 트랜잭션을 재시도하는 로직을 구현

import mysql.connector
import time

def execute_with_retry(cursor, query, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query)
            return True
        except mysql.connector.Error as err:
            if err.errno == 1205:  # Lock wait timeout
                if attempt < max_retries - 1:
                    time.sleep(0.1 * (2 ** attempt))  # 지수 백오프
                    continue
                else:
                    raise
            else:
                raise
    return False

2. lock wait timeout 조정

시스템 전체 설정 변경

-- 현재 설정 확인
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 세션별 조정 (1-1073741824 초 범위)
SET SESSION innodb_lock_wait_timeout = 120;

-- 전역 설정 변경
SET GLOBAL innodb_lock_wait_timeout = 120;

 

MySQL 설정 파일(my.cnf) 수정

[mysqld]
innodb_lock_wait_timeout = 120

 

3. 데이터베이스 설계 최적화

인덱스 최적화

-- 자주 사용되는 WHERE 절 컬럼에 인덱스 추가
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_user_status ON users(status, created_at);

-- 복합 인덱스 활용
CREATE INDEX idx_user_status_date ON users(status, created_at, updated_at);

쿼리 최적화

-- AS-IS: 전체 테이블 스캔
UPDATE users SET last_login = NOW() WHERE email LIKE '%@gmail.com';

-- TO-BE: 인덱스 활용
UPDATE users SET last_login = NOW() 
WHERE email_domain = 'gmail.com' AND status = 'active';

---

2025 MySQL 오류 예방을 위한 Best Practices

1. 트랜잭션 관리 전략

-- 짧은 트랜잭션 유지
BEGIN;
-- 필요한 작업만 수행
UPDATE users SET last_login = NOW() WHERE id = 123;
COMMIT; -- 즉시 커밋

2. 배치 처리 최적화

대용량 데이터 처리 시 배치 단위로 분할:

-- 한 번에 모든 데이터 처리 (비추천)
UPDATE large_table SET status = 'processed' WHERE created_date < '2024-01-01';

-- 배치 단위로 처리 (추천)
DELIMITER $$
CREATE PROCEDURE UpdateInBatches()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;

    REPEAT
        UPDATE large_table 
        SET status = 'processed' 
        WHERE created_date < '2024-01-01' 
        AND status != 'processed'
        LIMIT batch_size;

        SET done = ROW_COUNT() < batch_size;

        -- 다른 트랜잭션에게 기회 제공
        SELECT SLEEP(0.01);

    UNTIL done END REPEAT;
END$$
DELIMITER ;

3. 모니터링 및 알람 설정

-- 성능 스키마를 활용한 모니터링
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_STAR as total_statements,
    SUM_LOCK_TIME/1000000 as total_lock_time_ms,
    AVG_LOCK_TIME/1000000 as avg_lock_time_ms
FROM performance_schema.table_io_waits_summary_by_table 
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY total_lock_time_ms DESC
LIMIT 10;

---

innodb 트랜잭션 문제 심화 분석

1. 트랜잭션 격리 수준 이해

격리 수준	특징	락 경합 위험도
READ UNCOMMITTED	가장 낮은 격리, 더티 리드 허용	낮음
READ COMMITTED	커밋된 데이터만 읽기	중간
REPEATABLE READ	동일 트랜잭션 내 일관된 읽기	높음
SERIALIZABLE	가장 높은 격리, 순차 실행	매우 높음

-- 현재 격리 수준 확인
SELECT @@transaction_isolation;

-- 세션별 격리 수준 조정
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 데드락 분석 및 해결

-- 데드락 정보 확인
SHOW ENGINE INNODB STATUS;

 

데드락 예방 전략

1. 일관된 테이블 접근 순서 유지
2. 트랜잭션 시간 최소화
3. 적절한 인덱스 설계

3. MySQL 성능 튜닝 파라미터

# my.cnf 최적화 설정
[mysqld]
# InnoDB 버퍼 풀 크기 (물리 메모리의 70-80%)
innodb_buffer_pool_size = 8G

# 로그 파일 크기
innodb_log_file_size = 256M

# 동시 쓰기 스레드 수
innodb_write_io_threads = 8
innodb_read_io_threads = 8

# 락 대기 시간
innodb_lock_wait_timeout = 50

# 데드락 감지 활성화
innodb_deadlock_detect = ON

---

실전 예제: DB 장애 상황별 대응

Case 1: 웹 애플리케이션에서 빈번한 ERROR 1205

상황: 사용자 등록 시 빈번한 락 타임아웃 발생

-- 문제 쿼리
UPDATE user_counters SET count = count + 1 WHERE type = 'registration';
INSERT INTO users (name, email, created_at) VALUES ('John', 'john@example.com', NOW());

 

해결책

-- 카운터 테이블 분리 및 비동기 처리
INSERT INTO users (name, email, created_at) VALUES ('John', 'john@example.com', NOW());
-- 카운터 업데이트는 별도 프로세스에서 배치 처리

Case 2: 대용량 배치 작업 시 락 타임아웃

상황: 야간 배치 작업 중 다른 트랜잭션과 충돌

-- 개선된 배치 처리 방식
SET SESSION innodb_lock_wait_timeout = 300;

START TRANSACTION;
SELECT id FROM batch_queue WHERE status = 'pending' LIMIT 100 FOR UPDATE SKIP LOCKED;
-- 선택된 레코드만 처리
UPDATE batch_queue SET status = 'processing' WHERE id IN (...);
COMMIT;

-- 실제 비즈니스 로직 처리
-- ...

START TRANSACTION;
UPDATE batch_queue SET status = 'completed' WHERE id IN (...);
COMMIT;

---

문제 진단을 위한 쿼리 분석 도구

1. 슬로우 쿼리 로그 활용

-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

2. Performance Schema 활용

-- 가장 많은 락 대기를 발생시키는 쿼리 확인
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_LOCK_TIME/1000000 as total_lock_time_ms,
    AVG_LOCK_TIME/1000000 as avg_lock_time_ms
FROM performance_schema.events_statements_summary_by_digest 
WHERE SUM_LOCK_TIME > 0
ORDER BY total_lock_time_ms DESC
LIMIT 10;

3. 실시간 모니터링 스크립트

#!/bin/bash
# lock_monitor.sh

while true; do
    echo "=== $(date) ==="
    mysql -e "
    SELECT 
        p.ID,
        p.USER,
        p.HOST,
        p.DB,
        p.COMMAND,
        p.TIME,
        p.STATE,
        LEFT(p.INFO, 100) as QUERY
    FROM information_schema.PROCESSLIST p
    WHERE p.COMMAND != 'Sleep' 
    AND p.TIME > 30
    ORDER BY p.TIME DESC;
    "
    sleep 10
done

---

해결 팁 및 권장사항

1. 개발 단계 체크리스트

• 모든 UPDATE/DELETE 쿼리에 적절한 WHERE 절과 인덱스 확인
• 트랜잭션 범위 최소화
• 배치 처리 시 LIMIT 절 활용
• 예외 처리 시 명시적 롤백 구현

2. 운영 환경 모니터링

-- 일일 락 타임아웃 발생 현황
SELECT 
    DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) as date,
    COUNT(*) as timeout_count
FROM mysql.general_log 
WHERE argument LIKE '%Lock wait timeout exceeded%'
GROUP BY DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+00:00'))
ORDER BY date DESC;

3. 장기적 성능 최적화

1. 정기적인 테이블 분석: ANALYZE TABLE 실행
2. 인덱스 사용률 점검: 불필요한 인덱스 제거
3. 파티셔닝 고려: 대용량 테이블의 경우
4. 읽기 전용 복제본 활용: 읽기 부하 분산

---

마무리

MySQL ERROR 1205 lock wait timeout exceeded 오류는 올바른 진단과 체계적인 접근을 통해 효과적으로 해결할 수 있습니다.

핵심은 트랜잭션의 범위를 최소화하고, 적절한 인덱스를 설계하며, 시스템 파라미터를 환경에 맞게 조정하는 것입니다.

정기적인 모니터링과 성능 분석을 통해 문제를 사전에 예방하고, 발생 시 신속하게 대응할 수 있는 체계를 구축하시기 바랍니다.

더 자세한 MySQL 트러블슈팅 정보는 MySQL 공식 문서를 참조하세요.

---

참고 자료:

• MySQL Performance Schema 가이드
• InnoDB 트랜잭션 모델
• MySQL 최적화 가이드