SELECT FOR UPDATE SKIP LOCKED | Notes

SELECT ... FOR UPDATE는 읽은 행에 잠금을 걸어, 트랜잭션이 끝날 때까지 다른 트랜잭션이 그 행을 고치거나 같은 방식으로 잠그지 못하게 하는 쿼리다. 여기에 SKIP LOCKED를 붙이면 이미 잠긴 행을 만났을 때 기다리지 않고 없는 것처럼 건너뛴다. 둘을 합치면 DB 테이블 하나를 작업 큐로 쓰면서, 워커 여러 대가 동시에 꺼내도 서로 다른 작업을 받게 만들 수 있다.

MySQL은 8.0.1부터 SKIP LOCKED와 NOWAIT를 지원한다. PostgreSQL은 9.5부터 지원한다.

공식 문서: MySQL 8.4, Locking Reads

큐에서 작업 하나 꺼내기

jobs 테이블에 처리할 작업이 한 줄씩 쌓여 있다고 하자. 워커는 아래 쿼리로 다음 작업 하나를 잠그고 읽는다.

sql

-- 꺼낼 수 있는 대기 작업 중 가장 오래된 하나를 잠그고 읽음
SELECT * FROM jobs
WHERE status = 'PENDING'
  AND available_at <= NOW()
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

WHERE status = 'PENDING': 아직 아무도 가져가지 않은 작업만 고른다.
AND available_at <= NOW(): 꺼내도 되는 시각이 된 작업만 고른다. available_at은 '이 시각 전에는 꺼내지 말라'는 값이다. 새 작업은 넣는 순간의 시각을 넣어 바로 꺼낼 수 있게 하고, 외부 한도에 막혀 미뤄야 하는 작업은 이 값을 미래로 바꾼다. 이 조건이 없으면 막힌 작업만 계속 꺼냈다 놓느라 뒤의 작업 차례가 오지 않는다.
ORDER BY id: 먼저 들어온 작업부터 꺼낸다.
LIMIT 1: 맨 앞 하나만 가져온다.

FOR UPDATE의 잠금

FOR UPDATE는 '곧 고칠 목적으로 읽으니 내가 끝낼 때까지 끼어들지 말라'는 뜻이다. 잠금은 컬럼에 저장되는 값이 아니라 DB가 행에 붙였다 떼는 표시라서 SELECT로 조회해도 보이지 않는다.

잠금이 풀리는 시점은 행을 고친 순간이 아니라 트랜잭션이 끝나는 순간(커밋 또는 롤백)이다. 그래서 이 쿼리는 반드시 트랜잭션 안에서 실행해야 한다. 자동 커밋 모드에서 단독으로 실행하면 쿼리 하나가 곧 트랜잭션 하나라서, 결과를 받는 순간 잠금도 풀린다.

FOR UPDATE가 없으면 워커 두 대가 같은 순간에 같은 PENDING 행을 읽고, 같은 작업을 두 번 처리한다.

SKIP LOCKED가 하는 일

FOR UPDATE만 있으면 뒤에 온 워커는 앞 워커가 잡은 행의 잠금이 풀릴 때까지 기다린다. 그동안 다른 행이 비어 있어도 줄을 서기 때문에, 워커를 여러 대 띄워도 꺼내는 단계가 한 줄로 처리된다.

SKIP LOCKED를 붙이면 뒤에 온 워커는 잠긴 행을 건너뛰고 그다음 행을 잡는다.

작업 1, 2를 넣고 두 트랜잭션에서 같은 쿼리를 동시에 실행하면, 한쪽은 1번을 받고 다른 쪽은 기다리지 않고 바로 2번을 받는다.

SKIP LOCKED로 읽은 결과는 잠긴 행이 빠진 일부라서, 테이블 전체를 정확히 봐야 하는 일반 조회에는 쓰지 않는다. MySQL 문서도 이 옵션을 큐처럼 여러 소비자가 행을 나눠 가져가는 용도로 설명한다.

잠금은 짧게, 상태 컬럼이 이어받기

작업 처리에 몇 초가 걸린다면, 잠금을 처리가 끝날 때까지 쥐고 있으면 안 된다. 잠금을 유지하려면 트랜잭션을 열어 두어야 하고, 열린 트랜잭션은 DB 커넥션을 붙잡기 때문이다. 이 문제는 @Transactional과 트랜잭션 범위에서 다뤘다.

그래서 꺼내는 트랜잭션 안에서 상태를 바로 RUNNING으로 바꾸고 커밋한다.

sql

-- 같은 트랜잭션에서 상태를 바꾼 뒤 바로 커밋함
UPDATE jobs
SET status = 'RUNNING', attempts = attempts + 1, started_at = NOW()
WHERE id = :id;

꺼내는 짧은 순간: 행 잠금이 다른 워커를 막는다.
커밋 뒤 처리하는 몇 초: status = 'RUNNING'이 첫 쿼리의 WHERE status = 'PENDING'에 걸려 다른 워커가 다시 고르지 않는다.

잠금이 '꺼내는 순간'을 맡고 상태 컬럼이 '처리 중'을 맡는 식으로 역할을 나눈 구조다. 처리가 끝나면 다시 짧은 트랜잭션으로 DONE이나 FAILED로 바꾼다.

이 구조에서는 처리 도중 워커가 죽으면 행이 RUNNING에 머문다. 잠금이었다면 트랜잭션이 끊기며 풀렸겠지만, 상태 컬럼은 누가 바꾸기 전까지 그대로다. 그래서 started_at이 오래된 RUNNING 행을 주기적으로 PENDING으로 되돌리는 정리 작업이 함께 필요하다.

인덱스

InnoDB는 쿼리를 처리하며 훑은 인덱스 레코드에 잠금을 건다. WHERE 조건에 맞는 인덱스가 없어서 테이블 전체를 훑으면, 조건과 상관없는 행까지 잠길 수 있다. 큐 테이블에는 꺼내기 조건에 맞춘 인덱스를 둔다.

sql

-- status로 거르고 available_at으로 범위를 좁히는 꺼내기 쿼리용
CREATE INDEX idx_jobs_status_available_at ON jobs (status, available_at);

공식 문서: MySQL 8.4, Locks Set by Different SQL Statements in InnoDB

Spring Data JPA에서 쓰기

JPQL에는 SKIP LOCKED 문법이 없어서 네이티브 쿼리로 쓴다. 상태를 바꾸는 쿼리에는 @Modifying을 붙인다.

kotlin

interface JobRepository : JpaRepository<Job, Long> {
    // 트랜잭션 안에서 불러야 잠금이 유지됨
    @Query(
        value = """
            SELECT * FROM jobs
            WHERE status = 'PENDING' AND available_at <= NOW()
            ORDER BY id LIMIT 1
            FOR UPDATE SKIP LOCKED
        """,
        nativeQuery = true,
    )
    fun findNextPendingForUpdate(): Job?
 
    @Modifying
    @Query(
        value = "UPDATE jobs SET status = 'RUNNING', attempts = attempts + 1, started_at = NOW() WHERE id = :id",
        nativeQuery = true,
    )
    fun markRunning(id: Long): Int
}

워커는 꺼내기와 상태 변경만 짧은 트랜잭션으로 묶고, 실제 처리는 트랜잭션 밖에서 한다.

kotlin

fun runNext(): Boolean {
    // 꺼내서 RUNNING으로 바꾸는 동안만 잠금을 잡고 바로 커밋함
    val job = transaction.execute {
        jobRepository.findNextPendingForUpdate()?.also { jobRepository.markRunning(it.id!!) }
    } ?: return false
 
    // 트랜잭션 없이 처리함; 느린 외부 호출을 기다려도 커넥션을 잡지 않음
    val ok = process(job)
 
    // 결과만 다시 짧은 트랜잭션으로 기록함
    transaction.executeWithoutResult {
        if (ok) jobRepository.markDone(job.id!!) else jobRepository.markFailed(job.id!!)
    }
    return true
}

markRunning 뒤에 같은 트랜잭션에서 job 객체를 다시 읽으면 상태가 여전히 PENDING으로 보인다. 직접 쓴 UPDATE는 영속성 컨텍스트에 반영되지 않기 때문이며, 자세한 내용은 JPA 쓰기 시점과 영속성 컨텍스트에 정리했다.

작업을 큐에 넣는 쪽을 데이터 저장과 같은 트랜잭션으로 묶는 방법은 Transactional Outbox에서 다룬다. 생산자와 소비자를 나누는 일반 구조는 Producer-Consumer 패턴에 있다.

큐에서 작업 하나 꺼내기

FOR UPDATE의 잠금

SKIP LOCKED가 하는 일

잠금은 짧게, 상태 컬럼이 이어받기

인덱스

Spring Data JPA에서 쓰기

연결된 노트 5