비관적·낙관적 동시성 제어: FOR UPDATE·Version Column·재시도

핵심 요약

동시성 제어는 단순히 Lock을 많이 거는 문제가 아니라, 충돌을 언제 발견하고 어떤 범위에서 직렬화할지를 정하는 설계입니다.

비관적 동시성 제어
→ 충돌 가능성이 높다고 보고 업무 변경 전에 Row Lock을 선점

낙관적 동시성 제어
→ 읽기 단계에서는 잠그지 않고 저장할 때 Version 일치 여부로 충돌 검출

Oracle에서 일반 UPDATE도 변경 Row에 자동으로 Row Lock을 획득합니다. 따라서 단순한 한 문장 변경이라면 먼저 SELECT FOR UPDATE를 수행하는 것이 항상 더 안전한 것은 아닙니다. 검증과 변경을 조건부 UPDATE 한 문장으로 표현할 수 있는지 먼저 판단하고, 여러 단계 계산이나 여러 Table 변경을 같은 현재 상태에서 수행해야 할 때 비관적 Lock을 검토합니다.

학습 목표

• 비관적·낙관적 동시성 제어의 충돌 검출 시점을 비교한다.
• SELECT FOR UPDATE의 Lock 범위와 Transaction 수명을 설명한다.
• NOWAIT, WAIT, WAIT FOREVER, SKIP LOCKED의 차이를 구분한다.
• FOR UPDATE OF가 조인에서 잠글 Table을 지정하는 문법임을 이해한다.
• Version Column을 비교하고 증가시키는 안전한 갱신 SQL을 작성한다.
• SQL%ROWCOUNT = 0의 여러 원인을 구분하고 즉시 저장해야 하는 이유를 설명한다.
• ORA_ROWSCN과 ROWDEPENDENCIES의 한계를 업무 Version과 비교한다.
• Retry·Backoff·Jitter·Idempotency를 Transaction 경계와 연결한다.

1. 먼저 업무 불변조건을 정의한다

동시성 제어 방식보다 먼저 절대로 깨지면 안 되는 업무 규칙을 적습니다.

예를 들어 재고 차감 업무라면 다음과 같습니다.

quantity는 0보다 작아질 수 없다.
동일 request_id는 한 번만 처리한다.
취소 또는 만료된 주문은 차감할 수 없다.

그다음 경쟁 대상을 식별합니다.

• 같은 상품 Row 한 건만 경쟁하는가?
• 여러 계좌·주문·재고 Row를 함께 변경하는가?
• 사용자가 화면을 편집하는 시간이 긴가?
• 충돌 시 기다림과 즉시 실패 중 어느 쪽이 업무에 맞는가?
• 같은 요청을 재전송해도 결과가 한 번만 발생하는가?

이 질문에 따라 원자적 DML, 비관적 Lock, 낙관적 Version, Idempotency Key를 조합합니다.

2. 비관적 동시성 제어와 Lock 수명

비관적 방식은 변경 전에 대상 Row를 잠급니다.

SELECT balance,
       version_no
FROM   account
WHERE  account_id = :account_id
FOR UPDATE WAIT 3;

SELECT FOR UPDATE로 선택한 Row는 다른 Transaction이 잠그거나 변경하지 못하도록 Row Lock을 획득합니다. 이 Lock은 Cursor를 닫거나 Fetch를 끝냈다고 해제되는 것이 아니라, 현재 Transaction이 COMMIT 또는 ROLLBACK될 때까지 유지됩니다.

FOR UPDATE로 Lock 획득
→ 같은 Transaction에서 업무 조건 검증
→ 관련 UPDATE·INSERT·DELETE 수행
→ COMMIT 또는 ROLLBACK
→ Row Lock 해제

적합한 상황

• 충돌 빈도가 높고 업무 Transaction이 매우 짧음
• 현재 Row 상태를 기준으로 여러 단계 계산을 수행해야 함
• 여러 Table 변경 전에 기준 Row를 확실히 선점해야 함
• 충돌 시 대기·즉시 실패 정책을 명확히 적용해야 함

주요 비용

• Lock 대기와 Timeout
• 긴 Transaction으로 인한 처리량 저하
• Connection Pool Session 장기 점유
• 여러 Row의 접근 순서가 다를 때 Deadlock 위험
• 장애 시 Blocking 영향 범위 확대

일반 DML도 수정 Row에 Row Lock을 획득하므로, 다음과 같은 한 문장 조건부 갱신에는 선행 SELECT FOR UPDATE가 불필요할 수 있습니다.

UPDATE product_stock
SET    quantity = quantity - :order_qty
WHERE  product_id = :product_id
  AND  quantity >= :order_qty;

이 SQL은 재고 검증과 차감을 한 Statement에 결합합니다. 성공 여부는 영향 Row 수로 판정합니다.

3. FOR UPDATE 대기 정책

Oracle 26ai의 SELECT ... FOR UPDATE는 잠긴 Row를 만났을 때 다음 정책을 제공합니다.

26ai에서는 WAIT 시간에 SECONDS, MILLISECONDS, MICROSECONDS 단위를 지정할 수 있고, 단위를 생략하면 초가 기본입니다.

SELECT job_id
FROM   job_queue
WHERE  status = 'READY'
ORDER BY job_id
FETCH FIRST 1 ROW ONLY
FOR UPDATE SKIP LOCKED;

NOWAIT로 Lock 충돌이 발생하면 현재 Statement가 실패합니다. 이것을 Transaction 전체가 자동으로 Rollback됐다고 가정하면 안 됩니다. 예외 처리에서 현재 Transaction 상태와 이전 DML을 확인하고 명시적으로 ROLLBACK 또는 업무에 맞는 처리를 수행합니다.

SKIP LOCKED는 잠긴 Row를 보이지 않게 제외합니다. 따라서 동일 문서나 동일 주문 Row를 반드시 수정해야 하는 일반 편집 화면에서 충돌을 해결하는 방법이 아닙니다. 다른 작업을 대신 가져와도 되는 Multi-Consumer Queue에 적합합니다.

4. FOR UPDATE의 범위와 OF 절

FOR UPDATE는 Top-Level SELECT에서 사용하며, Subquery 내부에는 지정할 수 없습니다.

조인 Query에서 OF 절을 생략하면 Query에 참여한 여러 Table의 선택 Row가 잠금 대상이 될 수 있습니다. OF 절은 특정 Column만 잠그는 기능이 아니라, 어떤 Table 또는 View의 Row를 잠글지 식별하는 기능입니다.

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

위 SQL에서 o.status라는 Column 자체만 잠기는 것이 아닙니다. o.status가 속한 orders의 선택 Row가 잠금 대상이 됩니다. OF 절에는 실제 Column 이름을 사용해야 하며 Alias Column 이름을 사용할 수 없습니다.

잠금 범위를 좁히는 것은 불필요한 Blocking을 줄이는 데 중요하지만, 이후 변경할 모든 Row가 제대로 보호되는지 업무 흐름 전체에서 검증해야 합니다.

5. Lock 보유 시간을 짧게 유지한다

다음 구조는 피합니다.

화면을 열면서 FOR UPDATE
→ 사용자가 5분 동안 입력
→ 외부 API 호출
→ 저장
→ COMMIT

Row와 Database Session을 사용자 생각 시간 동안 점유하기 때문입니다. 대신 화면 조회는 일반 SELECT로 수행하고, 저장 요청이 들어왔을 때 짧은 Transaction 안에서 Lock·검증·DML을 수행합니다.

일반 SELECT로 화면 표시
→ 사용자 입력
→ 저장 요청
→ 짧은 Transaction 시작
→ 필요한 Lock·검증·DML
→ 즉시 COMMIT 또는 ROLLBACK

여러 Row를 잠그는 경우

전체 기능이 동일한 정규화된 Key 순서로 Row에 접근하도록 설계합니다.

항상 작은 account_id → 큰 account_id 순서로 Lock

단일 Multi-Row SQL의 ORDER BY만 보고 모든 실행 환경에서 원하는 Lock 취득 순서가 보장된다고 단정하지 않습니다. 핵심 업무에서는 Row를 결정적인 순서로 명시적으로 획득하고, 실제 실행·Fetch 방식과 Deadlock Trace를 테스트합니다.

6. 낙관적 동시성 제어의 기본 패턴

낙관적 방식은 읽을 때 Row Lock을 선점하지 않고, 저장할 때 처음 읽은 Version이 여전히 같은지 검사합니다.

SELECT order_id,
       status,
       amount,
       version_no
FROM   orders
WHERE  order_id = :order_id;

저장 SQL은 Version 비교와 증가를 같은 Statement에 포함합니다.

UPDATE orders
SET    status     = :new_status,
       amount     = :new_amount,
       version_no = version_no + 1
WHERE  order_id   = :order_id
  AND  version_no = :old_version;

결과는 다음처럼 해석합니다.

영향 Row 수 = 1
→ Version이 일치했고 저장 성공

영향 Row 수 = 0
→ Version 충돌, Row 삭제, 추가 업무 Predicate 불일치 중 하나

0건을 곧바로 “다른 사용자가 수정했다”라고 단정하지 않습니다. Tenant 조건, 상태 조건, 권한 조건, Soft Delete 조건 등이 WHERE 절에 있다면 어느 Predicate가 불일치했는지 최신 Row를 다시 읽어 구분합니다.

PL/SQL에서 SQL%ROWCOUNT

SQL%ROWCOUNT는 가장 최근에 실행된 SELECT 또는 DML을 가리킵니다. 다른 SQL이나 Subprogram 호출이 실행되면 값의 대상이 바뀔 수 있으므로 DML 직후 지역 변수에 저장합니다.

UPDATE orders
SET    status     = :new_status,
       version_no = version_no + 1
WHERE  order_id   = :order_id
  AND  version_no = :old_version;

l_updated_count := SQL%ROWCOUNT;

Application Driver의 Update Count를 사용하는 경우에도 같은 원칙으로 대상 DML의 결과를 즉시 보관합니다.

7. Version Column 설계 원칙

명시적인 숫자 Version은 업무 충돌 계약을 가장 쉽게 표현합니다.

CREATE TABLE document_item (
    document_id   NUMBER PRIMARY KEY,
    content_text  VARCHAR2(4000),
    version_no    NUMBER DEFAULT 1 NOT NULL
);

장점

• 처음 읽은 상태와 현재 상태를 단순 등치 비교 가능
• 성공한 업무 변경마다 1씩 증가시키는 규칙을 명시적으로 통제
• Timestamp 정밀도·Time Zone·생성 주체와 분리
• REST API의 ETag·If-Match 같은 계약과 연결하기 쉬움

운영상 주의점

모든 변경 경로가 Version을 증가시켜야 합니다. Batch, 관리자 Tool, Trigger, 다른 Service가 Version 증가를 우회하면 충돌 검출이 무력화됩니다.

업무 Column 변경
→ version_no = version_no + 1을 같은 DML에 포함

updated_at을 Version으로 사용할 수도 있지만 다음을 검증해야 합니다.

• 값의 생성 주체가 Database인가 Application인가?
• 여러 변경이 Timestamp 정밀도 안에서 같은 값이 될 수 있는가?
• Time Zone 변환이나 문자열 직렬화로 값이 달라지는가?
• 모든 변경 경로가 Timestamp를 갱신하는가?

8. ORA_ROWSCN과 ROWDEPENDENCIES의 한계

ORA_ROWSCN은 Row의 최근 변경과 관련된 SCN을 보여 주지만, 기본 NOROWDEPENDENCIES에서는 Block 수준의 Dependency를 반영합니다.

NOROWDEPENDENCIES
→ 같은 Block의 다른 Row 변경도 대상 Row의 ORA_ROWSCN에 영향을 줄 수 있음

ROWDEPENDENCIES
→ Row-Level Dependency Tracking 사용

그러나 ROWDEPENDENCIES를 사용해도 ORA_ROWSCN을 마지막 Transaction의 정확한 Commit SCN으로 보면 안 됩니다. 공식 문서상 실제 Commit SCN 이상인 값이 반환될 수 있습니다.

ROWDEPENDENCIES의 추가 특성은 다음과 같습니다.

• Row마다 Row-Level Dependency 정보를 저장
• 각 Row 크기가 6Byte 증가
• Table 생성 후 단순 ALTER로 설정을 바꿀 수 없음
• 기본값은 NOROWDEPENDENCIES
• 공식적으로는 Replication의 병렬 전파 등에 주로 유용

따라서 Application의 업무 Version 계약에는 보통 명시적인 version_no가 더 명확합니다. ORA_ROWSCN을 사용하려면 False Conflict, Row 이동·재정의, 구조 변경, 정확도 기대를 별도로 검증해야 합니다.

9. 충돌 후 Retry 절차

충돌을 감지했다고 같은 SQL을 즉시 반복하는 것은 안전한 Retry가 아닙니다.

오류 또는 0건 감지
→ 현재 Transaction의 성공·실패 범위 확정
→ 필요하면 ROLLBACK
→ 최신 데이터 재조회
→ 업무 조건과 새 값 재계산
→ 제한된 횟수로 재시도
→ Exponential Backoff + Jitter
→ 계속 실패하면 사용자 또는 상위 업무에 전달

Retry 대상 구분

Backoff는 모든 Client가 즉시 동시에 재시도해 다시 충돌하는 현상을 줄입니다. Jitter는 재시도 시점을 분산합니다. 횟수와 총 대기 시간에는 상한을 둡니다.

10. Idempotency와 Unique Constraint

Retry가 안전하려면 같은 요청을 여러 번 보내도 업무 효과가 한 번만 발생해야 합니다.

CREATE TABLE processed_request (
    request_id    VARCHAR2(100) PRIMARY KEY,
    result_code   VARCHAR2(30),
    processed_at  TIMESTAMP NOT NULL
);

업무 변경과 요청 ID 등록을 같은 Transaction에서 처리합니다.

request_id INSERT 시도
→ 성공: 업무 DML 수행 후 결과 저장·COMMIT
→ Unique 충돌: 기존 처리 결과 조회

요청 ID 등록을 먼저 Commit하고 업무 변경을 별도 Transaction에서 처리하면, 요청은 등록됐지만 실제 업무는 실패한 불일치가 생길 수 있습니다. 반대로 업무를 먼저 Commit하고 요청 ID를 나중에 등록하면 중복 실행을 막지 못할 수 있습니다.

11. 방식 선택과 혼합 전략

혼합 방식도 가능합니다. 일반 편집은 낙관적으로 처리하고, 결제 확정이나 재고 예약처럼 충돌이 높은 짧은 구간만 비관적으로 잠글 수 있습니다.

12. 진단과 운영 지표

동시성 제어가 효과적인지는 다음 지표로 확인합니다.

• 기능별 Version 충돌 건수와 충돌률
• NOWAIT·Timeout·Deadlock 오류 건수
• 평균·상위 Percentile Lock 대기시간
• Transaction 보유 시간과 Connection 점유시간
• Retry 횟수·성공률·최종 실패율
• Idempotency 중복 요청 감지 건수
• SQL%ROWCOUNT = 0의 실제 원인 분포
• Blocking Session·SQL ID·업무 Key·Request ID

단순히 Lock Wait를 줄이는 것만 목표로 삼지 않습니다. 충돌을 숨기거나 데이터를 덮어쓰지 않으면서 처리량과 사용자 경험을 함께 평가합니다.

13. 혼동하기 쉬운 판단

핵심 판단 순서

업무 불변조건과 경쟁 Row는 무엇인가?
→ 한 문장 조건부 DML로 해결할 수 있는가?
→ 충돌 빈도와 Transaction 길이는 얼마인가?
→ 대기·즉시 실패·건너뛰기 중 어떤 정책이 맞는가?
→ FOR UPDATE의 Table 범위와 Lock 순서는 적절한가?
→ Version 비교와 증가가 모든 변경 경로에서 지켜지는가?
→ 0건·오류·Network 불명확 결과를 어떻게 구분하는가?
→ Retry와 Idempotency가 같은 Transaction 경계에 맞게 설계됐는가?