출고 작업자 8명이 한 명처럼 일한다

중급SELECT이커머스499 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스 물류센터에서는 출고 작업자 8명이 태블릿의 「다음 건」 버튼으로 출고 대기 한 건을 받아 포장한다. 평일 오전 출고 대기는 400건 남짓이고, 포장 한 건에 2~4분이 걸린다. 센터장의 말이다. "8명이 있는데 출고 속도는 한 명일 때랑 비슷해요. 태블릿이 자꾸 모래시계예요."

테이블과 인덱스

shipping: 34,908행 · 183블록 · PK shipping_id · status(READY 출고 대기 · PACKED · SHIPPED · DELIVERED …) · 오전 09:00 READY 412행 · status에는 인덱스도 히스토그램도 없다
앱은 트랜잭션 하나로 일한다: 「다음 건」 SELECT → 화면에 첫 행 → 포장 → 「완료」 UPDATE · COMMIT

원본 SQL

sql

-- 「다음 건」 — 출고 대기 중 가장 오래된 한 건 (앱은 첫 행만 화면에 띄운다)
SELECT shipping_id, order_id, address_id
  FROM shipping
 WHERE status = 'READY'
 ORDER BY shipping_id
   FOR UPDATE;

-- 포장이 끝나면 「완료」
UPDATE shipping SET status = 'PACKED' WHERE shipping_id = :id;
COMMIT;

실행계획 학습용 예시

xplan

[「다음 건」 한 번 — ALLSTATS LAST (앱은 한 행만 가져갔다)]
------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |      1 |00:00:00.01 |     597 |          |
|   1 |  FOR UPDATE         |          |      1 |        |      1 |00:00:00.01 |     597 |          |
|   2 |   SORT ORDER BY     |          |      1 |   5818 |    412 |00:00:00.01 |     183 | 36864 (0)|
|*  3 |    TABLE ACCESS FULL| SHIPPING |      1 |   5818 |    412 |00:00:00.01 |     183 |          |
------------------------------------------------------------------------------------------------------
   3 - filter("STATUS"='READY')

[시간표 — 평일 09:00 · 출고 대기 412 건 · 태블릿 8대 중 셋]
시각       작업자 A (SID 201)                        작업자 B (SID 214)                       작업자 C (SID 233)
09:00:00   「다음 건」 → 412 행 잠금 · #88123 포장 시작
09:00:05                                             「다음 건」 → 기다림
09:00:12                                                                                      「다음 건」 → 기다림
09:03:10   「완료」 UPDATE · COMMIT → 412 행 풀림     깨어남 → 남은 411 행 잠금 · #88124        (이제 B 를 기다림)
09:06:02                                             「완료」 COMMIT                          깨어남 → 410 행 잠금 · #88125

[V$SESSION — 09:02:00]
SID   EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
201   SQL*Net message from client       120                      (포장 중 — 트랜잭션을 연 채)
214   enq: TX - row lock contention     115   201                SELECT … FOR UPDATE
233   enq: TX - row lock contention     108   201                SELECT … FOR UPDATE

[V$LOCK — 09:02:00]
SID   TYPE  대상 / 이름표       LMODE  REQUEST  BLOCK
201   TM    SHIPPING              3      0        0
201   TX    A 의 트랜잭션          6      0        1
214   TM    SHIPPING              3      0        0
214   TX    A 의 트랜잭션          0      6        0
233   TM    SHIPPING              3      0        0
233   TX    A 의 트랜잭션          0      6        0

[09:00 ~ 10:00 출고 완료] 21 건 — 8명이 포장 2 ~ 4분이면 120 ~ 240 건이어야 한다

문제

실행계획과 시간표, V$LOCK을 근거로 B와 C가 무엇을 기다리는지, 첫 행 하나만 보는 SELECT가 왜 다른 작업자를 막는지 설명하시오.
8명이 동시에 서로 다른 건을 받도록 고치시오.
SKIP LOCKED, NOWAIT, WAIT n이 각각 어떤 화면에 맞는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 「다음 건」 — 남이 잡은 건은 건너뛰고 한 건만 잠근다 · 집은 즉시 커밋
DECLARE
  CURSOR c IS
    SELECT shipping_id, order_id, address_id
      FROM shipping
     WHERE status = 'READY'
     ORDER BY shipping_id
       FOR UPDATE SKIP LOCKED;
  r c%ROWTYPE;
BEGIN
  OPEN c;
  FETCH c INTO r;                -- SKIP LOCKED 는 가져오는 행만 잠근다 — 한 행 가져오고 닫는다
  IF c%FOUND THEN
    UPDATE shipping SET status = 'PICKING' WHERE CURRENT OF c;   -- 집었다는 표시
  END IF;
  CLOSE c;
  COMMIT;                        -- 포장 전에 커밋 — 포장 3분 동안 쥐는 락이 없다
END;

-- 포장 완료 — 짧은 트랜잭션 하나
UPDATE shipping SET status = 'PACKED' WHERE shipping_id = :id AND status = 'PICKING';
COMMIT;

-- (선택) 대기열을 정렬된 채로 찾는다 — FULL 183 블록 · 정렬 대신
CREATE INDEX IX_SHIPPING_STATUS ON shipping (status, shipping_id);

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

xplan

[고친 뒤 — 학습용 예시]
  「다음 건」 한 번   FETCH 1 행 · 잠그는 행 1 · Buffers 약 186 (인덱스를 만들면 한 자릿수)
  B · C 는 A 가 잡은 #88123 을 건너뛰고 #88124 · #88125 를 받는다 — 기다림 0
  집은 즉시 COMMIT → 포장 동안 쥐는 락 없음 · 09:00 ~ 10:00 출고 21 → 약 160 건

모범 답안 서술

A의 SELECT … FOR UPDATE는 커서를 여는 순간 결과 집합 전체(READY 412행)를 잠근다. 앱이 첫 행 하나만 화면에 띄워도 412행이 A의 트랜잭션에 묶인다(Buffers 183 → 597이고, 그 차이 412가 행마다 잠그러 간 current 읽기다). 그리고 A는 포장하는 3분 동안 COMMIT하지 않으므로 그동안 락을 쥐고 있다. B와 C의 같은 SELECT는 A가 잠근 첫 행에서 멈춘다. V$LOCK에는 TX REQUEST 6이 보이고, 이벤트는 enq: TX - row lock contention, blocking_session은 201이다. A가 COMMIT하면 B가 깨어나 남은 411행을 통째로 잠그고, C는 이제 B를 기다린다. 8명이 한 줄로 서서 한 명씩 일하는 셈이다. 고친 판은 커서를 FOR UPDATE SKIP LOCKED로 열고 한 행만 FETCH한다. SKIP LOCKED는 가져오는 행만 잠그고 다른 세션이 잠근 행은 건너뛰므로, 8명이 기다리지 않고 서로 다른 건을 받는다. 집은 건은 상태를 PICKING으로 바꾸고 곧바로 COMMIT한다. 포장하는 3분 동안 락을 쥐지 않는 짧은 트랜잭션이다. ROWNUM = 1로 자르는 것은 답이 아니다. 모두가 같은 첫 행을 노려 같은 줄에 서고, SKIP LOCKED와 함께 쓰면 첫 행이 잠겼을 때 0건이 된다. SKIP LOCKED는 '아무 건이나 하나'면 되는 대기열(출고 큐, 작업 큐)에 맞다. NOWAIT는 기다리지 않고 바로 ORA-00054를 받으므로, "다른 사람이 처리 중"이라고 알려 줄 화면, 곧 특정 한 건을 고치는 편집 화면에 맞다. WAIT n은 락이 금방 풀리는 짧은 작업에서 n초까지만 기다렸다가 ORA-30006을 받는 방식이다. 잠깐의 충돌은 넘기면서도 사람을 끝없이 세워 두지 않는다.

해설

SKIP LOCKED는 순서를 보장하지 않는다. 잠긴 건을 건너뛰므로 가장 오래된 건이 늦게 나갈 수 있다. 순서가 업무 규칙이라면(선착순 처리 등) 다른 설계가 필요하다.
PICKING 상태에도 대가가 있다. 작업자가 태블릿을 끄고 사라지면 그 건이 PICKING에 남는다. 그래서 일정 시간이 지난 PICKING을 READY로 되돌리는 정리 작업과, 누가 집었는지 적는 작업자 칼럼을 함께 둔다. 새 상태값은 업무 쪽과 합의한다.
인덱스 (status, shipping_id)에도 대가가 있다. 상태가 바뀔 때마다 인덱스도 고친다(출고 한 건에 두세 번). 183블록이면 FULL로 읽어도 견딜 만하니 꼭 필요하지는 않다. 만든다면 status에 히스토그램이 있어야 옵티마이저가 READY가 412행이라는 것을 안다(지금은 6종 중 하나로 보고 E-Rows를 5,818로 잡았다).
SELECT … FOR UPDATE의 TM 모드를 문서는 RS(2)로 적지만, 이 교재의 컨테이너에서는 3이었다(교재 12화). 어느 쪽이든 TM끼리는 서로 막지 않는다. 막는 것은 TX다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황