1) SID, 시리얼번호, 테이블명 확인
SELECT
A.SID, A.SERIAL#, A.USERNAME, A.PROCESS, B.OBJECT_NAME,
DECODE(C.LMODE,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
DECODE(A.COMMAND,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP
TABLE',26,'LOCK TABLE','UNKNOWN') "SQL",
DECODE(A.LOCKWAIT, NULL,'NO WAIT','WAIT') "STATUS"
FROM
V$SESSION A, DBA_OBJECTS B, V$LOCK C
WHERE
A.SID=C.SID AND B.OBJECT_ID=C.ID1
AND C.TYPE='TM';
2) SID, 테이블명 확인
SELECT
VO.SESSION_ID, DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE,DO.OWNER,
VO.XIDUSN, VO.LOCKED_MODE
FROM
V$LOCKED_OBJECT VO , DBA_OBJECTS DO
WHERE
VO.OBJECT_ID = DO.OBJECT_ID;
3) 테이블명, LOCK 상태 확인
SELECT
T1.OBJECT_NAME,
DECODE(LOCKED_MODE, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5,
'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') LOCK_MODE
FROM
DBA_OBJECTS T1, V$LOCKED_OBJECT T2
WHERE
T1.OBJECT_ID = T2.OBJECT_ID;
4) LOCK 상태와 세션 문자열 확인
(출력정렬 스크립트)
SET LINESIZE 132 PAGESIZE 66
BREAK ON KILL ON USERNAME ON TERMINAL
COLUMN KILL HEADING 'KILL STRING' FORMAT A13
COLUMN RES HEADING 'RESOURCE TYPE' FORMAT 999
COLUMN ID1 FORMAT 9999990
COLUMN ID2 FORMAT 9999990
COLUMN LMODE HEADING 'LOCK HELD' FORMAT A20
COLUMN REQUEST HEADING 'LOCK REQUESTED' FORMAT A20
COLUMN SERIAL# FORMAT 99999
COLUMN USERNAME FORMAT A10 HEADING "USERNAME"
COLUMN TERMINAL HEADING TERM FORMAT A6
COLUMN TAB FORMAT A35 HEADING "TABLE NAME"
COLUMN OWNER FORMAT A9
COLUMN ADDRESS FORMAT A18
SELECT
NVL(S.USERNAME,'INTERNAL') USERNAME,
NVL(S.TERMINAL,'NONE') TERMINAL,
L.SID||','||S.SERIAL# KILL,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) TAB,
DECODE(L.LMODE,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',NULL) LMODE,
DECODE(L.REQUEST,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',NULL) REQUEST
FROM
V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE
L.SID = S.SID
AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
2. LOCK 걸린 테이블 세션 제거
USAGE) ALTER SYSTEM KILL SESSION 'SESSION ID, 시리얼번호'
EX) ALTER SYSTEM KILL SESSION '189,26359'
3. PROCESS ID를 찾아내어 OS에서 제거
(LOCK 걸린 테이블의 세션 제거가 안될 경우)
1) PID 검색
SELECT
SUBSTR(S.USERNAME,1,11) "ORACLE USER", P.PID "PROCESS ID",
S.SID "SESSION ID", S.SERIAL#, OSUSER "OS USER",
P.SPID "PROC SPID",S.PROCESS "SESS SPID", S.LOCKWAIT "LOCK WAIT"
FROM
V$PROCESS P, V$SESSION S, V$ACCESS A
WHERE
A.SID=S.SID
AND P.ADDR=S.PADDR
AND S.USERNAME != 'SYS';
2) 리눅스에서 PID 제거
위 SQL 검색결과에서 LOCK걸린 V$SESSION.PROCESS 번호를 아래의 PID에 넣으면
된다.
$KILL -9 PID
===============================
DDL 작업 시 LOCK 문제 해결 예제
===============================
DDL LOCK은 다중 사용자의 DDL COMMAND로부터 DATABASE의 SCHEMA
OBJECT를 보호하기 위하여 사용되는 LOCK으로 SCHEMA OBJECT(TABLE,
PROCEDURE,PACKAGE, VIEW, SYNONYM, TRIGGER, FUNCTION PACKAGE BODY,
CLUSTER)를 생성하거나 삭제할 때 발생한다.
간혹 이러한 COMPILE이나 GRANT작업중 LIBRARY CACHE LOCK(ALSO CALLED
BREAKABLE PARSE LOCK)을 점유하지 못하여 작업이 되지 않는 경우가
발생하게 되는데 이러한 경우 DYNAMIC VIEW 의 DDL LOCK 정보를 조회하여
문제 해결을 할 수가 있다. 다음은 LOCK MONITORING VIEW 생성 및 BLOCKING
LOCK에 대한 예와 그 해결 방법에 대한 내용이다.
DYNAMIC TABLES
DBA_DDL_LOCKS : DATABASE의 모든 DDL LOCK에 대한 상태 정보
DBA_LOCK_INTERNAL : LIBRARY CACHE LOCK에 대한 상태 정보
DYNAMIC TABLE 생성
SYS USER(SVRMGRL)로 $ORACLE_HOME/RDBMS/ADMIN/CATBLOCK.SQL을 실행
다음은 PROCEDURE에서 수행되는 DML작업에 대한 LOCK CONFLICT가 이
PROCEDURE에 대한 DDL작업을 방해하는 예이다.
(마찬가지로 LONG RUNNING QUERY가 수행되고 있는 PROCEDURE에 대한 GRANT,
COMPILE과 같은 작업이 방해 받는 경우 역시 아래의 방법에 따라 그 원인을
확인해 볼 수 있다.
이때의 해결방법은 수행중인 PROCEDURE가 종료될 때 까지 기다리거나 KILL
시키는 방법이 있겠다.)
BLOCKING DDL LOCK 발생
SESSION A :
SQL> CREATE OR REPLACE PROCEDURE DELEMP(NO IN NUMBER) IS
BEGIN
DELETE EMP WHERE EMPNO = NO;
END;
SQL> EXEC DELEMP(7900);
(EMP TABLE에 대하여 TABLE LOCK 및 TX LOCK이 점유된다.)
SESSION B :
SQL> EXEC DELEMP(7900);
| <= HANGUP
(SESSION A에서 선점된 TX LOCK이 RELEASE될 때까지 WAITING 상태를
갖게 되며 SHARE MODE LIBRARY CACHE LOCK이 점유된다.)
SESSION C :
SVRMGR> CONNECT INTERNAL
SVRMGR> SELECT SESSION_ID, SUBSTR(LOCK_TYPE, 1, 10),
SUBSTR(MODE_HELD, 1, 10),
SUBSTR(MODE_REQUESTED, 1, 15), SUBSTR(LOCK_ID1, 1, 40),
SUBSTR(LOCK_ID2, 1, 10)
FROM DBA_LOCK_INTERNAL;
.
.
10 TRANSACTIO NONE EXCLUSIVE 65538 1904
10 TABLE/PROC SHARE NONE SCOTT.DELEMP 31FBC1D8
.
SESSION D :
SQL> DROP PROCEDURE DELEMP;
| <= HANGUP
SESSION C :
SQL> SELECT SESSION_ID, SUBSTR(LOCK_TYPE, 1, 10), SUBSTR(MODE_HELD, 1,
10),
SUBSTR(MODE_REQUESTED, 1, 15), SUBSTR(LOCK_ID1, 1, 40),
SUBSTR(LOCK_ID2, 1, 10)
FROM DBA_LOCK_INTERNAL;
10 TABLE/PROC NULL NONE SCOTT.DELEMP 31FBC1D8
10 TABLE/PROC SHARE NONE SCOTT.DELEMP 31FBC1D8
13 TABLE/PROC EXCLUSIVE NONE SCOTT.DELEMP 31FBC1D8
13 TABLE/PROC NONE EXCLUSIVE SCOTT.DELEMP 31FBC1D8
SQL> SELECT SESSION_ID, SUBSTR(NAME, 1, 15), SUBSTR(TYPE, 1, 10),
SUBSTR(MODE_HELD, 1, 15), SUBSTR(MODE_REQUESTED, 1, 15)
FROM DBA_DDL_LOCKS;
10 DELEMP TABLE/PROC NULL NONE
12 DELEMP TABLE/PROC NULL NONE
13 DELEMP TABLE/PROC NULL NONE
13 DELEMP TABLE/PROC EXCLUSIVE NONE
(SESSION D에 의해 점유된 DDL LOCK이 DBA_DDL_LOCKS에서 확인되나 WAITING
SESSION은 확인이 되지 않고 있으나 DBA_LOCK_INTERNAL에서는 BLOCKING
LOCK과 WAITING LOCK이 확인되고 있다.)
SESSION E:
SQL> ALTER PROCEDURE DELEMP COMPILE;
| <= HANGUP
SESSION C :
9 DELEMP TABLE/PROC NONE EXCLUSIVE
13 DELEMP TABLE/PROC EXCLUSIVE NONE
.
.
(SESSION D에 의해 선점된 DDL LOCK으로 인해 SESSION E의 DDL LOCK이
WAITING 상태에 있다.)
LOCK CONFLICT 해결
이제까지 DBA_LOCK_INTERNAL와 DBA_DDL_LOCKS을 조회하여 INTERNAL LOCK 및
DDL LOCK에 대하여 확인해 보았다. 이 예의 경우는 개발 작업 시 일어날 수
있는 상황을 재현해 본 것으로 해결 방법은 선점된 LOCK을 잡고 있는 SESSION
을 찾아 LOCK을 RELEASE해주는 것이다.
이 경우에서의 최선의 해결책은 선점된 DML LOCKING SESSION을 찾아 COMMIT
이나 ROLLBACK을 시키는 것이다.
다음은 위에서 예로 든 상황에 대한 해결 과정이다.
SQL> SELECT OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'EMP' AND OWNER = 'SCOTT';
SQL> SELECT * FROM V$LOCK WHERE TYPE IN ('TM', 'TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
30199BBC 30199BD0 10 TM 4769 0 3 0 2919 0
30176508 30176518 10 TX 65580 1908 0 6 2919 0
10364FE0 103650AC 12 TX 65580 1908 6 0 2946 1
30199B48 30199B5C 12 TM 4769 0 3 0 2946 0
조회 결과 OBJECT_ID에 대하여 TM LOCK을 잡고 있는 SESSION 중 TX LOCK을
선점한 SESSION ID가 12인 것을 알 수 있다.
SESSION 12에서 TX COMMIT을 발행하면 BLOCKING LOCK이 RELEASE되고 DDL
작업들이 이루어진다.
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE
OBJECT_TYPE='PROCEDURE' AND OWNER='WISEMASTER';
TRUNCATE TABLE ICOYPRDT_IF
COMMIT
SELECT * FROM ICOYPRDT_IF
EXECUTE WISEMASTER.IF_ICOYPRDT6('2008','05')
[출처] ORACLE LOCK & KILL|작성자 박쥐
댓글 없음:
댓글 쓰기