MySQL 8.4에서 SKIP LOCKED 사용 시 조건과 한계

MySQL 8.4에서는 SELECT ... FOR UPDATE 또는 SELECT ... FOR SHARE 구문에 SKIP LOCKED 옵션을 사용할 수 있습니다. 이 옵션은 잠긴 행(row)이 있을 때 해당 행을 건너뛰고 즉시 결과를 반환하도록 하여, 잠금 대기(lock wait)를 피할 수 있게 합니다.

SKIP LOCKED의 동작 조건

• 잠긴 행이 존재할 경우, 해당 행을 결과 집합에서 제외하고 나머지 잠기지 않은 행만 반환합니다.
• 잠금이 걸린 행에 대해 대기하지 않고 즉시 쿼리를 완료합니다.
• 이 기능은 행 수준 잠금(row-level lock)에만 적용됩니다.
• 잠긴 행을 건너뛰므로 결과가 일관적이지 않을 수 있습니다.
• 트랜잭션이 시작된 상태에서(autocommit이 비활성화된 상태)만 사용할 수 있습니다.

SKIP LOCKED 사용 시 한계 및 주의사항

• 잠긴 행을 건너뛰므로 결과가 불완전하거나 일관성이 떨어질 수 있어 일반적인 트랜잭션 처리에는 적합하지 않습니다.
• 복제 환경에서, 특히 스테이트먼트 기반 복제(statement-based replication)에서는 안전하지 않습니다.
• 중첩된 서브쿼리에서 외부 쿼리에만 SKIP LOCKED를 지정해도 서브쿼리의 행은 이 절만으로 잠금되지 않으며, 서브쿼리에도 별도로 잠금 옵션을 지정해야 잠금이 적용됩니다.
• 잠금 대기를 피하기 위한 용도로 주로 사용되며, 예를 들어 여러 세션이 동일한 큐(queue) 테이블에 접근할 때 잠금 충돌을 줄이는 데 유용합니다.

예시

아래는 공식 문서의 동작을 설명하는 예제이며 이 블로그에서는 MySQL 서버로 실행하지 않았습니다. 테스트용 DB에서 InnoDB 테이블 t와 기본 키 i를 만들고 1, 2, 3을 넣은 상황을 전제로 합니다. 세 세션은 같은 테스트 DB에 접속하며, 확인이 끝나면 각 세션에서 ROLLBACK;으로 잠금을 해제합니다.

# 세션 1: 행 i=2에 대해 잠금 획득
START TRANSACTION;
SELECT * FROM t WHERE i = 2 FOR UPDATE;

# 세션 2: 같은 행에 대해 NOWAIT 옵션으로 잠금 시도 시 즉시 오류 발생
START TRANSACTION;
SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT; -- 오류 발생

# 세션 3: SKIP LOCKED 옵션으로 잠긴 행을 건너뛰고 나머지 행 반환
START TRANSACTION;
SELECT * FROM t FOR UPDATE SKIP LOCKED; -- i=1, i=3 반환

따라서 SKIP LOCKED는 잠금 대기를 피하고자 할 때 유용하지만, 데이터 일관성이 중요한 일반 트랜잭션 처리에는 적합하지 않으며, 사용 시 결과가 불완전할 수 있음을 반드시 고려해야 합니다.

참고 자료

• 17.7.2.4 Locking Reads