전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
Lock Types
- 오라클은 일반적으로 아래와 같이 3가지 락 타입을 가지고 있다.
- DML locks : 데이터를 동시에 변경할 수 있는 메커니즘을 제공한다.
- DDL locks : 객체 구조의 정의를 보호한다.
- Internal locks and latches : 내부 자료구조를 보호하기 위해 내부 락(internal lock)과 래치를 사용한다. 래치는 오라클이 사용하는 경량의 저수준의 직렬 장치이며, 기능에서는 락과 비슷하다.
DML Locks
TX (Transaction) Locks
- TX 락은 트랜젝션이 첫 번째 변경을 시작할 때 획득하며, 커밋 또는 롤백을 수행할 때까지 유지된다.
- 다른 세션이 트랜잭션을 완료할 때까지 기다릴 수 있는 큐잉 메커니즘으로 사용된다.
락을 거는 과정은 다음과 같다.
- 락을 걸고 싶은 로우의 주소를 찾는다.
- 락 관리자에서 줄을 선다.
- 리스트에 락을 건다.
- 리스트를 검색하여 다른 누군가 해당 로우에 락을 걸었는지 조회한다.
- 여러분이 락을 걸었다는 사실을 입증하기 위해 리스트에 새 엔트리를 만든다.
- 리스트에 락을 푼다.
나중에 커밋할 때 다음과 같은 절차를 계속 해야 한다.
- 다시 락 관리자에 줄을 선다.
- 락 리스트에 락을 건다.
- 리스트를 검색하여 설정한 락을 해제한다.
- 리스트에 락을 푼다.
락을 획득하고 해제하는 과정은 다음과 같다.
- 락을 걸고 싶은 로우의 주소를 찾는다.
- 해당 로우로 간다.
- 그 자리에 있느 로울에 락을 건다.
세 개의 V$ 테이블을 이용하여 락에 대한 정보를 알 수 있다.
- V$TRANSACTION : 활동 중인 모든 트랜젝션에 대한 엔트리를 담고 있다.
- V$SESSION : 로그인한 세션을 보여준다.
- V$LOCK : 락을 기다리는 세션 뿐만 아니라 보유 중인 모든 enqueue 락에 대한 엔트리를 포함하고 있다.
- EMP와 DEPT 테이블의 복제본을 만든다.
SQL> create table dept_1
2 as select * from dept;
Table created.
SQL> create table empt_1
2 as select * from emp;
Table created.
SQL> alter table dept_1
2 add constraint dept_1_pk
3 primary key(deptno);
Table altered.
SQL> alter table emp_1
2 add constraint emp_1_pk
3 primary key(deptno);
Table altered.
SQL> alter table emp_1
2 add constraint emp_1_fk_dept
3 foreign key(deptno)
4 references dept_1(deptno);
Table altered.
SQL> create index emp_1_deptno_idx
2 on emp_1(deptno);
Index created.
- 이제 트랜젝션을 시작한다.
SQL> update dept_1
2 set dname = initcap(dname);
4 rows updated.
- 시스템의 상태를 조회한다.
SQL> select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER;
USERNAME SID RBS SLOT SEQ LMODE REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
UBACK 1088 3 14 457984 6 0
SQL> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 14 457984
다음과 같은 점을 발견할 수 있다.
- V$LOCK 테이블의 LMODE는 6이고 request는 0이다. LMODE가 6은 배타 락이고 request가 0은 락을 요청하지는 않았지만 가지고 있다는 것을 의미한다.
- 한 개의 로우만 있다. V$LOCK 테이블은 락 테이블이라기 보다는 큐잉 테이블에 가깝다.
- ID1과 ID2 컬럼을 취해서 몇 가지 조작을 수행했다. 오라클은 세 가지의 16비트 숫자를 저장할 필요가 있지만 두 개의 컬럼만 사용했다. 그래서 첫 번째 컬럼 ID1은 두 가지 숫자를 가지고 있다. trunc(id1/power(2,16)) rbs에서는 2^16 값으로 나누고 bitand(id1,to_number('ffff','xxxx'))+0 slot에서는 상위 비트를 마스크(mask)한다. 그래서 한 개의 숫자(ID1)에 숨어 있는 두 개의 숫자를 다시 얻을 수 있다.
- RBS, SLOT 그리고 SEQ 값은 V$TRANSACTION 정보와 일치한다. 이들이 트랜잭션 ID다.
- 다른 세션에서 다음과 같이 실행한다.
SQL> update emp_1 set ename = upper(ename);
14 rows updated.
SQL> update dept_1 set deptno = deptno-10;
4 rows updated.
- V$ 쿼리를 다시 실행한다.
SQL> select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER;
USERNAME SID RBS SLOT SEQ LMODE REQUEST
----------- ---------- ---------- ---------- ---------- ---------- ----------
UBACK 1043 3 11 457999 0 6
UBACK 1043 8 38 451952 6 0
UBACK 1088 3 11 457999 6 0
SQL> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
8 38 451952
3 11 457999
- 트랜잭션 ID가 (3,11,457999)가 새로운 트랜잭션을 시작했다는 것이다.
- SID=7인 새 세션과 관련해서 이번에는 V$LOCK에 2개의 로우가 존재한다.
- 한 로우는 세션이 소유한(LMODE가 6)인 락을 보여주고 또한 배탁 락에 대한 요청을 의미하는 REQUEST거 6인 로우도 보여준다.
- RBS/SLOT/SEQ 값은 락을 보유하고 있는 트랜잭션 ID이다.
- SID 703인 트랜잭션은 SID가 7인 트랜잭션을 블로킹하고 있다.
- 좀 더 자세히 보려면 아래와 같이 하면 된다.
SQL> select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------- ------------------------------ ----------
UBACK 1088 is blocking UBACK 1043
- SID가 1088인 트랜잭션을 커밋하고 쿼리를 다시 실행하면 락을 요청한 로우가 사라진 것을 발견할 수 있다.
SQL> select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER;
USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------- ---------- ---------- ---------- ---------- ---------- ----------
UBACK 1043 8 38 451952 6 0
SQL> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
8 38 451952
- 락을 포기하는 순간 요청 로우는 사라졌다. 큐잉 메카니즘이기 때문이다.
SQL> create table t
2 ( x int primary key,
3 y varchar2(4000)
4 )
5 /
Table created.
SQL> insert into t (x, y)
2 select rownum, rpad('*', 148, '*')
3 from dual
4 connect by level <= 46
46 rows created.
SQL> select length(y),
2 dbms_rowid.rowid_block_number(rowid) blk,
3 count(*), min(x), max(x)
4 from t
5 group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
--------- ------ -------- -------- -------
148 4599 46 1 46
- 테이블 T는 동일한 블록에 46개의 로우를 담고 있다.
SQL> create or replace procedure do_update(p_n in number)
2 as
3 pragma autonomous_transaction;
4 l_rec t%rowtype;
5 resource_busy exception;
6 pragma exception_init(resource_busy, -54);
7 begin
8 select *
9 into l_rec
10 from t
11 where x = p_n
12 for update NOWAI
14 do_update( p_n+1);
15 commit;
16 exception
17 where resource_busy
18 then
19 dbms_output.put_line('locked out trying to select row '|| p_n);
20 commit;
21 when no_data_found
22 then
23 dbms_output.put_line('we finished - no problems ;);
24 commit;
25 end;
26 /
procedure created.
- 라인 14는 반복적으로 락을 걸 새로운 기본키 값을 입력으로 해서 do_update 프로시져를 재귀적으로 호출한다.
- 프로시져를 실행한다.
SQL> exec do_update(1);
locked out trying to select row 38
PL/SQL procedure successfully completed
- 출력 결과는 37로우까지 락을 걸 수 있었지만 38번째 로우를 위한 트랜잭션 슬롯이 소진되었음을 보여준다.
- 주어진 블록에 대해 최대 37 트랜잭션이 동시에 블록을 액세스 할 수 있다.
- 조금 더 작은 문자열을 가지고 예제를 다시 해본다.
SQL> truncate table t;
Table truncated.
SQL> insert into t (x,y)
2 select rownum, rpad('*', 147, '*')
3 from dual
4 connect by level <= 46;
46 rows created
SQL> select length(y)
2 dbms_rowid.rowid_block_number(rowid) blk,
3 count(*), min(x), max(x)
4 from t
5 group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
--------- ------ -------- -------- -------
147 4663 46 1 46
SQL> exec do_update(1);
we finish - no problem
PL/SQL procedure successfully completed.
- 이번에는 한 바이트 차이로 성공적으로 완료했다. 이 경우는 블록에 추가적인 46바이트의 공간으로 인하여 적어도 9개의 트랜잭션이 추가로 동일한 블록을 액세스할 수 있었다.
TM (DML Enqueue) Locks
- TM 락은 내용을 변경하는 동안 테이블 구조를 변경하지 않도록 보장하는데 사용된다.
- 이것은 다른 사용자가 테이블을 ALTER 또는 DROP 명령을 수행하지 못하도록 한다.
|Session1|
|SQL> select * from dept_1 for update nowait;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> update dept_1 set deptno = deptno-10;
4 rows updated.|
Session2|
SQL> drop table dept_1;
drop table dept_1
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified|
Note
- 오라클 11g release 2 이후에서는 DDL 대기 시간을 조절하기 위해서 DDL_LOCK_TIMEOUT을 설정할 수 있다.
- 이것은 일반적으로 ALTER SESSION 명령어를 통해 이루어진다.
- 예를 들면 DROP 테이블 명령을 내리기 전에 ALTER SESSION SET DDL_LOCK_TIMEOUT = 60; 을 먼저 수행할 수 있다.
- 서버로 보낸 DROP TABLE 명령어는 오류를 리턴하기 전에 60초 동안 대기한다.
- 다음은 TM 락이 V$LOCK 테이블에서 어떻게 나타나는지 보여준다.
SQL> create table t1 ( x int );
Table created.
SQL> create table t2 ( x int );
Table created.
SQL> insert into t1 values ( 1 );
1 row created.
SQL> insert into t2 values ( 1 );
1 row created.
SQL> select (select username
from v$session
where sid = v$lock.sid) username,
sid,
id1,
id2,
lmode,
request, block, v$lock.type
from v$lock
where sid = (select sid
from v$mystat
where rownum=1)
/
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- --
UBACK 1088 217429 0 3 0 0 TM
UBACK 1088 217430 0 3 0 0 TM
UBACK 1088 655400 457568 6 0 0 TX
SQL> select object_name, object_id
2 from user_objects
3 where object_name in ('T1','T2');
OBJECT_NAME OBJECT_ID
-------------- ----------
T1 217429
T2 217430
- 트랜잭션당 한 개의 TX 락을 얻을 수 있는 반면, 변경하는 객체 수만큼 TM 락을 얻을 수 있다.
- TM 락의 ID1 컬럼은 DML 락이 걸린 객체의 ID라서 락이 걸려 있는 객체를 찾기 쉽다.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4409
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.