아무도 고치지 않은 주문인데 '다른 사용자가 먼저 수정했습니다'가 세 번에 한 번 뜬다

중급DDL한빛유통526 / 700

상황

한빛유통 콜센터의 「결제 수단 변경」 화면은 결제 전(PENDING) 주문의 결제 수단을 바꾼다. 지난달 개발팀은 동시 수정으로 변경이 사라지는 일을 막으려고 낙관적 잠금을 넣었다. 테이블은 고치지 않고, 주문을 열 때 ORA_ROWSCN을 함께 읽어 두었다가 저장할 때 비교한다. 반영 다음 날부터 상담원들의 불만이 쏟아졌다. "아무도 안 건드린 주문인데 저장하면 '다른 사용자가 먼저 수정했습니다'가 떠요. 다시 열고 저장하면 또 떠요."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록(블록 하나에 약 112행) · PK_T_ORD (ORD_SEQ) · 만들 때 옵션을 주지 않았다(기본값 NOROWDEPENDENCIES)
결제 콜백(PG사)과 배송 상태 배치가 최근 주문의 ORD_ST, PAY_DT를 하루 약 46,000번 고치고 바로 COMMIT한다. 최근 주문은 테이블 끝쪽 블록에 모여 있다.
상담원이 주문을 열어 두는 시간은 보통 1 ~ 3분이다.
점검 시간은 매일 02:00 ~ 02:30이다.

원본 SQL

sql

-- 주문 열기
SELECT ORD_SEQ, CUS_ID, ORD_ST, PAY_TP, ORD_AMT, ORA_ROWSCN AS ROW_SCN
  FROM T_ORD
 WHERE ORD_SEQ = :ord_seq;

-- 저장 — 연 뒤로 아무도 안 고쳤을 때만 (0행이면 "다른 사용자가 먼저 수정했습니다")
UPDATE T_ORD
   SET PAY_TP = :pay_tp
 WHERE ORD_SEQ = :ord_seq
   AND ORA_ROWSCN = :row_scn;
COMMIT;

실행계획 학습용 예시

xplan

[저장 실패 한 건 — 주문 98,214 (PENDING)]
14:20:03   주문 열기 → ROW_SCN 48,213,907
14:21:40   저장 → UPDATE 0행 → "다른 사용자가 먼저 수정했습니다"
           그 사이 주문 98,214 를 고친 트랜잭션: 없음 (감사 로그)
           같은 블록의 다른 주문을 고친 결제 콜백: 9번 (14:20:11 ~ 14:21:33)

[같은 블록의 행 — 14:21:40]
SELECT ORD_SEQ, ORD_ST, ORA_ROWSCN, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLK
  FROM T_ORD WHERE ORD_SEQ BETWEEN 98201 AND 98312;
ORD_SEQ   ORD_ST     ORA_ROWSCN    BLK
98,201    COMPLETE   48,214,566    1183
98,202    COMPLETE   48,214,566    1183
 …
98,214    PENDING    48,214,566    1183     ← 14:20 이후 아무도 고치지 않았다
 …
98,312    COMPLETE   48,214,566    1183     (112행 모두 같은 값)

[저장 UPDATE — ALLSTATS LAST (0행)]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |          |      1 |        |      0 | 00:00:00.01 |       3 |
|   1 |  UPDATE                       | T_ORD    |      1 |        |      0 | 00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | T_ORD    |      1 |      1 |      0 | 00:00:00.01 |       3 |
|*  3 |    INDEX UNIQUE SCAN          | PK_T_ORD |      1 |      1 |      1 | 00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORA_ROWSCN"=:ROW_SCN)
   3 - access("ORD_SEQ"=:ORD_SEQ)

[반영 뒤 일주일]  저장 1,920번 · 0행 실패 604번 (31%) · 실제로 다른 사람이 같은 주문을 고친 경우 3번

문제

주문 98,214는 아무도 고치지 않았는데 왜 0행이 되는지 ORA_ROWSCN의 성질과 위의 조회 결과로 설명하시오.
거짓 충돌이 나지 않도록 고치시오. 고친 방법의 대가와, 다른 방법을 골랐을 때의 차이도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 점검 시간(02:00)에 행마다 SCN 을 적는 테이블로 다시 만든다 — ROWDEPENDENCIES 는 만들 때만 줄 수 있다
CREATE TABLE T_ORD_RD ROWDEPENDENCIES
AS SELECT * FROM T_ORD;

ALTER TABLE T_ORD RENAME TO T_ORD_OLD;
ALTER TABLE T_ORD_OLD RENAME CONSTRAINT PK_T_ORD TO PK_T_ORD_OLD;
ALTER INDEX PK_T_ORD RENAME TO PK_T_ORD_OLD;
ALTER TABLE T_ORD_RD RENAME TO T_ORD;
ALTER TABLE T_ORD ADD CONSTRAINT PK_T_ORD PRIMARY KEY (ORD_SEQ);
-- 다른 인덱스 · 권한 · 동의어 · 트리거를 옮기고 건수를 대조한 뒤 T_ORD_OLD 를 지운다

-- 화면 SQL 은 그대로 — 이제 ORA_ROWSCN 은 그 행을 마지막으로 고친 트랜잭션의 커밋 SCN 이다
UPDATE T_ORD
   SET PAY_TP = :pay_tp
 WHERE ORD_SEQ = :ord_seq
   AND ORA_ROWSCN = :row_scn;
COMMIT;

개선 후 실행계획 학습용 예시

xplan

[다시 만든 뒤 — 같은 블록의 이웃 행 (학습용 예시)]
ORD_SEQ   ORD_ST     ORA_ROWSCN
98,213    COMPLETE   48,214,402     ← 결제 콜백이 고친 행
98,214    PENDING    48,170,015     ← 다시 만든 뒤 아무도 고치지 않은 행은 그대로
98,215    COMPLETE   48,214,566
  저장 0행 실패   31% (604번) → 0.2% 안팎 (실제로 같은 주문을 고친 경우만)
  T_ORD          896 → 약 970블록 (행마다 6바이트) · 저장 UPDATE 는 그대로 Buffers 3

모범 답안 서술

ORA_ROWSCN은 기본 설정(NOROWDEPENDENCIES)에서 행 단위가 아니라 블록 단위의 값이다. 블록 안의 어느 행이든 고친 트랜잭션이 COMMIT하면 그 블록의 SCN이 올라가고, 블록 안의 모든 행이 같은 ORA_ROWSCN을 돌려준다. 위 조회에서 블록 1183의 112행이 모두 48,214,566인 것이 그 증거다. 주문 98,214는 14:20 이후 아무도 고치지 않았지만, 같은 블록의 다른 주문 9건을 결제 콜백이 고치고 COMMIT했다. 그래서 상담원이 열 때 읽은 48,213,907과 저장할 때의 값이 달라지고, 2번 줄의 filter("ORA_ROWSCN"=:ROW_SCN)에서 행이 걸러져 0행이 된다. 최근 주문은 테이블 끝쪽 몇 블록에 모여 있고 콜백이 하루 46,000번 그 블록들을 고치므로, 저장 1,920번 가운데 604번(31%)이 거짓 충돌로 실패했다. 실제로 같은 주문을 고친 경우는 3번뿐이었다. 고친 판은 점검 시간에 T_ORD를 ROWDEPENDENCIES로 다시 만든다. 그러면 행마다 마지막으로 고친 트랜잭션의 SCN을 따로 적으므로, 이웃 행이 바뀌어도 98,214의 ORA_ROWSCN은 그대로다. 화면 SQL은 고치지 않아도 되고, 실패는 실제로 같은 주문을 고친 경우만 남는다(31% → 0.2% 안팎). 대가는 둘이다. 행마다 6바이트가 늘어 896 → 약 970블록이 된다. 그리고 이 옵션은 테이블을 만들 때만 정할 수 있고 ALTER TABLE로는 켤 수 없으므로, CTAS나 DBMS_REDEFINITION으로 다시 만들고 PK와 인덱스, 권한을 옮겨야 한다. 테이블을 다시 만들 수 없다면 다른 방법이 있다. 버전 칸 VER_NO를 더하는 방법은 행 단위로 정확하지만, 콜백과 배치처럼 T_ORD를 고치는 모든 쪽이 버전을 올려야 한다. 열 때 읽은 값을 WHERE에서 비교하는 방법(AND PAY_TP = :old_pay_tp AND ORD_ST = :old_ord_st)은 테이블을 고치지 않고 이 화면이 지키려는 칸만 본다. 콜백이 ORD_ST를 COMPLETE로 바꾸는 진짜 충돌은 잡고, PAY_DT처럼 상관없는 칸이 바뀐 것으로는 실패하지 않는다.

해설

ORA_ROWSCN은 COMMIT된 변경만 반영한다. 다른 세션이 COMMIT 전에 그 행을 고치고 있으면 저장 UPDATE는 그 행에서 기다렸다가, 상대가 COMMIT하면 바뀐 값을 보고 0행이 되고 ROLLBACK하면 그대로 저장한다. 버전 칸도 똑같이 동작한다.
SCN_TO_TIMESTAMP(ORA_ROWSCN)으로 "언제 고쳤나"를 보여 주는 화면도 블록 단위의 함정을 똑같이 겪는다. 게다가 오래된 SCN은 시각으로 바꾸지 못하고 에러(ORA-08181)가 난다.
서비스를 멈출 수 없으면 DBMS_REDEFINITION으로 중간 테이블을 ROWDEPENDENCIES로 만들어 옮긴다. 896블록짜리 테이블은 CTAS로도 몇 초면 끝나므로 점검 시간에 처리하는 편이 단순하다. 다시 만드는 동안 들어온 주문이 빠지지 않게 화면과 콜백을 멈춘다.
블록 단위 ORA_ROWSCN도 쓸모가 있다. 오래 바뀌지 않은 블록을 찾거나 대략 언제 고쳐졌는지 보는 데는 충분하다. 다만 행 하나의 충돌을 가리는 데는 쓸 수 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황