MySQL 데드락 실전 분석: SHOW ENGINE INNODB STATUS 읽는 법

Deadlock found when trying to get lock; try restarting transaction. 이 메시지를 처음 보면 대개 재시도 로직을 넣고 넘어갑니다. 재시도는 맞는 대응이지만, 왜 걸렸는지 모르면 부하가 늘 때마다 같은 자리에서 다시 터집니다.

원인은 SHOW ENGINE INNODB STATUS에 다 적혀 있습니다. 다만 출력이 16진수 덤프까지 섞여 있어서 어디를 봐야 할지 감이 안 옵니다. 실제로 데드락을 두 종류 만들어서 그 로그를 줄 단위로 뜯어보겠습니다.

MySQL 8.0.46, 격리 수준은 기본값 REPEATABLE-READ입니다.

먼저 로그를 남기게 만듭니다

SHOW ENGINE INNODB STATUS는 마지막 데드락 하나만 들고 있습니다. 그 사이에 다른 데드락이 나면 덮어씁니다. 운영에서 원인을 쫓으려면 전부 남겨야 합니다.

SET GLOBAL innodb_print_all_deadlocks = ON;

켜두면 데드락이 날 때마다 MySQL 에러 로그에 같은 내용이 쌓입니다. 성능 부담은 거의 없으니 개발·스테이징에는 그냥 켜두는 편이 낫습니다.

관련 설정도 같이 확인해 두면 좋습니다.

innodb_lock_wait_timeout : 50      -- 락을 못 얻고 기다리는 한계 (초)
innodb_deadlock_detect   : ON      -- 데드락 감지. 끄면 위 타임아웃까지 그냥 기다린다

첫 번째 — 교과서적인 역순 접근

두 트랜잭션이 같은 두 행을 반대 순서로 잠그는 경우입니다.

T1: update account set balance=balance-10 where id=1;
T2: update account set balance=balance-10 where id=2;
    -- 여기서 둘 다 첫 행을 잡은 상태
T1: update account set balance=balance-10 where id=2;   -- T2 가 쥐고 있다
T2: update account set balance=balance-10 where id=1;   -- T1 이 쥐고 있다

한쪽이 죽습니다.

MySQLTransactionRollbackException / errorCode=1213 / sqlState=40001
Deadlock found when trying to get lock; try restarting transaction

에러 코드 1213, SQLSTATE 40001입니다. 재시도 대상을 코드에서 걸러낼 때 이 둘을 보면 됩니다. 락 대기 타임아웃(1205)과는 다른 상황이니 구분해서 처리하는 게 좋습니다.

로그에서 볼 부분만 추리면 이렇습니다.

*** (1) TRANSACTION:
TRANSACTION 1819, ACTIVE 0 sec starting index read
MySQL thread id 13, ... query id 35 root updating
update account set balance=balance-10 where id=2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 index PRIMARY of table `demo`.`account`
trx id 1819 lock_mode X locks rec but not gap
 0: len 4; hex 80000001; asc     ;;        <- id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `demo`.`account`
trx id 1819 lock_mode X locks rec but not gap waiting
 0: len 4; hex 80000002; asc     ;;        <- id = 2

읽는 순서는 네 줄이면 충분합니다.

• 맨 아래 SQL이 그 트랜잭션이 마지막으로 시도한 구문입니다. 범인이 아니라 피해 지점입니다.
• HOLDS THE LOCK(S)가 이미 쥐고 있는 것
• WAITING FOR THIS LOCK이 기다리는 것
• index PRIMARY of table ...로 어느 테이블 어느 인덱스인지

hex 80000001이 id=1입니다. InnoDB는 부호 있는 정수의 최상위 비트를 뒤집어 저장하므로 0x80000001에서 0x80000000을 빼면 1입니다. asc 칸이 비어 있는 건 출력 불가능한 바이트라서고, 문자열 컬럼이면 asc a;;처럼 값이 그대로 보입니다.

lock_mode X locks rec but not gap — 배타 락이고 레코드만 잠급니다. 갭은 건드리지 않습니다. 이게 다음 사례와의 대비점입니다.

(1)이 id=1을 쥐고 id=2를 기다리고, (2)가 id=2를 쥐고 id=1을 기다립니다. 원이 만들어졌습니다.

해법은 단순합니다. 접근 순서를 고정하면 됩니다. 여러 행을 갱신하는 트랜잭션이라면 항상 id 오름차순처럼 정해진 순서로 잠그면 원이 생기지 않습니다. 애플리케이션에서 Collections.sort(ids) 한 줄이 끝인 경우가 많습니다.

두 번째 — 서로 다른 값을 넣는데 왜 부딪히나

이쪽이 실무에서 훨씬 자주 만나는 유형입니다. 흔한 get-or-create 패턴입니다.

// 있으면 쓰고 없으면 만든다
select * from coupon where code = 'C-150' for update;   // 없다
insert into coupon (code) values ('C-150');

두 요청이 서로 다른 코드로 동시에 들어옵니다. T1은 C-150, T2는 C-160. 겹치는 값이 하나도 없는데 데드락이 납니다.

T1: MySQLTransactionRollbackException / errorCode=1213
T2: 커밋 성공

로그를 보면 이유가 보입니다.

*** (1) TRANSACTION:
TRANSACTION 1960, ACTIVE 0 sec inserting
insert into coupon (code) values ('C-160')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 8 page no 5 index code of table `demo`.`coupon`
trx id 1960 lock_mode X locks gap before rec
 0: len 5; hex 432d323030; asc C-200;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8 page no 5 index code of table `demo`.`coupon`
trx id 1960 lock_mode X locks gap before rec insert intention waiting
 0: len 5; hex 432d323030; asc C-200;;

*** (2) TRANSACTION:
TRANSACTION 1961, ACTIVE 0 sec inserting
insert into coupon (code) values ('C-150')

*** (2) HOLDS THE LOCK(S):
... lock_mode X locks gap before rec
 0: len 5; hex 432d323030; asc C-200;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... lock_mode X locks gap before rec insert intention waiting
 0: len 5; hex 432d323030; asc C-200;;

*** WE ROLL BACK TRANSACTION (2)

asc C-200을 보세요. 네 군데 전부 같은 값입니다. 테이블에는 C-100과 C-200이 들어 있었고, C-150도 C-160도 그 사이에 들어갑니다. 둘 다 같은 틈을 노린 겁니다.

여기서 두 종류의 락이 등장합니다.

locks gap before rec는 갭 락입니다. C-200 앞의 빈 구간을 잠급니다. 없는 행을 FOR UPDATE로 조회하면 InnoDB는 “그 자리에 아무도 끼어들지 마라”는 뜻으로 갭을 잠급니다. REPEATABLE-READ에서 팬텀 리드를 막는 장치입니다.

중요한 건 갭 락끼리는 서로 호환된다는 점입니다. T1과 T2가 같은 갭에 갭 락을 걸었는데 둘 다 성공했습니다. 이 단계에서는 아무 일도 없습니다.

insert intention waiting이 삽입 의도 락입니다. 갭에 실제로 INSERT하려면 필요한데, 이건 다른 트랜잭션의 갭 락과 충돌합니다. T1이 삽입하려면 T2의 갭 락이 풀려야 하고, T2도 마찬가지입니다. 서로를 기다립니다.

마지막 줄 WE ROLL BACK TRANSACTION (2)가 MySQL의 판정입니다. 되돌린 양이 적은 쪽을 희생시킵니다.

무엇을 고쳐야 하나

접근 순서를 맞추는 건 여기서 소용이 없습니다. 순서가 문제가 아니라 같은 갭을 두 트랜잭션이 동시에 노린 것이 문제이기 때문입니다.

실무에서 쓸 만한 방향은 세 가지입니다.

첫째, SELECT ... FOR UPDATE를 빼고 INSERT를 먼저 시도합니다. 유니크 제약에 걸리면 그때 조회로 넘어갑니다. 없는 행을 잠그지 않으므로 갭 락이 생기지 않습니다.

insert into coupon (code) values ('C-150')
  on duplicate key update id = id;

같은 조건으로 세 번 돌려봤습니다.

round 1  T1: 커밋  |  T2: 커밋
round 2  T1: 커밋  |  T2: 커밋
round 3  T1: 커밋  |  T2: 커밋

데드락이 사라집니다. 유니크 인덱스에 실제로 값을 넣으면 레코드 락이 잡히지, 빈 구간을 선점하지 않기 때문입니다.

둘째, 트랜잭션을 짧게 만듭니다. 조회와 삽입 사이에 외부 API 호출 같은 게 끼어 있으면 갭 락을 쥐고 있는 시간이 길어져 충돌 확률이 올라갑니다.

셋째, 격리 수준을 READ-COMMITTED로 내리면 갭 락이 대부분 사라집니다. 다만 팬텀 리드를 허용하게 되니 애플리케이션 전체에 영향이 갑니다. 데드락 하나 때문에 바꿀 일은 아니고, 이미 그쪽으로 갈 이유가 있을 때 같이 얻는 이득 정도로 보는 게 맞습니다.

세 번째 — 인덱스가 없으면 데드락이 아니라 대기입니다

“WHERE 조건에 인덱스가 없으면 데드락이 난다”는 말을 자주 보는데, 실제로 재현해 보니 증상이 달랐습니다.

owner 컬럼에 인덱스가 없는 테이블에서 T1이 where owner='a'로 UPDATE하고 3초간 커밋하지 않았습니다. 그 사이 T2가 전혀 다른 행인 where owner='b'를 UPDATE했습니다.

T1 (owner='a' 로 UPDATE 후 3초 보유) : 커밋 성공
T2 (owner='b' 로 UPDATE 시도)       : 성공 — 대기 2715ms

데드락은 안 났습니다. 대신 T2가 2.7초를 기다렸습니다. 인덱스가 없으니 T1의 UPDATE가 전체 행을 훑으면서 스캔한 행을 전부 잠갔고, 관계없는 owner='b' 행까지 묶인 겁니다.

증상으로 구분하면 이렇습니다. 데드락은 즉시 1213으로 죽고, 인덱스 부재는 느려지다가 innodb_lock_wait_timeout(기본 50초)에 걸려 1205로 죽습니다. 응답이 느려진다는 신고가 먼저 오고 에러는 나중에 나옵니다.

물론 잠기는 범위가 넓어지면 데드락 확률도 같이 올라갑니다. 인덱스를 붙이는 게 두 문제를 한 번에 줄이는 건 맞습니다. 다만 인덱스가 없다고 곧바로 데드락이 나는 건 아닙니다. 로그에 1213이 없는데 인덱스만 의심하고 있으면 엉뚱한 데를 파게 됩니다.

재시도는 어떻게 거나

데드락은 완전히 없앨 수 없습니다. 줄이는 것과 재시도를 같이 가져가야 합니다.

Spring이라면 이렇게 걸러냅니다.

@Service
class CouponFacade {

    private final CouponService service;   // 트랜잭션은 이쪽에

    @Retryable(
        retryFor = { DeadlockLoserDataAccessException.class },
        maxAttempts = 3,
        backoff = @Backoff(delay = 50, multiplier = 2, random = true)
    )
    public void issue(String code) {
        service.issueInTx(code);           // 여기서 트랜잭션이 시작되고 끝난다
    }
}

@Service
class CouponService {
    @Transactional
    public void issueInTx(String code) { ... }
}

세 가지를 지켜야 합니다.

재시도는 트랜잭션 바깥에서 걸어야 합니다. 위처럼 계층을 나누는 이유가 이겁니다. @Retryable과 @Transactional을 같은 메서드에 붙이면 이미 롤백 표시가 된 트랜잭션 안에서 다시 시도하게 되거나, 두 AOP의 적용 순서에 결과가 좌우됩니다. 재시도가 트랜잭션을 통째로 다시 열어야 의미가 있습니다.

백오프에 지터를 넣습니다. 위의 random = true가 그것입니다. 충돌한 두 트랜잭션이 같은 간격으로 재시도하면 똑같이 다시 부딪힙니다.

락 대기 타임아웃(1205)과 구분합니다. 데드락은 즉시 재시도해도 되지만, 타임아웃은 앞선 트랜잭션이 아직 길게 돌고 있다는 뜻이라 재시도가 상황을 악화시킬 수 있습니다.

락 전략 자체를 어떻게 고를지는 JPA 동시성 이슈 해결: 낙관적 락 vs 비관적 락에 정리해 두었습니다. 낙관적 락으로 갈 수 있는 구간이면 애초에 이 문제를 안 만나는 경우도 많습니다.

정리

로그에서 볼 곳은 네 군데입니다. 각 트랜잭션의 마지막 SQL, HOLDS THE LOCK(S), WAITING FOR THIS LOCK, 그리고 index ... of table ....

lock_mode X locks rec but not gap이면 레코드 락입니다. 접근 순서를 고정하면 풀립니다.

locks gap before rec와 insert intention waiting이 같이 보이면 갭 락입니다. 서로 다른 값을 넣고 있어도 같은 틈이면 부딪힙니다. 순서 정렬로는 안 풀리고, 없는 행을 잠그지 않는 쪽으로 바꿔야 합니다.

그리고 innodb_print_all_deadlocks를 켜두세요. 마지막 하나만 남는 로그로는 재현이 어려운 문제를 쫓을 수 없습니다.