[Oracle] lock에 대한 근본적인 원인 해결

• 장시간 동안 commit을 하지 않은 트랜잭션이 없게 해야함
• 락이 자주 발생하는 특정 테이블에 대해서 갱신 작업시간을
  팀마다 또는 부서마다 별도의 시간으로 나눠서 작업하게 함

• 다음과 같이 높은 레벨로 거는 락을 피하는게 바람직함

위의 상황을 만들어놓고 다음과 같이 오렌지에서 확인합니다.

락 홀더를 찾으려면?

---

문제1. dept 테이블에 데이터를 update 하는 프로세서를 2개를 만들어서 락 waiting 상황을 만들고 오렌지에서 홀더와 웨이터를 찾으시오

1. 먼저 wait event 모니터에서 tx enqueue 대기이벤트로 대기하는 세션의 sid를 알아냅니다.

2. 이 sid로 lock monitor로 가서 홀더와 웨이터의 SQL을 조회합니다.

---

문제2. 위의 상황을 오렌지가 아니라 SQL 스크립트로 알아내게 하시오

-- tx_lock_full.sql 

-- 락 모니터링 및 Kill 명령어 생성
SET PAGESIZE 30
SET LINESIZE 300
SET WRAP OFF

COLUMN sid FORMAT 999 HEADING "SID"
COLUMN username FORMAT A8 HEADING "USERNAME"
COLUMN type FORMAT A4 HEADING "TYPE"
COLUMN lmode FORMAT 999 HEADING "LMODE"
COLUMN request FORMAT 999 HEADING "REQUEST"
COLUMN block FORMAT 9 HEADING "BLOCK"
COLUMN holding FORMAT A10 HEADING "HOLDING"
COLUMN waiting FORMAT A10 HEADING "WAITING"
COLUMN status FORMAT A8 HEADING "STATUS"
COLUMN wait_st FORMAT A8 HEADING "WAIT"

-- 락 상황 조회
SELECT 
    l.sid,
    NVL(s.username,'?') as username,
    l.type,
    l.lmode,
    l.request,
    l.block,
    DECODE(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') as holding,
    DECODE(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') as waiting,
    DECODE(l.block,1,'BLOCKER',DECODE(l.request,0,'HOLDER','WAITER')) as status,
    CASE WHEN sw.event LIKE '%TX%' THEN 'TX-WAIT' 
         ELSE 'OK' END as wait_st
FROM v$lock l, v$session s, v$session_wait sw
WHERE l.sid = s.sid
  AND l.sid = sw.sid (+)
  AND l.type IN ('TX', 'TM')
  AND (l.lmode > 0 OR l.request > 0)
ORDER BY l.block DESC, l.sid;

PROMPT
PROMPT ======================= KILL COMMANDS =======================

-- BLOCKER Kill 명령어
COLUMN kill_blocker FORMAT A80 HEADING "KILL BLOCKER COMMAND"
SELECT 'ALTER SYSTEM KILL SESSION '''||l.sid||','||s.serial#||''';' as kill_blocker
FROM v$lock l, v$session s
WHERE l.sid = s.sid
  AND l.type = 'TX'
  AND l.block = 1;

PROMPT
PROMPT ==================== OPTIONAL WAITER KILL ====================

-- WAITER Kill 명령어
COLUMN kill_waiter FORMAT A80 HEADING "KILL WAITER COMMAND"  
SELECT 'ALTER SYSTEM KILL SESSION '''||l.sid||','||s.serial#||''';' as kill_waiter
FROM v$lock l, v$session s
WHERE l.sid = s.sid
  AND l.type = 'TX'
  AND l.request > 0;
COLUMN block FORMAT 9 HEADING "B"
COLUMN holding FORMAT A6 HEADING "HOLD"
COLUMN waiting FORMAT A6 HEADING "WANT"
COLUMN status FORMAT A6 HEADING "STAT"
COLUMN wait_st FORMAT A4 HEADING "WAIT"

💡 ocp 시험 문제
틀린 답:
하위 레벨의 락이 상위 레벨의 락으로 자동으로 올라간다.(escalate)
--> 오답