상담원 둘이 같은 고객을 고치면 먼저 저장한 쪽의 변경이 사라진다

기초UPDATE한빛유통524 / 700

상황

한빛유통 콜센터의 「고객 정보 수정」 화면은 고객을 열 때 그 고객 한 행을 읽어 띄우고, 「저장」을 누르면 화면의 칸을 전부 그대로 UPDATE한다. 저장은 하루 약 3,000번이다. 3월 12일 상담원 1이 마케팅팀 요청대로 고객 CUS_000417의 등급을 B에서 A로 올렸다. 다음 날 그 고객이 "승급 문자를 받았는데 화면에는 아직 B 등급이에요"라고 전화했다. 에러 로그에는 아무것도 없었다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD(A 1% · B 9% · C 90%) · RGN_ID 지역
화면은 커넥션 풀을 쓴다. 고객을 여는 SELECT와 저장 UPDATE는 서로 다른 요청이고, 저장 요청 안에서 UPDATE 뒤 바로 COMMIT한다.
감사 로그: 2월에 같은 고객을 두 사람이 10분 안에 저장한 경우가 41번 있었다.
이 문제에서는 M_CUS에 칸을 더해도 된다.

원본 SQL

sql

-- 「고객 정보 수정」: 고객을 열 때
SELECT CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD
  FROM M_CUS
 WHERE CUS_ID = :cus_id;

-- 「저장」: 화면의 칸을 전부 그대로 쓴다
UPDATE M_CUS
   SET CUS_NM = :cus_nm, RGN_ID = :rgn_id, GND_TP = :gnd_tp,
       BTH_YMD = :bth_ymd, CUS_GD = :cus_gd
 WHERE CUS_ID = :cus_id;
COMMIT;

실행계획 학습용 예시

xplan

[시간표 — 3월 12일 · 고객 CUS_000417]
시각        상담원 1 (SID 63) · VIP 승급 처리               상담원 2 (SID 118) · 주소 이전 문의
14:02:10    고객 열기 → RGN_ID 03 · CUS_GD B
14:02:25                                                    고객 열기 → RGN_ID 03 · CUS_GD B
14:03:05    CUS_GD 를 A 로 바꿔 저장 → UPDATE 1행 · COMMIT
14:03:46                                                    RGN_ID 를 05 로 바꿔 저장 → UPDATE 1행 · COMMIT
                                                            (화면에 남아 있던 CUS_GD 'B' 까지 그대로 쓴다)
14:04 이후  M_CUS : RGN_ID 05 · CUS_GD B   ← 상담원 1 의 승급이 사라졌다

[저장 UPDATE — ALLSTATS LAST (14:03:46 · 상담원 2)]
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |          |      1 |        |      0 | 00:00:00.01 |       4 |
|   1 |  UPDATE             | M_CUS    |      1 |        |      0 | 00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN | PK_M_CUS |      1 |      1 |      1 | 00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"=:CUS_ID)

[V$SESSION — 14:03:46]  enq: TX 기다림 없음 · 에러 없음 (상담원 1 은 41초 전에 COMMIT 했다)

[감사 로그 — 2월]  같은 고객을 10분 안에 두 사람이 저장 41번 · 그중 앞사람의 변경이 사라진 경우 38번

문제

시간표를 보고 상담원 1의 승급이 왜 사라졌는지, 왜 아무도 기다리지 않았고 에러도 나지 않았는지 설명하시오.
늦게 저장하는 쪽이 먼저 저장된 변경을 모른 채 덮어쓰지 못하도록 고치시오. 고객을 열 때 SELECT … FOR UPDATE로 잠가 두는 방법이 이 화면에 맞지 않는 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 버전 칸 (기존 행은 0)
ALTER TABLE M_CUS ADD (VER_NO NUMBER DEFAULT 0 NOT NULL);

-- 고객을 열 때 — 버전도 같이 읽어 화면이 들고 있는다
SELECT CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD, VER_NO
  FROM M_CUS
 WHERE CUS_ID = :cus_id;

-- 저장 — 내가 읽은 뒤로 아무도 고치지 않았을 때만 쓴다
UPDATE M_CUS
   SET CUS_NM = :cus_nm, RGN_ID = :rgn_id, GND_TP = :gnd_tp,
       BTH_YMD = :bth_ymd, CUS_GD = :cus_gd,
       VER_NO = VER_NO + 1
 WHERE CUS_ID = :cus_id
   AND VER_NO = :ver_no;
-- SQL%ROWCOUNT = 0 이면 COMMIT 하지 않고 "다른 상담원이 먼저 고쳤습니다" → 새 값을 다시 읽어 보여 준다
COMMIT;

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

xplan

[고친 뒤 — 같은 시간표 (학습용 예시)]
14:03:05  상담원 1 : UPDATE … AND VER_NO = 7 → 1행 · VER_NO 8 · COMMIT
14:03:46  상담원 2 : UPDATE … AND VER_NO = 7 → 0행 → "다른 상담원이 먼저 고쳤습니다"
          → 다시 읽기 (CUS_GD A · VER_NO 8) → RGN_ID 만 바꿔 저장 → 1행 · VER_NO 9
  저장 UPDATE 는 그대로 PK 한 건 · Buffers 4 · 기다림 0 · 앞사람의 변경이 사라진 경우 38번 → 0

모범 답안 서술

14:03:46의 UPDATE는 PK로 한 행만 고치는 4블록짜리 문장이고 기다림도 에러도 없었다. 문제는 SQL의 효율이 아니라 값의 출처다. 상담원 2의 화면은 14:02:25에 읽은 CUS_GD = 'B'를 그대로 들고 있다가, 주소만 바꾸고 저장하면서 화면의 칸을 전부 SET에 넣었다. 그래서 41초 전에 상담원 1이 COMMIT한 승급(A)을 옛 값 B로 덮어썼다. 두 사람이 같은 행을 읽고 각자 고친 값을 쓰면서 앞사람의 변경이 사라지는 이 모양이 갱신 손실(Lost Update)이다. 아무도 기다리지 않은 것은 SELECT가 행을 잠그지 않고, 상담원 2의 UPDATE가 실행될 때 상담원 1의 트랜잭션은 이미 COMMIT으로 끝나 있었기 때문이다. 오라클은 COMMIT된 값을 덮어쓰는 UPDATE를 정상으로 처리한다. 2월에만 38번 같은 일이 있었는데도 에러 로그에 남지 않은 이유다. 고친 판은 고객 행에 버전 칸 VER_NO를 더하고, 고객을 열 때 버전을 함께 읽는다. 저장할 때는 WHERE CUS_ID = :cus_id AND VER_NO = :ver_no로 내가 읽은 버전일 때만 고치고 VER_NO = VER_NO + 1로 올린다. 상담원 2의 저장은 버전이 7에서 8로 바뀐 뒤라 0행을 고친다. 화면은 0행이면 COMMIT하지 않고 "다른 상담원이 먼저 고쳤습니다"를 띄운 뒤 새 값을 다시 읽어 보여 준다. 저장 문장은 여전히 PK 한 건(Buffers 4)이고 기다림도 늘지 않는다. 잠그지 않고 저장할 때만 확인하는 이 방식이 낙관적 동시성 제어다. 고객을 열 때 SELECT … FOR UPDATE로 잠그는 비관적 제어는 이 화면에 맞지 않는다. 상담원이 통화하는 몇 분 동안 행 락을 쥐므로, 그 고객을 고치려는 등급 배치와 다른 화면이 그만큼 기다린다. 화면을 열어 둔 채 자리를 비우면 기다림은 끝이 없다. 같은 고객을 동시에 고치는 일은 하루 3,000번 저장 가운데 한두 번이므로, 모두를 잠그기보다 드문 충돌을 저장할 때 잡는 편이 싸다.

해설

버전 칸 대신 읽어 둔 값을 WHERE에 모두 넣는 방법도 같은 원리다(AND CUS_GD = :old_gd AND RGN_ID = :old_rgn …). 칸을 더하지 않아도 되지만 칸이 많으면 문장이 길어지고, NULL인 칸은 =로 비교되지 않으므로 따로 처리해야 한다.
상담원 1의 UPDATE가 아직 COMMIT 전이었다면 상담원 2의 UPDATE는 그 행에서 기다렸다가, COMMIT 뒤 현재 값으로 WHERE를 다시 확인한다. 버전이 이미 8이므로 이때도 0행이 된다. 두 저장이 겹쳐도 버전 검사는 맞게 동작한다.
바뀐 칸만 SET에 넣는 동적 UPDATE도 피해를 줄인다. 다만 두 사람이 같은 칸을 고치면 여전히 늦은 쪽이 이기므로 버전 검사를 대신하지는 못한다.
ALTER TABLE … ADD (VER_NO NUMBER DEFAULT 0 NOT NULL)은 11g부터 기존 행을 고치지 않고 딕셔너리만 바꾸므로 큰 테이블에서도 금방 끝난다. 그래도 DDL이라 잠깐 테이블 락을 잡으므로 한가한 시간에 돌린다.
고객을 고치는 다른 프로그램도 버전을 올려야 화면이 그 변경을 알아챈다. 쓰는 쪽이 여럿이면 모두가 올리도록 강제하는 방법(트리거)까지 함께 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황