Oracle Lock 매커니즘

2021-11-23 Database

Oracle에서 제공하는 Lock은 DML Lock, DDL Lock, System Lock 이렇게 크게 3가지 종류가 있습니다. DML Lock과 DDL Lock은 특정 트랜잭션이 데이터베이스의 레코드나 스키마를 변경할 때 해당 Data Block에 잠금 정보(ITL)를 저장합니다. 이 잠금 정보는 Undo Segment의 트랜잭션 테이블에 대한 포인터이며, 이를 통해 트랜잭션 테이블에서 트랜잭션의 활성화 여부를 확인하여 잠금의 상태를 파악할 수 있습니다. 만약 동시에 여러 트랜잭션이 하나의 리소스에 잠금을 요청하면 해당 Data Block에는 각각의 트랜잭션 잠금 정보(ITL)가 생성되고, 가장 먼저 실행된 트랜잭션이 잠금을 획득하여 리소스를 액세스 합니다. 그리고 나머지 트랜잭션의 작업은 공유 풀의 Queue에 넣고 선입선출(FIFO) 합니다. 이런 방식으로 잠금을 하는 일련의 과정을 Enqueue라고 합니다. System Lock은 내부 프로세스들이 공유 메모리를 액세스 할 때 획득해야 되는 잠금입니다.

Enqueue

Enqueue 되는 과정을 이해하면 애플리케이션을 개발할 때 많이 쓰이는 DML문이 내부적으로 어떤식으로 Locking 되는지 알 수 있어 유용 하지만, 복잡해서 머리가 아플 수도 있습니다. ~~😱 저만 그렇게 생각할 수도…~~

Oracle Locking Mechanisms 문서를 보면 아래와 같은 설명이 있습니다.

Enqueues are shared memory structures (locks) that serialize access to database resources.

They can be associated with a session or transaction.

if you request a table lock (a DML lock) you will receive an enqueue.

“DML을 실행하면 해당 리소스의 액세스를 직렬화하여 세션 또는 트랜잭션과 연결시킨 공유 메모리 구조체 Enqueue를 얻습니다.”라는 내용입니다. 위에서는 Enqueue가 잠금을 획득하는 과정이라고 말했는데 Document에서는 리소스와 트랜잭션의 스트림 역할을 하는 구조체라고 표현하고 있습니다. 이것은 Oracle이 제공하는 Document에서 Enqueue를 “객체(구조체)”와 “과정”을 모호하게 표현하기 때문입니다. (~~Oracle Document를 보면 혼랍스럽고 짜증…~~)

Enqueue의 사전적 의미로 보면 큐에 넣는 행위를 말하기 때문에 Document에서 말한 Enqueue 구조체는 Enqueue Resource라고 표현하는 것이 맞는 것 같습니다. 다시 한번 정리하자면 트랜잭션이 리소스에 대한 잠금을 요청하면 트랜잭션과 리소스의 스트림 역할을 하는 Enqueue Resource를 생성하고, Enqueue Resource를 이용해 해당 리소스를 액세스하여 잠금을 설정하는 과정을 Enqueue라고 합니다.

Enqueue Resource는 GES, GCS와 LCK, LMD, LMON 등의 백그라운드 프로세스들에 의해 관리 된다고 합니다. Enqueue와 관련된 프로세스들도 상세하게 다루면 좋겠지만… 너무 내용이 방대해질 것 같아서 이번엔 스킵하고 다음 기회에 알아보겠습니다.

그럼 이제 Enqueue가 되는 과정을 알아보겠습니다. (😭보시다가 부족한 부분이 있으면 피드백 부탁드립니다.)

트랜잭션이 특정 리소스에 대한 잠금(Lock)을 요청하면 AUM(Automatic Undo Management)이 적절한 온라인 상태의 Undo segment를 찾습니다. 만약 할당할 곳이 없다면 Undo segment를 생성합니다.

Undo segment를 할당 받는 과정에서 경합이 발생하면 enq: US - contention 이벤트가 발생합니다.
Undo segment header에 있는 Transaction Table에 Slot을 생성하고 요청 트랜잭션에 XID(Transaction ID)를 부여합니다.

Undo segment Number, Transaction Table Slot Number, Squence Number의 조합으로 XID(Transaction ID)를 생성합니다.

Squence Number을 얻기 위해 Cache에서 nextval을 호출하는 동안 경합이 발생하면 Enq: SQ - contention 이벤트가 발생합니다.

하나의 Undo segment에 여러개의 트랜잭션이 할당될 수 있습니다.
Enqueue Resource를 할당하기 위해 고유 식별값을 구합니다.

고유 식별값은 요청Type-ID1-ID2 구성되며 요청 Lock Type에 따라 ID1, ID2 값이 달라집니다.

요청타입이 TM이면 ID1 값은 Object ID이고 ID2 값은 0.

요청타입이 TX이면 ID1 값의 상위 16비트 값은 Undo Segment Number, 하위 16비트는 Transaction Slot Number이고 ID2 값은 Seqeunce Number.
```
-- 참고 GV$RESOURCE, GV$ENQUEUE_LOCK
SELECT L.INST_ID, L.SID, L.TYPE, L.ID1, L.ID2
FROM  GV$LOCK L
WHERE L.SID = 4
```
고유 식별값에 해시 함수를 적용한 값으로 Shared Pool에 있는 Enqueue Hash Chain에서 Hash Bucket을 찾은 다음 Hash Bucket의 Hash Chain을 따라가며 Enqueue Resource를 찾습니다. 만약 존재하면 재사용하고 없다면 Enqueue Resource를 생성하여 Hash Chain에 연결합니다.

Enqueue Hash Chain를 액세스 하기 위해서는 Enqueue Hash Chain Latch를 획득해야 합니다.

동시에 여러 트랜잭션에서 동일한 리소스에 접근한다면 하나의 Enqueue Resource에 여러 트랜잭션이 연결될 수 있기 때문에 Enqueue Resource에는 Owner(소유자) Queue와 Waiter(대기자) Queue가 존재합니다.
트랜잭션이 리소스에 대한 액세스를 얻기 위해서 Data Block header에 ITL(Interested Transaction List) Slot을 생성합니다.
ITL의 개념은 어떤 식당(리소스)에 많은 사람들(트랜잭션)이 오면 자신의 정보를 대기표(ITL)에 작성하고, 순차적으로 입장(Lock 획득)합니다.

ITL은 XID(Transaction ID), UBA(Undo block address), LCK(changed row count), FLAG(처리 상태) 등의 정보가 포함 됩니다.

ITL은 재사용될 수 있기 때문에 트랜잭션이 종료되어도 삭제 되지 않으며, Delayed block cleanout이 발생하거나 다른 프로세스에 의해 블록이 변경되면 정리됩니다.

MAXTRANS count <= Data Block에 할당된 ITL count 이면 enq: TX - allocate ITL entry 대기 이벤트 발생.
```
SELECT INI_TRANS, MAX_TRANS, OWNER FROM DBA_TABLES WHERE TABLE_NAME='테이블명';
```
INITRANS 초기에 생성되는 ITL Slot 갯수이며 기본값은 테이블의 경우 1이고 클러스터 및 인덱스의 경우 2입니다.

ITL Slot 당 24 Byte 공간이 필요합니다.

INITRANS의 값을 증가 시키면 병렬 트랜잭션 환경에서 실행되는 DML의 성능을 향상시킬 수 있습니다.

INITRANS은 블록 크기의 50% 미만이어야 합니다.

MAX_TRANS 값은 255입니다.
잠금(Lock) 여부를 확인하기 위해 Data Row Header의 LB(Lock Byte)가 참조하는 Data Block header의 ITL Slot으로 Transaction Table(Undo Segment)을 검사합니다.

////////////////////////// Block header ////////////////////////// Object id on Block? Y seg/obj: 0xc66a csc: 0x00.2ec5e34 itc: 2 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck 0x01 0x0016.026.00003222 0x01c0003f.0410.26 ---- 1 fsc … 0x02 0x0016.025.00003222 0x01c0003f.0410.25 C--- 0 scn … ////////////////////////// Row header ////////////////////////// tab 0, row 0, @0x13cd tl: 1009 fb: --H-FL-- lb: 0x1 cc: 2 ... tab 0, row 1, @0xfdc tl: 1009 fb: --H-FL-- lb: 0x2 cc: 2
잠금(Lock)을 획득할 수 있는 경우에는 현재 Enqueue Resource의 Owner Queue에 자신을 등록하고, 만약 다른 트랜잭션에 의해 잠긴 상태이면 해당 ITL Slot을 참고하여 잠금을 소유하고 있는 Enqueue Resource의 Waiter Queue에 자신을 등록합니다.

다른 트랜잭션의 잠금(Lock)으로 인해 대기 해야될 경우 enq: TX - row lock contention 이벤트가 발생합니다.

두 개의 트랜잭션에서 Lock 경합이 발생할 때 V$LOCK를 조회 해보면 ID1, ID2가 동일한 값이 보입니다. 그 이유는 대기 중인 트랜잭션이 잠금을 획득한 리소스의 Waiter Queue에 포함되어 있기 때문입니다. 이후 첫 번째 트랜잭션 잠금이 해제 되면 Waiter Queue에서 대기중인 두 번째 트랜잭션의 Enqueue를 꺼내기 때문에 ID1, ID2가 변경되는 것을 확인할 수 있습니다. (🤣사실 이 부분은 제 추측입니다.)
리소스에 대한 잠금(Lock)을 획득한 트랜잭션의 DML 수행을 위해 이전 정보를 Undo segment에 기록하고 변경될 정보를 Redo Buffer에 기록합니다.
커밋이 수행되면 Redo Buffer에 SCN(System Change Number)을 할당하고 Redo Log 파일에 기록합니다.
DBWR 프로세스가 변경된 Data Block들을 데이터 파일에 기록합니다.
Lock을 포함한 모든 리소스에 대한 점유가 해제 됩니다.

☞ Enqueue Event 관련 참고 사이트

☞ Enqueue Event 관련 뷰

V$TRANSACTION: 현재 실행 중인 트랜잭션 정보 확인 뷰.

V$SESSION_WAIT: 다른 트랜잭션에 의해 대기 중인 Enqueue Event를 조회할 수 있습니다.

SELECT * 
FROM GV$SESSION_WAIT
WHERE EVENT LIKE 'enq%';

V$SESSION_EVENT: ACTIVE, INACTIVE 상태의 Enqueue Event를 조회할 수 있습니다.

SELECT DISTINCT SE.SID, SE.EVENT, SE.TOTAL_WAITS, SE.EVENT_ID, S.USERNAME, S.STATUS, S.OSUSER, S.MACHINE, S.PROCESS, S.BLOCKING_SESSION
FROM GV$SESSION_EVENT SE
INNER  JOIN GV$SESSION S ON S.SID = SE.SID
WHERE SE.EVENT LIKE 'enq%'
-- AND S.STATUS = 'ACTIVE'
ORDER BY SID;

V$SYSTEM_EVENT: Enqueue Event 통계 확인

SELECT * 
FROM GV$SYSTEM_EVENT
WHERE EVENT LIKE 'enq: TM%'
OR EVENT LIKE 'enq: TX%'
ORDER BY EVENT;

V$SEGMENT_STATISTICS: ITL 부족에 의한 경합이 많이 발생하는지 확인할 수 있으며 INITRANS 을 조절하여 튜닝할 수 있습니다.

SELECT * 
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'ITL waits';

V$PARAMETER: 시스템 파라메터 뷰

V$RESOURCE_LIMIT: 시스템 파라메터 뷰

V$LATCH: 래치 관련 뷰

DML Lock

병행 처리되는 트랜잭션의 무결성을 보장하기 위해 Row Lock(TX), Table Lock(TM)을 동시에 획득합니다. DML Lock은 timeout이 없기 때문에 세션이 종료되지 않는 이상 지속되기 때문에 더욱 신경 써야합니다. 단, 트랜잭션이 비정상적으로 종료되면 PMON에 의해 자동 Rollback 됩니다.

Row Lock(TX) : INSERT, UPDATE, DELETE, MERGE 또는 SELECT...FOR UPDATE 같은 DML을 사용하면 Row-Level Exclusive Lock(행 단위 배타적 잠금)을 획득하며 커밋 또는 롤백 시 해제 됩니다.

Oracle 10g 이후 버전부터 TX(Transaction) Lock은 Row 단위로만 적용되기 때문에 Lock Escalation이 발생하지 않습니다.

SELECT … FOR UPDATE 잠금을 획득하기 위해 무한정 기다립니다.

SELECT … FOR UPDATE NOWAIT 잠금을 획득하지 못하면 즉시 에러가 발생.

SELECT … FOR UPDATE WAIT second(0 ~ 4294967295) second(초) 만큼 잠금 획득을 기다립니다.

SELECT … FOR UPDATE OF column_name, column_name… 여러개의 테이블이 조인될 때 OF에 정의된 컬럼을 사용하는 테이블에만 잠금을 획득합니다.

Table Lock(TM): DML을 사용하면 Row Lock(TX)과 더불어 Table Lock(TM)도 자동으로 획득 하는데, 그 이유는 데이터 변경 사항을 반영 도중에 다른 트랜잭션에서 테이블을 재정의하는 DDL 같은 작업을 방지하기 위해서입니다.

Row Share(RS) : subshare (SS)이라고도 부르며, 트랜잭션이 테이블의 잠긴 행을 갖고 있으며 이를 업데이트할 계획임을 나타냅니다. 이 모드는 테이블 잠금의 최소 제한 모드로 테이블에 대해 가장 높은 수준의 동시성을 제공합니다.
Row Exclusive(RX): subexclusive(SX)이라고도 부르며, 일반적으로 DML을 사용하면 이 모드로 Table Lock을 획득합니다.
Share(S) : 테이블이 공유잠금 상태임을 나타내며 동시에 여러 트랜잭션에서 Share(S)을 획득할 수 있습니다. 그리고 Share(S)는 Row Exclusive(RX)과 호환되지 않기 때문에 만약 특정 트랜잭션에서 Share(S)를 획득한 테이블을 다른 트랜잭션에서 쓰기 작업을 하려고 하면 블로킹 상태가 됩니다. 반대로 다른 트랜잭션에서 쓰기 작업으로 Row Exclusive(RX)을 획득한 상태라면 Share(S)는 블로킹 됩니다. 단, 동일 트랜잭션 내에서는 테이블에 Share(S)된 상태에서 쓰기 작업이 가능하며 이때 모드가 Share Row Exlusive(SRX)로 변경되어 실행(Lock Escalation) 됩니다.
Share Row Exclusive(SRX): share-subexclusive(SSX)이라고도 부르며, Share(S)보다 더 제한적으로 사용됩니다. Share Row Exclusive(SRX) Lock을 획득하면 다른 트랜잭션에서 Row Share(RS)를 제외한 Lock을 동시에 사용할 수 없습니다. 또한 Share(S)와 마찬가지로 동일 트랜잭션 내에서는 쓰기 작업을 할 수 있습니다.
Exclusive(X): 가장 제한적인 모드이며 Share Row Exclusive(SRX)과 거의 같으며 차이점은 다른 트랜잭션에서 어떤 Lock도 동시에 사용할 수 없습니다.

Oracle은 단순 읽기 연산(SELECT)에는 Lock을 사용하지 않으며, DML문을 사용하면 Row Lock(TX)과 Table Lock(TM, RX)을 획득하게 됩니다. 만약 수동으로 테이블에 Table Lock(TM)을 설정하고 싶다면 Lock Table 연산을 사용할 수 있고 Lock Table로 지정된 Lock은 커밋 또는 롤백되면 해제 됩니다. 이와 관련된 자세한 내용을 확인하려면 여기를 보세요.

LOCK TABLE 테이블명 IN {ROW SHARE|ROW EXCLUSIVE|SHARE|SHARE ROW EXCLUSIVE|EXCLUSIVE} MODE {WAIT|NOWAIT}

-- 예시
LOCK TABLE test IN SHARE MODE NOWAIT
...
COMMIT;

Table Lock(TM) 호환성 ☞ docs.oracle.com

Lock 확인 쿼리

-- 현재 세션 SID 조회
SELECT userenv('sid') FROM DUAL;

-- Oracle RAC 사용 시 GV$...로 조회해야하고 단일 인스턴스 Oracle은 V$...로 조회하면 하면 되고, V$LOCK 대신 DBA_LOCK을 써도 된다.

/* LOCK 상세 조회
STATUS가 ACTIVE(실행중)인 것은 Q.SQL_TEXT가 나오지만 INACTIVE(현재 실행중이 아닌 상태)인 것은 Q.SQL_TEXT가 NULL이다.
또한 Q.SQL_TEXT는 세션에서 실행한 마지막 쿼리가 보이기 때문에 다른 세션에서 lock쿼리를 실행하고 현재 세션에서 확인 쿼리를 해야 lock쿼리를 확인할 수 있다.
*/
SELECT L.SID, S.SERIAL#, 
      L.INST_ID, -- Oracle Rac의 인스턴스 ID
      O.OBJECT_NAME, -- Lock으로 점유하고 있는 리소스(테이블 등)
      S.BLOCKING_SESSION,  -- 현재 세션으로 인해 블로킹 상태인 세션 SID
	  L.TYPE AS LOCK_TYPE, -- TX, TM, UL 등
      DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share(RS)', 
                       3, 'Row Exlusive(RX)', 
                       4, 'Share(S)', 
                       5, 'Share Row Exlusive(SRX)', 
                       6, 'Exclusive(X)') AS LOCK_MODE, 
       DECODE(L.REQUEST, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share(RS)', 
                       3, 'Row Exlusive(RX)', 
                       4, 'Share(S)', 
                       5, 'Share Row Exlusive(SRX)', 
                       6, 'Exclusive(X)') AS REQUEST_LOCK_MODE, 
        L.ID1, L.ID2, 
        L.CTIME, -- LOCK을 점유한 시간(초)
        L.BLOCK,  S.USERNAME, S.STATUS, S.OSUSER, S.MACHINE, S.PROCESS, S.SQL_ID, Q.SQL_TEXT
FROM GV$LOCK L
INNER  JOIN GV$SESSION S ON S.SID = L.SID
LEFT OUTER JOIN DBA_OBJECTS O ON O.OBJECT_ID = L.ID1
LEFT OUTER JOIN GV$SQLAREA Q ON Q.HASH_VALUE = S.SQL_HASH_VALUE AND Q.INST_ID = S.INST_ID
WHERE L.SID IN (SID, SID...) -- 특정 SID로 조회할 때
AND   S.OSUSER = 'PC이름'  -- 특정 PC에서 만든 세션만 볼 때 
-- AND L.TYPE IN ('TM', 'TX', 'UL') IN절에 여러개를 넣으면 쿼리가 오래 걸림.
ORDER BY L.SID;
 
 -- LOCK 걸린 세션 종료(동일 세션에서는 종료할 수 없고 다른 세션을 열어서 KILL해야 합니다.)
 ALTER SYSTEM KILL SESSION 'SID, SERIAL';

DDL Lock

CREATE, DELETE, ALTER 같은 DDL 작업이 다른 트랜잭션에 의해 객체를 참조하거나 변경 또는 삭제되는 것을 방지합니다.

DDL 작업 중에는 참조되는 개별 스키마 객체만 잠기며 Data Dictionary는 잠기지 않습니다.
DDL Lock을 명시적으로 요청할 수 없습니다.
DDL 작업은 참조하는 스키마에 Exclusive DDL Lock을 획득해야 되는데 DML 실행 등으로 리소스가 사용중이라서 잠금을 획득하지 못할 경우 DDL_LOCK_TIMEOUT(기본값 0) 시간 만큼 대기합니다. 그래도 잠금을 획득하지 못하면 “ORA-00054: 리소스 사용 중”이라는 오류가 발생합니다.
프로시저를 생성할 때는 프로시저에서 참조하는 스키마 객체에 Share DDL Lock을 획득하여 컴파일이 완료되기 전까지 객체의 변경 또는 삭제되는 것을 방지하고, 다른 프로시저를 생성할 때 동일한 스키마 객체에 대한 참조를 허용하여 프로시저 생성에는 문제가 없도록 합니다.
DBA_DDL_LOCKS

System Lock

내부 데이터베이스 및 메모리 구조를 보호하기 위한 Lock이며 사용자가 직접 액세스할 수 없습니다. 래치(LATCH), 뮤텍스(Mutexes), Internal Lock이 있습니다.

래치(LATCH)

SGA 내부의 공유 영역에 대한 잠금을 보장하여 메모리 구조의 무결성을 유지하기 위한 저수준의 내부 Lock입니다. 일반적으로 Lock 개념과 다르기 때문에 Lock이라 부르지 않고 래치라고 부릅니다. 래치는 하드웨어를 제어하기 위해 가벼운 명령을 실행하기 때문에 아주 빠른 속도로 동작하지만 래치 획득의 순서를 보장하지 않습니다. 또한 SGA에 접근하는 모든 프로세스는 반드시 해당 영역을 관장하는 래치를 획득해야 하고 그 과정에서 경합이 많이 발생하면 성능이 떨어지게 됩니다. 경합을 발생시키는 원인은 다양하지만 개발자라면 Hard Parsing을 유발하는 SQL을 주의해야 합니다.

SQL 문을 실행하면 Library Cache에 SQL 커서(구문 분석 트리 및 실행 계획 등)를 포함하는 LCO(Library Cache Object) 객체를 생성합니다. 이후 동일한 쿼리가 실행되면 LCO를 재사용(Soft Parsing)하고, 새로운 쿼리를 실행하면 LCO를 생성(Hard Parsing)하여 실행합니다. Soft Parsing은 Library Cache latch lock를 Shared 모드로 획득하며 Hard Parsing은 쓰기 작업을 해야 되기 때문에 Exclusive 모드로 획득합니다. 그렇게 때문에 동시에 많은 Hard Parsing되는 SQL이 실행되면 Library Cache latch lock 경합을 유발합니다. 그리고 Library Cache latch lock은 LCO가 참조하고 있는 스키마 객체가 DDL로 인해 변경되면 LCO가 무효화되고 Lock이 해제됩니다. 이러한 Library cache lock의 특성 때문에 breakable parse lock이라고도 부릅니다.

래치 SPIN

일반적으로 프로세스가 래치를 요청하면 willing-to-wait 모드로 획득하게 됩니다. willing-to-wait 모드는 래치 획득에 실패할 경우 일정 횟수(_SPIN_COUNT) 만큼 재요청을 하고 그래도 획득에 실패한다면 SLEEP 상태로 대기합니다. 그 후 특정 조건을 만족하면 SLEEP 상태를 해제하고 다시 래치를 요청하는 방법입니다.

래치 Dead Lock

Dead Lock이 거의 발생하지 않습니다. 이유는 어떤 래치를 획득한 프로세스가 자신이 소유한 래치보다 낮은 레벨의 래치를 획득하려면 no-wait모드로 획득을 시도하기 때문입니다. ☞참고

뮤텍스(Mutexes)

뮤텍스는 Oracle 10gR2부터 제공된 OS에서 동작하는 메커니즘으로, Critical section을 포함하는 여러 프로세스가 동시에 공유 리소스를 액세스하고자 할 때 하나의 프로세스(스레드)만 액세스할 수 있도록 합니다. 시스템(OS)은 특정 프로세스의 임계영역에 들어갈 때 잠금을 획득하고 다른 프로세스(혹은 쓰레드)가 접근하지 못하도록 하고 임계영역에서 나오면 해당 잠금을 해제합니다. 만약 잠금된 영역에 다른 프로세스가 접근하면 대기큐에서 기다리도록 합니다. 단, 뮤텍스가 공유모드일 때는 다른 프로세스에서 동시 참조를 허용합니다. 뮤텍스는 래치보다 적은 메모리를 사용하고 SPIN을 수행하지 않기 때문에 래치보다 처리 속도가 빠릅니다.

Internal Lock

내부 잠금은 래치 및 뮤텍스보다 더 높은 수준의 복잡한 메커니즘으로 동작하며 Dictionary Cache locks, File and log management locks, tablespace and rollback segment locks 등에 사용되는 내부 Lock입니다.

외래키(Foreign Key) Lock

부모 테이블과 자식 테이블간에 참조 관계에 있을 때, 부모 테이블의 기본키를 수정하거나 행을 삭제할 경우 참조하고 있는 테이블의 외래키 또한 변경 되어야 되기 때문에 참조 테이블에도 잠금(Lock)을 획득하게 됩니다.

다음과 같은 참조 관계에서 상황별로 Lock이 어떻게 발생하는지 알아 보겠습니다.

-- ON DELETE 옵션은 CASCADE, SET NULL이 있으며 ON DELETE 옵션 생략 시 RESTRICT 모드로 작동합니다.
ALTER TABLE CHILD ADD CONSTRAINT FK_TEST_LOCK FOREIGN KEY (PARENT_SEQ) REFERENCES PARENT(PARENT_SEQ) 
ON DELETE CASCADE;

참조 관계에서 외래키 값은 null 중복을 허용합니다.

Oracle 11g 이후 버전에서는 자식테이블에 외래키 인덱스 뿐만 아니라 기본키를 설정하지 않아도 행 단위 잠금을 획득합니다.

Case 1) 부모 테이블의 기본키 값을 변경하는 경우

부모 테이블의 기본키를 참조하는 자식 테이블의 값도 변경되어야 되는데, Oracle은 외래키의 옵션으로 ON UPDATE를 제공하지 않기 때문에 참조하는 외래키가 존재할 경우 오류가 발생합니다.

UPDATE PARENT 
SET PARENT_SEQ = 5000
WHERE PARENT_SEQ = 1000;

---
SQL Error [2292] [23000]: ORA-02292: 무결성 제약조건(RECEIPT.FK_TEST_LOCK)이 위배되었습니다 - 자식 레코드가 발견되었습니다

이런 오류를 회피하기 위해서는 부모 테이블에 트리거를 생성하여 기본키 값을 변경할 때 자식 테이블의 외래키 값도 같이 변경 해주면 됩니다.

CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER
UPDATE ON PARENT
FOR EACH ROW
BEGIN
    IF :new.PARENT_SEQ != :old.PARENT_SEQ THEN
        UPDATE CHILD
        SET PARENT_SEQ = :new.PARENT_SEQ
        WHERE PARENT_SEQ = :old.PARENT_SEQ;
    END IF;
END;

이 트리거는 ON UPDATE CASCADE 옵션과 동일한 동작을 하며, UPDATE 문이 실행되면 부모 테이블(PARENT)에 TX Lock과 TM(RX) Lock이 걸립니다. 그 후 트리거가 실행면서 자식 테이블(CHILD)에도 TX Lock과 TM(RX) Lock이 걸립니다. 아래 그림을 보면 TM Lock은 PARENT, CHILD 테이블 객체에 잠금을 표시 하기 위해 2개의 행으로 표시되고 TX Lock은 트랜잭션 리소스를 의미하는 것이기 때문에 하나만 표시됩니다.

UPDATE를 실행하고 커밋되지 않은 상태에서 다른 트랜잭션이 잠금된 Row를 읽는 것은 가능하지만 쓰기 작업으로 TX Lock을 요청하면 TX Lock 끼리는 서로 호환되지 않기 때문에 요청 트랜잭션은 블로킹 상태가 됩니다.

UPDATE CHILD
SET NAME = 'CHILD2'
WHERE CHILD_SEQ = 1;

Case 2) 부모 테이블의 기본키가 아닌 일반 컬럼의 값을 UPDATE 하는 경우

기본키가 아닌 일반 컬럼은 자식 테이블에 영향을 미치지 않기 때문에 업데이트하는 부모 테이블에만 잠금(Lock)이 설정됩니다.

UPDATE PARENT 
SET NAME = 'parent-new'
WHERE PARENT_SEQ = 1000;

Case 3) 자식 테이블 외래키 값을 변경하는 경우

자식 테이블의 외래키 값을 변경하면 자식 테이블에는 TX Lock과 TM(RX) Lock이 걸리고, 참조 대상인 부모 테이블의 스키마가 변경되면 안되므로 부모 테이블에는 TM(RX) Lock이 걸립니다.

UPDATE CHILD 
SET PARENT_SEQ = 1000
WHERE CHILD_SEQ = 1;

커밋되지 않은 상태에서 다른 세션을 통해 부모 테이블의 기본키가 아닌 컬럼 값을 변경하면 Lock 경합이 발생하지 않습니다. 아래 그림을 보면 호환되지 않는 TX Lock은 서로 다른 리소스 식별자 (ID1, ID2) 값을 가지고 있는 것을 확인할 수 있습니다.

UPDATE PARENT 
SET NAME = 'parent-new'
WHERE PARENT_SEQ = 1000;

Case 4) 부모 테이블 기본키 값을 삭제하는 경우

부모 테이블의 Row를 삭제하는 경우 부모 테이블에 TX Lock을 걸고 만약 부모키(PARENT_SEQ)를 참조하고 있는 자식의 외래키(CHILD_SEQ) 옵션이 CASCADE(참조하는 자식 Row 삭제) 또는 SET NULL(참조하는 자식 Row Null로 수정)이면 자식 테이블에도 TX Lock이 설정됩니다. ON DELETE 옵션이 설정되어 있지 않다면 참조 무결성 오류가 발생됩니다.

DELETE FROM PARENT
WHERE PARENT_SEQ = 1000;