/
/

[TIL] Real MySQL SELECT FOR UPDATE with NOWAIT and SKIP LOCKED

박상준
2025.03.23
·
8 min read

SELECT FOR UPDATE NOWAIT

동작

  • 잠금 충돌 시 반응

    • 조회하는 레코드가 이미 다른 트랜잭션에 의해 잠겨있는 경우

    • 쿼리가 대기하지 않고 즉시 에러를 반환한다.

  • 에러 메시지

    • Lock wait timeout exceeded 같은 메시지 출력

  • InnoDB innodb_lock_wait_timeout 설정와 유사하다

    • 해당 설정은 InnoDB 스토리지의 엔진에서 사용하는 설정이다

      • 트랜잭션이 락 을 기다리는 최대 시간을 정의하는데

      • 해당 시간을 초과하면 락을 획득하지 못한 트랜잭션이 실패한다.

      • 기본값은 50초이다.

      • 최솟값은 1초이다.

    • NOWAIT 쿼리 옵션은

      • 해당 값을 0으로 설정한 효과와 동일하다.

      • innodb_lock_wait_timeout 옵션은 명시적으로 0으로 설정이 불가능하기에

      • NOWAIT 옵션이 대체한다.

  • 트랜잭션 유지

    • 쿼리가 에러로 실패해도 트랜잭션이 닫히지가 않는다

    • 명시적으로 COMMIT 혹은 ROLLBACK 을 수행해야한다.

장점

  • 잠금을 기다리지 않고 즉시 결과를 확인가능하다.

  • 대체 로직을 실행할 수 있다.

  • 불필요한 대기 시간을 줄일 수 있음.

언제쓰노

  • 잠금 대기 자체가 비정상적인 상황인 경우 사용하면 유용하다.

실습

start transaction;
select *
from departments
where dept_no = 'd001' for
update nowait;
-- dept_no = 'd001' is locked

-- 다른 세션에서

start transaction;
select *
from departments
where dept_no = 'd001' for
update nowait;
-- 에러반환

[2025-03-22 22:55:04] [HY000][3572] Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

SELECT FOR UPDATE SKIP LOCKED

동작 방식

  • 잠금 충돌 시 반응

    • 조회하려는 레코드 중에 이미 잠긴 레코드는 건너뛴다.

    • 잠기지 않은 레코드만 잠가서 반환.

  • 결과 크기

    • 잠긴 레코드가 제외

      • 예상보다 적은 결과가 반환될 수 있음.

      • 모든 레코드가 잠기면 빈 결과 집합이 반환된다고 함.

    • 주의

      • 데이터가 반환되지 않더라도 갭락이 발생할 수 있따.

        갭락??

        • InnoDB 스토리지 엔진에서 사용되는 잠금 매커니즘의 하나라고 한다.

        • 인덱스 레코드 사이의 간격(gap) 을 잠그는 행위라고 한다

          • 인덱스 레코드 사이의 간격은

            • 실제 데이터 행이 존재하는 않는 범위 를 말하는 데

            • 예를 들어

              • select ~ where id > 7 인 경우

              • 실제로 데이터는 id 15 20 이 존재하는 데 없는 행인

              • 8 9 등의 값에 대해도 잠금을 하는 역할임.

              • 없는 값을 읽는 팬텀 리드 등을 방지.

        • ex

          • 트랜잭션이 특정 조건을 만족하는 레코드를 조회하고 수정하는 동안에

          • 다른 트랜잭션이 그 범위에 새로운 데이터를 추가하지 못하도록 보호하는 역할

언제 쓰노

  • 선착순 처리할떄

    • 쿠폰 발급이나 작업큐 등에서 여러 트랜잭션이 동시에 사용가능한 리소스를 가져가야하는 경우 사용함.

  • ORDER BY 와 LIMIT 사용시

    • 특정 순서로 데이터를 처리하면서 잠긴 레코드를 건너뛰고자 하는 경우 자주 사용된다.

실습

start transaction;
select *
from departments
limit 1
for
update skip locked;

+-------+---------+---------+
|dept_no|dept_name|emp_count|
+-------+---------+---------+
|d001   |Marketing|null     |
+-------+---------+---------+

--
다른 세션에서 조회하기

start transaction;
select *
from departments
limit 1
for
update skip locked;

+-------+---------+---------+
|dept_no|dept_name|emp_count|
+-------+---------+---------+
|d002   |Finance  |null     |
+-------+---------+---------+

  • d001 은 이미 세션1에 의해 락이 걸려있다.

  • d001 을 조회할 수 없으니 d002 를 조회하게 된다.

select *
from departments
limit 1
for
update nowait ; 해보면

당연

[HY000][3572] Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

에러가 발생한다.

조인과 같이 사용하기

기본적인 동작

  • 잠금 범위

    • SELECT FOR UPDATE 조인된 모든 테이블의 레코드에 대해 잠금을 수행한다.

  • 문제

    • 1:N 관계에서 부모 테이블(1) 이 잠기면 자식 테이블(N) 이 모든 관련 레코드가 영향을 받는다.

문제 상황

start transaction;
select *
from departments d
         join dept_emp de on d.dept_no = de.dept_no
where d.dept_no = 'd001'
limit 1
for
update skip locked;

//
+-------+---------+---------+------+-------+----------+----------+
|dept_no|dept_name|emp_count|emp_no|dept_no|from_date |to_date   |
+-------+---------+---------+------+-------+----------+----------+
|d001   |Marketing|null     |10017 |d001   |1993-08-03|9999-01-01|
+-------+---------+---------+------+-------+----------+----------+

// 다른 세션에서 작업

start transaction;
select *
from departments d
         join dept_emp de on d.dept_no = de.dept_no
where d.dept_no = 'd001'
limit 1
for
update skip locked;

// 빈 레코드 반환

  • 부모테이블이 잠겨있기에 하위 자식 테이블에 대한 내용도 조회를 할 수가 없음;;

  • 동시성이 너무 떨어진다.

해결 방안

  • OF 구문을 사용하면 된다.

    • 역할

      • 잠글 테이블을 명시적으로 지정해 불필요한 잠금을 방지할 수 있다.

    • 수정 쿼리

      start transaction;
select *
from departments d
         join dept_emp de on d.dept_no = de.dept_no
where d.dept_no = 'd001'
limit 1
for
update of de skip locked;

+-------+---------+---------+------+-------+----------+----------+
|dept_no|dept_name|emp_count|emp_no|dept_no|from_date |to_date   |
+-------+---------+---------+------+-------+----------+----------+
|d001   |Marketing|null     |10017 |d001   |1993-08-03|9999-01-01|
+-------+---------+---------+------+-------+----------+----------+

// 다른 세션

start transaction;
select *
from departments d
         join dept_emp de on d.dept_no = de.dept_no
where d.dept_no = 'd001'
limit 1
for
update of de skip locked;

+-------+---------+---------+------+-------+----------+----------+
|dept_no|dept_name|emp_count|emp_no|dept_no|from_date |to_date   |
+-------+---------+---------+------+-------+----------+----------+
|d001   |Marketing|null     |10055 |d001   |1992-04-27|1995-07-22|
+-------+---------+---------+------+-------+----------+----------+

      • de 테이블의 첫번 째 레코드는 잠겨있기에 skip 처리하고

        • 다음 행을 조회하여 해당 결과를 반환한다.

- 컬렉션 아티클