락과 데드락

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,                      -- enq: TX - row lock contention
       s.seconds_in_wait,
       s.blocking_session AS blocker_sid,
       s.machine,
       s.program
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

blocking_session 이 원인 세션의 SID입니다. event 가 enq: TX - row lock contention 이면 행 락 대기입니다. seconds_in_wait 으로 얼마나 오래 막혔는지 봅니다.

SELECT s.sid, s.serial#, s.username, s.status,
       s.last_call_et AS idle_sec,   -- 마지막 작업 이후 경과 시간
       s.machine, s.program,
       q.sql_text
FROM   v$session s
LEFT JOIN v$sql q ON q.sql_id = s.prev_sql_id
WHERE  s.sid = :blocker_sid;

status 가 INACTIVE 이고 idle_sec 이 크면 사람이 커밋하지 않고 자리를 비운 경우입니다. ACTIVE 면 실제로 긴 작업이 도는 중이므로 끊기 전에 담당자를 먼저 확인합니다.

-- 확인 후에만 실행합니다
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

끊으면 그 세션의 트랜잭션이 롤백됩니다. 되돌릴 양이 많으면 롤백에도 시간이 걸립니다. 무엇을 하던 세션인지 확인하지 않고 끊으면 업무 데이터가 통째로 사라질 수 있으므로, 운영에서는 담당자 확인을 원칙으로 합니다.

SELECT c.table_name,
       c.constraint_name,
       cc.column_name
FROM   user_constraints c
JOIN   user_cons_columns cc
  ON   cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND    NOT EXISTS (
         SELECT 1
         FROM   user_ind_columns ic
         WHERE  ic.table_name  = c.table_name
         AND    ic.column_name = cc.column_name
         AND    ic.column_position = 1
       )
ORDER BY c.table_name;

여기 나온 FK 컬럼에는 인덱스를 만드는 것을 검토합니다. 부모 삭제가 전혀 없는 마스터 테이블이라면 그대로 둬도 되지만, 대부분은 만들어 두는 편이 안전합니다.

DECLARE
  CURSOR c_target IS
    SELECT order_id FROM orders
    WHERE  status_cd = 'READY' AND order_dt < SYSDATE - 30;
  v_cnt NUMBER := 0;
BEGIN
  FOR r IN c_target LOOP
    UPDATE orders SET status_cd = 'EXPIRED' WHERE order_id = r.order_id;
    v_cnt := v_cnt + 1;

    IF MOD(v_cnt, 5000) = 0 THEN
      COMMIT;   -- 5000건마다 락을 풀어 줍니다
    END IF;
  END LOOP;
  COMMIT;
END;
/

커밋 주기가 너무 짧으면 오히려 느려지고, 너무 길면 락을 오래 잡습니다. 수천 건 단위가 일반적인 출발점이고 실제 환경에서 측정해 조정합니다. 중간 커밋을 하는 순간 전체 되돌리기는 포기하는 것이므로, 재실행해도 안전한 조건인지 먼저 확인합니다.

-- 기본은 0초. 락을 못 잡으면 즉시 ORA-00054
ALTER SESSION SET ddl_lock_timeout = 30;

-- 인덱스 생성은 ONLINE 옵션으로 DML을 막지 않고 만들 수 있습니다
CREATE INDEX idx_orders_status ON orders (status_cd) ONLINE;

ONLINE 옵션은 DML을 막지 않지만 부하는 그대로 발생하고 시간도 더 걸립니다. 작업 시간대를 잡는 것이 기본이고 ONLINE은 보조 수단입니다.