MySQL 데드락 해결: 전략과 모범 사례

MySQL 데드락은 트랜잭션이 서로를 순환적으로 차단할 때 발생합니다. InnoDB는 이 순환을 감지하고 하나의 트랜잭션을 롤백하여 다른 트랜잭션이 계속 진행될 수 있도록 합니다. 이 자동 롤백은 도움이 되지만, 문제가 무해해지는 것은 아닙니다. 애플리케이션은 여전히 오류를 보게 되고, 사용자 작업이 실패할 수 있으며, 반복되는 데드락은 정상적인 트래픽 급증을 지원 사고로 바꿀 수 있습니다.

바쁜 트랜잭션 시스템에서 가끔 한 번씩 발생하는 데드락은 드문 일이 아닙니다. 지속적인 데드락은 신호입니다. 일반적으로 트랜잭션이 너무 광범위하거나, 행이 일관되지 않은 순서로 잠기거나, 쿼리가 필요한 것보다 더 많은 데이터를 스캔하고 있음을 의미합니다.

MySQL 데드락 이해하기

현대 MySQL 작업에서 데드락 문제 해결은 일반적으로 InnoDB를 의미합니다. InnoDB는 기본 트랜잭션 엔진이며 행, 인덱스, 갭 및 넥스트 키 잠금을 사용하기 때문입니다. 다른 엔진은 다르게 차단, 타임아웃 또는 쓰기를 직렬화할 수 있지만, 대부분의 애플리케이션 데드락 사고는 InnoDB에서 발생합니다.

데드락 순환

데드락은 일반적으로 다음과 같은 패턴을 따릅니다:

트랜잭션 A가 리소스 X에 대한 잠금을 획득합니다.
트랜잭션 B가 리소스 Y에 대한 잠금을 획득합니다.
트랜잭션 A가 리소스 Y에 대한 잠금을 획득하려고 시도하지만, B가 보유하고 있기 때문에 기다려야 합니다.
트랜잭션 B가 리소스 X에 대한 잠금을 획득하려고 시도하지만, A가 보유하고 있기 때문에 기다려야 합니다.

이 시점에서 두 트랜잭션 모두 진행할 수 없습니다. InnoDB는 이 대기 순환을 감지하고 하나의 트랜잭션(종종 데드락 희생자라고 함)을 롤백합니다. 애플리케이션은 일반적으로 SQL 오류 코드 1213 (ER_LOCK_DEADLOCK)을 받습니다. 마지막 문장뿐만 아니라 전체 트랜잭션이 사라지므로 재시도 로직은 처음부터 트랜잭션을 다시 실행해야 합니다.

데드락의 일반적인 원인

데드락은 일반적으로 잘못된 트랜잭션 설계 또는 비효율적인 쿼리에서 비롯됩니다:

장기 실행 트랜잭션: 오랜 시간 동안 잠금을 보유하는 트랜잭션은 충돌 가능성을 극적으로 증가시킵니다.
일관되지 않은 작업 순서: 동일한 행 또는 테이블 집합을 업데이트하지만 순서가 다른 두 트랜잭션.
누락되거나 비효율적인 인덱스: 인덱스가 누락되었거나 충분히 선택적이지 않은 경우, InnoDB는 애플리케이션이 예상한 것보다 훨씬 더 많은 인덱스 레코드 또는 범위를 검사하고 잠글 수 있어 잠금 표면적이 증가합니다.
높은 동시성: 당연히 동일한 데이터 세트에 대한 동시 쓰기가 많을수록 충돌 가능성이 높아집니다.

데드락 진단 및 분석

데드락이 발생하면 첫 번째 단계는 관련된 트랜잭션과 그들이 보유한 특정 잠금을 식별하는 것입니다. MySQL의 주요 진단 도구는 SHOW ENGINE INNODB STATUS입니다.

SHOW ENGINE INNODB STATUS 사용

다음 명령을 실행하고 출력을 검사하여 특히 LATEST DETECTED DEADLOCK 섹션을 찾습니다.

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK 출력은 중요한 포렌식 데이터를 제공하며, 다음을 자세히 설명합니다:

관련된 트랜잭션(ID, 상태 및 기간).
데드락이 발생했을 때 희생자가 실행 중이던 SQL 문.
대기 중이던 특정 행과 인덱스.
차단 트랜잭션이 보유한 리소스.

팁: 로그 구문 분석 도구는 이러한 데드락 항목을 자동으로 추출하고 분류할 수 있으며, 이는 종종 MySQL 오류 로그에도 기록됩니다.

반복되는 사고의 경우 여러 샘플을 캡처하십시오. SHOW ENGINE INNODB STATUS는 가장 최근에 감지된 데드락을 표시하므로 새 데드락이 이전 증거를 덮어쓸 수 있습니다. 데드락이 빈번한 시스템에서는 MySQL 버전과 운영 정책이 허용하는 경우 오류 로그에 데드락 로깅을 활성화하십시오:

SET GLOBAL innodb_print_all_deadlocks = ON;

볼륨을 고려하지 않고 추가 로깅을 활성화된 상태로 두지 마십시오. 사고 중에는 유용하지만, 시끄러운 시스템은 많은 로그 데이터를 생성할 수 있습니다.

데드락 보고서를 읽을 때 코드를 변경하기 전에 세 가지를 찾으십시오:

어떤 SQL 문이 관련되었습니까?
어떤 인덱스가 사용되었습니까?
트랜잭션이 동일한 리소스를 다른 순서로 잠갔습니까?

마지막 질문이 일반적으로 가장 많은 것을 알려줍니다. 하나의 코드 경로가 accounts를 업데이트한 다음 ledger_entries를 업데이트하고, 다른 경로가 ledger_entries를 업데이트한 다음 accounts를 업데이트하는 경우, 해결책은 아마도 서버 설정이 아닙니다. 해결책은 하나의 순서에 동의하는 것입니다.

한 가지 실용적인 트릭은 두 트랜잭션을 스크래치 노트에 붙여넣고 행을 잠글 수 있는 모든 문장을 표시하는 것입니다: UPDATE, DELETE, 고유 키가 있는 테이블에 대한 INSERT, SELECT ... FOR UPDATE, 그리고 일부 외래 키 검사. 단일 실패한 쿼리만 보지 않고 그 주변의 전체 시퀀스를 볼 때 데드락이 종종 명확해집니다.

예방 전략 1: 트랜잭션 최적화

데드락을 방지하는 가장 효과적인 방법은 잠금이 유지되는 시간을 줄이고 리소스에 액세스하는 방식을 표준화하는 것입니다.

1. 트랜잭션을 짧고 원자적으로 유지

트랜잭션은 절대적으로 필요한 작업만 캡슐화해야 합니다. 트랜잭션이 오래 실행될수록 잠금을 더 오래 유지하고 충돌 가능성이 높아집니다.

나쁜 관행: 데이터를 가져오고, 애플리케이션 계층에서 복잡한 비즈니스 로직을 수행한 다음, 모두 하나의 긴 트랜잭션 내에서 데이터를 업데이트합니다.
모범 사례: 비즈니스 로직을 트랜잭션 외부에서 실행합니다. 트랜잭션에는 SELECT FOR UPDATE, 업데이트/삽입 및 COMMIT 단계만 포함되어야 합니다.

예를 들어, 트랜잭션을 열고 결제 제공자를 호출하거나, 이메일을 보내거나, 원격 API를 기다리지 마십시오. 이러한 작업은 수백 밀리초 또는 몇 초가 걸릴 수 있으며, 그 동안 데이터베이스 잠금은 여전히 유지됩니다. 먼저 계산할 수 있는 것을 계산한 다음, 원자적이어야 하는 가장 작은 읽기 및 쓰기 세트에 대해 트랜잭션을 엽니다.

2. 리소스 액세스 순서 표준화

이것은 아마도 가장 중요한 단일 예방 전략일 것입니다. 두 개의 특정 테이블(예: orders 및 inventory)과 상호 작용하는 모든 코드가 항상 동일한 순서(예: orders 다음 inventory)로 테이블(또는 행)을 잠그려고 시도하면 순환 종속성이 불가능해집니다.

트랜잭션 A	트랜잭션 B
테이블 X 잠금	테이블 Y 잠금
테이블 Y 잠금	테이블 X 잠금 (데드락 위험)

두 트랜잭션이 모두 시퀀스(X 다음 Y)를 따랐다면, 트랜잭션 B는 A가 끝날 때까지 기다리기만 하면 되어 데드락을 방지할 수 있습니다.

이것은 행에도 적용됩니다. 송금이 두 계정 간에 돈을 이동하는 경우, 항상 낮은 계정 ID를 먼저 잠그고 높은 계정 ID를 두 번째로 잠급니다:

START TRANSACTION;

SELECT account_id, balance
FROM accounts
WHERE account_id IN (100, 200)
ORDER BY account_id
FOR UPDATE;

-- 차변 및 대변 적용

COMMIT;

일관된 순서가 없으면 반대 방향의 두 동시 송금이 데드락을 일으킬 수 있습니다. 하나는 계정 100을 잠그고 다른 하나는 계정 200을 잠근 다음 각각 다른 행을 기다립니다.

3. `SELECT FOR UPDATE` 전략적으로 사용

동일한 트랜잭션에서 즉시 수정될 데이터를 읽을 때 SELECT FOR UPDATE를 사용하여 즉시 배타적 잠금을 획득하십시오. 이렇게 하면 읽기와 쓰기 사이에 두 번째 트랜잭션이 동일한 행을 변경하는 것을 방지할 수 있습니다. 데드락을 마술처럼 제거하지는 않습니다. 잠금을 명시적으로 만들어 순서와 타이밍을 제어할 수 있게 합니다.

-- 지정된 행에 대해 즉시 잠금 획득
SELECT amount FROM accounts WHERE user_id = 123 FOR UPDATE;
-- 애플리케이션에서 계산 수행
UPDATE accounts SET amount = new_amount WHERE user_id = 123;
COMMIT;

예방 전략 2: 인덱싱 및 쿼리 튜닝

인덱싱이 좋지 않으면 InnoDB가 필요한 것보다 더 많은 행을 잠그게 되므로 일반적인 근본 원인입니다.

1. 쿼리가 잠금에 인덱스를 사용하는지 확인

MySQL이 WHERE 절을 기반으로 행을 찾아야 할 때 조건과 일치하는 인덱스 레코드를 잠급니다. 적절한 인덱스가 없으면 InnoDB는 전체 테이블 스캔을 수행하고 몇 개의 행만 필요하더라도 테이블 전체(또는 광범위한 범위)를 잠글 수 있습니다.

업데이트, 삭제, 조인 및 범위 스캔을 위해 행을 찾는 데 사용되는 열에 적절한 인덱스가 있는지 확인하십시오.
외래 키가 인덱싱되었는지 확인하십시오.

데드락 보고서에 표시된 문장에 대해 EXPLAIN을 사용하십시오:

EXPLAIN UPDATE orders
SET status = 'paid'
WHERE user_id = 42 AND status = 'pending';

MySQL이 소수의 행을 업데이트하기 위해 테이블의 큰 부분을 스캔하는 경우 예상치 못한 잠금을 보유할 수 있습니다. (user_id, status)와 같은 복합 인덱스는 스캔 범위를 좁힐 수 있습니다. 올바른 인덱스는 실제 쿼리 패턴과 데이터 분포에 따라 달라지므로 EXPLAIN과 프로덕션과 유사한 데이터로 확인하십시오.

2. 갭 잠금 최소화

InnoDB는 기본 REPEATABLE READ 격리 수준에서 팬텀 읽기를 방지하기 위해 갭 잠금(인덱스 레코드 사이의 범위에 대한 잠금)을 사용합니다. 일관성에 필수적이지만, 이러한 잠금은 범위가 겹칠 때 데드락을 유발하는 경우가 많습니다.

동시 쓰기 작업이 많은 상황을 처리하고 있고 다른 읽기 동작을 허용할 수 있다면 특정 세션의 격리 수준을 READ COMMITTED로 전환하는 것을 고려하십시오.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

경고: 격리 수준을 전역적으로 또는 부주의하게 변경하면 애플리케이션이 예상하지 못한 동작(예: 반복 불가능한 읽기)이 발생할 수 있습니다. 위험을 이해하고 테스트한 경우에만 READ COMMITTED를 사용하십시오.

해결 전략: 애플리케이션 측 재시도 로직

최상의 예방 전략을 사용하더라도 극심한 부하에서는 데드락이 가끔 발생할 수 있습니다. InnoDB가 희생자를 자동으로 롤백하므로 애플리케이션은 이 오류를 정상적으로 처리하도록 설계되어야 합니다.

MySQL은 SQL 오류 코드 1213 (ER_LOCK_DEADLOCK)을 사용하여 데드락을 보고합니다.

트랜잭션 재시도 구현

애플리케이션은 오류 1213을 포착하고 작업을 다시 시도해도 안전한 경우 전체 트랜잭션을 재시도해야 합니다. 재시도는 START TRANSACTION부터 시작해야 합니다. 실패한 문장만 다시 실행하지 마십시오.

오류 1213 포착: 데이터베이스 커넥터는 데드락 오류를 인식해야 합니다.
대기: 차단 트랜잭션이 커밋할 시간을 주기 위해 재시도 전에 짧고 무작위적인 백오프 시간을 도입합니다.
재시도: 전체 트랜잭션 시퀀스를 다시 시도합니다.
재시도 제한: 무한 루프를 방지하기 위해 사용자 요청을 실패시키기 전에 최대 재시도 횟수(예: 3~5회)를 구현합니다.

MAX_RETRIES = 5

for attempt in range(MAX_RETRIES):
    try:
        db_connection.execute("START TRANSACTION")
        # ... 복잡한 데이터베이스 작업 ...
        db_connection.execute("COMMIT")
        break # 성공
    except DeadlockError:
        if attempt < MAX_RETRIES - 1:
            time.sleep(0.1 * (attempt + 1)) # 지수 백오프
            continue
        else:
            raise DatabaseFailure("지속적인 데드락으로 인해 트랜잭션이 실패했습니다.")

외부 효과를 트리거하는 트랜잭션에 주의하십시오. 트랜잭션이 이메일을 보내거나, 카드를 청구하거나, 메시지를 게시하거나, 다른 서비스를 호출하는 경우, 맹목적인 재시도는 해당 부작용을 중복시킬 수 있습니다. 이러한 경우 멱등성 키를 사용하거나 데이터베이스 트랜잭션이 커밋된 후에 부작용을 이동하십시오.

고급 설정 및 모범 사례

잠금 대기 시간 초과 조정

MySQL에는 트랜잭션이 잠금을 위해 대기해야 하는 시간을 정의하는 설정이 있습니다:

SET GLOBAL innodb_lock_wait_timeout = 50; -- 최대 50초 대기

innodb_lock_wait_timeout을 너무 낮게 설정하면 정상적인 경합 중에 트랜잭션이 실패할 수 있습니다. 너무 높게 설정하면 사용자 요청이 애플리케이션이 허용할 수 있는 것보다 더 오래 기다리게 할 수 있습니다. 이 설정은 잠금 대기를 처리하며 데드락 감지 자체를 처리하지 않으므로 주요 데드락 수정으로 취급하지 마십시오.

모범 사례 요약

영역	모범 사례
트랜잭션 설계	트랜잭션을 짧게 유지하고, 빠르게 실행하며, 즉시 커밋하거나 롤백합니다.
잠금 순서	애플리케이션 전체에서 행/테이블에 액세스하고 잠그는 엄격하고 표준화된 순서를 설정합니다.
인덱싱	조회 또는 업데이트에 사용되는 모든 열이 행 수준 잠금을 효율적으로 활용하기 위해 적절히 인덱싱되었는지 확인합니다.
진단	반복되는 데드락 패턴에 대해 `SHOW ENGINE INNODB STATUS` 출력 및 MySQL 오류 로그를 정기적으로 검토합니다.
애플리케이션 처리	SQL 오류 1213을 정상적으로 처리하기 위해 애플리케이션 계층에 강력한 재시도 로직을 구현합니다.

실용적인 디버깅 흐름

프로덕션 데드락 알림이 발생하면 유용한 흐름은 다음과 같습니다:

데드락 보고서가 덮어쓰여지기 전에 캡처합니다.
두 문장과 관련된 인덱스를 식별합니다.
단일 쿼리뿐만 아니라 각 문장 주변의 트랜잭션을 재구성합니다.
코드 경로가 행이나 테이블을 다른 순서로 잠그는지 확인합니다.
EXPLAIN을 실행하고 스캔이 예상보다 넓은 경우 인덱스를 추가하거나 조정합니다.
안전하고 멱등성 있는 트랜잭션을 위한 재시도 로직이 있는지 확인합니다.

데드락은 동시 트랜잭션 시스템의 일부이지만, 반복되는 데드락은 일반적으로 수정 가능합니다. 더 짧은 트랜잭션, 일관된 잠금 순서, 더 나은 인덱스 및 신중한 재시도 로직은 서버 변수를 무작위로 조정하는 것보다 더 효과적입니다.