락 타입 (by xsoft) [2012.10.29]
목차
오라클은 일반적으로 락을 세 가지로 분류함
| 구분 | 설명 |
|---|---|
| DML 락 | DML 락은 INSERT, UPDATE, MERGE 그리고 DELETE 문을 말함. 또한 이는 데이터를 동시에 변경할 수 있는 메커니즘을 제공함. |
| DDL 락 | DDL 락은 CREATE, ALTER 문 등을 의미함. DDL 락은 객체 구조의 정의를 보호함. |
| 내부 락과 래치 | 오라클은 내부 자료구조를 보호하기 위해 내부 락(internal lock)과 래치(latch)를 사용함. 예를들어, 오라클은 쿼리를 파싱하고 최적화된 실행계획을 생성하고, 라이브러리 캐시를 래칭한 다음, 다른 세션이 공유할 수 있도록 실행계획을 라이브러리 캐시에 저장함. 래치는 오라클이 사용하는 경량의 저수준 직렬(serialization) 장치이며, 기능에서는 락과 비슷함. |
1. DML 락
- DML 락은 한 번에 한 사람만 로우를 변경하도록 하여, 다른 누군가가 작업 중인 테이블을 DROP 할 수 없도록 보증하는데 사용함.
1-1) TX(Transaction) 락
- TX 락은 트랜잭션이 첫 번째 변경을 시작할 때 획득되며, 트랜잭션이 커밋 또는 롤백을 수행할 대가지 유지됨.
- 또한, 다른 세션의 트랜잭션이 완료될 때까지 기다릴 수 있는 큐잉 매커니즘으로 사용됨.
1-1-1) 락 관리자가 있는 데이터베이스
- 전통적인 메모리 기반으로 한, 락 관리자가 있는 데이터베이스에서 로우에 락을 거는 과정
| 1 | 락을 걸고 싶은 로우의 주소를 찾는다. |
| 2 | 락 관리자에 줄을 선다(락 관리자는 직렬화 되어야 함) |
| 3 | 리스트에 락을 건다. |
| 4 | 리스트를 검색하여 다른 누군가 해당 로우에 락을 걸었는지 조회한다. |
| 5 | 여러분이 락을 걸었다는 사실을 입증하기 위해 리스트에 새 엔트리를 만든다. |
| 6 | 리스트에 락을 푼다. |
- 나중에 변경사항을 커밋할 대 다음의 절차를 계속해야 함.
| 1 | 다시 락 관리자에 줄을 선다. |
| 2 | 락 리스트에 락을 건다. |
| 3 | 리스트를 검색하여 설정한 락을 해제한다. |
| 4 | 리스트에 락을 푼다. |
1-1-2) 오라클
- 오라클에서 락을 거는 과정
| 1 | 락을 걸고 싶은 로우의 주소를 찾는다. |
| 2 | 해당 로우로 간다. |
| 3 | 그 자리에 있는 로우에 락을 건다. 어딘가에 따로 존재하는 긴 리스트에 락을 거는 것이 아니라, 로우가 있는 위치에 락은 건다( (NOWAIT 옵션을 사용하지 않았고, 로우에 이미 락이 걸렸다면 락을 건 트랜잭션이 끝날 때까지 기다린다) |
- 오라클은 락을 데이터의 속성으로 저장하기 때문에 전통적인 락 관리자가 필요없음.
- 오라클 락 모니터링을 위해 사용하는 Dynamic Performance View는 아래와 같다.
| 구분 | 설명 |
|---|---|
| V$TRANSACTION | 활동 중인 모든 트랜잭션에 대한 엔트리를 담고 있다. |
| V$SESSION | 로그인을 한 세션을 보여준다. |
| V$LOCK | 락을 기다리는 세션뿐만 아니라 보유 중인 모든 enqueue 락에 대한 엔트리를 포함하고 있다. 이 뷰에서는 세션 락이 건 각 로우에 대한 정보는 볼 수 없으며, 로우가 한개든 수백맨 로우든 이 뷰는 단지 한 개의 로우만 보여준다. |
- 이에 대한 테스트 Case는 아래와 같다.
-- 1. (Session_1) 샘플 데이터 생성
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;
/
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO);
ALTER TABLE EMP ADD CONSTRAINT EMP_FK_DEPT FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
CREATE INDEX EMP_DEPTNO_IDX ON EMP(DEPTNO);
-- 2. (Session_1) 트랜잭션 시작
UPDATE DEPT
SET DNAME = INITCAP(DNAME)
;
4 rows updated.
;
-- 3. (Session_1) Lock 모니터링
/***************************************************************************************************************************/
-- LMODE가 6이라는 것은 배타 락을 의미하며, REQUEST가 0 이라는 것은 락을 요청하지 않았지만 락을 갖고 있다는 것을 의미함.
-- V$LOCK에는 여러개의 로우가 락이 걸려 있더라도 한개의 로우만 보여줌.
-- L.ID1을 통해 V$TRANSACTION의 XIDUSN, XIDSLOT 2개 값을 얻을 수 있고, L.ID2와 V$TRANSACTION의 XIDSQN 와는 동일함.
-- 즉, V$LOCK에서 ID1, ID2를 통해 V$TRANSACTION의 트랜잭션 ID를 얻을 수 있음.
/***************************************************************************************************************************/
@mysess
;
MY_SESSION_INFO
--------------------------------------------------
DB Name : DB
Sid, Serial# : 9563, 45
OS Process : 3952:4172 (CPID), 684848 (SPID)
DB User : USER
OS User : raxsoft
Module Info : 01@ mysess.sql
Program Info : 01@ mysess.sql
Machine[Term] : RAXSOFT [RAXSOFT]
;
SELECT S.USERNAME,
L.SID,
L.ID1,
TRUNC(L.ID1 / POWER(2, 16)) RBS,
BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
L.ID2 SEQ,
L.LMODE,
L.REQUEST
FROM V$LOCK L,
V$SESSION S
WHERE L.SID = S.SID
AND L.TYPE = 'TX'
AND S.USERNAME = USER
;
USERNAME SID ID1 RBS SLOT SEQ LMODE REQUEST
--------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
APPS 9563 1363148812 20800 12 3 6 0
;
SELECT XIDUSN,
XIDSLOT,
XIDSQN,
TO_CHAR(XIDUSN, 'XXXX') || TO_CHAR(XIDSLOT, 'XXXX') || TO_CHAR(XIDSQN, 'XXXX') TO_HEX_XID,
XID
FROM V$TRANSACTION
;
XIDUSN XIDSLOT XIDSQN TO_HEX_XID XID
---------- ---------- ---------- ------------------------------ ----------------
20800 12 3 5140 C 3 5140000C00000003
;
-- 4. (Session_2) EMP, DEPT 테이블 수정
@mysess
;
MY_SESSION_INFO
-----------------------------------------------------
DB Name : DB
Sid, Serial# : 9604, 45
OS Process : 3448:5492 (CPID), 656242 (SPID)
DB User : USER
OS User : raxsoft
Module Info : 01@ mysess.sql
Program Info : 01@ mysess.sql
Machine[Term] : RAXSOFT [RAXSOFT]
;
UPDATE EMP
SET ENAME = UPPER(ENAME)
;
14 rows updated.
UPDATE DEPT
SET DEPTNO = DEPTNO - 10
;
-- 5. (Session_3) Lock 모니터링
/***************************************************************************************************************************/
-- V$LOCK에서 보면 Session_1(SID : 9563)과 Session_2(SID : 9604) 2개 모두 REQUEST가 0인 락을 보유하고 있음(블로킹 세션)
-- 또한 Session_2(SID : 9604)는 REQUEST가 6인 상태이므로 락으로 인해 대기중인 상태.
-- 여기서 Session_2는 Session_1의 XID와 값이 같기 때문에 같은 트랜잭션을 처리하고 있는 것을 확인할 수 있음.
/***************************************************************************************************************************/
@mysess
;
MY_SESSION_INFO
-------------------------------------------------
DB Name : DB
Sid, Serial# : 9595, 67
OS Process : 2480:4776 (CPID), 652122 (SPID)
DB User : USER
OS User : raxsoft
Module Info : 01@ mysess.sql
Program Info : 01@ mysess.sql
Machine[Term] : RAXSOFT [RAXSOFT]
;
SELECT S.USERNAME,
L.SID,
L.ID1,
TRUNC(L.ID1 / POWER(2, 16)) RBS,
BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
L.ID2 SEQ,
L.LMODE,
L.REQUEST
FROM V$LOCK L,
V$SESSION S
WHERE L.SID = S.SID
AND L.TYPE = 'TX'
AND S.USERNAME = USER
;
USERNAME SID ID1 RBS SLOT SEQ LMODE REQUEST
--------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
APPS 9604 1363148812 20800 12 3 0 6 -- 같은 트랜잭션
APPS 9563 1363148812 20800 12 3 6 0 -- 같은 트랜잭션
APPS 9604 1363869742 20811 46 2 6 0
;
SELECT XIDUSN,
XIDSLOT,
XIDSQN,
TO_CHAR(XIDUSN, 'XXXX') || TO_CHAR(XIDSLOT, 'XXXX') || TO_CHAR(XIDSQN, 'XXXX') TO_HEX_XID,
XID
FROM V$TRANSACTION
;
XIDUSN XIDSLOT XIDSQN TO_HEX_XID XID
---------- ---------- ---------- ------------------------------ ----------------
20800 12 3 5140 C 3 5140000C00000003
20811 46 2 514B 2E 2 514B002E00000002
;
-- 6. (Session_3) V$LOCK 셀프조인 모니터링
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.ID1 = B.ID1
AND A.ID2 = B.ID2
AND A.BLOCK = 1 -- Lock을 잡고 있는 세션
AND B.REQUEST > 0 -- Lock으로 인해 대기하는 세션
;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- -------------------------- -------- ----------
APPS 9563 is blocking APPS 9604
;
-- 7. (Session_1) 트랜잭션 Commit한 이후 Lock 모니터링
COMMIT
;
SELECT S.USERNAME,
L.SID,
L.ID1,
TRUNC(L.ID1 / POWER(2, 16)) RBS,
BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
L.ID2 SEQ,
L.LMODE,
L.REQUEST
FROM V$LOCK L,
V$SESSION S
WHERE L.SID = S.SID
AND L.TYPE = 'TX'
AND S.USERNAME = USER
;
USERNAME SID ID1 RBS SLOT SEQ LMODE REQUEST
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
USER 9604 1363869742 20811 46 2 6 0
;
SELECT XIDUSN,
XIDSLOT,
XIDSQN,
TO_CHAR(XIDUSN, 'XXXX') || TO_CHAR(XIDSLOT, 'XXXX') || TO_CHAR(XIDSQN, 'XXXX') TO_HEX_XID,
XID
FROM V$TRANSACTION
;
XIDUSN XIDSLOT XIDSQN TO_HEX_XID XID
---------- ---------- ---------- ------------------------------ ----------------
20811 46 2 514B 2E 2 514B002E00000002
;
1-1-3) 데이터 락킹과 트랜잭션 관리 방안
- 데이터베이스 블록 맨 위에는, 해당 블록에 대한 트랜잭션 테이블을 저장하고 있으며, 어느 정도 오버헤드를 유발하는 공간이 존재함.
- 트랜잭션 테이블은 그 블록에서 데이터에 락을 걸고 있는 각각의 실제 트랜잭션에 대한 엔트리를 담고 있으며, INITRANS, MAXTRANS 속성으로 제어가 됨.
| 구분 | 설명 |
|---|---|
| INITRANS | 블록에 동시에 엑세스 가능한 트렉젝션의 초기수를 나타내며, 기본값은 2이다(딕셔너리에서는 1로 관찰되어도 실제 값은 2임) |
| MAXTRANS | 동시에 엑세스 가능한 트렉젝션의 최대값으로 INITRANS의 상대적 개념이며, 기본값은 255이다. |
- 각 블록은 기본값으로 2개의 트랜잭션 슬롯으로 시작하며, 하나의 블록을 동시에 액세스 할 수 있는 트랜잭션 수는 MAXTRANS 값과 블록의 여유공간에 의해 제어됨.
- 아래에는 하나의 블록에 데이터를 모두 넣고, 최대 몇개의 트랜잭션 슬롯을 할당할 수 있는지 볼 수 있는 테스트 Case임.
-- 1. 샘플 데이터 생성
DROP TABLE T;
CREATE TABLE T (X INT PRIMARY KEY,
Y VARCHAR2(4000))
;
INSERT INTO T
SELECT ROWNUM,
RPAD('*', 148, '*')
FROM DUAL
CONNECT BY LEVEL <= 46
;
-- 2. 데이터 블록 확인
/***************************************************************************************************************************/
-- 테이블 T의 동일한 블록에 46개의 로우를 담았고, 이를 데이터로 확인함.
-- (만약, 148개의 문자열이 아니라 조금 더 클 경우 2개의 블록에 나누어 저장해야 함.
/***************************************************************************************************************************/
SELECT LENGTH(Y),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLK,
COUNT(*),
MIN(X),
MAX(X)
FROM T
GROUP BY LENGTH(Y),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
;
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
148 1377886 46 1 46
;
-- 3. DO_UPDATE 프로시저 생성
/***************************************************************************************************************************/
-- DO_UPDATE 프로시저는 재귀호출을 통해 LOCK을 걸 때 트랜잭션 슬롯을 몇개까지 사용할 수 있는지 확인하는 내용.
-- 하나의 블록에 데이터가 꽉 차있을 경우와, 빈 볼륵을 남겨 두었을때 각각을 살펴봄.
/***************************************************************************************************************************/
CREATE OR REPLACE PROCEDURE DO_UPDATE (p_n IN NUMBER)
AS
pragma autonomous_transaction;
l_rec t%rowtype;
resource_busy exception;
pragma exception_init (resource_busy, -54);
BEGIN
SELECT *
INTO L_REC
FROM T
WHERE X = P_N
FOR UPDATE NOWAIT
;
DO_UPDATE(p_n + 1);
COMMIT;
EXCEPTION
WHEN RESOURCE_BUSY THEN
DBMS_OUTPUT.PUT_LINE('locked out trying to select now ' || p_n );
COMMIT;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('we finished - no problem' );
COMMIT;
END;
/
Procedure created.
;
-- 4. 꽉 찬 블록 테스트
/***************************************************************************************************************************/
-- 38 로우에서 트랜잭션 슬롯이 소진되어 Lock을 수행할 수 없음.
/***************************************************************************************************************************/
EXEC DO_UPDATE(1)
;
locked out trying to select now 38
PL/SQL procedure successfully completed.
;
-- 5. 빈 블록 확보한 후 테스트
/***************************************************************************************************************************/
-- 블록에 46바이트 공간을 확보한 후 수행할 경우, 9 로우에 추가로 트랜잭션 슬롯 확보가 가능함.
/***************************************************************************************************************************/
TRUNCATE TABLE T
;
INSERT INTO T
SELECT ROWNUM,
RPAD('*', 147, '*')
FROM DUAL
CONNECT BY LEVEL <= 46
;
SELECT LENGTH(Y),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLK,
COUNT(*),
MIN(X),
MAX(X)
FROM T
GROUP BY LENGTH(Y),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
;
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
147 1377886 46 1 46
;
EXEC DO_UPDATE(1)
;
we finished - no problem
PL/SQL procedure successfully completed.
;
- INITRANS, MAXTRANS 값을 설정해야 할 경우는 아래와 같다.
- 자주 변경되고, 블록당 로우 수가 많은 테이블 또는 인덱스일 경우.
- 동시 트랜잭션 수를 미리 예상하고, 블록에 충분한 공간을 확보하기 위해서 PCTFREE, INITRANS를 증가시킬 수 있음.
- 처음부터 블록이 거의 가득 찰 것으로 예상된다면, 반드시 INITRANS와 PCTFREE를 증가시켜 주어야 힘.
1-2) TM(DML Enqueue) 락
- TM락은 테이블의 내용을 변경하는 동안 테이블 구조를 변경하지 않도록 보장하는 데 사용됨.
- 만약 Session_1에서 테이블을 수정할 동안에, Session_2에서 테이블 변경을 하려고 할 경우 'ORA-00054' 에러를 만남.
- 오라클은 11g 릴리즈 2 이후에서는 DDL 대기 시간을 조절하기 위해서 DDL_LOCK_TIMEOUT을 설정할 수 있으며, 일반적으로 ALTER SESSION 명령어를 통해 이루어짐.
- 예를들어, DROP 테이블 명령을 내리기 전에 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 60'을 먼저 수행할 수 있음.
서버로 보낸 DROP TABLE 명령어는 오류를 리턴하기 전에 60초 동안 대기함(물론 그 전에 성공할 수 있음)
- 'ORA-00054' 테스트 Case
-- 1. (Session_1) DEPT 테이블 UPDATE
UPDATE DEPT
SET DEPTNO = DEPTNO + 10
;
-- 2. (Session_2) DEPT 테이블 DROP
DROP TABLE DEPT
;
DROP TABLE DEPT
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
;
- TM 락 테스트 Case
-- 1. (Session_1) 샘플 테이블 생성
CREATE TABLE T1(X INT);
CREATE TABLE T2(X INT);
-- 2. (Session_2) 샘플 데이터 생성
INSERT INTO T1 VALUES(1);
INSERT INTO T2 VALUES(1);
-- 3. (Session_2) Lock 모니터링
/***************************************************************************************************************************/
-- TM Lock일 경우, L.ID1의 값은 OBJECT_ID와 동일하므로, Lock이 걸린 OBJECT_NAME을 찾을 수 있음.
/***************************************************************************************************************************/
SELECT (SELECT USERNAME
FROM V$SESSION
WHERE SID = L.SID) USERNAME,
L.SID,
L.ID1,
L.ID2,
L.LMODE,
L.REQUEST,
L.BLOCK,
L.TYPE
FROM V$LOCK L
WHERE L.SID = (SELECT SID
FROM V$MYSTAT
WHERE ROWNUM = 1)
;
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TYPE
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----
APPS 9595 104970298 0 3 0 0 TM -- TM 락일 경우 ID1과 OBJECT_ID는 동일
APPS 9595 104970299 0 3 0 0 TM -- TM 락일 경우 ID1과 OBJECT_ID는 동일
APPS 9595 1367080983 3 6 0 0 TX
;
SELECT OBJECT_NAME,
OBJECT_ID
FROM USER_OBJECTS
WHERE OBJECT_ID IN (104970298, 104970299)
;
OBJECT_NAME OBJECT_ID
------------- ----------
T1 104970298
T2 104970299
;
2. DDL 락
- DDL 락은 DDL 작업 동안 다른 세션에 의한 변경으로부터 객체를 보호하기 위해 자동으로 객체에 걸림.
- 예를들어, 만약에 DDL 작업 'ALTER TABLE T'를 수행하면, 다른 세션이 테이블 T에 DDL 락과 TM 락을 획득하지 못하도록 하면서 테이블 T에 일반적으로 배타 DDL 락을 건다.
- 오라클 11g는 규칙으로 사용되었던 것을 변경했다.
- 과거에 'ALTER TABLE T'는 테이블에 배타 DDL 락을 걸었으나,
- DDL 락은 DDL 문을 수행하는 동안 유지되고, 그 후에는 즉시 해제되는데, DDL 앞 뒤에 묵시적인 커밋을 수행함으로써 효과적으로 이루어짐.
- DDL 문에 대한 pseudo 코드는 아래와 같다.
BEGIN
COMMIT;
DDL-STATEMENT
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
- DDL은 커밋하면서 시작하므로, 롤백해야 하는 상황이 되더라도 트랜잭션이 롤백하지 않도록 DDL 시작 전에 커밋함.
- DDL 락의 3가지 종류는 아래와 같다.
| 구분 | 설명 |
|---|---|
| 배타 DDL 락 | 다른 세션이 DDL 락 또는 TM 락을 획득하지 못하게 함. 이것은 DDL 작업을 하는 동안 테이블을 쿼리할 수 있지만, 어떤 방법으로든 테이블을 변경할 수 없다는 것을 의미함. |
| 공유 DDL 락 | 다른 세션에 의해 변경되는 것에 대비하여 참조하는 객체 구조를 보호하지만, 데이터에 대한 변경은 허용함. |
| Breakable Parse 락 | shared pool에 게시된 실행계획처럼 객체 간의 의존 관계를 등록함. 만약, 특정 객체에 대해 DDL을 수행하면, 오라클은 객체 간의 의존 관계를 등록한 객체 리스트를 살펴보고, 의존 관계를 무효화시킨다. 그러므로, breakable parse 락은 깨지기 쉬우며, DDL이 발생하는 것을 막지 않음. |
2-1) 배타 DDL 락
- 대부분 DDL은 배타 DDL 락을 얻으며, DDL을 수행하는 동안 변경 대상에서 제외됨.
- 이 시간 동안 SELECT를 이용해서 테이블 T를 쿼리할 수 있지만, 모든 다른 DDL 문을 포함한 대부분의 작업은 하지 못하게 됨.
ALTER TABLE T MOVE
;
Table altered.
;
2-2) 공유 DDL 락
- ONLINE 키워드는 인덱스를 만드는 방법을 변경함.
- 데이터 변경을 막는 배타 DDL 락을 획득하는 대신에 오라클은 테이블에 로우 레벨(모드 2)의 TM 락만 획득함.
이것은 다른 DDL이 수행하는 것을 효과적으로 막을 수 있지만, DML은 정상적으로 수행되도록 허용함. - 오라클은 DDL이 수행하는 동안 테이블의 변경 기록을 유지하고 CREATE 작업을 마칠 때, 테이블의 변경 기록을 새로운 인덱스에 적용함으로써, 데이터의 가용성을 매우 증가시킴.
-- 1. (Session_1) 샘플 데이터 생성
DROP TABLE T;
CREATE TABLE T AS
SELECT *
FROM ALL_OBJECTS
;
Table created.
;
SELECT OBJECT_ID
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'T'
;
OBJECT_ID
----------
89221723
;
-- 2. (Session_1) ONLINE으로 인덱스 생성
CREATE INDEX T_N01 ON T(OWNER, OBJECT_TYPE, OBJECT_NAME) ONLINE
;
-- 3. (Session_2) Lock 모니터링
/***************************************************************************************************************************/
-- TM 락의 LMODE의 값은 로우 레벨인 2로 확인됨
-- DL(Direct load) 락은 인덱스 생성이 일어나는 동안, 대상 테이블에 direct path load를 막는 데 사용됨.
-- (즉, 테이블에 direct path load를 하면서, 동시에 인덱스를 생성할 수 없다는 것을 의미함)
-- 여기서는 10g 버전이라 나타나지 않았지만, 책에는 11g에서 추가된 OD 락이 설명되어 있다.
-- OD(Online DDL)락은 온라인 DDL을 허용하는데, 이전 버전에서는 완벽한 온라인 DDL이 아니었음.
-- (10g 이하 버전에서는 동시에 일어나는 작업을 막기 위해 CREATE 문의 시작과 끝에 락을 취함)
-- 11g 버전에서 OD 락은 완벽하게 온라인으로 작동하며, CREATE 문의 시작과 끝에 배타 락을 요구하지 않음.
/***************************************************************************************************************************/
SELECT (SELECT USERNAME
FROM V$SESSION
WHERE SID = L.SID) USERNAME,
L.SID,
L.ID1,
L.ID2,
L.LMODE,
L.REQUEST,
L.BLOCK,
L.TYPE
FROM V$LOCK L
WHERE L.ID1 = 89221723
;
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TYPE
--------- ---------- ---------- ---------- ---------- ---------- ---------- ----
APPS 19010 89221723 0 2 0 2 TM
APPS 19010 89221723 0 3 0 2 DL
APPS 19010 89221723 0 3 0 2 DL
;
2-3) Breakable Parse 락
- Breakable Parse 락은, 세션이 문장을 파싱할 때 그 문장에 의해 참조되는 모든 객체를 대상으로 parse락을 거는데, 만약 참조되는 객체가 어떤 식으로든 DROP 되거나 변경되면, 파싱되어 캐시된 문장을 shared pool에서 무효화(invalidated) 시키기 위해 락을 취함.
- DDL 락의 정보를 보는 데 필요한 뷰는 'DBA_DDL_LOCKS' 이며, V$로 시작하는 뷰는 없다.
COLUMN OWNER format a10
COLUMN NAME format a30
COLUMN TYPE format a30
COLUMN MODE_HELD format a9
COLUMN MODE_REQUESTED format a9
SELECT DDL.SESSION_ID SID,
DDL.OWNER,
DDL.NAME,
DDL.TYPE,
DDL.MODE_HELD HELD,
DDL.MODE_REQUESTED REQUEST
FROM DBA_DDL_LOCKS DDL
WHERE SESSION_ID = (SELECT SID
FROM V$MYSTAT
WHERE ROWNUM = 1)
;
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ------------------------------ ------------------------------ ------------------ ------------
19010 SYS XML_SCHEMA_NAME_PRESENT Table/Procedure/Type Null None
19010 XDB XDh1jUheYAR5PgNAgAILJCxg== 25 Share None
19010 SYS DBMS_SQL Table/Procedure/Type Null None
19010 XDB DBMS_XDBZ0 Body Null None
19010 SYS ORA_TQ_BASE$ Table/Procedure/Type Null None
19010 SYS XML_SCHEMA_NAME_PRESENT Body Null None
19010 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
19010 SYS DBMS_STANDARD Table/Procedure/Type Null None
19010 XDB XDF5l4V627ZiTgQ4u5GGZmJA== 25 Share None
19010 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
19010 SYS SYSEVENT Table/Procedure/Type Null None
19010 SYS PLITBLM Table/Procedure/Type Null None
19010 SYS DBMS_OUTPUT Body Null None
19010 XDB XDF5l4V6s9ZiTgQ4u5GGZmJA== 25 Share None
19010 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
19010 SYS LOGIN_USER Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Body Null None
19010 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
19010 SYS DBMS_SYS_SQL Body Null None
19010 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
19010 SYS DBMS_SQL Body Null None
19010 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
19010 XDB XDbD/PLZ01TcHgNAgAIIegtw== 25 Share None
19010 SYS DBMS_OUTPUT Table/Procedure/Type Null None
;
- 살아 있는 breakable parse 락을 보기 위한 테스트 Case
-- 1. 테스트 프로시저 생성 및 수행
CREATE OR REPLACE PROCEDURE P
AS
BEGIN
NULL;
END;
/
EXEC P
;
-- 2. 프로시저 P에 대한 내용을 DBA_DDL_LOCKS 뷰에서 확인
SELECT DDL.SESSION_ID SID,
DDL.OWNER,
DDL.NAME,
DDL.TYPE,
DDL.MODE_HELD HELD,
DDL.MODE_REQUESTED REQUEST
FROM DBA_DDL_LOCKS DDL
WHERE SESSION_ID = (SELECT SID
FROM V$MYSTAT
WHERE ROWNUM = 1)
;
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ------------------------------ ------------------------------ ------------------ ----------------
19010 SYS XML_SCHEMA_NAME_PRESENT Table/Procedure/Type Null None
19010 XDB XDh1jUheYAR5PgNAgAILJCxg== 25 Share None
19010 SYS DBMS_SQL Table/Procedure/Type Null None
19010 XDB DBMS_XDBZ0 Body Null None
19010 SYS ORA_TQ_BASE$ Table/Procedure/Type Null None
19010 SYS XML_SCHEMA_NAME_PRESENT Body Null None -- 대상
19010 USER P Table/Procedure/Type Null None
19010 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
19010 XDB XDF5l4V627ZiTgQ4u5GGZmJA== 25 Share None
19010 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
19010 SYS SYSEVENT Table/Procedure/Type Null None
19010 SYS PLITBLM Table/Procedure/Type Null None
19010 SYS DBMS_OUTPUT Body Null None
19010 SYS AW_DROP_PROC Table/Procedure/Type Null None
19010 XDB XDF5l4V6s9ZiTgQ4u5GGZmJA== 25 Share None
19010 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
19010 SYS LOGIN_USER Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Body Null None
19010 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
19010 SYS DBMS_SYS_SQL Body Null None
19010 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
19010 SYS DBMS_SQL Body Null None
19010 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
19010 XDB XDbD/PLZ01TcHgNAgAIIegtw== 25 Share None
19010 SYS IDGEN1$ Table/Procedure/Type Null None
19010 SYS DBMS_OUTPUT Table/Procedure/Type Null None
;
-- 3. 프로시저 재 컴파을 후 DBA_DDL_LOCKS 뷰 확인
/***************************************************************************************************************************/
-- 프로시저 컴파일을 통해 parse 락이 해제된 것 확인 가능.
-- 이와 같이, DBA_DDL_LOCKS 뷰는 일부 코드가 테스트 시스템 도는 개발 시스템에서 컴파일되지 않는다는 것을 발견할 때 유용함.
/***************************************************************************************************************************/
ALTER PROCEDURE P COMPILE;
SELECT DDL.SESSION_ID SID,
DDL.OWNER,
DDL.NAME,
DDL.TYPE,
DDL.MODE_HELD HELD,
DDL.MODE_REQUESTED REQUEST
FROM DBA_DDL_LOCKS DDL
WHERE SESSION_ID = (SELECT SID
FROM V$MYSTAT
WHERE ROWNUM = 1)
;
SID OWNER NAME TYPE HELD REQUEST
---------- ---------- ------------------------------ ------------------------------ ------------------ -------------
19010 SYS XML_SCHEMA_NAME_PRESENT Table/Procedure/Type Null None
19010 XDB XDh1jUheYAR5PgNAgAILJCxg== 25 Share None
19010 SYS DBMS_SQL Table/Procedure/Type Null None
19010 XDB DBMS_XDBZ0 Body Null None
19010 SYS ORA_TQ_BASE$ Table/Procedure/Type Null None
19010 SYS XML_SCHEMA_NAME_PRESENT Body Null None
19010 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
19010 XDB XDF5l4V627ZiTgQ4u5GGZmJA== 25 Share None
19010 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
19010 SYS SYSEVENT Table/Procedure/Type Null None
19010 SYS PLITBLM Table/Procedure/Type Null None
19010 SYS DBMS_OUTPUT Body Null None
19010 SYS AW_DROP_PROC Table/Procedure/Type Null None
19010 XDB XDF5l4V6s9ZiTgQ4u5GGZmJA== 25 Share None
19010 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
19010 SYS LOGIN_USER Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
19010 SYS DBMS_APPLICATION_INFO Body Null None
19010 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
19010 SYS DBMS_SYS_SQL Body Null None
19010 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
19010 SYS DBMS_SQL Body Null None
19010 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
19010 XDB XDbD/PLZ01TcHgNAgAIIegtw== 25 Share None
19010 SYS IDGEN1$ Table/Procedure/Type Null None
19010 SYS DBMS_OUTPUT Table/Procedure/Type Null None
;
- EBR(Edition-based redefinition)
- 오라클 11g 릴리즈 2 이상부터 지원하는 내용.
- EBR에서는 현재 코드를 실행하는 사용자를 방해하지 않고, 데이터베이스에서 코드에 EXECUTE 권한을 주거나, 재컴파일할 수 있음.
- 또한, 한 스키마에서 동일한 저장 프로시저의 다수 버전을 가질 수 있도록 허용함.
- 이것은, 새로운 에디션에서는 다른 사용자가 사용 중인 프로시저의 현재 버전과 다투지 않고, 새로운 에디션에서 그 프로시저의 복제본으로 작업이 가능하도록 함.
3. 래치
- 엑셈 아스타 만화 참고 자료
- 래치(latch)는 공유된 데이터 구조, 객체 그리고 파일에 대한 다수 사용자 액세스를 조정하는 데 사용되는 경량의 직렬화 장치.
- 래치는 아주 짧은 시간(예를 들어, 내부 메모리의 데이터 구조를 변경하는 데 걸리는 시간)동안 유지되도록 설계된 락으로, shared pool에서 데이터베이스 블록 버퍼 캐시 도는 라이브러리 캐시처럼 특정 메모리 구조를 보호하는 데 사용됨.
- 래치는 2가지 모드로 작동함.
- willing-to-wait : 일반적으로 기꺼이 기다릴 수 있는 모드로, 래치를 당장 사용하지 못하면 짧은 시간 동안 대기하다가 나중에 작업을 다시 시도함.
- immediate : 개념적으로 'SELECT FOR UPDATE NOWAIT'와 비슷하며, 래치가 가용될 때까지 앉아서 기다리느니, 차라리 다른 무언가를 하러 간다는 것을 의미함.
- 래치는 큐 방식이 아닌 랜덤 방식이기 때문에, 대기자 열은 존재하지 않음.
3-1) 래치 Spinning
3-1-1) Spin에 의한 Latch 획득
1. Process A래치 획득 시도 > 해당 래치를 점유한 Process가 존재하지 않으므로 래치 획득
2. Process B 래치 획득 시도 > Process A가 래치를 점유하고 있으므로 래치 획득 실패
3. Process B는 _SPIN_COUNT의 수 만큼 스핀을 수행(스핀을 수행한 후 다시 래치 획득 시도. 기본값 : 2000) > spin으로 인한 CPU 사용률 증가
4. 만일 여러 번의 스핀을 반복한 후에도 래치를 획득하지 못할 경우, Process B는 Sleep 상태로 전환 > SPIN 행위 자체가 CPU를 사용하기 때문에, 래치 경합에 의해서 많은 수의 프로세스가 동시에 스핀을 수행할 경우 높은 cpu 사용율을 보일 수 있음.
- 래치를 얻기 위한 psuedo 코드는 아래와 같다.
LOOP
FOR i IN 1..2000 LOOP
try to get latch
if got latch, retuen
if i = 1 then misses = misses + 1
END LOOP;
INCREMENT WAIT COUNT
sleep
ADD WAIT TIME
END LOOP;
3-1-2) Posting에 의한 Latch 획득
1. Process A 래치 획득 시도 > 해당 래치를 점유한 프로세스가 존재하지 않으므로 래치 획득
2. Process B 래치 획득 시도 > Process A가 래치를 점유하고 있으므로 래치 획득 실패
3. Process B를 대기목록(wait list)에 등록
4. Process A가 자원을 해제할 때 대기목록으로 알려 줌
5. 대기목록에서 대기하던 프로세스 중 하나가 래치를 획득
(순서보장을 하지 않음. 대기 목록에서 깨어난 프로세스가, 래치를 획득하려고 하는 그 찰나의 순간에, 신규로 래치를 획득하고자 하는 다른 프로세스가 래치를 획득 할 수 있기 때문)
3-2) immediate 모드
- 현재 다른 래치들을 보유하고 있는 프로세스가, 현재 보유한 래치중 가장 최근에 획득한 래치보다 더 낮거나 같은 레벨의 래치를 획득하고자 할 때, No-Wait모드로 획득
- 만일 no-wait 모드의 래치획득에 실패하면, 획득하고자 하는 래치보다 높은 레벨의 래치는 모두 해제하고 올바른 순서로 다시 시도
3-3) 공유 자원을 래칭하는 비용 측정하기
4. 뮤텍스
- 뮤텍스(mutex)는 래치와 매우 흡사한 직렬화 장치이며, 이름 자체의 뜻은 상호 배타(mutual exclusion)를 의미함.
- 오라클 10g 릴리즈 1부터 도입되었으며, 뮤텍스는 래치보다 1/5 정도의 코드와, 1/7 정도의 메모리 사용량만을 필요로 함.
- 뮤텍스가 래치보다 가벼운 직렬화 장치이지만, 래치를 모두 대체할 수는 없으며, 이는 래치가 enqueue를 대체하지 못하는 것과 같은 내용이다.
- 지난_뮤텍스_테스트_내용_링크
5. 수동 락킹과 사용자 정의 락
5-1) 수동 락킹
- 'SELECT FOR UPDATE' 문이 수동으로 데이터에 락을 거는 가장 흔한 방법.
- 또한, LOCK TABLE 문을 이용해서 수동으로 데이터에 락을 걸 수 있으나, 이는 락을 거는 대상의 범위가 넓기 때문에 잘 사용하지는 않음.
이 방식을 굳이 사용해야 한다면, 'LOCK TABLE IN EXCLUSIVE MODE' 문을 사용하여, 다른 트랜잭션에 의해 방해받지 않고 수정 작업을 마칠 수 있음.
5-2) 사용자 정의 락 생성하기
- 만약, 오라클 외부의 서버 파일 시스템 상에 존재하는 파일에 쓰기를, 한 사람이 한 번씩 사용토록 통제를 하고 싶을경우 DBMS_LOCK 패키지를 통해 제어가 가능함.
(DBMS_LOCK 패키지를 이용해, 파일을 열고, 쓰고, 닫기 전에 파일명을 따서 명명된 락을 배타 모드로 요구하고, 파일을 닫은 후에 수동으로 락을 해제할 수 있음)
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2012년 11월 03일
- 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컨설팅에서 번역한 'Expert Oracle Database'를 참고하였습니다.*