MySQL :: SKIP LOCKED로 쿠폰 중복 발급 막기

TL;DR

• 쿠폰 발급 시스템에서 동시에 여러 사용자가 쿠폰을 요청할 경우 갱신 손실(Lost Update) 문제가 우려되었습니다.
• SELECT ... FOR UPDATE를 사용했지만, 잠긴 행 대기로 인해 Lock wait timeout exceeded 오류가 발생했습니다.
• SKIP LOCKED를 적용하여 잠긴 행을 건너뛰고 사용 가능한 쿠폰을 SELECT 하도록 개선했습니다.
• 이를 통해 대기 시간을 줄이고 동시성을 향상시켰습니다.

---

1. 들어가며

동시성이 중요한 기능을 개발할 때 예상치 못한 문제가 발생할 수 있습니다. 특히, 다수의 사용자가 동시에 특정 데이터를 수정하거나 가져가야 하는 경우, 데이터 잠금과 트랜잭션 대기 문제가 빈번하게 발생합니다.

업무 중 쿠폰 발급 기능을 개발하면서 이와 같은 문제를 경험했고,
해결 과정에서 SKIP LOCKED라는 기능을 알게 되었습니다.
현재까지도 유용하게 사용 중인 키워드기에 정리해서 작성하고자 합니다.

---

2. 문제 상황

저는 프로젝트에서 쿠폰 발급 기능을 개발하고 있었습니다.
쿠폰 발급 방식은 사전에 생성된 쿠폰 코드를 DB에 저장해두고, 사용자가 발급 요청할 때 쿠폰 row 속 user_id 컬럼값을 업데이트하는 방식을 사용했습니다.

처음엔 단순히 생각했으나, 여러 사용자가 동시에 쿠폰을 요청할 때가 문제였습니다.
동일한 행을 수정하려고 시도하면서 갱신 손실(Lost Update) 문제가 우려되었습니다.

이 경우 두 사용자가 사실 상 동일한 쿠폰을 사용하게 되고, 먼저 쿠폰을 사용한 한 명만이 혜택을 보게 됩니다. 크리티컬한 상황이기에 반드시 막아야했습니다.

초기에는 SELECT ... FOR UPDATE 로 비관락(Pessimistic Lock)을 사용해서, 행에 잠금을 걸어 해결하려 했습니다.

예시 쿼리:

SELECT * FROM coupons WHERE user_id IS NULL LIMIT 1 FOR UPDATE;

하지만,

• 다른 트랜잭션에서 동시에 동일한 쿼리를 실행하면,Lock wait timeout exceeded 오류가 발생했습니다.
• 잠긴 행이 열리길 기다리다 timeout 이 난 것입니다.

사실 굳이 잠금 대기를 해야 할 이유가 없습니다.
이미 발급 진행 중인 쿠폰은 건너 뛰고, 주인이 없는 쿠폰을 하나 가져오기만 하면 됩니다.

DBA 분께 자문한 결과, SKIP LOCKED를 사용하면 이 문제를 해결할 수 있음을 알게 되었습니다.

---

3. SKIP LOCKED란?

MySQL 8.0.1부터 제공되는 SKIP LOCKED는 잠긴 행을 건너뛰고 잠금이 걸리지 않은 행을 선택하도록 합니다. 이를 통해 트랜잭션 대기 시간을 줄이고, 경합이 심한 테이블에서도 효율적으로 데이터를 조회할 수 있습니다.

MySQL 공식문서에서는 이게 사용될 수 있는 가장 대표적인 예시로 티켓팅 예매창을 소개했습니다.

좌석 예매 시 이미 결제 진행 중인 곳은 보통 누를 수 없게 되어있습니다.
이 경우 보통은 메타데이터(미선택/진행중/결제완료 등)를 두고, 예매 진행 시 ’진행중‘ 상태로 업데이트해서 다른 사람의 진입을 막습니다.

이 중 ‘진행중’ 상태처럼 race condition 을 막는 용도의 상태값은 SKIP LOCKED 사용으로 대체 가능합니다.

3.1. 기본 사용법

CREATE TABLE t (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(255) NOT NULL,
    locked BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO t (value, locked) VALUES
    ('row1', FALSE),
    ('row2', FALSE),
    ('row3', FALSE);

트랜잭션 실행 시 FOR UPDATE SKIP LOCKED를 사용하면 잠긴 행을 건너뛰고 사용 가능한 행만 조회할 수 있습니다.

START TRANSACTION;
SELECT * FROM t WHERE locked = FALSE LIMIT 1 FOR UPDATE SKIP LOCKED;

3.2. SKIP LOCKED 동작 방식

• SELECT ... FOR UPDATE SKIP LOCKED → 잠긴 행을 무시하고 사용 가능한 행만 조회
• UPDATE ... WHERE ... SKIP LOCKED → 잠긴 행을 건너뛰고 업데이트 실행

3.3 SKIP LOCKED vs NOWAIT

SKIP LOCKED 외에NOWAIT 옵션도 있습니다.

기능	동작 방식
SKIP LOCKED	잠긴 행을 건너뛰고 즉시 사용 가능한 행만 조회합니다.
NOWAIT	잠긴 행을 만나면 대기하지 않고 즉시 오류를 반환합니다.

예제:

SELECT * FROM coupons WHERE user_id IS NULL LIMIT 1 FOR UPDATE NOWAIT;

• 만약 다른 트랜잭션이 쿠폰을 잠그고 있다면 즉시 Lock wait timeout 오류를 발생시킵니다.
• 즉, NOWAIT는 잠긴 행을 기다리지 않고, 바로 실패하도록 만드는 방식입니다.

3.4 테이블 적용

행 단위 뿐만 아니라 테이블에도 적용 가능합니다.
여러 테이블이 포함된 쿼리의 경우, 특정 테이블에만 SKIP LOCKED 옵션이 적용되도록 할 수 있습니다.

예시:

START TRANSACTION;
SELECT seat_no, row_no, cost
FROM seats s JOIN seat_rows sr USING ( row_no )
WHERE seat_no IN ( 3,4 ) AND sr.row_no IN ( 5,6 )
AND booked = 'NO'
FOR UPDATE OF s SKIP LOCKED;

---

4. SKIP LOCKED 적용

쿠폰 발급 기능의 동시성 문제를 해결하기 위해 SKIP LOCKED를 적용했습니다.

SELECT * FROM coupons WHERE user_id IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED;

개선된 플로우를 그림으로 정리해보면 아래와 같습니다.

위 다이어그램에서 사용자 1의 트랜잭션이 먼저 실행되면서 잠금이 발생하지만, SKIP LOCKED를 사용한 사용자 2는 잠긴 행을 건너뛰고 사용 가능한 다른 쿠폰을 바로 선택합니다.
즉 두 트랜잭션이 같은 쿠폰을 발급 받을 가능성은 없어집니다.

---

5. 적용 결과

• SKIP LOCKED 적용으로 동시성 이슈가 해결되고
• 쿠폰 발급 기능의 Latency도 30% 정도 개선되었습니다.

---

6. Reference

https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/