꿈꾸는 개발자, DBA 커뮤니티 구루비

DDL Locks

DDL 락은 DDL 작업 동안 다른 세션에 의한 변경으로부터 객체를 보호하기 위해 자동으로 객체에 걸린다.

아래와 같은 순서로 DDL은 실행된다.


Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;

세 가지의 DDL 락이 존재한다.

배타 DDL 락 : 다른 세션이 DDL 락 또는 TM(DDL) 락을 획득하지 못하게 한다. DDL 작업 동안 쿼리는 가능하지만 어떤 방법으로 테이블은 변경할 수 없다.
공유 DDL 락 : 다른 세션에 의해 변경되는 것에 대비하여 참조하는 객체 구조를 보호하지만 데이터에 대한 변경은 허용된다.
Breakable Parse 락 : shared pool에 캐시된 실행계획처럼 객체 간의 의존 관계를 등록한다.

아래의 문장을 수행하는 동안 테이블 T는 변경 대상에서 제외된다.


SQL> Alter table t add new_column date;

Table altered.

오라클에서 몇몇 DDL 작업은 DDL 락 없이 일어날 수 있다.


SQL> create index t_idx on t(x) ONLINE;

Index created.

ONLINE 키워드는 실제로 인덱스를 만드는 방법을 변경한다.
데이터 변경을 막는 배타 DDL 락을 획득하는 대신에 오라클은 테이블에 로우 레벨(모드 2)의 TM 락만 획득한다.
다른 DDL은 막을 수 있지만 DML은 정상적으로 수행된다.

어느 정도 크기를 갖는 테이블을 생성하여 ONLINE 작업을 살펴보자.


SQL> create table t as select * from all_objects;

Table created.

SQL> select object_id from user_objects where object_name = 'T';

OBJECT_ID
---------
    89791

테이블 T에 대하여 인덱스를 생성하라.


SQL> create index t_idx on t(owner, object_type, object_name) ONLINE;

Index created.

동시에 또 다른 세션에서 테이블 T에 걸린 락을 보기 위해 아래 쿼리를 실행한다.


SQL> select (select username
  2  from v$session
  3  where sid = v$lock.sid) username,
  4  sid, id1, id2, lmode, request, block, v$lock, type
  5  from v$lock
  6  where id1 = 89791
  7  /

USERNAME    SID    ID1    ID2    LOME   REQUEST  BLOCK TY
----------- ---- ----- ------ ------- --------- ------ --
OPS$TKYTE   702  89791      0       3         0      0 DL
OPS$TKYTE   702  89791      0       3         0      0 DL
OPS$TKYTE   702  89791      0       4         0      0 OD
OPS$TKYTE   702  89791      0       2         0      0 TM

테이블 T를 대상으로 4개의 락을 볼 수 있다.
DL 락 2개는 direct load 락이다.
- direct load 락은 인덱스 생성이 일어나는 동안 대상 테이블에 direct path load를 막는다.

아래의 명령어를 처리하는 동안에 EMP와 DEPT에 대해 공유 락을 건다. 테이블의 내용을 변경할 수는 있으나 테이블의 구조를 변경할 수는 없다.


SQL> Create view MyView
  2  as
  3  select emp.*
  4  from emp, dept
  5  where emp.deptno = dept.deptno;
View created.

마지막 타입의 DDL 락은 breakable parse 락이다. 세션이 문장을 파싱할 때 그 문장에 의해 참조되는 모든 객체를 대상으로 parse 락을 건다. 만약 참조되는 객체가 어떤 식으로던 drop 되거나 변경되면 파싱되어 캐쉬된 문장을 shared pool에서 무효화시키기 위해서 이 락을 취한다.

DDL 락의 정보를 보는 데 필요한 뷰는 DBA_DDL_LOCK이다.


SQL> select session_id sid, owner, name, type,
  2  mode_held held, mode_requested request
  3  from dba_ddl_locks;

SID     OWNER   NAME                TYPE                    HELD    REQUES
----    ------  ------------------  --------------------    ----    ------
1012    EXBACK  EXBACK              18                      Null    None
1088    SYS     DBMS_CRYPTO         Body                    Null    None
1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None
1088    SYS     UTL_RAW             Body                    Null    None

주의할 점은 OWNER는 락의 소유자가 아니라 락이 걸린 객체의 소유자를 나타낸다.

살아있는 breakable parse 락을 보기 위해서 우선 저장 프로시저 P를 생성하고 실행한다.


SQL> create or replace procedure p as begin null; end;
  2  /

Procedure created.

SQL> exec p;

PL/SQL procedure successfully completed.

P에 대한 내용을 DBA_DDL_LOCKS 뷰에서 볼 수 있다.


SQL> select session_id sid, owner, name, type,
  2  mode_held held, mode_requested request
  3  from dba_ddl_locks;

SID     OWNER   NAME                TYPE                    HELD    REQUES
----    ------  ------------------  --------------------    ----    ------
1012    EXBACK  EXBACK              18                      Null    None
1088    SYS     DBMS_CRYPTO         Body                    Null    None
1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None
1088    SYS     UTL_RAW             Body                    Null    None
1088    UBACK   P                   Table/Procedure/Type    Null    None

프로시져를 재컴파일하고 뷰를 다시 검색한다.


SQL> alter procedure p compile;

Procedure altered.

SQL> select session_id sid, owner, name, type,
  2  mode_held held, mode_requested request
  3  from dba_ddl_locks;

SID     OWNER   NAME                TYPE                    HELD    REQUES
----    ------  ------------------  --------------------    ----    ------
1012    EXBACK  EXBACK              18                      Null    None
1088    SYS     DBMS_CRYPTO         Body                    Null    None
1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None
1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None
1088    SYS     UTL_RAW             Body                    Null    None

parse 락이 사라진 것을 볼 수 있다. 해제되었기 때문이다.