외래키 인덱스가 없을 때 부모 DELETE 한 건이 만든 대기 체인: V$LOCK으로 본 TM 락 모드

자식이 하나도 없는 가맹점을 지우려다 멈춘 세션

세 세션이 같은 시간에 움직였습니다.

1. 세션 A가 정산 거래 테이블 SETTLE_TX의 행 하나를 수정하고 커밋하지 않았습니다.
2. 세션 B가 가맹점 테이블 MERCHANT_MASTER에서 1000번 가맹점을 지웠습니다. 이 가맹점에는 정산 거래가 한 건도 없습니다.
3. 세션 C가 2번 가맹점의 정산 거래를 한 건 INSERT했습니다.

B와 C는 둘 다 멈췄습니다. A가 건드린 행과도, B가 지우려는 가맹점과도 관계없는 C까지 기다린 것입니다. 원인은 SETTLE_TX.MERCHANT_ID 외래키에 인덱스가 없다는 것 하나였습니다.

외래키에는 인덱스를 만들라는 말은 익숙합니다. 확인하고 싶었던 것은 그 이유를 락 모드 번호로 보는 일이었습니다. 부모 DELETE가 자식 테이블에 어떤 TM 락을 언제 요구하는지, 그 락이 언제까지 남는지, 누가 누구를 막는지를 Oracle 21c XE에서 V$LOCK과 V$SESSION으로 추적했습니다. 측정 전에 세운 가설은 네 가지였습니다.

• 인덱스가 없으면 B는 자식 테이블에 TM 모드 4(S)를 요청하고, A의 모드 3(RX) 때문에 기다린다.
• C는 A와 호환되는 모드 3을 요청하지만, 먼저 줄을 선 B 뒤에서 기다린다.
• B의 DELETE 문장이 끝나면, 커밋 전이라도 자식 테이블에 모드 4가 남지 않는다.
• 인덱스가 있으면 아무도 기다리지 않는다.

넷 다 맞았습니다. 하지만 A가 락을 푼 다음 장면은 예상과 달랐습니다.

세 세션과 관찰 세션 하나

실습 환경은 Oracle Database 21c Express Edition(21.3.0.0.0)의 PDB인 XEPDB1입니다. DBeaver로 테스트 전용 USER LOCK_LAB에 접속했고, 운영 DB가 아닌 블로그 실습 환경입니다. 권한은 SYSTEM 계정으로 CREATE SESSION, CREATE TABLE, 쿼터, 그리고 V$LOCK과 V$SESSION을 보기 위한 SELECT_CATALOG_ROLE만 주었습니다.

가맹점과 정산 거래를 가정한 블로그용 가상 시나리오입니다. 가맹점은 1,000건, 정산 거래는 50,000건입니다. 락 동작은 건수와 무관해서, 시나리오를 여러 번 다시 만들어도 부담이 없는 규모로 골랐습니다. 거래는 1~900번 가맹점에만 배정해서 901~1000번은 자식 행이 없는 가맹점으로 남겼습니다. 삭제할 1000번 가맹점의 자식 행은 0건으로 확인했습니다.

CREATE TABLE merchant_master (
  merchant_id   NUMBER PRIMARY KEY,
  merchant_name VARCHAR2(50),
  status        VARCHAR2(10)
);

INSERT INTO merchant_master
SELECT LEVEL, 'MERCHANT_' || LPAD(LEVEL, 4, '0'), 'ACTIVE'
  FROM dual CONNECT BY LEVEL <= 1000;

CREATE TABLE settle_tx (
  tx_id       NUMBER PRIMARY KEY,
  merchant_id NUMBER NOT NULL,
  amount      NUMBER,
  settled_at  DATE,
  CONSTRAINT fk_settle_merchant FOREIGN KEY (merchant_id)
    REFERENCES merchant_master (merchant_id)
);

INSERT INTO settle_tx
SELECT LEVEL, MOD(LEVEL - 1, 900) + 1, MOD(LEVEL * 13, 50000), DATE '2026-09-01' + MOD(LEVEL, 30)
  FROM dual CONNECT BY LEVEL <= 50000;
COMMIT;

외래키 컬럼에 인덱스가 있는지는 아래 쿼리로 확인했습니다. 단일 컬럼 FK 기준이라, 운영 스키마 점검에 그대로 쓸 수 있습니다.

SELECT c.table_name, c.constraint_name, cc.column_name,
       CASE WHEN EXISTS (SELECT 1
                           FROM user_ind_columns ic
                          WHERE ic.table_name      = cc.table_name
                            AND ic.column_name     = cc.column_name
                            AND ic.column_position = cc.position)
            THEN 'INDEXED' ELSE 'NO INDEX' END AS fk_index
  FROM user_constraints c
  JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
 ORDER BY c.table_name, c.constraint_name, cc.position;

세션 구분과 관찰 쿼리

DBeaver에서 SQL 편집기 네 개를 열었습니다. A, B, C는 각각 별도 세션(SID 2181, 854, 1456)이고 수동 커밋 모드입니다. 자동 커밋이면 수정하자마자 커밋돼 락이 바로 사라지기 때문입니다. 세 편집기에는 DBMS_APPLICATION_INFO.SET_CLIENT_INFO로 A, B, C를 표시해 두고, 네 번째 편집기 D에서 아래 두 쿼리로 관찰했습니다.

-- 락: 쥔 모드와 요청한 모드
SELECT s.client_info AS sess, l.type, o.object_name,
       DECODE(l.lmode,   0, '-', 2, 'RS(2)', 3, 'RX(3)', 4, 'S(4)', 5, 'SRX(5)', 6, 'X(6)') AS held,
       DECODE(l.request, 0, '-', 2, 'RS(2)', 3, 'RX(3)', 4, 'S(4)', 5, 'SRX(5)', 6, 'X(6)') AS requested,
       l.block, l.ctime
  FROM v$lock l
  JOIN v$session s ON s.sid = l.sid
  LEFT JOIN user_objects o ON l.type = 'TM' AND o.object_id = l.id1
 WHERE s.username = 'LOCK_LAB'
   AND s.client_info IN ('A', 'B', 'C')
   AND l.type IN ('TM', 'TX')
 ORDER BY s.client_info, l.type, o.object_name;

-- 대기: 요청 모드는 P1의 하위 16비트, 대상 객체는 P2
SELECT s.client_info AS sess, s.event,
       CASE WHEN s.event LIKE 'enq: TM%' THEN BITAND(s.p1, 65535) END AS req_mode,
       CASE WHEN s.event LIKE 'enq: TM%'
            THEN (SELECT object_name FROM user_objects WHERE object_id = s.p2) END AS wait_object,
       (SELECT b.client_info FROM v$session b WHERE b.sid = s.blocking_session) AS blocker
  FROM v$session s
 WHERE s.username = 'LOCK_LAB'
   AND s.client_info IN ('A', 'B', 'C')
 ORDER BY s.client_info;

B는 자식 테이블에 S(4)를 요청했다

A가 UPDATE settle_tx SET amount = amount + 0 WHERE tx_id = 1을 실행하자, A는 SETTLE_TX에 RX(3)와 행 락(TX)을 쥐었습니다. A의 UPDATE는 FK 컬럼을 건드리지 않았고, 부모 테이블에는 락을 잡지 않았습니다. 이어서 B가 DELETE FROM merchant_master WHERE merchant_id = 1000을 실행했고, C가 2번 가맹점의 거래를 INSERT했습니다.

B는 부모 테이블에 RX(3)를 잡은 뒤, 자식 테이블 SETTLE_TX에 S(4)를 요청하고 있었습니다. S(4)는 다른 세션의 RX(3)와 함께 있을 수 없는 모드입니다. 이 시점에 B에게는 TX가 없었습니다. 부모 행을 아직 지우지 못한 상태에서 기다리고 있었다는 뜻입니다. C도 자식 행을 넣기 위해 부모 테이블에 RX(3)를 잡았습니다. 자식 INSERT가 부모 테이블에도 TM 락을 잡는다는 점은 A의 UPDATE와 다른 부분입니다.

대기 체인은 C → B → A였습니다. C가 요청한 RX(3)는 A가 쥔 RX(3)와 충돌하지 않습니다. 그런데도 C는 기다렸고, 막는 세션으로 B가 표시됐습니다. 먼저 줄을 선 B의 S(4) 요청을 앞질러 갈 수 없었던 것으로 해석됩니다. 자식이 없는 가맹점 하나를 지우려는 DELETE가, 자식 테이블에 새 거래를 넣는 다른 세션까지 멈추게 했습니다.

A가 풀어 줬는데 B는 다시 멈췄다

A를 ROLLBACK했습니다. 이제 B가 끝나고, 그다음 C가 진행될 것으로 예상했습니다. 실제로는 다음과 같았습니다.

C는 SETTLE_TX의 RX(3)를 얻어 INSERT를 끝냈습니다. B는 TX를 새로 얻었으니 부모 행을 지운 것입니다. 그런데 B는 SETTLE_TX에 S(4)를 다시 요청하고 있었고, 이번에 막는 세션은 C였습니다. B의 TX, B의 새 S(4) 요청, C의 RX(3)는 지속 시간(CTIME)이 모두 같았습니다. A가 ROLLBACK한 그 순간에 함께 일어난 일입니다. C를 ROLLBACK하자 B는 비로소 완료됐습니다.

같은 순서로 다시 실행해도 똑같이 재현됐습니다. C를 빼고 A와 B만으로 실행하면, A가 ROLLBACK하는 순간 B가 바로 끝났습니다.

A ROLLBACK 직후	C가 기다리고 있을 때	C가 없을 때
B의 상태	S(4) 재요청, 대기	완료
B를 막는 세션	C	없음
C의 상태	RX(3) 획득, 완료	–

관찰한 사실만 놓고 보면, B는 부모 행을 지우기 전에 S(4)를 한 번 요청하고 지운 뒤에 다시 한 번 요청했습니다. 그 사이에 대기 중이던 C의 RX(3)가 먼저 허용됐습니다. 첫 번째 S(4)가 허용됐다가 풀린 뒤 다시 요청한 것으로 추정되지만, 내부에서 왜 두 번 요청하는지는 이번 실험으로 확인하지 않았습니다. 운영 관점에서 중요한 것은 결과입니다. 인덱스 없는 FK 하나 때문에, B 뒤에 줄을 섰던 무관한 세션이 이번에는 B를 막았습니다.

문장이 끝나면 자식 테이블 락은 남지 않았다

순서를 바꿔, 아무도 자식 테이블을 쥐고 있지 않을 때 B가 부모 DELETE를 먼저 끝내도록 했습니다. 커밋은 하지 않았습니다.

세션	작업	결과	SETTLE_TX에 쥔 락
B	부모 1000번 DELETE (커밋 전)	완료	없음 (부모 RX(3)와 TX만)
C	자식 INSERT	바로 완료	RX(3)
A	자식 UPDATE	바로 완료	RX(3)

인덱스 없는 FK는 트랜잭션이 끝날 때까지 자식 테이블을 잠근다는 설명을 볼 수 있습니다. 하지만 21c XE에서 B는 DELETE 문장이 끝난 뒤 자식 테이블에 아무 TM 락도 남기지 않았습니다. 문제는 커밋 전까지의 시간이 아니었습니다. 부모 DELETE 문장이 실행되는 순간에 자식 테이블의 다른 트랜잭션과 S(4)를 두고 부딪히는 것이 문제였습니다.

인덱스를 만들자 바뀐 것

자식 FK 컬럼에 인덱스를 만든 뒤, A, B, C를 처음과 같은 순서로 실행했습니다.

CREATE INDEX ix_settle_merchant ON settle_tx (merchant_id);

세 세션 모두 기다리지 않았습니다. B는 부모 테이블과 자식 테이블에 모두 RX(3)를 쥐었고, 요청 중인 락이나 막는 세션은 없었습니다. 인덱스가 없을 때와 비교하면 차이가 분명합니다.

B의 SETTLE_TX 락	인덱스 없음	인덱스 있음
DELETE 문장 실행 중	S(4) 요청 (두 번 관찰), 대기	대기 없이 완료
문장이 끝난 뒤, 커밋 전	없음	RX(3) 유지
자식 DML 세션과 충돌	있음	없음

인덱스가 있을 때 B는 더 약한 락을 더 오래 쥐었습니다. RX(3)끼리는 함께 있을 수 있으므로 자식 DML과 부딪히지 않았습니다.

TM 락 대기를 만났을 때 볼 것

판단 기준

• 대기 이벤트가 enq: TM - contention이고 BITAND(P1, 65535)가 4이면, 대상 객체(P2)가 자식 테이블인지 먼저 확인합니다. 인덱스 없는 FK와 부모 DELETE를 의심할 근거가 됩니다.
• 막는 세션(BLOCKING_SESSION)이 원인이라고 단정하면 안 됩니다. 이번 실험에서 C는 처음에는 B 뒤에서 기다리는 쪽이었다가, A가 풀린 뒤에는 B를 막는 쪽이 됐습니다. 세션을 정리하기 전에 각 세션이 어떤 모드를 쥐고 어떤 모드를 요청하는지부터 봐야 합니다.
• 부모 행을 지우는 작업이 있는 테이블이라면, 자식 FK 컬럼에 인덱스를 두는 것이 가장 직접적인 해결입니다. 인덱스를 추가하기 어렵다면 부모 삭제를 자식 DML이 적은 시간대로 옮기거나, 실제 삭제 대신 상태값으로 처리하는 방법을 고려할 수 있습니다.
• 인덱스가 있으면 부모 DELETE 세션은 커밋할 때까지 자식 테이블에 RX(3)를 유지합니다. 자식 DML과는 충돌하지 않지만, 테이블 전체에 배타적 락이 필요한 작업은 그 커밋을 기다리게 될 수 있습니다.

조건이 달라지면 결과가 바뀔 수 있는 지점

• 자식 행이 있는 부모를 지우면 ORA-02292로 실패합니다. ON DELETE CASCADE나 SET NULL이 걸린 FK는 측정하지 않았습니다.
• 부모 키를 UPDATE하는 경우와 MERGE는 확인하지 않았습니다.
• 복합 컬럼 FK는 인덱스의 앞쪽 컬럼이 FK 컬럼과 같은 순서여야 합니다. 위 점검 쿼리는 단일 컬럼 FK 기준이라 복합 FK에는 보완이 필요합니다.

운영에서 추가로 확인할 것

• 부모 테이블에서 DELETE가 일어나는 배치와 그 자식 테이블들의 FK 인덱스 여부
• V$SESSION에서 요청 모드 4인 enq: TM - contention 대기가 나타나는 시간대
• FK 인덱스를 추가할 때 자식 테이블 DML에 늘어나는 인덱스 유지 비용

이 테스트의 한계

단일 인스턴스 XE에서 세션 세 개로만 확인했습니다. 자식 테이블은 하나, FK는 단일 컬럼이었습니다. 삭제한 부모는 자식 행이 없는 가맹점이었고, 대기 시간이나 처리 시간은 측정하지 않았습니다.

부모 한 행을 지우는 동안 자식 테이블 전체에 S(4)가 두 번 요청됐다

FK 인덱스가 없을 때, 자식이 한 건도 없는 가맹점 하나를 지우는 DELETE는 자식 테이블 전체에 S(4)를 요청했습니다. 자식 행 하나를 수정 중인 세션이 있으면 그 요청은 기다렸고, 그 뒤에 온 무관한 INSERT까지 함께 멈췄습니다. 앞의 세션이 풀린 뒤에도 B는 S(4)를 한 번 더 요청했고, 그 사이 먼저 끝난 C에게 다시 막혔습니다. 반면 문장이 끝난 뒤에는 자식 테이블에 락이 남지 않았습니다. 그래서 위험한 순간은 커밋 전까지의 시간 전체가 아니라 부모 DELETE 문장이 실행되는 동안이었습니다. 인덱스를 만들자 B는 RX(3)만 쥐었고, 세 세션 중 누구도 기다리지 않았습니다.