SELECT FOR UPDATE 실전: OF·NOWAIT·WAIT·SKIP LOCKED

핵심 요약

SELECT FOR UPDATE는 조회 결과의 Base Row를 이후 UPDATE·DELETE하기 전에 미리 잠그는 명시적 Row Lock 문장입니다. 일반 SELECT는 Undo 기반 일관 읽기를 수행하지만 Row Lock을 선점하지 않습니다.

일반 SELECT
→ 일관 읽기
→ 다른 Transaction의 DML을 Row Lock으로 막지 않음

SELECT FOR UPDATE
→ 선택 Row에 배타적 Row Lock 획득
→ 다른 Transaction의 UPDATE·DELETE·FOR UPDATE를 대기시킴
→ COMMIT 또는 ROLLBACK까지 유지

조인에서는 FOR UPDATE OF의 Column이 잠글 Table 또는 View의 Row를 식별합니다. 특정 Column만 잠그는 Column Lock 문법이 아닙니다.

기본                  Row가 잠금 해제될 때까지 대기
NOWAIT                즉시 현재 문장 실패
WAIT n                지정 시간까지 대기, 기본 단위는 초
WAIT FOREVER          무기한 대기
SKIP LOCKED           이미 잠긴 Row를 제외하고 잠글 수 있는 Row 반환

학습 목표

• 일반 SELECT와 SELECT FOR UPDATE의 차이를 설명한다.
• 조인에서 FOR UPDATE OF가 잠글 Table의 Row를 정하는 방식을 설명한다.
• NOWAIT, WAIT, WAIT FOREVER, SKIP LOCKED의 업무 의미를 구분한다.
• Cursor의 Fetch Size와 실제 Lock 범위를 혼동하지 않는다.
• View·집계·Row Limiting 제한을 고려해 안전한 Lock SQL을 설계한다.

1. SELECT FOR UPDATE가 필요한 경우

Oracle은 UPDATE와 DELETE가 실제 Row를 변경할 때 자동으로 Row Lock을 획득합니다. 따라서 단일 DML로 업무 조건과 변경을 함께 표현할 수 있다면 별도의 선행 Lock이 반드시 필요한 것은 아닙니다.

SELECT FOR UPDATE는 다음처럼 조회한 현재 값으로 여러 검증과 후속 DML을 수행하기 전에 Row가 바뀌지 않도록 선점해야 할 때 사용합니다.

계좌 Row Lock
→ 계좌 상태·잔액 검증
→ 관련 이체 내역 생성
→ 잔액 변경
→ COMMIT 또는 ROLLBACK

SELECT balance, account_status
FROM   account
WHERE  account_id = :account_id
FOR UPDATE;

잠금을 얻은 뒤에는 같은 Transaction에서 필요한 DML을 수행하고 신속히 종료합니다.

UPDATE account
SET    balance = balance - :amount
WHERE  account_id = :account_id;

COMMIT;

SELECT FOR UPDATE는 Row를 변경하지 않고도 잠글 수 있지만, 잠금을 오래 유지하면 다른 Transaction의 처리량과 응답시간을 악화시킵니다.

2. 잠금 유지 범위와 일반 SELECT의 차이

SELECT FOR UPDATE로 획득한 Row Lock은 Cursor를 닫거나 Application에서 결과를 모두 읽었다고 해제되지 않습니다. Lock은 해당 Transaction의 COMMIT 또는 ROLLBACK까지 유지됩니다.

따라서 사용자 입력, 승인 대기, 장시간 계산, 외부 API 호출을 Row Lock 보유 구간에 넣지 않는 것이 기본입니다.

3. 조인과 FOR UPDATE OF

고객과 주문을 함께 조회하되 주문 Row만 변경할 예정이라고 가정합니다.

SELECT c.customer_name,
       o.order_id,
       o.status
FROM   customer c
JOIN   orders o
  ON   o.customer_id = c.customer_id
WHERE  o.order_id = :order_id
FOR UPDATE OF o.status;

의미는 다음과 같습니다.

OF o.status
→ ORDERS Table의 선택 Row를 잠글 대상으로 식별
→ status Column만 잠그는 것이 아님

OF 절에 적은 구체적인 Column 자체는 Lock 범위를 Column 단위로 좁히지 않습니다. 다만 Column이 속한 Table 또는 View를 식별합니다. Column Alias가 아니라 실제 Column 이름을 사용해야 합니다.

OF 절을 생략하면 Query가 선택한 Row 중 모든 참여 Table의 Row가 Lock 대상이 됩니다.

SELECT c.customer_name, o.order_id
FROM   customer c
JOIN   orders o
  ON   o.customer_id = c.customer_id
WHERE  o.order_id = :order_id
FOR UPDATE;

위 SQL은 고객과 주문 양쪽의 선택 Row를 잠글 수 있으므로, 실제 변경 대상이 주문뿐이면 불필요한 경합을 만들 수 있습니다.

4. OF 절과 Row 수를 구분한다

FOR UPDATE OF는 어느 Table의 Row를 잠글지 정하지만, 반환하거나 선택하는 Row 수 자체를 줄이지 않습니다.

Lock Row 수를 좌우하는 요소
1. WHERE Predicate의 선택도
2. Join 결과의 크기
3. 잠글 Table의 수

예를 들어 FOR UPDATE OF o.status를 사용해도 조건이 넓어 주문 10만 건이 선택되면 ORDERS의 많은 Row가 잠길 수 있습니다. PK·Unique Key·Tenant Key·상태 조건 등으로 대상 Row를 먼저 좁혀야 합니다.

5. 기본 대기·NOWAIT·WAIT·WAIT FOREVER

기본 대기

WAIT, NOWAIT, SKIP LOCKED를 지정하지 않으면 필요한 Row가 해제될 때까지 기다립니다.

NOWAIT

SELECT status
FROM   orders
WHERE  order_id = :order_id
FOR UPDATE NOWAIT;

다른 Transaction이 Row를 잠근 상태라면 현재 문장이 즉시 실패합니다. 이 실패가 이전에 같은 Transaction에서 성공한 모든 작업을 자동으로 Rollback한다는 뜻은 아닙니다. Application은 예외를 처리하고 COMMIT 또는 ROLLBACK 여부를 명시적으로 결정해야 합니다.

WAIT

Oracle AI Database 26ai에서는 정수와 함께 SECONDS, MILLISECONDS, MICROSECONDS를 지정할 수 있으며, 단위를 생략하면 초가 기본입니다.

FOR UPDATE WAIT 3
FOR UPDATE WAIT 500 MILLISECONDS
FOR UPDATE WAIT 200000 MICROSECONDS

WAIT FOREVER

FOR UPDATE WAIT FOREVER

기본 동작처럼 Row Lock이 해제될 때까지 무기한 기다립니다.

Database Lock 대기시간은 Application Statement Timeout, HTTP·RPC Timeout, Connection Pool 대기시간, 전체 SLA보다 길게 방치하지 않도록 조정합니다.

6. SKIP LOCKED

SKIP LOCKED는 조건에 맞는 Row 중 다른 Transaction이 이미 잠근 Row를 제외하고, 현재 잠글 수 있는 Row만 반환합니다.

SELECT job_id, payload
FROM   job_queue
WHERE  status = 'READY'
ORDER BY priority, job_id
FOR UPDATE SKIP LOCKED;

Multi-Consumer Queue에서 여러 Worker가 서로 다른 작업을 가져갈 때 유용합니다.

Worker A: Job 1 Lock
Worker B: Job 1을 기다리지 않고 잠기지 않은 Job 처리

중요한 결과 특성은 다음과 같습니다.

• 잠긴 Row가 결과에서 빠지므로 전체 조건 Row를 완전하게 반환하지 않는다.
• 실행할 때마다 잠금 상태에 따라 반환 집합이 달라질 수 있다.
• Worker가 Rollback하면 해당 Row는 다시 처리 후보가 될 수 있다.
• 업무 상태 전이는 중복 실행에 안전해야 한다.
• 전체 순서, 재전송, 가시성 Timeout 등이 중요하면 Transactional Event Queues·Advanced Queuing 같은 전용 기능을 검토한다.

따라서 특정 계좌·주문처럼 반드시 그 Row를 처리해야 하는 화면 요청에는 SKIP LOCKED를 적용하면 단순한 “없음”과 “잠겨서 건너뜀”을 구분하기 어려울 수 있습니다.

7. WAIT와 SKIP LOCKED의 Table Lock 주의점

WAIT와 SKIP LOCKED는 Row Lock 경합 정책입니다. 대상 Table이 다른 Session에 의해 Exclusive Mode로 잠겨 있으면 Table Lock이 해제될 때까지 결과가 반환되지 않을 수 있습니다.

특히 Oracle 공식 문서는 Exclusive Table Lock 상태에서는 WAIT에 지정한 시간과 관계없이 SELECT FOR UPDATE가 차단될 수 있음을 주의시킵니다.

SKIP LOCKED
≠ 모든 종류의 Lock을 무조건 건너뜀

WAIT 1 SECOND
≠ Exclusive Table Lock까지 반드시 1초 안에 종료

대기 분석 시 Row Lock뿐 아니라 Table Lock과 DDL 경합 가능성도 함께 확인합니다.

8. Cursor OPEN과 Fetch Size

명시적 Cursor의 Query에 FOR UPDATE가 있으면 Cursor를 OPEN할 때 결과 집합의 Row가 잠깁니다. 첫 번째 FETCH 때부터 한 건씩 잠그는 구조가 아닙니다.

OPEN FOR UPDATE Cursor
→ 결과 집합 식별
→ 결과 Row Lock
→ 첫 FETCH 이전부터 Lock 보유

따라서 Fetch Size를 10으로 줄였다고 해서 Lock Row 수도 자동으로 10건으로 제한되는 것은 아닙니다. Batch 크기는 Predicate와 SQL 구조에서 명확히 제한해야 하며, Fetch Size를 동시성 제어 수단으로 사용하지 않습니다.

9. 문법 및 Query 구조 제한

FOR UPDATE는 Top-Level SELECT에만 지정할 수 있고 Subquery 안에는 직접 지정할 수 없습니다. 또한 다음 구성과 함께 사용할 수 없습니다.

• DISTINCT
• Cursor Expression
• Set Operator
• GROUP BY
• Aggregate Function
• row_limiting_clause

Oracle의 row_limiting_clause인 FETCH FIRST ... ROWS ONLY 또는 OFFSET ... FETCH는 for_update_clause와 함께 지정할 수 없습니다.

Queue Batch를 구현할 때는 Fetch Size만 줄이는 방식이나 지원되지 않는 문법 조합에 의존하지 말고, 후보 Key 선정과 상태 재검증을 포함한 검증된 Pattern 또는 전용 Queue 기능을 사용합니다.

10. View에서의 제한

일반적으로 View에 대한 FOR UPDATE는 지원되지 않습니다. 다만 Optimizer가 View를 상위 Query Block으로 Merge하고 변환된 Query에서 Base Row를 잠글 수 있으면 성공할 수 있습니다.

다음 요소는 View Merging을 막아 ORA-02014를 발생시킬 수 있습니다.

• DISTINCT
• GROUP BY
• Aggregate
• Set Operator
• NO_MERGE Hint 등 View Merging 차단 요소

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

또한 Merged View라도 OF 절이 가리키는 View Column의 Base 표현식이 단순 Column이 아니면 ORA-01733이 발생할 수 있습니다. 복잡한 View에 Lock을 맡기기보다 잠글 Base Row의 Key를 확정하고 Base Table을 명시적으로 잠그는 방식이 더 예측 가능합니다.

11. 안전한 Transaction 설계

권장 흐름은 다음과 같습니다.

Transaction 시작
→ PK·Unique Key 등으로 필요한 Row만 FOR UPDATE
→ 업무 조건 즉시 검증
→ 관련 DML 수행
→ COMMIT 또는 ROLLBACK

피해야 할 흐름은 다음과 같습니다.

광범위한 Row FOR UPDATE
→ 사용자 입력 대기
→ 외부 API 장시간 호출
→ 여러 Table을 서로 다른 순서로 Lock
→ 늦은 COMMIT

여러 Transaction이 같은 Row들을 서로 다른 순서로 잠그면 Deadlock 위험이 커집니다. 업무별 Lock 순서를 통일하고, ORA-00060, NOWAIT, Timeout을 정상적인 충돌 결과로 처리할 예외 전략을 준비합니다.

12. 진단·적용 절차

1. UPDATE 전에 선행 Lock이 정말 필요한가?
2. 어느 Table의 어떤 Row를 잠글 것인가?
3. Predicate가 PK·Unique Key 수준으로 충분히 좁은가?
4. 기본 대기·NOWAIT·WAIT·SKIP LOCKED 중 업무 의미에 맞는가?
5. Cursor OPEN 시 실제 잠기는 전체 결과 집합은 몇 건인가?
6. Transaction 안에 사용자 대기·외부 호출이 있는가?
7. Timeout 계층과 예외·재시도 정책이 일치하는가?
8. View·집계·Row Limiting 문법 제한을 위반하지 않는가?

운영에서는 Lock 대기시간, 잠긴 Row 수, Timeout·NOWAIT 오류, Deadlock, Transaction 지속시간, Queue 재처리·중복 처리 지표를 함께 확인합니다.

혼동하기 쉬운 판단