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
작업들이 이루어진다.