[MySQL] NOWAIT와 SKIP LOCKED 활용: 대기 없는 작업 큐 구현과 한계

데이터베이스를 다루다 보면 여러 개의 프로세스가 같은 작업 테이블을 두고 경쟁하는 상황을 자주 만나게 됩니다. 흔히 "작업 큐(Job Queue)"라고 부르는 구조인데, 배치 워커 여러 대가 동시에 떠서 대기 중인 작업(PENDING) 행을 하나씩 가져가 처리하는 패턴입니다. 이때 가장 먼저 떠올리는 방법이 SELECT ... FOR UPDATE로 행을 잠근 뒤 상태를 변경하는 것인데, 워커 수가 늘어나는 순간 문제가 생깁니다. 한 워커가 이미 잠근 행을 다른 워커가 FOR UPDATE로 또 조회하면, 뒤따라온 워커는 잠금이 풀릴 때까지 그대로 대기(Lock Wait)하게 됩니다. 워커가 많아질수록 대기 행렬이 길어지고, 결국 innodb_lock_wait_timeout에 걸려 타임아웃 에러를 뿜어내는 워커가 늘어납니다.

이 문제를 해결하기 위해 MySQL 8.0부터 InnoDB가 지원하는 것이 바로 FOR UPDATE NOWAIT와 FOR UPDATE SKIP LOCKED입니다. 이번 글에서는 두 옵션의 정확한 동작 원리, 작업 큐에 적용하는 구체적인 SQL 패턴, 그리고 실무에서 반드시 알아야 할 기아 현상(Starvation)과 복제 관련 한계까지 깊이 있게 다루어 보겠습니다.

1. 잠금 대기가 작업 큐에서 문제가 되는 이유

일반적인 FOR UPDATE는 조회 대상 행에 배타 잠금(Exclusive Lock)을 겁니다. 다른 트랜잭션이 같은 행을 FOR UPDATE나 UPDATE로 접근하면, 먼저 잠금을 가진 트랜잭션이 커밋 또는 롤백할 때까지 뒤의 트랜잭션은 그대로 블로킹됩니다. 예를 들어 다음과 같은 큐 테이블이 있다고 가정해 보겠습니다.

CREATE TABLE job_queue (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  status ENUM('PENDING','PROCESSING','DONE','FAILED') NOT NULL DEFAULT 'PENDING',
  worker_id VARCHAR(64) DEFAULT NULL,
  payload JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_status_id (status, id)
);

워커가 다음과 같이 조회한다면,

START TRANSACTION;
SELECT id FROM job_queue
WHERE status = 'PENDING'
ORDER BY id
LIMIT 1
FOR UPDATE;

워커 A가 id=1 행을 잠근 순간, 워커 B와 워커 C는 같은 조건으로 같은 행을 노리다가 대기 상태에 빠집니다. 큐 앞부분의 행 하나를 두고 수십 개의 워커가 줄을 서게 되는 셈인데, 이는 처리량(Throughput)을 떨어뜨릴 뿐 아니라 워커 스레드·커넥션을 불필요하게 점유해 커넥션 풀 고갈로 이어질 수 있습니다. 여기서 필요한 것이 "잠긴 행이면 기다리지 말고 즉시 실패하거나, 아예 건너뛰고 다음 행을 잡는" 동작입니다.

2. NOWAIT: 대기 대신 즉시 에러를 반환한다

FOR UPDATE NOWAIT는 대상 행에 이미 다른 트랜잭션의 잠금이 걸려 있으면, 대기하지 않고 즉시 에러를 반환합니다. MySQL 8.0 기준 에러 코드는 3572(ER_LOCK_NOWAIT)이며, 메시지는 "Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set."입니다.

SELECT id FROM job_queue
WHERE id = 1
FOR UPDATE NOWAIT;
-- ERROR 3572 (HY000): Statement aborted because lock(s)
-- could not be acquired immediately and NOWAIT is set.

주의할 점은, NOWAIT는 "이 특정 행을 반드시 처리해야 하는" 상황에 어울리는 옵션이라는 점입니다. 작업 큐처럼 "아무 PENDING 행이나 하나 가져오면 되는" 상황에서는 NOWAIT만으로는 부족합니다. ORDER BY id LIMIT 1 FOR UPDATE NOWAIT로 짜면, 맨 앞 행이 잠겨 있을 때 쿼리 자체가 실패해 버리기 때문에 애플리케이션이 에러를 잡아 재시도(retry) 로직을 직접 구현해야 합니다. 즉 "포기하고 다음 후보로 넘어가는" 동작이 아니라 "이 행을 못 잡으면 통째로 실패"하는 동작이라는 차이를 반드시 이해해야 합니다.

3. SKIP LOCKED: 잠긴 행을 건너뛰고 다음 행을 잡는다

반면 FOR UPDATE SKIP LOCKED는 잠긴 행을 결과 집합에서 아예 제외하고, 잠기지 않은 다음 행으로 넘어갑니다. 에러도 발생하지 않고 대기도 하지 않습니다. 작업 큐 패턴에는 이쪽이 훨씬 자연스럽습니다.

START TRANSACTION;
SELECT id, payload FROM job_queue
WHERE status = 'PENDING'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- 애플리케이션에서 반환된 id로 상태 갱신
UPDATE job_queue
SET status = 'PROCESSING', worker_id = 'worker-07'
WHERE id = :selected_id;

COMMIT;

워커 A가 id=1을 잠근 상태에서 워커 B가 같은 쿼리를 실행하면, MySQL은 id=1을 건너뛰고 잠기지 않은 id=2를 대신 반환합니다. 워커가 몇 대이든 서로 대기하지 않고 각자 다른 행을 즉시 잡아갈 수 있어, 대기 행렬 자체가 사라지는 것이 핵심 이득입니다.

다만 공식 문서에 명시된 대로 SKIP LOCKED는 결과가 비결정적(nondeterministic)입니다. 어떤 행이 잠겨 있고 어떤 행이 비어 있는지는 실행 시점의 동시성 상황에 따라 달라지므로, 같은 쿼리를 반복 실행해도 매번 다른 행이 반환될 수 있습니다. 이 때문에 구문 기반 복제(Statement-Based Replication)에서는 사용이 안전하지 않다고 매뉴얼에 명시되어 있으며, 반드시 행 기반 복제(Row-Based Replication) 또는 MIXED 모드를 사용하는 환경에서만 써야 합니다. binlog_format을 STATEMENT로 운영 중인 레거시 환경이라면 이 옵션을 도입하기 전에 반드시 확인이 필요합니다.

3-1. NOWAIT와 SKIP LOCKED 동작 비교

방식	잠긴 행을 만났을 때 동작	에러 발생 여부	대기 여부	적합한 상황
기본 FOR UPDATE	잠금이 풀릴 때까지 대기	innodb_lock_wait_timeout 초과 시 발생	대기함	반드시 그 행을 순서대로 처리해야 할 때
FOR UPDATE NOWAIT	즉시 에러 반환, 쿼리 자체 실패	즉시 발생 (3572)	대기하지 않음	특정 행 하나를 콕 집어 처리해야 하는 단건 작업
FOR UPDATE SKIP LOCKED	잠긴 행은 결과에서 제외, 다음 행으로 진행	발생하지 않음	대기하지 않음	아무 대기 작업이나 하나 가져오면 되는 큐 처리

4. 실전 작업 큐 패턴에서 놓치기 쉬운 세부 사항

SKIP LOCKED 자체는 간단하지만, 실무에서 안정적으로 동작하려면 다음 조건들이 함께 갖춰져야 합니다.

• SELECT와 UPDATE를 같은 트랜잭션 안에서 묶어야 합니다. SELECT로 행을 잠근 뒤 UPDATE로 상태를 바꾸기 전에 트랜잭션이 커밋되면, 그 사이에 다른 워커가 같은 행을 다시 잡아갈 여지가 생깁니다.
• 워커가 커밋 없이 죽는 경우를 대비해야 합니다. 워커 프로세스가 UPDATE 전에 비정상 종료되면 트랜잭션이 롤백되면서 잠금도 풀리고 status도 PENDING 그대로 남기 때문에 자동으로 안전하지만, UPDATE까지 성공한 뒤 실제 작업 처리 중 죽으면 status가 PROCESSING인 채로 영구히 남는 "좀비 작업"이 생깁니다. 이를 막으려면 heartbeat 컬럼(예: locked_at)을 두고, 일정 시간 이상 PROCESSING 상태인 행을 별도 배치로 PENDING으로 되돌리는 감시 로직이 필요합니다.
• ORDER BY와 LIMIT는 반드시 인덱스로 뒷받침되어야 합니다. WHERE status='PENDING' ORDER BY id LIMIT 1 쿼리가 idx_status_id(status, id) 같은 복합 인덱스를 타지 않으면, SKIP LOCKED 이전에 이미 풀 스캔으로 인한 성능 저하와 불필요한 갭 락(Gap Lock) 확산이 발생합니다. 인덱스 설계와 실행 계획 확인 방법은 WHERE 절 완전 정복과 EXPLAIN ANALYZE 해석법 글에서 다룬 기준을 그대로 적용하면 됩니다.
• 필요한 컬럼만 조회해야 합니다. 큐 조회 쿼리에 SELECT *를 습관적으로 쓰면 payload 같은 큰 JSON 컬럼까지 매번 읽어오게 되어 잠금을 쥐고 있는 시간이 늘어납니다. SELECT * 가 DB를 느리게 만드는 이유에서 설명한 것처럼, 잠금 구간에서는 특히 필요한 컬럼만 선택하는 습관이 중요합니다.

5. 격리 수준과 갭 락의 함정

기본 격리 수준인 REPEATABLE READ에서는 WHERE status='PENDING'처럼 범위를 지정하는 조건이 인덱스를 완전히 좁히지 못하면, InnoDB가 레코드 락뿐 아니라 갭 락까지 걸어 넥스트 키 락(Next-Key Lock)을 형성합니다. SKIP LOCKED는 이미 레코드에 걸린 잠금을 만난 행을 건너뛰는 기능이지, 갭 락으로 인해 새로운 행 삽입 자체가 막히는 상황까지 해결해 주지는 않습니다. 즉 워커들이 서로의 처리 대상 행을 스킵하는 데는 문제가 없더라도, 큐에 새 작업을 INSERT하는 별도 트랜잭션이 갭 락에 걸려 대기할 수 있습니다.

이 때문에 작업 큐처럼 조회·갱신이 잦고 삽입도 빈번한 테이블에는 READ COMMITTED 격리 수준을 사용하는 것이 더 안전합니다. READ COMMITTED에서는 InnoDB가 갭 락을 사용하지 않고(고유 인덱스를 이용한 검색이 아닌 한) 레코드 락 위주로 동작하기 때문에, SKIP LOCKED와 조합했을 때 잠금 경합 범위가 훨씬 좁아집니다. 다만 격리 수준을 낮추면 반복 읽기 일관성이 사라지므로, 큐 처리 로직 외에 같은 트랜잭션에서 다른 정합성이 중요한 조회를 함께 수행하고 있다면 영향 범위를 먼저 점검해야 합니다.

6. 실무에서 반드시 확인해야 할 한계

6-1. 기아 현상(Starvation)

SKIP LOCKED는 "먼저 온 순서대로 공정하게" 처리한다는 보장을 하지 않습니다. 특정 워커가 어떤 행을 오래 붙들고 있으면, 그 행은 다른 워커들에게 계속 건너뛰어지고, 뒤에 있는 행들이 먼저 처리되어 버립니다. 순서가 중요한 큐(예: 결제 순서 보장이 필요한 트랜잭션 큐)에는 SKIP LOCKED를 그대로 적용하면 안 됩니다. 처리 순서 보장이 필요하다면 파티션 키나 샤드 키로 워커를 아예 분리하는 설계를 검토해야 합니다.

6-2. 지원 버전과 스토리지 엔진

NOWAIT와 SKIP LOCKED는 MySQL 8.0.1부터 InnoDB에서 지원되며, MyISAM 등 다른 스토리지 엔진이나 8.0 이전 버전(5.7 이하)에서는 사용할 수 없습니다. 레거시 버전이 섞여 있는 환경이라면 반드시 버전을 확인해야 합니다.

6-3. 중복 처리 방지의 최종 보루는 UNIQUE 제약

SKIP LOCKED와 트랜잭션 잠금을 정확히 구현했더라도, 애플리케이션 배포 실수나 커넥션 설정 오류(예: 오토커밋이 꺼지지 않은 커넥션 풀)로 인해 이론과 다르게 동작할 가능성은 항상 존재합니다. 따라서 실제 작업 처리 결과를 저장하는 테이블에는 job_id에 UNIQUE 제약을 걸어, 잠금 로직이 실패하더라도 중복 처리 결과가 두 번 저장되는 것을 DB 차원에서 막아 두는 것이 더 안전합니다.

7. 언제 무엇을 선택할까: 정리

• 여러 워커가 "아무 대기 작업이나" 가져가는 큐 패턴이라면 SKIP LOCKED를 기본으로 선택합니다.
• 특정 행 하나를 정확히 지정해서 잠가야 하고, 잠겨 있으면 즉시 실패를 알려 상위 로직에서 재시도나 대체 로직을 태워야 한다면 NOWAIT가 맞습니다.
• 처리 순서 보장이 반드시 필요한 큐라면 두 옵션 모두 적합하지 않으며, 샤딩이나 단일 소비자 구조를 검토해야 합니다.
• 도입 전에는 반드시 격리 수준(READ COMMITTED 권장), 인덱스(status, id 복합), binlog_format(ROW 또는 MIXED), MySQL 버전(8.0.1 이상)을 함께 점검해야 합니다.

8. 실측 기록: 워커 동시 실행 결과

이론적 설명만으로는 중복 처리 여부나 실제 처리량, 기아 현상 발생 여부를 확신할 수 없습니다. 아래 절차대로 실제 워커 여러 대를 동시에 띄워 관찰한 뒤 결과를 채워 넣는 것이 좋습니다.

1. 워커 스크립트를 3~5개 프로세스로 동시에 기동하고, 각 워커가 SELECT ... FOR UPDATE SKIP LOCKED로 가져온 id를 로그로 남깁니다.
2. 처리 완료 후 동일 id가 두 워커의 로그에 동시에 등장하는지 확인해 중복 처리 여부를 검증합니다.
3. 일정 시간(예: 60초) 동안 각 워커가 처리한 행 수를 합산해 전체 처리량을 계산하고, 기본 FOR UPDATE 방식과 비교합니다.
4. 워커 한 대를 의도적으로 느리게 만들어(예: sleep 삽입) 그 워커가 잡은 행 근처의 다른 행들이 계속 건너뛰어지는지, 즉 기아 현상이 실제로 발생하는지 관찰합니다.

[여기에 실제 워커 실행 코드 또는 절차, 중복 처리 발생 여부, 처리량 수치, 기아 현상 관찰 기록을 채워 넣으세요]