동시성 튜닝 - 채번·핫블록·락 경합

이 장에서 배우는 것

신간 예약 판매가 시작되면 같은 책을 주문하는 요청이 짧은 시간에 몰린다. 평소에는 즉시 끝나던 주문 SQL이 이때만 느려진다면 실행계획과 읽은 블록 수만으로 원인을 설명하기 어렵다. 한 행을 찾는 비용은 작아도 다른 트랜잭션의 종료를 기다리는 시간은 길 수 있기 때문이다.

앞 장에서 다룬 대량 DML은 한 작업이 처리하는 양과 유지 비용이 중심이었다. 여기서는 여러 작업이 같은 번호와 재고 행을 동시에 다룰 때의 정확성과 응답 시간을 살펴본다. 목표는 온라인 서점의 주문 접수 화면에서 중복 주문 번호와 초과 판매를 방지하면서 불필요한 직렬 처리를 줄이는 것이다.

• MAX+1 채번에서 중복 번호가 발생하는 순서를 설명하고 시퀀스로 바꾼다.
• 재고 확인과 차감을 하나의 조건부 UPDATE로 묶는다.
• 비관적 락과 낙관적 락의 적용 범위 및 실패 처리 기준을 구분한다.
• 실행계획, 논리 읽기, 대기 이벤트를 함께 읽어 병목의 위치를 판단한다.
• 두 세션 실험으로 대기와 재검증을 관찰하고 트랜잭션 경계를 점검한다.

문제 상황

서점은 예약 판매 주문을 접수할 때 주문 번호를 구하고 재고를 확인한 뒤 주문 행을 저장한다. 기존 구현은 다음과 같은 순서였다. 애플리케이션은 조회한 값을 기억했다가 별도 SQL에 전달했다.

SELECT NVL(MAX(order_id), 0) + 1
FROM bc_order;

SELECT qty
FROM bc_stock
WHERE book_id = 101;

-- 애플리케이션에서 재고가 충분한지 확인한다.
UPDATE bc_stock
SET qty = :previous_qty - :requested_qty
WHERE book_id = 101;

INSERT INTO bc_order(order_id, book_id, quantity)
VALUES (:new_order_id, 101, :requested_qty);

COMMIT;

문제는 세 가지였다. 첫째, 두 세션이 같은 최대 주문 번호를 읽어 동일한 다음 번호를 만들었다. 기본 키가 중복 저장은 막았지만 한 요청은 대기한 뒤 오류로 끝났다. 둘째, 두 세션이 같은 재고를 읽은 뒤 계산 결과를 덮어써 판매 수량과 재고가 어긋났다. 셋째, 이를 막으려고 주문 테이블 전체에 잠금을 걸자 서로 다른 책을 주문하는 요청까지 줄을 섰다.

이 장의 실습은 Oracle 19c의 기본 격리 수준인 READ COMMITTED를 사용한다. 전용 실습 스키마에서 실행하며 주문 테이블에는 20,000건, 재고 테이블에는 한 권의 재고 10개를 준비한다. 외부 결제나 배송 요청은 포함하지 않는다. 핵심은 데이터베이스 안에서 주문 저장과 재고 차감을 하나의 트랜잭션으로 처리하는 것이다.

아래 수치 표의 성능 값은 해석 방법을 설명하기 위한 가상 관측값이다. 실제 Oracle에서 측정했다고 주장하는 값이 아니다. 완성 코드에는 실제 실행계획과 버퍼 통계를 수집하는 스크립트를 포함한다. 고정된 업무 출력과 환경에 따라 달라지는 성능 출력을 구분해서 읽어야 한다.

채번 경쟁과 핫블록을 구분한다

MAX+1은 번호를 예약하지 않는다

일반 SELECT가 반환한 최대값은 그 번호를 독점할 권리를 뜻하지 않는다. 두 세션이 각각 20,000을 읽으면 둘 다 20,001을 계산한다. 첫 세션이 해당 번호를 INSERT하고 커밋하지 않은 상태에서 두 번째 세션이 같은 번호를 INSERT하면 두 번째 세션은 기다릴 수 있다. 첫 세션이 커밋하면 중복 키 오류가 발생하고, 롤백하면 두 번째 INSERT가 진행될 수 있다.

최대값을 찾는 작업 자체는 빠를 수 있다. 기본 키 인덱스의 끝부분을 읽는 최솟값·최댓값 최적화가 적용되면 테이블 전체를 읽지 않는다. 따라서 MAX+1의 문제를 무조건 전체 스캔 문제라고 설명하면 진단이 어긋난다. 적은 논리 읽기로도 잘못된 동시성 제어를 구현할 수 있다.

시퀀스(sequence)는 테이블의 현재 최대값을 읽지 않고 번호를 발급한다. 동일한 비순환 시퀀스가 발급한 값을 그대로 사용하는 요청 사이에서는 채번 경쟁으로 같은 값이 반환되지 않는다. 다만 직접 입력한 번호나 별도 채번 경로와 충돌하는 것까지 막아 주지는 않으므로 기본 키 제약은 유지한다.

시퀀스 값은 트랜잭션을 롤백해도 되돌아가지 않는다. 캐시(cache)에 확보한 값을 장애나 인스턴스 종료로 사용하지 못할 수도 있다. 주문 번호의 결번은 정상적으로 발생할 수 있으며, 발급 순서와 커밋 순서도 같지 않다. 화면 정렬은 주문 번호만으로 업무 발생 순서를 추정하지 말고 별도의 시간 및 정렬 기준으로 정의한다.

운영 테이블을 시퀀스로 전환할 때는 기존 채번 요청을 통제하고 시작값을 정해야 한다. 서비스가 계속 MAX+1을 사용하는 동안 최대값만 읽고 시퀀스를 만들면 전환 직후 충돌할 수 있다. 예제는 쓰는 세션이 없는 새 스키마에서 데이터를 준비한 다음 20,001부터 시작한다.

번호 중복과 블록 집중은 다른 문제다

시퀀스로 바꾸어도 순차 번호를 받는 인덱스의 오른쪽 끝 블록에는 INSERT가 집중될 수 있다. 여러 세션이 짧은 시간에 같은 블록을 사용하려는 상황을 핫블록(hot block) 문제라고 부른다. 시퀀스 캐시를 늘리면 번호 확보 작업의 빈도는 줄지만 인덱스 삽입 위치가 분산되는 것은 아니다.

또한 같은 재고 행을 갱신하는 대기와, 서로 다른 행이 우연히 같은 블록에 있어 발생하는 경쟁은 구분해야 한다. 전자는 업무상 같은 자원을 변경하는 충돌이다. 후자는 블록 구조와 동시 접근 양상이 관련될 수 있다. 대기 이름 하나만 보고 인덱스를 재구성하거나 재고 행을 여러 개로 나누지 않는다.

번호 순서로 범위를 검색하지 않는 인덱스라면 역방향 키 인덱스를 검토할 수 있지만, 기존 범위 검색에 영향을 줄 수 있다. 이 사례에서는 블록 집중이 입증되지 않았으므로 인덱스 형태를 바꾸지 않는다. 먼저 채번 경쟁을 제거하고 재고 잠금의 보유 시간을 줄인다.

재고 차감과 락의 선택

판단 조건을 갱신문 안에 둔다

재고 확인과 차감을 분리하지 않고 다음 조건부 UPDATE로 처리한다. 요청 수량은 양의 정수여야 하며, qty와 version_no는 NULL을 허용하지 않는다.

UPDATE bc_stock
SET qty = qty - :requested_qty,
    version_no = version_no + 1
WHERE book_id = 101
  AND qty >= :requested_qty;

영향받은 행이 한 건이면 차감에 성공한 것이다. 0건이면 해당 책이 없거나 재고가 부족하다. 이 예제에서는 책 101이 존재함을 보장하므로 0건을 재고 부족으로 해석한다. 일반 서비스에서는 상품 없음과 재고 부족을 같은 응답으로 처리할지 별도 확인할지 결정해야 한다.

같은 행을 다른 세션이 수정하고 있으면 UPDATE는 기다릴 수 있다. 앞선 변경이 커밋되면 대기하던 문장은 변경된 상태에 맞춰 조건을 재검증한다. 재고가 부족해졌다면 차감하지 않는다. 이 설명은 예제의 READ COMMITTED를 전제로 하며, 다른 격리 수준에서 발생하는 직렬화 오류까지 같은 방식으로 취급해서는 안 된다.

정확한 재고를 한 행에서 관리한다면 그 행의 변경은 순서를 가져야 한다. 조건부 UPDATE가 대기를 없애는 것은 아니다. 조회와 애플리케이션 판단 사이의 틈을 없애고 필요한 변경만 짧게 수행하게 한다. 결제 서버 호출이나 사용자 입력 대기를 이 트랜잭션 안에 넣으면 단순한 UPDATE도 오래 기다리게 된다.

비관적 락과 낙관적 락

비관적 락(pessimistic locking)은 변경할 행을 먼저 잠근 상태에서 판단하는 방식이다. SELECT FOR UPDATE로 행을 확보한 뒤 여러 값을 확인하고 갱신할 수 있다. 다만 단순한 재고 차감이라면 조건부 UPDATE만으로 충분하다. UPDATE 자체도 행을 잠그므로 조회문을 추가할 필요가 없다.

낙관적 락(optimistic locking)은 조회 시점의 버전을 기억하고 갱신 조건에 넣는 방식이다. 버전 7을 읽었다면 UPDATE의 조건에 version_no = 7을 포함한다. 다른 트랜잭션이 먼저 수정하여 버전이 8이 되었다면 영향받은 행 수가 0이므로 오래된 판단을 적용하지 않는다.

낙관적 락도 UPDATE를 실행할 때는 데이터베이스의 행 잠금을 사용하며 대기할 수 있다. 차이는 조회부터 수정까지 잠금을 유지하지 않는다는 점이다. 사용자가 재고 조정 화면을 열어 두는 동안 행을 잠그지 않고, 저장할 때 충돌을 알려 주는 상황에 적합하다. 버전을 사용하는 모든 변경 경로가 버전을 증가시켜야 이 약속이 유지된다.

재고 변경 방식은 판단 시간과 충돌 처리 정책에 따라 선택한다

방식	보장하는 것	충돌 시 처리	적합한 작업
조건부 UPDATE	현재 수량에 대한 차감 조건	대기 후 조건 재검증, 0건 확인	짧은 주문 접수
SELECT FOR UPDATE	확보한 행을 잠근 상태의 판단	대기 또는 지정한 잠금 오류	여러 속성을 확인하는 짧은 변경
버전 조건 UPDATE	조회 이후 수정 여부 확인	0건이면 새로 조회하고 사용자 판단	사람이 편집하는 관리 화면

여러 책을 한 주문에서 차감한다면 모든 경로가 같은 book_id 순서로 잠금을 획득하게 한다. 세션마다 다른 순서로 여러 행을 잠그면 교착 상태가 발생할 수 있다. 재시도는 원인을 가리는 반복문이 아니라 횟수 제한과 중복 주문 방지 정책을 갖춘 트랜잭션 단위 처리여야 한다.

실행계획과 대기 시간을 함께 읽는다

실제 실행 통계를 포함한 실행계획에서 Buffers는 해당 커서 실행의 논리 읽기 비용을 판단하는 단서다. 부모 연산의 값에 자식 작업이 포함될 수 있으므로 각 행의 Buffers를 모두 더해서 총량으로 삼지 않는다. 또한 NEXTVAL 조회 커서의 통계만으로 시퀀스 내부의 모든 비용이나 주문 트랜잭션 전체 비용을 대표할 수는 없다.

가상 관측값은 읽기 비용과 잠금 대기를 따로 해석해야 함을 보여 준다

관측 대상	변경 전	변경 후	해석
채번 계획의 핵심 연산	INDEX FULL SCAN (MIN/MAX)	SEQUENCE, FAST DUAL	주문 인덱스에서 최대값을 찾지 않는다
채번 커서 Buffers	3	0	예시 수치이며 내부 비용 전체가 아니다
재고 UPDATE 접근 경로	기본 키 INDEX UNIQUE SCAN	기본 키 INDEX UNIQUE SCAN	경로가 같아도 정확성은 달라진다
재고 UPDATE Buffers	4	4	적은 읽기만으로 대기를 설명할 수 없다
다른 세션의 잠금 보유 시간	2,000ms	20ms	외부 작업을 분리한 가상 비교다

표의 시간 감소는 SQL 조건을 추가하면 자동으로 얻는 결과가 아니다. 잠금을 보유한 채 수행하던 외부 작업을 트랜잭션 밖으로 옮겼다는 별도의 가정이 있다. 실제 비교에서는 동시 세션 수, 주문 수량 분포, 커밋 위치를 고정하고 처리량, 응답 시간 분포, 오류 및 재시도 수를 함께 기록한다.

Oracle의 enq: TX - row lock contention은 다른 트랜잭션과의 충돌을 조사하는 출발점이다. 같은 행의 변경뿐 아니라 미커밋 중복 키 INSERT 등에서도 나타날 수 있다. buffer busy waits는 블록 사용 경쟁의 단서지만 특정 인덱스의 끝 블록이라는 결론을 바로 내려 주지는 않는다. enq: TX - allocate ITL entry는 블록 안의 트랜잭션 정보를 기록할 공간과 관련된 경쟁을 살펴볼 단서다.

실습 중 대기 세션은 다음 조회로 확인할 수 있다. 별도의 관찰 세션에 V_$SESSION 조회 권한이 필요하다. STATE가 WAITING인지 먼저 보고 EVENT와 BLOCKING_SESSION을 연결한다. 기다리고 있지 않은 세션의 EVENT는 직전 대기일 수 있다.

SELECT sid, serial#, state, event, wait_class,
       blocking_session, seconds_in_wait, sql_id
FROM v$session
WHERE username = 'BOOKLAB'
ORDER BY sid;

잠금을 보유한 세션은 SQL 실행을 마치고 클라이언트의 다음 요청을 기다리는 중일 수도 있다. 따라서 대기 세션의 SQL만 고쳐서는 문제가 남는다. 차단 세션이 마지막 변경 이후 왜 커밋하지 않았는지를 애플리케이션 호출 흐름과 함께 확인한다. 다중 인스턴스 환경에서는 GV$SESSION과 인스턴스 식별자까지 연결해야 한다.

Oracle 19c와 MySQL 8은 같은 업무 규칙을 서로 다른 세부 기능으로 구현한다

항목	Oracle 19c	MySQL 8, InnoDB
자동 번호	시퀀스의 NEXTVAL 사용	AUTO_INCREMENT 사용, 일반 시퀀스 객체는 제공하지 않음
기본 격리 수준	READ COMMITTED	REPEATABLE READ
재고 조건부 변경	UPDATE 후 SQL%ROWCOUNT 확인	UPDATE 후 영향받은 행 수 확인
명시적 잠금 조회	FOR UPDATE, NOWAIT, WAIT n	FOR UPDATE, NOWAIT 지원. 대기 제한은 관련 설정 사용
범위 조건의 잠금	InnoDB의 넥스트 키 잠금 방식과 다름	격리 수준과 접근 경로에 따라 갭을 포함한 잠금 가능
실행 및 대기 관찰	DBMS_XPLAN, V$SESSION	8.0.18 이상 EXPLAIN ANALYZE, performance_schema의 잠금 정보

MySQL에서 잠금 조회와 후속 변경을 묶으려면 명시적 트랜잭션을 사용해야 한다. 두 제품 모두 번호의 결번을 허용해야 하며 낙관적 락이 갱신 잠금을 없애 주지는 않는다. MySQL의 실행계획에는 Oracle의 Buffers와 같은 의미의 값이 그대로 나오지 않으므로 숫자를 직접 대조하지 않는다.

세부 사실 확인에는 Oracle 시퀀스 문서, Oracle 동시성과 일관성 문서, MySQL 잠금 조회 문서를 참고할 수 있다.

완성 코드

다음 네 파일은 Oracle 19c용 SQL*Plus 스크립트다. macOS 또는 Linux에 Oracle 접속이 가능한 SQL*Plus 클라이언트를 준비한다. 새 실습 스키마에는 CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE 권한과 테이블스페이스 할당량이 필요하다. setup.sql은 한 번만 실행하며 같은 이름의 객체가 있으면 오류로 종료한다.

코드는 주문과 재고 테이블, 시퀀스, 주문 프로시저를 모두 생성한다. 프로시저는 성공하더라도 커밋하지 않는다. 실패 시 저장점(savepoint)까지 되돌려 호출 이전 작업을 보존하고, 최종 커밋 여부는 호출자가 결정한다. 컴파일 경고를 활성화하고 USER_ERRORS에 오류나 경고가 있으면 준비 작업을 실패시킨다. 여기서 실제 Oracle 실행 검증을 수행한 것은 아니므로 서버별 검증은 해당 검사와 실습 실행으로 확인한다.

setup.sql

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

CREATE TABLE bc_stock (
    book_id     NUMBER(12) CONSTRAINT bc_stock_pk PRIMARY KEY,
    qty         NUMBER(10) NOT NULL,
    version_no  NUMBER(10) DEFAULT 0 NOT NULL,
    CONSTRAINT bc_stock_qty_ck CHECK (qty >= 0)
);

CREATE TABLE bc_order (
    order_id  NUMBER(18) CONSTRAINT bc_order_pk PRIMARY KEY,
    book_id   NUMBER(12) NOT NULL,
    quantity  NUMBER(10) NOT NULL,
    CONSTRAINT bc_order_qty_ck CHECK (quantity > 0),
    CONSTRAINT bc_order_book_fk
        FOREIGN KEY (book_id) REFERENCES bc_stock(book_id)
);

INSERT INTO bc_stock(book_id, qty, version_no)
VALUES (101, 10, 0);

INSERT INTO bc_order(order_id, book_id, quantity)
SELECT LEVEL, 101, 1
FROM dual
CONNECT BY LEVEL <= 20000;

CREATE SEQUENCE bc_order_seq
    START WITH 20001
    INCREMENT BY 1
    MAXVALUE 999999999999999999
    NOCYCLE
    CACHE 1000
    NOORDER;

CREATE OR REPLACE PROCEDURE bc_buy (
    p_qty IN NUMBER
) AUTHID DEFINER
IS
BEGIN
    -- [A] 입력 검증은 행 잠금을 얻기 전에 수행한다.
    IF p_qty IS NULL OR p_qty <= 0 OR p_qty <> TRUNC(p_qty)
       OR p_qty > 9999999999 THEN
        RAISE_APPLICATION_ERROR(-20002, '수량은 범위 내 양의 정수여야 한다');
    END IF;

    -- [B] 이 호출에서 수행한 변경만 되돌릴 경계를 만든다.
    SAVEPOINT bc_buy_begin;

    BEGIN
        -- [C] 수량 확인, 차감, 버전 증가를 한 문장으로 처리한다.
        UPDATE bc_stock
        SET qty = qty - p_qty,
            version_no = version_no + 1
        WHERE book_id = 101
          AND qty >= p_qty;

        -- [D] 다른 SQL을 실행하기 전에 영향받은 행 수를 검사한다.
        IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '재고 부족');
        END IF;

        -- [E] 채번과 주문 저장은 재고 차감과 같은 트랜잭션이다.
        INSERT INTO bc_order(order_id, book_id, quantity)
        VALUES (bc_order_seq.NEXTVAL, 101, p_qty);
    EXCEPTION
        WHEN OTHERS THEN
            -- [F] 주문 저장 실패도 재고 차감과 함께 취소한다.
            ROLLBACK TO bc_buy_begin;
            RAISE;
    END;
END;
/

DECLARE
    v_errors PLS_INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_errors
    FROM user_errors
    WHERE name = 'BC_BUY'
      AND type = 'PROCEDURE';

    IF v_errors > 0 THEN
        RAISE_APPLICATION_ERROR(-20010, 'BC_BUY 컴파일 진단을 확인한다');
    END IF;
END;
/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER, tabname => 'BC_STOCK', cascade => TRUE);
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER, tabname => 'BC_ORDER', cascade => TRUE);
END;
/

COMMIT;

DECLARE
    v_qty      NUMBER;
    v_version  NUMBER;
    v_old      NUMBER;
    v_orders   NUMBER;
    v_rows     PLS_INTEGER;
BEGIN
    bc_buy(3);

    SELECT qty INTO v_qty
    FROM bc_stock WHERE book_id = 101;

    IF v_qty <> 7 THEN
        RAISE_APPLICATION_ERROR(-20011, '첫 차감 결과 불일치');
    END IF;
    DBMS_OUTPUT.PUT_LINE('차감 성공: 재고=7');

    BEGIN
        bc_buy(8);
        RAISE_APPLICATION_ERROR(-20012, '재고 부족 검증 실패');
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE <> -20001 THEN
                RAISE;
            END IF;
    END;

    SELECT qty INTO v_qty
    FROM bc_stock WHERE book_id = 101;

    IF v_qty <> 7 THEN
        RAISE_APPLICATION_ERROR(-20013, '실패 후 재고 불일치');
    END IF;
    DBMS_OUTPUT.PUT_LINE('재고 부족: 재고=7');

    -- [G] 조회 이후 다른 변경이 발생한 상태를 순서대로 만든다.
    SELECT version_no INTO v_old
    FROM bc_stock WHERE book_id = 101;

    bc_buy(1);

    UPDATE bc_stock
    SET qty = qty - 1,
        version_no = version_no + 1
    WHERE book_id = 101
      AND version_no = v_old
      AND qty >= 1;

    v_rows := SQL%ROWCOUNT;
    IF v_rows <> 0 THEN
        RAISE_APPLICATION_ERROR(-20014, '오래된 버전 검증 실패');
    END IF;
    DBMS_OUTPUT.PUT_LINE('버전 충돌: 변경 행=0');

    SELECT qty, version_no INTO v_qty, v_version
    FROM bc_stock WHERE book_id = 101;

    SELECT COUNT(*) INTO v_orders FROM bc_order;

    IF v_qty <> 6 OR v_version <> 2 OR v_orders <> 20002 THEN
        RAISE_APPLICATION_ERROR(-20015, '최종 상태 불일치');
    END IF;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('최종 상태: 재고=6, 버전=2, 주문=20002');
END;
/

EXIT SUCCESS

session_a.sql

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET SERVEROUTPUT ON
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

BEGIN
    bc_buy(4);
    DBMS_OUTPUT.PUT_LINE('A: 4개 차감, 커밋 대기');
END;
/

ACCEPT release CHAR PROMPT 'B를 실행한 뒤 Enter를 누른다: '
COMMIT;
PROMPT A: 커밋 완료
EXIT SUCCESS

session_b.sql

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET SERVEROUTPUT ON
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

PROMPT B: 4개 차감을 요청한다
BEGIN
    BEGIN
        bc_buy(4);
        RAISE_APPLICATION_ERROR(-20020, '예상과 달리 차감에 성공했다');
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE <> -20001 THEN
                RAISE;
            END IF;
            DBMS_OUTPUT.PUT_LINE('B: 재고 부족으로 차감하지 않았다');
    END;
END;
/

DECLARE
    v_qty NUMBER;
BEGIN
    SELECT qty INTO v_qty
    FROM bc_stock WHERE book_id = 101;

    IF v_qty <> 2 THEN
        RAISE_APPLICATION_ERROR(-20021, '동시 실행 결과 불일치');
    END IF;
    DBMS_OUTPUT.PUT_LINE('B: 최종 재고=2');
END;
/

COMMIT;
EXIT SUCCESS

measure.sql

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

SPOOL concurrency-plan.txt

PROMPT === MAX+1 ===
SELECT /*+ GATHER_PLAN_STATISTICS */ NVL(MAX(order_id), 0) + 1
FROM bc_order;

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

PROMPT === SEQUENCE ===
SELECT /*+ GATHER_PLAN_STATISTICS */ bc_order_seq.NEXTVAL
FROM dual;

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

PROMPT === BEFORE: APPLICATION VALUE ===
UPDATE /*+ GATHER_PLAN_STATISTICS */ bc_stock
SET qty = 5
WHERE book_id = 101;

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

ROLLBACK;

PROMPT === AFTER: CONDITIONAL UPDATE ===
UPDATE /*+ GATHER_PLAN_STATISTICS */ bc_stock
SET qty = qty - 1,
    version_no = version_no + 1
WHERE book_id = 101
  AND qty >= 1;

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

ROLLBACK;

SPOOL OFF
EXIT SUCCESS

measure.sql은 setup.sql 직후, 다른 세션이 작업하지 않을 때 실행한다. 이전 방식의 qty = 5는 현재 재고 6에서 애플리케이션이 1을 뺀 값을 전달한 상황이다. 두 UPDATE 모두 롤백하므로 재고는 그대로다. NEXTVAL로 소비한 번호는 복구되지 않는다.

실행 통계를 읽으려면 DBMS_XPLAN 실행 권한과 V_$SQL, V_$SQL_PLAN, V_$SESSION, V_$SQL_PLAN_STATISTICS_ALL에 대한 조회 권한이 필요하다. 실습 관리자가 필요한 권한만 부여한다. SQL*Plus 이외의 도구가 대상 SQL과 DISPLAY_CURSOR 사이에 내부 SQL을 실행하면 마지막 커서가 바뀔 수 있으므로 그때는 대상 SQL_ID와 자식 커서 번호를 명시한다.

줄별 해설

테이블 정의의 기본 키는 번호 중복을 최종적으로 차단한다. 재고의 CHECK 제약은 음수 저장을 막는 방어선이며, 업무적으로 재고가 충분한지 판단하는 조건을 대신하지 않는다. 초기 주문 20,000건은 인덱스 접근을 관찰하기 위한 과거 이력이다. 현재 재고 10개와 과거 판매량을 대사하는 모델은 아니다.

[A]는 NULL, 0, 음수, 소수와 저장 범위를 벗어난 수량을 거부한다. 잘못된 요청을 행 잠금 전에 걸러낸다. [B]는 프로시저 내부 변경을 되돌릴 위치를 정한다. 저장점 이름은 트랜잭션 안에서 공유되므로 호출자가 같은 이름을 사용하지 않는 규칙이 필요하다.

[C]는 재고를 읽어 애플리케이션에 전달하지 않고 현재 값에서 차감한다. version_no도 함께 증가시켜 관리 화면의 낙관적 갱신과 약속을 맞춘다. [D]는 바로 앞 UPDATE의 결과를 확인한다. SQL%ROWCOUNT는 이후 실행한 SQL에 의해 바뀌므로 필요한 위치에서 즉시 읽거나 변수에 저장한다.

[E]에서 주문 번호를 발급한다. 재고 부족이면 여기까지 도달하지 않지만, INSERT 이후 다른 이유로 롤백하면 결번은 생긴다. [F]는 주문 저장 실패 시 먼저 성공한 재고 차감을 취소하고 원래 오류를 다시 전달한다. 오류를 출력만 하고 정상 반환하면 호출자가 성공으로 오해할 수 있다.

[G]는 두 세션을 쓰지 않고도 오래된 버전이 거절되는지를 검사한다. 조회, 다른 변경, 오래된 버전의 UPDATE라는 순서를 한 세션에서 만든 기능 검사다. 실제 세션 사이의 잠금 대기는 session_a.sql과 session_b.sql로 따로 확인한다.

측정 파일의 GATHER_PLAN_STATISTICS는 해당 SQL의 실행 통계를 수집하게 한다. DISPLAY_CURSOR는 곧바로 앞서 실행한 SQL의 실제 계획을 조회한다. SELECT 결과를 끝까지 가져온 뒤 확인하는 것도 중요하다. 실행하지 않은 예상 계획만으로 실제 처리 행 수나 논리 읽기를 판단하지 않는다.

실행 결과

접속 서비스 이름은 환경에 맞게 바꾼다. 다음 명령은 비밀번호를 명령행에 넣지 않고 프롬프트에서 입력한다. 출력 예시는 비밀번호 프롬프트를 제외한 스크립트 출력이다. 한글 표시를 위해 클라이언트 문자셋과 터미널 인코딩도 맞춘다.

sqlplus -s booklab@//localhost:1521/ORCLPDB1 @setup.sql

차감 성공: 재고=7
재고 부족: 재고=7
버전 충돌: 변경 행=0
최종 상태: 재고=6, 버전=2, 주문=20002

이 출력은 코드가 상태를 검사한 뒤 내보내는 고정 문자열이다. 기대한 상태와 다르면 정상 출력 대신 오류로 종료한다. 생성된 프로시저의 컴파일 경고와 오류 역시 검사 대상이다.

다음으로 측정 파일을 실행한다. 결과는 화면과 concurrency-plan.txt에 기록된다. 실행계획의 식별자, Buffers, 실행 시간과 부가 설명은 환경마다 달라지므로 고정된 예상 출력으로 제시하지 않는다. 앞의 가상 수치 표를 측정 결과로 복사하지 말고 이 파일에 나온 값을 사용한다.

sqlplus -s booklab@//localhost:1521/ORCLPDB1 @measure.sql

MAX+1에서는 기본 키 인덱스의 최댓값 접근 여부를, 시퀀스 조회에서는 SEQUENCE 연산을 확인한다. 재고는 한 행짜리 작은 테이블이므로 기본 키가 있어도 전체 스캔이 선택될 수 있다. 계획 이름을 예상에 맞추려고 힌트를 추가하지 말고 실제 선택된 경로와 Buffers를 기록한다. 한 번의 차이가 캐시 상태나 블록 정리 작업 때문일 수 있으므로 같은 조건의 반복 실행도 비교한다.

이제 터미널 A에서 다음 파일을 실행한다. 입력 프롬프트가 나오면 Enter를 누르지 않고 둔다.

sqlplus -s booklab@//localhost:1521/ORCLPDB1 @session_a.sql

A: 4개 차감, 커밋 대기
B를 실행한 뒤 Enter를 누른다: 

터미널 B에서 다음 파일을 실행한다. 첫 문구가 나온 뒤 A의 트랜잭션이 끝나기를 기다린다. DBMS_OUTPUT은 블록이 끝나야 전달되므로 대기 시작 표시는 SQL*Plus의 PROMPT로 출력했다.

sqlplus -s booklab@//localhost:1521/ORCLPDB1 @session_b.sql

B: 4개 차감을 요청한다

터미널 A에서 Enter를 누르면 A는 다음 문구를 출력한다.

A: 커밋 완료

B에는 이어서 다음 문구가 출력된다. 대기 시간은 A에서 Enter를 누른 시점에 따라 달라지며 의도적으로 숫자로 출력하지 않는다.

B: 재고 부족으로 차감하지 않았다
B: 최종 재고=2

같은 실험을 반복하려면 모든 실습 세션이 종료한 뒤 재고와 버전을 초기화한다. 아래 초기화는 전용 실습 데이터에서만 사용하며 과거 주문 행은 유지한다. setup.sql 전체를 다시 실행할 필요는 없다.

UPDATE bc_stock
SET qty = 6, version_no = 2
WHERE book_id = 101;
COMMIT;

A가 커밋 대신 롤백하면 B는 재고 6을 기준으로 차감에 성공한다. 이 경우 현재 B 스크립트는 커밋 시나리오 전용 검증이므로 예상 불일치 오류를 낸다. 두 결과를 혼동하지 않도록 실험의 종료 방식을 먼저 정한다.

실무에서 자주 틀리는 것

MAX+1 앞에 테이블 잠금을 추가한다

다음 코드는 번호를 구하는 동안 전체 주문 테이블의 쓰기를 직렬화한다. 정확성 문제를 처리하면서 필요 이상으로 많은 요청을 함께 기다리게 만든다.

LOCK TABLE bc_order IN EXCLUSIVE MODE;

INSERT INTO bc_order(order_id, book_id, quantity)
SELECT NVL(MAX(order_id), 0) + 1, 101, 1
FROM bc_order;

번호 발급은 시퀀스로 분리한다. 다음은 채번 부분만 고친 코드이며, 실제 주문에서는 완성 코드처럼 재고 차감과 함께 실행한다.

INSERT INTO bc_order(order_id, book_id, quantity)
VALUES (bc_order_seq.NEXTVAL, 101, 1);

조회한 재고로 현재 값을 덮어쓴다

아래 코드는 조회 이후의 변경을 잃을 수 있다. CHECK 제약으로 음수를 막아도 이런 덮어쓰기는 검출하지 못한다.

UPDATE bc_stock
SET qty = :previous_qty - :requested_qty
WHERE book_id = 101;

현재 값에서 차감하고 영향받은 행 수를 확인한다. 관리 화면의 수정이라면 조회한 버전도 조건에 추가하고 0건을 충돌로 처리한다.

UPDATE bc_stock
SET qty = qty - :requested_qty,
    version_no = version_no + 1
WHERE book_id = 101
  AND qty >= :requested_qty;

하위 프로시저가 먼저 커밋한다

재고 차감 직후 커밋하면 주문 INSERT가 실패해도 차감만 남는다. 다음 순서는 주문과 재고의 원자성을 깨뜨린다.

UPDATE bc_stock
SET qty = qty - 1
WHERE book_id = 101 AND qty >= 1;

COMMIT;

INSERT INTO bc_order(order_id, book_id, quantity)
VALUES (bc_order_seq.NEXTVAL, 101, 1);

프로시저가 두 변경을 묶고 호출자가 커밋한다. 여러 업무를 묶는 호출자는 전체 실패 시 어디까지 롤백할지도 정해야 한다. 예제 프로시저의 저장점은 해당 호출의 변경만 취소한다.

BEGIN
    bc_buy(1);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

충돌을 무제한 재시도로 감춘다

재고 부족이나 버전 충돌을 즉시 반복하면 성공 가능성이 없는 요청이 부하를 늘린다. 다음 예는 낙관적 락의 오래된 버전을 계속 사용하는 잘못된 흐름이다.

LOOP
    UPDATE bc_stock
    SET qty = qty - 1,
        version_no = version_no + 1
    WHERE book_id = 101
      AND version_no = v_old_version
      AND qty >= 1;

    EXIT WHEN SQL%ROWCOUNT = 1;
END LOOP;

한 번 시도한 뒤 충돌을 호출자에게 알린다. 재조회와 재시도는 요청의 업무 의미를 다시 판단한 후 수행한다. 네트워크 오류로 커밋 결과를 모르는 경우에는 같은 주문을 다시 만들지 않도록 별도 요청 식별자와 유일성 제약도 필요하다.

UPDATE bc_stock
SET qty = qty - 1,
    version_no = version_no + 1
WHERE book_id = 101
  AND version_no = v_old_version
  AND qty >= 1;

IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20030, '상태를 다시 확인한다');
END IF;

한눈에 보기

동시성 문제는 기다리는 대상과 업무 불변식을 함께 확인한다

관찰한 현상	확인할 근거	우선 조치	검증 기준
주문 번호 중복	MAX+1 실행 순서와 기본 키 오류	시퀀스 전환, 기존 채번 통제	동시 INSERT의 번호 충돌 제거
재고 불일치	이전 조회값을 사용하는 UPDATE	조건부 차감과 행 수 검사	성공 주문 수량과 재고 변화 일치
같은 책의 긴 대기	차단 세션과 트랜잭션 보유 시간	잠금 중 외부 작업 제거	같은 부하에서 대기와 응답 시간 감소
오래된 화면의 덮어쓰기	조회 버전과 현재 버전	버전 조건 추가	충돌 시 변경 행 0건 처리
서로 다른 행의 블록 경쟁	대기 블록, 객체, 접근 집중도	실제 집중 위치에 맞는 구조 검토	대기 감소와 기존 검색 영향 확인

이 사례에서 실행계획은 어떤 블록에 접근하는지 설명하고, 대기 정보는 그 접근이 왜 즉시 끝나지 않는지 설명한다. 읽기 비용이 작다고 동시성 문제가 없다는 뜻은 아니다. 다음 장에서 주문 조회 화면을 단계적으로 개선할 때도 SQL 자체의 작업량과 다른 트랜잭션 때문에 소비한 시간을 나누어 판단한다.

연습 문제

1. 두 세션의 MAX+1 조회가 각각 Buffers 3으로 끝났다. 두 세션이 같은 주문 번호를 얻을 수 있는가. 기본 키 제약이 있을 때 두 번째 INSERT의 결과를 첫 세션의 커밋과 롤백으로 나누어 설명하라.
2. 재고 6에서 A가 4개를 차감하고 커밋하지 않았다. B도 조건부 UPDATE로 4개를 차감하려 한다. A가 커밋할 때와 롤백할 때 B의 영향받은 행 수 및 최종 재고를 구하라.
3. 관리 화면이 version_no = 12를 읽었다. 다른 작업이 재고만 변경하고 버전을 증가시키지 않았다. 관리 화면이 버전 조건으로 저장하면 무엇을 놓칠 수 있는가. 수정 규칙을 제시하라.
4. 순차 번호 인덱스에서 buffer busy waits가 관찰되었다. 시퀀스 캐시를 100에서 1,000으로 늘리면 해결된다고 결론 내려도 되는가. 추가로 확인할 근거와 변경 후 검증 항목을 제시하라.

정답과 해설

1. 같은 번호를 얻을 수 있다. 논리 읽기 수는 번호의 독점 여부와 관계없다. 첫 INSERT가 미커밋인 동안 두 번째 INSERT는 기다릴 수 있다. 첫 세션이 커밋하면 두 번째는 중복 키 오류가 나고, 롤백하면 다른 제약 위반이 없는 한 진행할 수 있다. 시퀀스로 번호 발급을 분리하되 기본 키도 유지한다.
2. A가 커밋하면 재고는 2가 된다. B는 대기 이후 조건을 재검증하여 0건을 변경하고 재고는 2로 남는다. A가 롤백하면 재고가 6으로 돌아오므로 B는 1건을 변경하여 2로 만든다. 이때 B도 커밋해야 그 결과가 확정된다. 두 경우의 최종 숫자는 같지만 성공한 주문의 주체가 다르다.
3. 버전이 여전히 12이므로 관리 화면은 중간 변경이 없었다고 오인할 수 있다. 버전으로 보호하는 상태를 변경하는 모든 경로에서 같은 UPDATE로 버전도 증가시켜야 한다. 배치, 운영 보정, 관리자 기능도 예외로 두지 않는다. 예제의 실습 초기화는 다른 세션이 모두 종료된 상태에서만 수행한다.
4. 그렇게 결론 내릴 수 없다. 캐시 증가는 시퀀스 번호 확보 빈도를 줄이지만 순차 키의 삽입 위치를 분산하지 않는다. 실제 대기 블록이 해당 인덱스인지, 시퀀스 관련 경쟁이 함께 있는지, 동시에 실행하는 세션 수가 얼마인지 확인한다. 변경 후에는 대기 시간과 처리량뿐 아니라 범위 검색 비용, 오류 수, 논리 읽기 변화도 같은 부하 조건에서 비교한다.