[MySQL]NOWAIT & SKIP LOCKED: 데이터베이스를 큐처럼 쓸 수 있다고?

Introduction

오랜만에 글 하나 쓰려고 예전에 노션에 정리해뒀던 문서 하나를 가져와봤다. 지금은 top priority에 해당하는 프로젝트에 참여하느라 잠시 중단된 작업인데, 현재 사용 중인 배치 프로세스를 더 높은 처리량 & 원활한 가용성을 보장하도록 개선하는 작업이었다. 배치 잡을 돌릴 때 건별로 이력(pending/processing/done/failed/...)을 남겨야 중간에 이런 저런 이유로 실패하더라도 주기적으로 상태를 체크해 해당 작업을 다시 실행할 수 있지 않겠나. 그런데 높은 처리량을 보장하려면 병렬로 실행해야 할텐데, 동시에 여러 인스턴스가 하나의 DB에 접근해서 히스토리를 가져와야 하는 상황이 발생하게 된다. 이때, 가져온 애들에 대해서는 lock을 걸어줘야 할 것이다. 문제는 동시성에 대한 성능 보장 역시 필요하다는 것. 어떻게 하면 좋을까? 이때 제안받은 방식이 바로 SKIP LOCKED이다.

 

NOWAIT & SKIP LOCKED

MySQL 8.0.1 버전 이상부터는 새로 도입된 NOWAIT와 SKIP LOCKED 옵션을 사용할 수 있다. 이게 무엇이길래?

지금까지 MySQL에서 lock의 경우, 어떤 트랜잭션이 레코드를 잠그고 있다면 다른 트랜잭션은 멀찌감치 서서 해당 잠금이 해제될 때까지 멀뚱멀뚱 기다려야만 했다. 때로는 해당 잠금이 일정 시간 이상 지나면 기다리던 트랜잭션은 잠금 획득 실패 에러 메시지를 받을 수도 있다.

그런데 NOWAIT 옵션을 사용하게 되면,

• SELECT 쿼리가 해당 레코드에 대해 즉시 잠금을 획득했다면 NOWAIT 옵션이 없을 때와 동일하게 실행된다.
• 그런데 해당 레코드가 다른 트랜잭션에 의해 잠겨진 상태라면 에러를 반환하면서 쿼리가 즉시 종료된다. 말 그대로 기다리지 않는(NO WAIT)것.

mysql > SELECT * FROM employess WHERE emp_no=10001 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted
    because lock(s) could not be acquired immediately and NOWAIT is set.

그럼 SKIP LOCKED 옵션은 무엇이냐, SELECT하려는 레코드가 다른 트랜잭션에 의해 이미 잠겨진 상태라면 에러를 반환하지 않고, 잠겨 있는 레코드는 무시한 채 잠금이 걸리지 않은 레코드만 가져온다. NOWAIT에 비해 훨씬 중요하니 공식 문서에서 소개하는 예시를 가져와봤다. 스포츠 경기를 직관하기 위해 표를 예매하는 시나리오를 생각해보자.

예약 프로세스로 살펴보는 SKIP LOCKED

경기 티켓을 예매할 때, 보통 경기장의 어느 구역에 앉고 싶은지 먼저 선택하라는 메시지가 뜬다. 그 다음에 이미 예약된 좌석은 회색으로, 빈 좌석은 파란색으로, 현재 선택한 좌석은 흰색으로 화면에 떠 있는 것을 볼 수 있다. 적어도 몇 분 동안 내가 이미 골라놓은 흰 색 좌석에 대해서는 안전한 상태이니 안심하고 예약할 수 있다.

예매를 끝까지 완료(혹은 중간에 포기)할 때까지 예약 시스템에서 내가 골라놓은 저 두 흰 좌석은 다른 사용자들 화면에서는 임시로 보류(선택하지 못하게)되어있을 것이다.

이는 다른 트랜잭션에서도 마찬가지. 두 개의 흰 좌석 옆으로 빨갛게 동그라미쳐진 저 회색 좌석들 역시 다른 누군가가 예매하려고 선택해놓은 좌석들이기에 해당 트랜잭션을 제외한 다른 트랜잭션에서는 pending 상태로 보일 것이다.

사실 MySQL에서 각 좌석과 관련된 상태 데이터(매진/주문 가능/보류 중)를 관리하는 방법이 없는 것은 아니었다. 다만, 8.0.1에서 소개하는 SKIP LOCKED로 훨씬 쉬워졌다는 것.

8.0.1 버전부터 SKIP LOCKED를 도입하면서, 테이블의 행을 비결정적으로 읽으면서 이미 잠겨있는 행이 있다면 이를 건너뛸 수 있게 되었다. 여기서 비결정적이라 함은 말 그대로 정해지지 않았다는 것인데, 예시를 살펴보자. 아래 SQL 문은 좌석 예매 상태를 표시하는 테이블이다. 100개의 샘플 좌석을 만들어 둔 상태이다.

CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES', 'NO') DEFAULT 'NO'
);
 
# generate 100 sample rows
INSERT INTO seats (seat_no)
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<100
)
SELECT * FROM my_cte;

만약 내가 2번 좌석부터 10번 좌석 사이에 2개의 좌석을 예매하고 싶다면 아래와 같이 하면 된다.

START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 10 AND booked = 'NO' LIMIT 2
FOR UPDATE SKIP LOCKED;

 이 경우 2번 좌석부터 10번 좌석 사이에 2개의 여분 좌석이 있다면 이를 반환할 것이다. 이때, 해당 좌석은 구문을 날린 시점에 정해지지 않는다. 3, 4번 좌석이 비어있다면 3번과 4번이, 5, 6번이 비어있다면 이 두 좌석을 건네줄 것이다. 즉, 이미 결정되어 있어서 건드리지 못하는 행은 제외하고서 결과를 반환하기에 비결정적이라는 표현을 쓰는 것.

그렇게 두 좌석을 받고 나면 아래와 같이 명령어를 던지거나 

UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 2 AND 3 
COMMIT;

혹은 모든 좌석이 점유되어 있어 롤백을 당할 수도 있다. 이렇게 FOR UPDATE SKIP LOCKED는 반환받은 집합에서 잠긴 행을 그대로 건너뛴다. 그리고 건네주는 시점에서 FOR UPDATE로 인해 아직 잠겨있지 않은 행은 해당 트랜잭션에 의해 잠기게 된다.

결론

• 대용량 트래픽이 몰리는 상황에서 SKIP LOCKED는 매우 유용하게 사용 가능한데, 특히 멀티 스레딩 환경에서 여러 워커가 저마다 행을 점유해야 하는 상황(ex. 표 예매 등)에 특화되어 있다. 즉, 큐로써 사용이 가능하다는 것.
• NOWAIT은 잠겨 있는 행에 대해서 전혀 필요로 하지 않을 때 혹은 비즈니스 로직이 잠겨 있는 행에 대해서 건드리는 게 흐름상 말이 안되는 경우에 사용할 수 있다.
• 같은 테이블 내에서 SKIP LOCKED와 NOWAIT을 사용하지 않는 한, 동일한 쿼리 내에서 SKIP LOCKED와 NOWAIT을 혼합해 쓸 수 있다.

Reference

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