[Oracle] 오라클 Lock 확인 및 해제

-- 락걸린 테이블 확인 
SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,
        VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE
FROM    V$LOCKED_OBJECT VO, DBA_OBJECTS DO
WHERE   VO.OBJECT_ID = DO.OBJECT_ID;

-- 해당 테이블에 LOCK 이 걸렸는지.
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID
AND B.TYPE='TM' AND C.OBJECT_NAME IN ('TB_CO_GENO');

-- 락발생 사용자와 SQL, OBJECT 조회
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
                A.PROGRAM, B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM V$LOCKED_OBJECT X, V$SESSION A, V$SQLTEXT B, DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND 
X.OBJECT_ID = D.OBJECT_ID AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE;

-- 현재 접속자의 SQL 분석
SELECT DISTINCT A.SID, A.SERIAL#,
       A.MACHINE, A.TERMINAL, A.PROGRAM,
       B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM  V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE

-- 락 세션 죽이기
  SELECT A.SID,   A.SERIAL#
  FROM V$SESSION A,  V$LOCK B,
       DBA_OBJECTS C
 WHERE A.SID = B.SID
   AND B.ID1 = C.OBJECT_ID
   AND B.TYPE = 'TM'
   AND C.OBJECT_NAME = 'TB_CO_GENO'
   
SID SERIAL#
--- -------
5   1
6   1

2. 다음 명령으로 SESSION들을 KILL한다.  ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
SQL> alter system kill session '5, 1';
SQL> alter system kill session '6, 1';


-- 락 세션 죽이는 sql 문 
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
       A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'
FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
WHERE  X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME;

자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기 때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가 Release 되기를 기다리게 되는 경우도 있다.
3. Transaction을 직접 처리하는 경우 (@Transaction을 사용하지 않고 @Override 를 사용) commit 이나 rollback 하지 않고 바로 빠져 나가는 경우에도 lock 이 발생될 수 있다. 거래가 바로 빠져 나가는 경우는 exception 이 발생했으나 catch 를 제대로 하지 못 한 경우가 흔하다.

출처 : http://dev-kimke.tistory.com/1