예매 트랜잭션의 교착과 배치 잠금
온라인 공연 예매 시스템은 MySQL 8.0 InnoDB를 사용한다. 좌석 1,200석을 3개 공연장에 나누어 판매하며, 오픈 10분 동안 초당 400건의 예매 요청이 몰린다. 최근 두 가지 장애가 발생했다. 첫째, 좌석 상태를 갱신하는 배치가 status='AVAILABLE' 조건으로 1만 건을 UPDATE하면서 인덱스가 없는 status 컬럼을 사용해 많은 행을 잠갔고, 일반 예매의 lock wait timeout이 50초를 넘었다. 둘째, 예매 트랜잭션이 좌석 행을 먼저 잠그고 쿠폰 행을 나중에 잠그는 경우와 그 반대 순서로 잠그는 경우가 섞여 deadlock 1213이 하루 37건 발생했다. 예매 로직은 SELECT ... FOR UPDATE를 사용하고 격리 수준은 REPEATABLE READ다. 경영진은 좌석 중복 판매를 절대 허용하지 않으면서 오픈 시간 처리량을 20% 이상 높이고, 매일 02시에 3분 이상 걸려도 되는 좌석 판매 현황 보고서를 같은 DB에서 읽어야 한다고 요구한다.
1. SELECT ... FOR UPDATE를 사용했는데도 deadlock이 발생한 이유를 잠금 순서와 트랜잭션 경계로 설명하고, 즉시 적용 가능한 완화책 3가지를 제시하시오.
Read the worked answer
FOR UPDATE는 같은 행에 대한 동시 갱신을 직렬화하지만, 서로 다른 자원을 다른 순서로 잡으면 교착이 된다. T1이 좌석 A를 잠그고 쿠폰 C를 기다리는 동안 T2가 C를 잠그고 A를 기다리면 hold-and-wait 순환이 성립한다. InnoDB는 이를 감지해 한쪽을 1213으로 롤백한다. REPEATABLE READ에서는 갭 락까지 잡혀 교착 가능성이 더 커질 수 있다.
완화책은 첫째, 모든 트랜잭션이 좌석→쿠폰처럼 고정된 순서로 잠그게 한다. 둘째, 쿠폰 검증과 외부 API 호출을 트랜잭션 밖으로 빼서 잠금 보유 시간을 줄인다. 셋째, 1213과 1205에 대해 짧은 backoff를 두고 재시도하며, 좌석에는 (performance_id, seat_id) unique 제약과 조건부 UPDATE를 함께 사용해 중복 판매를 막는다. 재시도만 추가하고 잠금 순서를 고치지 않으면 교착은 반복된다.
2. 배치 UPDATE가 일반 예매를 오래 막는 원인을 인덱스 관점에서 설명하고, 중복 판매를 막으면서 배치와 예매를 분리하는 스키마/처리 방안을 제시하시오.
Read the worked answer
status에 인덱스가 없으면 InnoDB는 조건에 맞는 행을 찾기 위해 넓은 범위를 스캔하고, REPEATABLE READ에서는 스캔한 레코드와 갭에 next-key lock을 잡는다. 그 결과 실제로 갱신하지 않은 행까지 잠겨 일반 예매가 대기한다.
해결은 status와 performance_id를 포함한 복합 인덱스를 만들고, 배치는 primary key 범위로 500건씩 나누어 커밋한다. 예매 쪽은 UPDATE seat SET status='SOLD' WHERE performance_id=? AND seat_id=? AND status='AVAILABLE'처럼 조건부 갱신을 사용하고 affected rows가 0이면 선점 실패로 처리한다. 예매 이력 테이블에는 (performance_id, seat_id) unique 제약을 둬 최종 방어선으로 삼는다. 배치 세션만 READ COMMITTED로 낮춰 갭 락을 줄일 수 있지만, 이는 보조 수단이며 인덱스와 짧은 트랜잭션이 우선이다.
3. 02시 보고서 쿼리가 3분 이상 실행되어도 된다는 요구를 만족시키면서 예매 트랜잭션에 미치는 영향을 최소화하려면 격리 수준과 읽기 경로를 어떻게 선택해야 하는가? REPEATABLE READ, READ COMMITTED, SERIALIZABLE을 비교하여 판단하시오.
Read the worked answer
보고서는 최신값보다 일관된 시점의 스냅샷이 중요하므로 SERIALIZABLE은 부적합하다. SERIALIZABLE은 읽기에도 공유 락을 걸어 예매 갱신을 막을 수 있다. READ COMMITTED는 문장마다 새 스냅샷을 보므로 3분짜리 다중 쿼리 보고서에서 앞뒤 값이 달라질 수 있다. REPEATABLE READ의 consistent read는 쓰기를 직접 막지 않으므로 같은 DB에서도 가장 무난하지만, 긴 읽기 트랜잭션이 undo를 오래 보관해 history list를 키울 수 있다.
가장 좋은 선택은 읽기 전용 replica나 분석용 스냅샷에서 보고서를 실행하는 것이다. 같은 primary를 써야 한다면 REPEATABLE READ로 START TRANSACTION WITH CONSISTENT SNAPSHOT을 열고 읽기 전용으로 제한하며, 3분을 넘기지 않게 timeout과 모니터링을 건다. 보고서를 여러 번 나눠 실행해야 한다면 단일 트랜잭션으로 묶어 스냅샷 일관성을 유지한다.