MySQL SELECT FOR UPDATE, 인덱스가 없으면 스캔한 행이 전부 잠긴다

재고를 차감하려고 SELECT … FOR UPDATE 로 행을 잠갔어요. 개발 DB 에서는 완벽하게 돌았는데, 트래픽이 오른 날 주문 API 전체가 타임아웃으로 무너져요. 쿼리는 한 줄도 안 바뀌었고요.

로그를 보면 Lock wait timeout exceeded 가 쏟아지는데, 서로 다른 상품을 주문한 요청들이에요. A 상품을 사는 사람과 B 상품을 사는 사람이 왜 서로를 기다리는지 설명이 안 돼요.

개발 환경에서 안 나오는 이유는 단순해요. 동시에 주문하는 사람이 없으니 락이 겹칠 일도 없거든요. 부하 테스트를 돌려도 서로 다른 상품을 흩어서 주문하도록 짰다면 역시 안 나와요.

그래서 이 문제는 대개 첫 프로모션 날, 첫 오픈런 날에 처음 만납니다.

왜 그런가

InnoDB 는 행에 락을 거는 게 아니라 인덱스 레코드에 락을 걸어요. 이 문장이 이 편의 전부예요.

WHERE 절 컬럼에 쓸 수 있는 인덱스가 있으면, 그 인덱스를 타고 내려가서 해당 레코드에만 락을 걸어요. 인덱스가 없으면 풀스캔을 하는데, 스캔하는 동안 지나가는 모든 레코드에 락을 걸어요. 조건에 맞는지는 그다음에 판단해요. 한 행을 잠그려던 쿼리가 결과적으로 테이블 전체를 멈춰 세우는 거예요.

여기서 격리 수준이 갈라요. REPEATABLE READ 에서는 조건에 안 맞는 행의 락도 트랜잭션이 끝날 때까지 유지돼요. READ COMMITTED 에서는 조건에 안 맞는 행의 락을 스캔 직후에 풀어 줘요. 그래서 같은 코드가 격리 수준에 따라 전혀 다르게 동작해요. MySQL 기본값은 REPEATABLE READ 라 최악의 경우를 밟습니다.

인덱스를 만들어도 끝이 아니에요. 락의 범위는 인덱스의 종류가 정해요. 기본키로 찾으면 해당 레코드만 잠기지만, 유니크하지 않은 보조 인덱스면 넥스트키 락이 걸려요. 레코드와 그 앞의 간격까지 함께 잠그는 거라, 그 범위로 들어오려는 INSERT 까지 막혀요.

코드로 보면

같은 쿼리, 다른 결과:

· product_code 에 인덱스가 없는 경우
BEGIN;
✕ SELECT * FROM stock
✕ WHERE product_code = 'A-100'
✕ FOR UPDATE;
✕ -- 스캔하며 지나간 전 행이 잠긴다

✓ CREATE INDEX idx_stock_code ON stock(product_code);

· 기다릴 수 없는 작업이면 즉시 실패시킨다
✓ SELECT * FROM stock WHERE id = 42
✓ FOR UPDATE NOWAIT;        -- 못 잡으면 바로 에러

· 큐에서 하나씩 집어가는 패턴이면
✓ SELECT * FROM jobs WHERE status='ready'
✓ ORDER BY id LIMIT 1
✓ FOR UPDATE SKIP LOCKED;   -- 잠긴 건 건너뛴다

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

없는 행을 FOR UPDATE 하면 갭 락이 걸려요. 아무도 그 자리에 새 행을 넣지 못해요. "재고 행이 없으면 만든다" 같은 코드가 동시에 두 곳에서 돌면 서로의 갭 락에 걸려 교착이 나요. 이건 인덱스를 제대로 만들어도 남는 문제예요.

innodb_lock_wait_timeout 기본값은 50초예요. 이게 실전에서 큰 의미를 가져요. 락을 못 잡은 요청이 50초를 기다리는 동안 앱 스레드와 커넥션을 하나씩 붙잡고 있거든요. 초당 100건이 들어오는 API 라면 50초 안에 5천 개의 요청이 대기에 쌓여요. DB 가 죽기 전에 앱이 먼저 죽어요. 그래서 이 값은 대개 3~5초로 내려야 해요.

교착은 다르게 처리돼요. InnoDB 는 교착을 감지하면 즉시 한쪽을 죽여요(Deadlock found when trying to get lock). 타임아웃을 기다리지 않아요. 그래서 로그에 교착이 보이면 오히려 다행이에요 — 락 대기 타임아웃보다 훨씬 빨리 끝나니까요.

PostgreSQL 은 구조가 달라요. 튜플 자체에 락을 걸고, 기본적으로 갭 락이 없어요. 그래서 이 편의 증상이 그대로 재현되지는 않아요. 다만 인덱스가 없으면 순차 스캔을 하면서 조건에 맞는 행을 잠그는 건 같고, 스캔 자체가 느려서 락 보유 시간이 길어지는 문제는 똑같이 생겨요.

처방

1. 잠글 컬럼에는 반드시 인덱스를 두세요. 그리고 가능하면 기본키로 잠그세요. 애플리케이션에서 조회는 조건으로 하되 잠금은 id 로 다시 하는 패턴이 안전해요.
2. innodb_lock_wait_timeout 을 짧게 잡으세요. 기본 50초는 사실상 "앱이 먼저 죽을 때까지 기다린다"예요. 3~5초면 대부분의 정상 경합은 통과하고 비정상만 걸러져요.
3. 기다릴 수 없는 작업이면 NOWAIT 로 즉시 실패시키세요. 사용자에게 "잠시 후 다시 시도해 주세요"를 빨리 주는 게, 30초 기다리게 하고 실패하는 것보다 나아요.
4. 작업 큐를 DB 로 만든다면 SKIP LOCKED 를 쓰세요. 잠긴 건 건너뛰고 다음 걸 집어가니까 워커끼리 줄 서지 않아요.
5. 락 획득 순서를 고정하세요. 여러 행을 잠가야 하면 항상 같은 순서로(예: id 오름차순) 잠그세요. 순서가 뒤섞이는 것만으로 교착이 나요.
6. 트랜잭션 안에서 잠근 뒤에 외부 호출을 하지 마세요. 결제 API 를 부르는 3초 동안 그 행은 계속 잠겨 있어요. 그 3초가 초당 100건짜리 API 에서는 300개의 대기를 만듭니다.
7. 재고 차감처럼 경합이 뻔한 자리는 락 대신 조건부 UPDATE 로 푸는 걸 먼저 검토하세요. UPDATE stock SET qty = qty - 1 WHERE id = :id AND qty >= 1 한 문장이면 락을 명시적으로 잡지 않고도 원자성이 보장되고, 영향받은 행 수로 성공 여부를 알 수 있어요. 잠글 필요가 없으면 안 잠그는 게 제일 좋아요.

지금 확인해 보세요

지금 누가 무엇을 잠그고 있나:

· MySQL — 대기 중인 락 관계
SELECT * FROM performance_schema.data_lock_waits;

· 누가 누구를 막는지 사람이 읽을 수 있게
SELECT r.trx_id AS waiting, r.trx_query AS waiting_q,
       b.trx_id AS blocking, b.trx_query AS blocking_q
  FROM performance_schema.data_lock_waits w
  JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id
  JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id;

· 최근 교착의 전문
SHOW ENGINE INNODB STATUS\G

· 락 범위는 실행 계획이 정한다 — 계획부터 본다
✓ EXPLAIN SELECT * FROM stock
✓ WHERE product_code = 'A-100' FOR UPDATE;
✕ type: ALL 이면 전 행이 잠긴다

핵심 한 줄락의 범위를 정하는 건 WHERE 가 아니라 인덱스다.