Oracle 19c 비인덱스 FK 잠금: 부모 DELETE 때 자식 테이블이 막히는 이유

Oracle 19c에서 자식 테이블의 외래키(FK) 컬럼에 인덱스가 없으면, 부모 키를 삭제하거나 변경할 때 자식 테이블 전체에 잠금이 걸릴 수 있어요. 평소 INSERT가 잘 된다는 이유만으로 안전하다고 판단하면 안 됩니다. 부모 키 DELETE·UPDATE·MERGE가 있는 업무라면 FK 선두 컬럼을 지원하는 인덱스가 있는지 먼저 확인해야 합니다.

다만 외래키마다 기계적으로 인덱스를 추가하는 것도 정답은 아닙니다. 부모 키를 전혀 삭제·변경하지 않고 자식 테이블 쓰기 비용이 더 중요한 관계라면 예외가 될 수 있어요. 실제 DML 경로와 기존 인덱스의 선두 컬럼을 함께 보고 결정해야 합니다.

비인덱스 FK 잠금은 언제 문제가 되나

외래키는 자식 행이 실제 부모 행을 가리키는지 보장합니다. 부모 키가 삭제되거나 값이 바뀌면 Oracle은 자식 행이 남아 있는지 확인해야 해요. 자식 FK를 빠르게 찾을 인덱스가 없으면 이 확인 범위가 커지고, 자식 테이블 잠금 때문에 서로 관련 없어 보이는 세션까지 기다릴 수 있습니다.

적용 조건·영향 범위

최소 예제로 잠금 조건을 구분하기

아래 예제는 운영 테이블이 아니라 별도 테스트 스키마에서 실행하세요. 부모 부서와 자식 주문의 단순 관계를 만들고, 일부러 자식 FK 인덱스를 생략합니다.

CREATE TABLE demo_department (
    department_id NUMBER(10) CONSTRAINT demo_department_pk PRIMARY KEY,
    department_name VARCHAR2(100) NOT NULL
);

CREATE TABLE demo_order (
    order_id NUMBER(10) CONSTRAINT demo_order_pk PRIMARY KEY,
    department_id NUMBER(10) NOT NULL,
    order_amount NUMBER(12,2) NOT NULL,
    CONSTRAINT demo_order_fk_department
        FOREIGN KEY (department_id)
        REFERENCES demo_department (department_id)
);

INSERT INTO demo_department (department_id, department_name)
VALUES (10, 'SALES');

INSERT INTO demo_department (department_id, department_name)
VALUES (20, 'SUPPORT');

INSERT INTO demo_order (order_id, department_id, order_amount)
VALUES (1001, 10, 50000);

COMMIT;

두 세션에서 서로 다른 부모 키에 대한 작업을 겹쳐 봅니다. 먼저 세션 A에서 부서 10의 자식 행을 변경하고 COMMIT하지 않습니다. 이어 세션 B에서 참조 자식 행이 없는 부서 20을 삭제합니다. 이 예제는 실행 결과를 보장하는 테스트 로그가 아니라 Oracle 19c에서 잠금 원인을 확인하기 위한 재현 절차입니다.

-- 세션 A: 자동 커밋을 끈 테스트 세션
UPDATE demo_order
   SET order_amount = order_amount + 1000
 WHERE order_id = 1001;
-- 아직 COMMIT 또는 ROLLBACK하지 않습니다.

-- 세션 B: 별도의 테스트 세션
DELETE FROM demo_department
 WHERE department_id = 20;

세션 B가 기다린다면 세션 A의 미완료 자식 DML과 비인덱스 FK 검사에 필요한 테이블 잠금의 관계를 확인합니다. V$SESSION·V$LOCK으로 차단 세션을 찾는 순서에 따라 대기 이벤트와 객체를 기록하세요. 세션 A에서 ROLLBACK한 뒤 B가 진행되는지 관찰하고, B에서도 ROLLBACK해 테스트 데이터를 되돌립니다.

잠금 유지 시간에 주의하세요. 비인덱스 FK 검사 때문에 자식 테이블에 잡는 잠금은 부모 키 변경 문장 실행 중 유지됩니다. 부모 DELETE가 끝난 뒤 COMMIT하지 않았다는 이유만으로 같은 테이블 잠금이 계속 유지된다고 보면 안 됩니다. 참조 자식이 있는 부서 10을 삭제해 발생하는 ORA-02292도 장시간 잠금 대기와는 다른 결과입니다. Oracle 19c 잠금 설명을 기준으로 두 현상을 구분하세요.

인덱스를 추가한 뒤에는 같은 순서로 다시 확인합니다. 작은 예제에서 대기가 관찰되지 않으면 정상 여부를 단정하지 말고 자동 커밋 설정, 기존 인덱스, DB 버전과 실제 대기 이벤트를 확인하세요. 운영 데이터로 부모 키 삭제를 재현하지 않습니다.

현재 FK와 지원 인덱스를 찾는 SQL

제약조건 이름만 보고 인덱스 존재를 추정하면 놓치기 쉽습니다. FK 컬럼과 인덱스 컬럼을 순서대로 펼쳐서 비교해야 해요. 우선 대상 스키마의 FK 목록을 좁힙니다.

SELECT c.owner,
       c.table_name,
       c.constraint_name,
       c.r_owner,
       c.r_constraint_name,
       cc.position,
       cc.column_name
  FROM all_constraints c
  JOIN all_cons_columns cc
    ON cc.owner = c.owner
   AND cc.constraint_name = c.constraint_name
   AND cc.table_name = c.table_name
 WHERE c.constraint_type = 'R'
   AND c.owner = :owner_name
 ORDER BY c.table_name, c.constraint_name, cc.position;

다음 쿼리는 대상 자식 테이블에 정의된 인덱스 컬럼 순서를 보여 줍니다. FK가 (department_id, region_id)라면 인덱스의 선두 두 컬럼에 이 FK 컬럼들이 모두 포함되는지 확인하세요. FK 선언 순서와 일치하는지만으로 판단하지 말고, 조회 조건에 적합한 컬럼 순서도 별도로 검토합니다. 뒤에 다른 컬럼이 더 붙는 것은 가능하지만, FK 컬럼 앞에 다른 컬럼이 끼면 같은 접근 경로라고 보기 어렵습니다.

SELECT i.owner,
       i.table_name,
       i.index_name,
       i.status,
       i.visibility,
       ic.column_position,
       ic.column_name
  FROM all_indexes i
  JOIN all_ind_columns ic
    ON ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.table_owner = i.table_owner
   AND ic.table_name = i.table_name
 WHERE i.table_owner = :owner_name
   AND i.table_name = :child_table_name
 ORDER BY i.index_name, ic.column_position;

인덱스를 추가하기 전 판단표

비인덱스 FK를 찾았다고 모두 즉시 생성하지는 않습니다. 부모 키 DML, 자식 테이블 크기, 기존 인덱스 중복, 쓰기량을 함께 판단해야 해요.

안전하게 인덱스를 만들고 검증하기

테스트 결과 인덱스가 필요하다면 이름, 테이블스페이스, 예상 크기, TEMP 여유, 생성 시간대와 동시 DML을 먼저 정합니다. 큰 운영 테이블은 일반 CREATE INDEX가 서비스에 줄 영향을 별도 검토해야 해요. 온라인 생성이 필요하다면 Oracle 19c ONLINE 인덱스 생성의 잠금·TEMP 점검도 같이 확인하세요.

CREATE INDEX demo_order_fk_i
    ON demo_order (department_id);

생성 뒤에는 인덱스 존재만 확인하지 말고 상태와 컬럼 순서를 다시 읽고, 동일한 두 세션 시나리오에서 대기 범위가 달라졌는지 관찰합니다. 애플리케이션의 부모 키 변경·삭제 경로도 테스트해야 해요.

SELECT index_name,
       status,
       visibility
  FROM user_indexes
 WHERE table_name = 'DEMO_ORDER';

SELECT index_name,
       column_position,
       column_name
  FROM user_ind_columns
 WHERE table_name = 'DEMO_ORDER'
 ORDER BY index_name, column_position;

롤백과 흔한 실수

테스트 객체는 세션 A·B 모두 ROLLBACK한 뒤 제거합니다. 운영에서 새 인덱스가 쓰기 지연이나 공간 문제를 일으켰다면 DROP 전 해당 인덱스가 다른 SQL에 사용되는지 확인해야 해요. 단순히 “FK용으로 만들었다”는 이유로 다른 조회가 의존하지 않는다고 단정할 수 없습니다.

• 제약조건이 ENABLED라는 사실을 FK 인덱스 존재로 착각하지 않습니다.
• 인덱스 어딘가에 FK 컬럼이 있다는 이유만으로 지원 인덱스라고 보지 않습니다. 선두 컬럼 순서가 중요합니다.
• 부모 INSERT까지 모두 같은 차단을 만든다고 설명하지 않습니다.
• ORA-02292 참조 무결성 오류와 장시간 잠금 대기를 같은 현상으로 취급하지 않습니다.
• 잠금이 보인다는 이유만으로 세션부터 종료하지 않습니다. SQL, 객체, 트랜잭션 시작 시각과 업무 영향부터 확인합니다.

확인 순서만 남기면

부모 키 DELETE·UPDATE·MERGE가 있는지 확인하고, 자식 FK 컬럼 순서와 기존 인덱스 선두 컬럼을 비교하세요. 운영 증거가 있고 지원 인덱스가 없다면 테스트 환경에서 인덱스 추가 전후의 대기와 쓰기 비용을 함께 측정합니다. 이 순서를 지키면 “외래키는 무조건 인덱스”라는 관행과 “제약조건이 있으니 자동으로 인덱스도 있겠지”라는 오해를 모두 피할 수 있어요.

공식 출처

• Oracle Database 19c Concepts — Locks and Foreign Keys
• Oracle Database 19c Development Guide — Guidelines for Indexing Foreign Keys
• Oracle Database 19c SQL Language Reference — Foreign Key Constraints