#44_140811_OWI_LOCK

# LOCK

l 오라클의 Lock 발생에 대한 정리 표

- RS : Row shared

- RX : Row exlusive

- X : exclusive

- 예를 들를 RX mode인 insert, update, delete 같은 명령어를 한 세션에서 수행하면 exclusive인 alter나 drop를 같은 세션인 다른 terminal에서는 수행할 수 없다

n Lock Mode 테스트

# scott 계정 SQL> update emp     set sal = 8000   where ename='KING'; # 다른 terminal 창의 scott 계정 SQL> drop table emp;     ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table emp     drop column sal; ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

# scott 계정 SQL> select ename, sal from emp     where ename = 'SCOTT'   for update; - for update : emp테이블을 조회하는 동안 누구도 업데이트 하지 못하도록 하는 옵션 ENAME             SAL ---------- ---------- SCOTT               0 # 다른 terminal 창의 scott 계정 SQL> drop table emp; ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table emp drop column sal; alter table emp drop column sal ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired - 위 2개의 경우를 수행결과 resource busy 에러가 뜨는 것을 확인할 수 있다

n 쿼리로 lock에 대한 holder와 waiter 확인

# scott 계정 SQL> select ename, sal from emp     where ename = 'SCOTT'   for update; ENAME             SAL ---------- ---------- SCOTT               0 # 다른 terminal 창의 scott 계정 SQL> update emp   2  set sal = 0 3  where ename='SCOTT'; - update 문을 수행한 터미널창은 lock이 걸린다

column holder for a10 column waiter for a10 select distinct o.object_name, sh.username||'('||sh.sid||')' "Holder", sw.username||'('||sw.sid||')' "Waiter", decode(lh.lmode,1,'null', 2,'row share', 3,'row exclusive', 4,'share', 5,'share row exclusive', 6,'exclusive') "Lock Type" from all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lh where lh.id1 = o.object_id and lh.id1 = lw.id1 and sh.sid = lh.sid and sw.sid = lw.sid and sh.lockwait is null and sw.lockwait is not null and lh.type = 'TM' and lw.type = 'TM'; OBJECT_NAME                    Holder     Waiter     Lock Type ------------------------------ ---------- ---------- -------------------------------- EMP                            SCOTT(82)  SCOTT(77)  row exclusive - 위의 쿼리를 수행하면 holder와 waiter를 보여주고 있다

n bitmap index 테이블에 lock이 걸리는 경우

# scott 계정 SQL> connect scott/tiger SQL> create table bitmap_table      (ename1 char(1000),       ename2 char(1000),       ename3 char(1000)); SQL> create bitmap index bitmap_table_index       on bitmap_table(ename1, ename2, ename3); SQL> insert into bitmap_table values('a', 'b', 'c'); # 다른 터미널창의 scott SQL> insert into bitmap_table values('a', 'b', 'c'); - bitmap index가 걸린 테이블에 insert 명령일 뿐이지만 holder는 exclusive로 걸리고watier는 share로 되어있는 것을 확인할 수 있다 - 이것은 bitmap의 특성 때문인데 bitmap의 장점은 성별과 같은 동일 데이터가 많을 때 속도를 높일 수 있지만 단점으로 세그먼트 단위로 락을 건다 - 그래서 본래 lock은 row 단위지만 단순 insert 명령어를 각각 다른세션에서 수행했을 뿐인데 세그먼트로 락이 걸려 위와 같은 상황이 벌어지는 것을 확인할 수 있다.