잠금과 공간 경합

#핵심 요약

DML은 대상 테이블에 Row Exclusive(모드 3) TM 락을 자동으로 잡음
다른 DML과는 호환되지만 DROP, TRUNCATE, ALTER와 배타 테이블 락(모드 6)과는 충돌함

자식 테이블의 외래키 컬럼에 인덱스가 없으면, 부모 행을 지울 때 자식의 미커밋 DML과 TM 경합이 생김
실습에서는 이것을 현업의 주요 TM 경합 원인으로 설명했음

여러 세션, 노드가 같은 테이블에 대량 INSERT하면 HWM 확장 요청이 몰림
ASSM, 익스텐트 사전 확보, 노드별 파티션 적재로 완화함

RAC에서 데이터 파일은 공유하지만 Undo 테이블스페이스는 인스턴스별로 따로 씀
한 인스턴스의 Undo가 부족하면 그 인스턴스에 접속한 업무만 실패할 수 있음

#개념과 동작 원리

#TM 락 충돌 유형

#HWM과 공간 관리

HWM(High-Water Mark)은 지금까지 사용된 적 있는 가장 높은 블록의 경계임
INSERT로 기존 공간이 부족하면 HWM을 올리거나 새 익스텐트를 확보하고, 이 공간 관리 정보 갱신이 HW enqueue로 직렬화됨
RAC에서는 공간 관리 블록이 노드 사이를 오가며 추가 경합이 생길 수 있음

실습에서는 ASSM을 19c 기본값으로 설명했음
세그먼트 공간 회수는 세그먼트 공간 회수에 있음

#Undo와 US enqueue

워크로드가 급증하면 Undo 세그먼트 수가 자동으로 늘고 새 세그먼트가 온라인으로 바뀌는데, 이때 동시 요청이 몰리면 US 경합이 생길 수 있음
Undo를 더 확장할 수 없으면 ORA-30036으로 DML이 실패함
Undo의 용도(롤백, 읽기 일관성, Flashback, 인스턴스 복구)와 보존 기간은 언두 보존에 있음

주의: undo_retention을 늘려도 US 경합이 줄어든다는 보장은 없고, 보관 Undo가 늘어나는 만큼 공간이 필요함
RETENTION GUARANTEE는 커밋된 Undo 재사용을 막아 공간이 부족하면 DML이 실패할 수 있음

#필수 명령어 또는 SQL

#Oracle 락의 종류와 TM 락 모드

-- RS
LOCK TABLE emp IN ROW SHARE MODE;
-- RX
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
-- S
LOCK TABLE emp IN SHARE MODE;
-- SRX
LOCK TABLE emp IN SHARE ROW EXCLUSIVE MODE;
-- X
LOCK TABLE emp IN EXCLUSIVE MODE;

결과 해석:

일반 DML(INSERT, UPDATE, DELETE, MERGE)은 RX 모드 TM 락을 잡음
SELECT ... FOR UPDATE는 10g까지 RS, 11g부터 RX로 바뀌었지만 RS와 RX는 서로 호환되므로 동시성 차이는 크지 않다고 수업 자료는 정리함

TM 락은 테이블 전체를 잠그는 것이 아니라 "이 테이블에서 누가 작업 중이니 구조(DDL)를 바꾸지 마라"는 깃발 역할이라, 다른 행의 DML은 막지 않음
SQL Server의 인텐트 락(Intent Lock)과 같은 개념임

#락 대기 전략과 데드락, 커밋 관리

블로킹: 한 트랜잭션이 락을 쥐고 있어 뒤 세션들이 줄줄이 대기하는 상태이며, 앞 세션이 COMMIT이나 ROLLBACK해야 풀림

데드락: 두 트랜잭션이 서로 상대가 가진 행을 기다리는 상태
Oracle이 스스로 탐지해 한쪽 문장을 ORA-00060으로 취소(그 문장만 롤백)하지만, 여전히 락을 쥔 블로킹 상태라 프로그램이 오류를 받고 COMMIT이나 ROLLBACK을 정해야 끝남

트랜잭션을 너무 길게 끌면 락을 오래 잡고 UNDO가 고갈될 수 있고, 너무 자주 커밋하면 커밋마다 LGWR가 로그 버퍼를 디스크로 내려 써서 log file sync 대기가 늘어남
적절한 단위로 묶어 커밋함

-- 기본값, LGWR 기록 완료까지 대기(동기)
COMMIT WRITE WAIT;
-- 기록을 지시만 하고 바로 진행(비동기)
COMMIT WRITE NOWAIT;
-- 기본값, 커밋마다 즉시 기록
COMMIT WRITE IMMEDIATE;
-- 커밋 요청을 모았다가 한꺼번에 기록
COMMIT WRITE BATCH;

결과 해석:

결제처럼 안전이 최우선인 작업은 기본값 WAIT + IMMEDIATE를 씀

수백만 건을 빠르게 넣는 대규모 배치처럼 속도가 더 중요하면 NOWAIT + BATCH로 커밋 대기를 줄일 수 있지만, 커밋 직후 인스턴스 장애가 나면 방금 커밋한 데이터가 유실될 작은 위험이 있음

#TM 락 확인

CREATE TABLE emp_tm AS SELECT * FROM emp;
-- 커밋하지 않음
INSERT INTO emp_tm (empno, ename, sal) VALUES (9999, 'TMTEST', 1000);

-- 결과: 즉시 ORA-00054
DROP TABLE emp_tm;
ALTER SESSION SET ddl_lock_timeout = 10;
-- 결과: 10초 기다린 뒤 ORA-00054
DROP TABLE emp_tm;

SELECT l.inst_id, l.sid, l.type, o.object_name, l.lmode, l.request
  FROM gv$lock l
  JOIN dba_objects o ON o.object_id = l.id1
 WHERE l.type = 'TM'
   AND o.owner = '<SCHEMA>';

결과 해석: ID1은 잠긴 테이블의 오브젝트 번호임
실습 결과는 EMP_TM, LMODE 3(DML의 Row Exclusive)
DROP 시도 자체는 대기 이벤트가 아니라 즉시 오류이므로 락 정보로 확인함
실습에서는 실습 뒤 1번 노드에서 ROLLBACK했음

배타 테이블 락 재현: 1번 노드가 LOCK TABLE emp_tm IN EXCLUSIVE MODE(모드 6)를 잡고 2번 노드가 UPDATE하면 2번 노드가 enq: TM - contention으로 기다림
GV$LOCK에서 1번 LMODE 6, 2번 REQUEST 3
LOCK TABLE은 트랜잭션이 끝날 때 풀림

#외래키 인덱스 유무 비교

CREATE TABLE dept_tm AS SELECT * FROM dept;
ALTER TABLE dept_tm ADD CONSTRAINT dept_tm_pk PRIMARY KEY (deptno);
ALTER TABLE emp_tm ADD CONSTRAINT emp_tm_fk FOREIGN KEY (deptno) REFERENCES dept_tm (deptno);
-- 커밋하지 않음
INSERT INTO emp_tm (empno, ename, deptno) VALUES (9998, 'FKTEST', 10);

-- 결과: 40번과 무관한데도 enq: TM - contention 대기
DELETE FROM dept_tm WHERE deptno = 40;

결과 해석:

양쪽을 롤백하고 자식 외래키 컬럼에 인덱스를 만든 뒤 같은 순서로 하면 2번 노드의 DELETE가 대기 없이 바로 성공했음

CREATE INDEX emp_tm_deptno_ix ON emp_tm (deptno);

결과 해석:

연습 문제: 카테고리(부모)와 상품_fk테스트(자식, 2만 건, 외래 키 카테고리번호)에서 야간 배치가 카테고리 일부를 UPDATE하는 동안 주간 상품 INSERT, UPDATE가 대기한다는 민원이 들어옴
현재 락 상태를 조회해 원인을 진단하고, 자식 외래 키 컬럼에 인덱스를 만들어 해결함
외래 키 컬럼에 인덱스가 없으면 부모 UPDATE, DELETE 때 자식 테이블 전체 범위로 TM 락 경합이 퍼질 수 있음

#HW 경합 재현과 완화

-- ASSM
CREATE TABLESPACE ts77 DATAFILE '+data' SIZE 200M SEGMENT SPACE MANAGEMENT AUTO;
-- MSSM
CREATE TABLESPACE ts78 DATAFILE '+data' SIZE 200M SEGMENT SPACE MANAGEMENT MANUAL;

SELECT tablespace_name, segment_space_management
  FROM dba_tablespaces
 WHERE tablespace_name IN ('TS77', 'TS78');

결과 해석:

실습에서는 MSSM 테이블에 두 노드 다섯 세션에서 동시에 INSERT ... SELECT * FROM emp를 1만 번 반복하며 비Idle 대기(enq: HW - contention, gc 계열, buffer busy 등)를 조회하고, 앞뒤로 AWR 스냅샷을 찍어 ADDM 보고서를 만들었음(ADDM과 진단 도구)
이어서 ASSM 테이블에 익스텐트를 미리 확보하고 같은 부하를 주어 비교했음

ALTER TABLE <SCHEMA>.emp77 ALLOCATE EXTENT (SIZE 100M);

SELECT inst_id, total_waits, ROUND(time_waited_micro / 1000000, 1) AS wait_sec
  FROM gv$system_event
 WHERE event = 'enq: HW - contention';

결과 해석: 두 측정 사이의 누적값 증가분을 비교함
비교 수치와 Elapsed 결과는 기재 없음
실습 완화 방법에는 크고 균일한 익스텐트 구성과 세그먼트 분할, 노드별 다른 파티션 적재도 있음
실습에서는 실습 뒤 VM 공간 확보를 위해 두 테이블스페이스를 INCLUDING CONTENTS AND DATAFILES로 지웠음

#Undo 점검과 설정

SELECT inst_id, value FROM gv$parameter WHERE name = 'undo_tablespace';

SELECT tablespace_name, COUNT(*)
  FROM dba_rollback_segs
 WHERE status = 'ONLINE'
 GROUP BY tablespace_name;

SELECT inst_id, MAX(maxquerylen) AS max_query_sec, MAX(tuned_undoretention) AS tuned_retention
  FROM gv$undostat
 GROUP BY inst_id;

ALTER TABLESPACE undotbs1 ADD DATAFILE '+data' SIZE 20M;
ALTER TABLESPACE undotbs2 ADD DATAFILE '+data' SIZE 30M;
ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH SID = '*';

결과 해석:

실습에서는 최장 쿼리가 1시간이라는 가정으로 3600을 설정했고, RETENTION GUARANTEE를 걸어 DBA_TABLESPACES.RETENTION으로 확인한 뒤 실습 VM의 Undo가 작아 NOGUARANTEE로 되돌렸음
공유 스토리지에서는 명령을 실행한 노드와 상관없이 대상 Undo 테이블스페이스가 어느 인스턴스용인지 확인함

#확인 문제: 야간 로그 적재를 두 노드로 나눴더니 더 느려짐

상황: 접속 로그 적재 배치를 두 노드에서 동시에 돌렸더니 오히려 느려졌고, 로그 테이블은 오래전에 만든 테이블스페이스에 있음

CREATE TABLESPACE ts_oldlog DATAFILE '+DATA' SIZE 300M SEGMENT SPACE MANAGEMENT MANUAL;
CREATE TABLE <SCHEMA>.t_access_log TABLESPACE ts_oldlog AS SELECT * FROM <SCHEMA>.emp WHERE 1 = 2;

결과 해석:

준비 블록은 한 세션에서 한 번만 실행하고, 아래 적재 블록만 두 노드의 네 세션에서 동시에 실행함

SET TIMING ON
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO <SCHEMA>.t_access_log SELECT * FROM <SCHEMA>.emp;
  END LOOP;
  COMMIT;
END;
/

SELECT inst_id, event, COUNT(*)
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND wait_class <> 'Idle'
 GROUP BY inst_id, event;

SELECT t.tablespace_name, t.segment_space_management
  FROM dba_tables s
  JOIN dba_tablespaces t ON t.tablespace_name = s.tablespace_name
 WHERE s.table_name = 'T_ACCESS_LOG';

-- 오늘 밤 바로 할 수 있는 조치: 공간을 미리 확보
ALTER TABLE <SCHEMA>.t_access_log ALLOCATE EXTENT (SIZE 100M);

-- 점검 시간의 구조 개선: ASSM 테이블스페이스로 이동
CREATE TABLESPACE ts_newlog DATAFILE '+DATA' SIZE 300M SEGMENT SPACE MANAGEMENT AUTO;
ALTER TABLE <SCHEMA>.t_access_log MOVE TABLESPACE ts_newlog;

결과 해석:

예상값: 대기 이벤트 enq: HW - contention, 공간 관리 방식 MANUAL

조치 뒤 같은 적재를 다시 돌려 Elapsed를 비교하고, 실습이 끝나면 두 테이블스페이스를 INCLUDING CONTENTS AND DATAFILES로 지움

실행 결과 확인 불가

#확인 문제: 업무 중 결제 테이블 컬럼 추가가 ORA-00054로 실패

상황: 결제 테이블에 컬럼을 추가하려는데 ORA-00054로 계속 실패하고, 업무 시간이라 서비스를 내릴 수 없음
실습에서는 2번 노드에서 t_payment에 한 행을 넣고 커밋하지 않은 채 1번 노드에서 ALTER TABLE ... ADD를 실행해 재현함

SELECT l.inst_id, l.sid, s.serial#, s.username, s.prev_sql_id, l.lmode
  FROM gv$lock l
  JOIN gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid
  JOIN dba_objects o ON o.object_id = l.id1
 WHERE l.type = 'TM'
   AND o.object_name = 'T_PAYMENT';

ALTER SESSION SET ddl_lock_timeout = 60;
ALTER TABLE <SCHEMA>.t_payment ADD (pay_method VARCHAR2(10));
-- 2번 노드 거래가 COMMIT하는 순간 이 ALTER가 완료됨

결과 해석:

예상값: Holder는 INST_ID 2, LMODE 3(미커밋 DML의 TM 락)

DDL은 기다리지 않고 바로 실패하므로 대기 이벤트가 아니라 락 정보(GV$LOCK의 TM)로 Holder를 찾음

DDL이 기다리는 동안 그 뒤에 온 DML도 기다릴 수 있으므로 부하가 낮은 시간에 짧은 대기 시간으로 함

실행 결과 확인 불가