PostgreSQL: Skip Locked

※ PostgreSQL: Skip Locked.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 동시성이 필요한 환경에서 Row Lock 회피로 사용하는 Skip Locked에 대한 내용입니다.

 

이 포스팅을 왜 하냐면 티켓팅 실패해서 합니다..

---

1. SKIP LOCKED?

SKIP LOCKED는 ANSI SQL 표준은 아니며 PostgreSQL, Oracle, MySQL 등에서 확장 기능으로 지원합니다.

 

PostgreSQL에서 SELECT ... FOR UPDATE로 특정 행을 잠그면, 다른 세션은 그 행이 풀릴 때까지 기다립니다.

하지만 SKIP LOCKED 옵션을 붙이면 이미 잠긴 행은 건너뛰고 바로 다음 후보 행을 반환합니다.

즉 대기/충돌 없이 안전하게 다른 데이터를 처리할 수 있습니다. (이미 다른 트랜잭션이 잡은 row를 건너뛰고 사용 가능한 row만 처리)

 

쉽게 말하면 다른 트랜잭션이 잡은 row는 건너뛰고, 즉시 처리 가능한 row만 가져오는 잠금 회피 기능입니다.

 

+ 더 쉽게 말하면 경쟁 중인 row를 무시하고 사용 가능한 row만 선점한다고 생각하시면 됩니다.

++ 친구로는 NOWAIT이 있습니다. (NOWAIT은 선택된 행에 대해 즉시 락을 획득할 수 없다면, 기다리지 않고 오류를 발생시킵니다.)

---

2. 사용 시나리오

2.1 선착순 콘서트/페스티벌 티켓 예매

• 동시에 수십만 명이 선착순으로 좌석 배정을 하는 콘서트 예매를 하려고 접속
• 좌석 테이블(seats)에서 status = 'AVAILABLE'인 좌석을 가져올 때, FOR UPDATE SKIP LOCKED 사용
• A 사용자가 이미 잡은 좌석은 B 사용자에게는 자동으로 제외
• 덕분에 두 명이 같은 좌석을 동시에 배정받는 "더블 부킹 사고 방지"

WITH s AS (
  SELECT seat_id
  FROM seats
  WHERE status = 'AVAILABLE'
  ORDER BY seat_id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE seats
SET status = 'HOLD', user_id = :user_id
FROM s
WHERE seats.seat_id = s.seat_id
RETURNING seats.*;

Session1

Session2

Session1에서 먼저 seat_id=1에 락을 걸었고, Session2는 락이 걸린 seat_id =1을 스킵하고 seat_id=2를 홀드 했습니다.

 

+ 사용자가 특정 좌석을 직접 지정해서 예매하는 서비스라면, NOWAIT이 더 맞습니다.

---

2.2 한정판 상품 판매 (Flash Sale)

• “한정 수량 1,000개” 상품을 동시에 수만 명이 담을 때
• 상품 재고 테이블(inventory)에서 FOR UPDATE SKIP LOCKED로 처리
• 이미 다른 사람이 결제 중인 재고는 스킵하고, 남은 재고만 선점
• 서버가 락 대기 때문에 느려지지 않고, 초고속으로 “매진 처리” 가능

WITH stock AS (
  SELECT id
  FROM inventory
  WHERE product_id = 101 AND status = 'AVAILABLE'
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE inventory
SET status = 'PENDING', reserved_by = :user_id
FROM stock
WHERE inventory.id = stock.id
RETURNING inventory.*;

---

2.3 물류/배송 작업 큐

• 수만 건의 배송 작업을 여러 워커 서버가 병렬로 처리
• 워커마다 FOR UPDATE SKIP LOCKED LIMIT n 쿼리를 실행
• 이미 다른 워커가 집어간 행은 건너뛰므로, 충돌 없이 일감이 자동 분배

---

2.4 광고 클릭 로그 집계

• 클릭 로그 테이블에서 status = 'NEW'인 데이터를 워커들이 동시에 가져와 집계 처리
• 한 워커가 특정 구간을 집어간 동안, 다른 워커는 자동으로 다른 구간을 가져가므로 중복 처리 없음

---

시나리오 예시가 점점 짧아지는 것 같이 보이는 건 기분 탓입니다.

오늘은 여기까지~

 

SELECT