DML Write Consistency: Current 갱신·Statement Restart·Lost Update

핵심 요약

Oracle의 기본 격리 수준인 READ COMMITTED에서는 UPDATE와 DELETE의 WHERE 절에 포함된 암시적 Query도 문장 단위 읽기 일관성을 보장받습니다. 그러나 실제 변경 대상 Row가 다른 Transaction에 의해 잠겨 있으면 대기하고, 선행 Transaction이 Commit한 뒤에는 새로 변경된 Row에 후행 DML이 진행될 수 있습니다.

따라서 다음 두 문제를 분리해야 합니다.

Database가 한 SQL 문장을 일관되게 실행하는 문제
≠
Application이 과거에 읽은 값의 유효성을 검증하는 문제

Oracle의 Row Lock은 같은 Row를 동시에 물리적으로 변경하지 못하게 하지만, 애플리케이션이 과거에 계산한 절대값을 나중에 저장하는 Lost Update까지 자동으로 막지는 않습니다.

학습 목표

• UPDATE의 후보 탐색과 실제 Row 변경을 구분한다.
• READ COMMITTED에서 충돌하는 Writer가 대기한 뒤 어떻게 진행되는지 설명한다.
• 공식 문서가 명시한 SELECT FOR UPDATE 재시작 사례와 일반 DML의 내부 구현을 구분한다.
• Lost Update가 발생하는 Read-Modify-Write 구조를 식별한다.
• 원자적 조건부 DML, 낙관적 Lock, 비관적 Lock을 업무 특성에 맞게 선택한다.
• SQL%ROWCOUNT, RETURNING, 오류 코드와 업무 로그로 충돌 결과를 판정한다.

1. 공식 문서로 보는 DML의 읽기와 쓰기

1.1 WHERE 절은 암시적 Query다

Oracle 공식 문서는 UPDATE의 WHERE 절에 의해 수행되는 암시적 Query도 일관된 결과 집합을 보장받는다고 설명합니다. 기본 READ COMMITTED에서는 문장이 열린 시점의 일관된 데이터가 후보 탐색 기준이 됩니다.

UPDATE orders
SET    status = 'CANCELLED'
WHERE  order_id = 100
  AND  status = 'READY';

개념적으로 다음 두 관점을 구분하면 이해하기 쉽습니다.

후보 탐색
→ 문장 시점의 일관된 View에서 WHERE 조건을 만족하는 Row 식별

실제 변경
→ 변경할 Row에 Row Lock을 확보하고 SET 표현식을 평가하여 갱신

UPDATE 문서에는 SET 절의 표현식이 각 Row를 갱신할 때 평가된다고 명시되어 있습니다. 이 특성은 balance = balance + :delta처럼 현재 Row 값에 변화량을 적용하는 SQL을 이해하는 근거가 됩니다.

1.2 Row Lock은 Writer를 직렬화한다

Oracle은 Row가 수정될 때 Row Lock을 획득합니다. 다른 Transaction이 같은 Row를 수정하려 하면 선행 Transaction이 끝날 때까지 대기합니다.

Session A가 Row 변경 후 미완료
→ Session B가 같은 Row 변경 시도
→ B는 A의 Commit 또는 Rollback까지 대기

이때 Reader는 일반적으로 Writer를 막지 않고, Writer도 일반 Query Reader를 막지 않습니다. 단, SELECT FOR UPDATE는 조회하면서 Row Lock을 획득하므로 일반 SELECT와 다릅니다.

2. READ COMMITTED의 충돌하는 Write

Oracle 공식 문서의 Conflicting Writes in Read Committed Transactions 설명은 다음 동작을 명시합니다.

선행 Transaction이 Rollback
→ 후행 Transaction은 선행 변경이 없었던 것처럼 진행

선행 Transaction이 Commit
→ 후행 Transaction은 잠금이 풀린 뒤 새로 변경된 Row에 의도한 UPDATE를 진행

이 동작 때문에 Row Lock이 존재해도 Lost Update가 발생할 수 있습니다.

사례: 절대값 덮어쓰기

초기 급여가 6,200이라고 가정합니다.

Session A: salary를 7,000으로 UPDATE, 아직 미완료
Session B: 과거 값 6,200을 기준으로 salary를 6,300으로 UPDATE 시도
Session B: A의 Row Lock 대기
Session A: COMMIT
Session B: 잠금 해제 후 6,300으로 UPDATE, COMMIT

최종값은 6,300이므로 A가 Commit한 7,000은 결과에서 사라집니다. 이것이 Lost Update입니다.

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

Row Lock이 보장하는 것
→ 같은 Row의 물리적 동시 변경 방지와 실행 순서 결정

Row Lock만으로 보장하지 않는 것
→ 후행 Writer가 사용한 과거 업무 값의 유효성

3. Current Row·Predicate 재평가·Statement Restart를 해석하는 기준

3.1 일반 DML의 내부 Restart를 계약처럼 가정하지 않는다

Oracle의 내부 실행 과정에서는 현재 Block, Row Lock, Transaction 상태, Consistent Read가 결합됩니다. 그러나 애플리케이션의 정합성을 다음과 같은 내부 구현 추정에 의존해서는 안 됩니다.

"Oracle이 알아서 Predicate를 다시 검사해 줄 것이다"
"내부 Restart가 Lost Update를 막아 줄 것이다"

공식 문서가 보장하는 핵심은 다음입니다.

• UPDATE의 암시적 Query는 문장 단위 일관된 후보 집합을 사용한다.
• 같은 Row의 충돌하는 Writer는 대기한다.
• 선행 Writer가 Commit하면 후행 Writer가 새로 변경된 Row에 UPDATE를 진행할 수 있다.
• 따라서 Application은 Lost Update 방지 전략을 직접 설계해야 한다.

3.2 공식적으로 명시된 SELECT FOR UPDATE 재시작 사례

Oracle Development Guide는 SELECT FOR UPDATE의 Return Set이 실행 중 변경되면 다음 절차가 일어날 수 있다고 설명합니다.

변경되지 않은 Row Lock 획득
→ Lock을 이용해 Read-Consistent Snapshot 확보
→ 남은 Row의 Lock 획득을 위해 Query Restart

이 사례는 Oracle이 문장 일관성과 Lock 집합을 맞추기 위해 Query를 재시작할 수 있음을 보여 줍니다. 다만 이를 모든 일반 UPDATE·DELETE의 공개된 동일 동작 규칙으로 확대 해석해서는 안 됩니다.

3.3 Statement Restart와 Application Retry

Application Retry는 최신 데이터 재조회, 업무 계산 재수행, 중복 효과 방지까지 포함해야 합니다. 같은 DML을 그대로 다시 보내는 것만으로 안전한 재시도가 되지는 않습니다.

4. Lost Update의 대표 구조

4.1 위험한 Read-Modify-Write

잔액이 100인 계좌를 두 Session이 동시에 읽는 상황입니다.

Session A: 100 조회 → 110 계산
Session B: 100 조회 → 120 계산
Session A: SET balance = 110 → COMMIT
Session B: SET balance = 120 → COMMIT

최종값은 120이며 A의 +10은 사라집니다.

-- 1. 과거 값 읽기
SELECT balance
FROM   account
WHERE  account_id = :id;

-- 2. Application에서 새 절대값 계산

-- 3. 과거 계산 결과 덮어쓰기
UPDATE account
SET    balance = :stale_new_balance
WHERE  account_id = :id;

4.2 Wait Event가 없어도 발생할 수 있다

Lost Update는 항상 긴 Row Lock 대기와 함께 나타나지 않습니다. 두 Transaction이 짧은 간격으로 순차 완료돼도, 후행 UPDATE가 과거 읽기값을 절대값으로 저장하면 앞선 변경을 덮어쓸 수 있습니다.

따라서 enq: TX - row lock contention이 관찰되지 않았다는 사실만으로 Lost Update를 배제하면 안 됩니다.

5. 원자적 UPDATE로 한 문장에 업무 규칙 넣기

5.1 상대값 갱신

단순 증가·감소는 읽고 계산한 절대값을 저장하기보다 한 SQL에서 수행하는 것이 안전합니다.

UPDATE account
SET    balance = balance + :delta
WHERE  account_id = :id;

두 Session이 각각 +10, +20을 실행하면 같은 Row의 변경은 직렬화되고, 각 SET 표현식은 Row가 갱신될 때 평가되므로 최종값에는 두 변화량이 모두 반영됩니다.

5.2 조건부 재고 차감

검증 조건과 변경을 한 Statement에 넣으면 검사와 변경 사이의 시간 간격을 제거할 수 있습니다.

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

SQL%ROWCOUNT = 1
→ 조건을 만족한 Row를 차감함

SQL%ROWCOUNT = 0
→ 상품 없음, 재고 부족, 추가 업무 조건 불충족 중 하나

0건은 SQL 실행 실패가 아니라 조건부 DML의 정상적인 업무 결과일 수 있습니다. 원인별 메시지가 필요하면 추가 조회나 명시적인 상태 코드를 사용합니다.

6. SQL%ROWCOUNT와 RETURNING의 정확한 사용

6.1 SQL%ROWCOUNT

SQL%ROWCOUNT는 가장 최근에 실행한 SELECT 또는 DML이 반환하거나 영향을 준 Row 수입니다. 다른 SQL이나 Subprogram이 실행되면 값의 대상이 바뀔 수 있으므로 DML 직후 지역 변수에 저장하는 것이 안전합니다.

UPDATE product_stock
SET    quantity = quantity - p_order_qty
WHERE  product_id = p_product_id
  AND  quantity >= p_order_qty;

l_affected_rows := SQL%ROWCOUNT;

6.2 RETURNING

변경 후 값을 추가 SELECT 없이 받을 수 있습니다.

UPDATE product_stock
SET    quantity = quantity - p_order_qty
WHERE  product_id = p_product_id
  AND  quantity >= p_order_qty
RETURNING quantity INTO l_remaining_qty;

주의할 점은 Statement가 0건을 변경하면 RETURNING 대상 변수 값이 정의되지 않는다는 것입니다. 성공 여부를 먼저 확인하고, 0건인 경우 반환 변수의 이전 값이나 임의 값을 업무 결과로 사용해서는 안 됩니다.

7. 낙관적 Lock: 원본 값 또는 Version 검증

사용자가 데이터를 읽은 뒤 나중에 저장하는 화면 업무에서는 충돌을 기다리기보다 저장 시점에 검출하는 방식이 적합할 수 있습니다.

7.1 원본 값 비교

Oracle 공식 문서는 마지막 조회 이후 Row가 바뀌지 않았는지 확인하기 위해 원래 값을 WHERE 절에 포함하는 Lost Update 방지 예를 제시합니다.

UPDATE employees
SET    email        = :new_email,
       phone_number = :new_phone
WHERE  employee_id  = :employee_id
  AND  email        = :old_email
  AND  phone_number = :old_phone;

7.2 Version Column

여러 Column을 비교하는 대신 Version 번호를 사용할 수 있습니다.

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

SQL%ROWCOUNT = 1
→ 읽은 뒤 Version이 유지되어 저장 성공

SQL%ROWCOUNT = 0
→ 다른 Transaction 변경, Row 삭제, 또는 다른 조건 불충족 가능

0건이면 최신 Row를 다시 조회해 충돌 원인을 구분하고, 사용자에게 병합·재입력·취소 중 적절한 선택을 제공하거나 최신 값으로 업무 계산을 다시 수행합니다.

8. 비관적 Lock: SELECT FOR UPDATE

변경 전에 Row를 선점해야 하는 짧은 Transaction에서는 SELECT FOR UPDATE를 사용할 수 있습니다.

SELECT balance
FROM   account
WHERE  account_id = :id
FOR UPDATE;

• 선택된 Row에 Exclusive Row Lock을 획득합니다.
• Lock은 Transaction의 Commit 또는 Rollback까지 유지됩니다.
• 다른 Writer와 SELECT FOR UPDATE는 해당 Row Lock을 기다립니다.
• 사용자 입력을 기다리는 긴 화면 Transaction에서는 Lock 보유 시간이 길어질 수 있습니다.

대기 정책은 상황에 따라 선택합니다.

SKIP LOCKED는 일반 화면 저장 충돌을 숨기기 위한 기능이 아니라, 여러 Consumer가 서로 잠긴 작업 Row를 건너뛰는 Queue 처리에 설계된 기능입니다.

9. SERIALIZABLE과 Application Retry

SERIALIZABLE Transaction은 Transaction 시작 이후 다른 Transaction이 변경하고 Commit한 Row를 갱신하려 하면 ORA-08177: Cannot serialize access for this transaction이 발생할 수 있습니다.

이 오류를 처리할 때는 다음을 구분합니다.

Database 오류 처리
→ Savepoint Rollback, Transaction 전체 Rollback, 업무 취소 여부 결정

Application 재시도
→ 최신 상태 재조회
→ 업무 규칙 재평가
→ 제한된 횟수와 Backoff 적용
→ 중복 효과 방지

ORA-08177을 받았다고 기존 계산 결과를 그대로 반복 저장하면 최신 업무 상태를 반영하지 못할 수 있습니다.

10. Idempotency와 외부 Side Effect

재시도 가능한 업무에는 Database 변경 이외의 효과가 섞일 수 있습니다.

• 결제 API 호출
• 메시지·Email 발송
• File 기록
• 다른 시스템 요청

이 작업들은 Database Rollback만으로 되돌아가지 않을 수 있습니다. 따라서 다음 패턴을 검토합니다.

Database 내부의 구현 세부사항이나 단순한 무조건 재시도에 외부 Side Effect의 정확성을 맡기면 안 됩니다.

11. 실전 진단 절차

11.1 SQL 구조 확인

과거 값을 SELECT한 뒤 절대값으로 UPDATE하는가?
→ Lost Update 후보

SET col = col + :delta 형태인가?
→ Current 값 기준 원자적 변경인지 업무 의미 확인

WHERE에 old_version 또는 원본 값이 있는가?
→ 낙관적 충돌 검출 가능

11.2 실행 결과 확인

• SQL%ROWCOUNT를 DML 직후 저장했는가?
• 0건을 오류가 아닌 업무 결과로 구분했는가?
• RETURNING 0건일 때 출력 변수를 사용하지 않는가?
• ORA-08177, ORA-00060, ORA-00054 등을 동일한 재시도 정책으로 뭉뚱그리지 않는가?

11.3 로그와 감사 정보

다음 값을 함께 기록하면 덮어쓰기 순서를 재구성하기 쉽습니다.

• 업무 Key와 요청 ID
• 읽은 원본 값 또는 Version
• 저장하려던 값과 실제 변경 후 값
• UPDATE Row Count
• Transaction 시작·Commit 시각
• 오류 코드와 재시도 횟수
• Idempotency Key

Wait Event는 보조 증거입니다. Lost Update 판정의 핵심은 과거에 읽은 값이 어떤 Predicate로 검증되었는지와 각 UPDATE가 어떤 값을 저장했는지입니다.

12. 방식 선택 기준

어떤 방식도 이름만 적용해서는 충분하지 않습니다. 충돌 시 대기·실패·재조회·병합·재시도 가운데 무엇을 할지 업무 정책으로 정의해야 합니다.

13. 혼동하기 쉬운 판단

14. 핵심 판단 순서

1. 변경을 한 SQL로 표현할 수 있는가?
2. 과거 조회값을 절대값으로 덮어쓰는가?
3. 현재 상태를 WHERE 조건이나 Version으로 검증하는가?
4. 충돌 시 기다릴지, 즉시 실패할지, 재시도할지 정했는가?
5. DML Row Count와 오류 코드를 업무 결과로 해석하는가?
6. 재시도 시 외부 Side Effect의 중복을 막는가?