목차

1. 락킹 이슈

• 1) Lost Update
• 2) 비관적 락킹
• 3) 낙관적 락킹
• 4) 낙관적 락킹인가, 비관적 락킹인가?
• 5) 블로킹
• 6) 데드락
• 7) 락 상승

1. 락킹 이슈

1) Lost Update

• lost update는 고질적인 데이터베이스 문제이며, 실제로 모든 다중 사용자 환경에서 발생함.
• lost update가 발생되는 case는 아래와 같다.

• 테스트 CASE

-- 0. 테스트 테이블 생성
DROP TABLE XSOFT_T_1;

CREATE TABLE XSOFT_T_1 (CNT NUMBER, VAL VARCHAR2(1000));

INSERT INTO XSOFT_T_1
SELECT ROWNUM CNT,
       CHR(CNT) VAL
FROM   (SELECT LEVEL CNT
        FROM   DUAL
        CONNECT BY LEVEL <= 100) T
WHERE  CNT BETWEEN 65 AND 74
ORDER BY 1
;

COMMIT;

SELECT *
FROM   XSOFT_T_1
;

       CNT VAL       
---------- ------    
         1 A         
         2 B         
         3 C         
         4 D         
         5 E         
         6 F         
         7 G         
         8 H         
         9 I         
        10 J         
;

-- 1. Session_1 에서 10번째 데이터 수정하기 위해 현재 데이터 확인
var v_val VARCHAR2(1000)

column val format a20

BEGIN
    SELECT VAL
    INTO   :v_val
    FROM   XSOFT_T_1
    WHERE  CNT = 10;
END;
/

SELECT :v_val VAL
FROM   DUAL
;

VAL                 
--------------------
J                        
;

-- 2. Session_2 에서도 10번째 데이터 수정하기 위해 현재 데이터 확인
var p_val VARCHAR2(1000)

column val format a20

BEGIN
    SELECT VAL
    INTO   :v_val
    FROM   XSOFT_T_1
    WHERE  CNT = 10;
END;
/

SELECT :v_val VAL
FROM   DUAL
;

VAL                 
--------------------
J                        
;

-- 3. Session_1 에서 10번째 데이터 수정
UPDATE XSOFT_T_1
SET    VAL = 'SESSION_1_J'
WHERE  CNT = 10
;

COMMIT;

SELECT VAL
FROM   XSOFT_T_1
WHERE  CNT = 10
;

VAL                   
--------------------  
SESSION_1_J           
;

-- 4. Session_2 에서도 10번째 데이터 수정
UPDATE XSOFT_T_1
SET    VAL = 'SESSION_2_J'
WHERE  CNT = 10
;

COMMIT;

SELECT VAL
FROM   XSOFT_T_1
WHERE  CNT = 10
;

VAL                   
--------------------  
SESSION_2_J           
;

• 이런 Case에 대해 오라클의 Forms나 APEX(Application Express)와 같은 툴은, FOR UPDATE를 자동 지원하므로 이와 같은 이슈는 발생하지 않음.
• 이처럼, lost update를 방지하지 위해 어떤 방법이 있는지 아래에서 살펴봄.

2) 비관적 락킹

• 비관적 락킹이란 사용자가 데이터를 변경할 의사가 있을 경우, 해당 로우에 락을 미리 걸어놓고 데이터를 수정하는 방식이다.
• 데이터를 수정하는 동안 다른 USER가 해당 데이터를 수정하려고 할 경우, 계속 락을 대기해야 하므로, 'FOR UPDATE NOWAIT'를 사용하여 현재 락이 걸려있음을 인지하도록 알려줌.
• 비관적 락킹은 커넥션이 유지되는 환경에서만 유용하며, C/S 환경에서 널리 사용되었으나, 어플리케이션 서버의 등장으로 덜 사용하게 됨.

-- 1. 테스트 테이블 생성
DROP TABLE EMP;

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);

COMMIT;

-- 2. (Session_1) 수정할 데이터 확인
var v_empno NUMBER
var v_ename VARCHAR2(1000)
var v_sal   NUMBER

COLUMN EMPNO format NUMBER
COLUMN ENAME format a20
COLUMN SAL   format NUMBER


BEGIN
    SELECT EMPNO,
           ENAME,
           SAL
    INTO   :v_empno,
           :v_ename,
           :v_sal
    FROM   EMP
    WHERE  EMPNO = 7934;
END;
/

SELECT :v_empno EMPNO,
       :v_ename ENAME,
       :v_sal SAL
FROM   DUAL
;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7934 MILLER                     1300
;

-- 3. (Session_1) 수정 데이터 변수에 넣음
EXEC :v_sal := 2000

-- 4. (Session_1) LOCK 설정
SELECT EMPNO,
       ENAME,
       SAL
FROM   EMP
WHERE  EMPNO = 7934
FOR UPDATE NOWAIT
;

-- 5. (Session_2) 같은 로우 데이터 UPDATE 시도
SELECT EMPNO,
       ENAME,
       SAL
FROM   EMP
WHERE  EMPNO = 7934
FOR UPDATE NOWAIT
;

ERROR at line 4:
ORA-00054: resource busy and acquire with NOWAIT specified
;

-- 5. (Session_1) UPDATE 수행
UPDATE EMP
SET    SAL = :v_sal
WHERE  EMPNO = 7934
;

COMMIT;

SELECT EMPNO,
       ENAME,
       SAL
FROM   EMP
WHERE  EMPNO = 7934
;

     EMPNO ENAME                       SAL   
---------- -------------------- ----------   
      7934 MILLER                     2000   
;

3) 낙관적 락킹

• 낙관적 락킹(optimistic locking)은 수정하기 바로 전까지 모든 락킹을 미루는 방법으로, 락을 힉득하지 않고 화면 정보를 변경하는 것.
• 이 방식의 단점은, 사용자가 로우를 수정했는데, 수정하는 동안 해당 데이터가 변경이 되었다면, 사용자는 다시 수정작업을 해야 함.
• 또한, 락킹을 사용하지 않기 때문에 대부분 낙관적 락킹을 사용할 경우 이슈가 안되나, 비관적 락킹으로 많이 구현되어 있을 경우, 이로 인해 블로킹 될 확률이 높으므로 비관적 락킹으로 전환해야 할지 고민해야 함.

3-1) 버전 컬럼을 이용한 낙관적 락킹

• 이것은 lost update로부터 보호하고 싶은 테이블에 last_update_date 같은 컬럼을 추가하여, timestamp값으로 변경유무를 체크하는 방식.
• 예를들어, UPDATE 하기 전 대상 데이터의 last_update_date 컬럼의 데이터를 읽은 후, 수정 작업을 마치고 최종 UPDATE 시 방금 전 읽었던 데이터와 동일하면 수정되지 않은 것으로 보고 UPDATE를 수행함.

-- 1. EMP 테이블에 LAST_UPDATE_DATE 컬럼 추가
ALTER TABLE EMP ADD (LAST_UPDATE_DATE TIMESTAMP DEFAULT SYSDATE NOT NULL );

SELECT EMPNO,
       SAL,
       LAST_UPDATE_DATE
FROM   EMP
;

     EMPNO        SAL LAST_UPDATE_DATE              
---------- ---------- ------------------------------
      7369        800 25-OCT-12 10.28.22.000000 PM  
      7499       1600 25-OCT-12 10.28.22.000000 PM  
      7521       1250 25-OCT-12 10.28.22.000000 PM  
      7566       2975 25-OCT-12 10.28.22.000000 PM  
      7654       1250 25-OCT-12 10.28.22.000000 PM  
      7698       2850 25-OCT-12 10.28.22.000000 PM  
      7782       2450 25-OCT-12 10.28.22.000000 PM  
      7788       3000 25-OCT-12 10.28.22.000000 PM  
      7839       5000 25-OCT-12 10.28.22.000000 PM  
      7844       1500 25-OCT-12 10.28.22.000000 PM  
      7876       1100 25-OCT-12 10.28.22.000000 PM  
      7900        950 25-OCT-12 10.28.22.000000 PM  
      7902       3000 25-OCT-12 10.28.22.000000 PM  
      7934       2000 25-OCT-12 10.28.22.000000 PM  
;

-- 2. (Session_1) 수정할 데이터 확인 후 수정작업 진행
var v_empno            NUMBER
var v_sal              NUMBER
var v_last_update_date VARCHAR2(1000)

COLUMN empno            format NUMBER
COLUMN sal              format NUMBER
COLUMN last_update_date format a30


BEGIN
    SELECT EMPNO,
           SAL,
           TO_CHAR(LAST_UPDATE_DATE, 'DD-MON-YY HH.MI.SSXFF AM') LAST_UPDATE_DATE
    INTO   :v_empno,
           :v_sal,
           :v_last_update_date
    FROM   EMP
    WHERE  EMPNO = 7934;
END;
/

SELECT :v_empno EMPNO,
       :v_sal SAL,
       :v_last_update_date LAST_UPDATE_DATE
FROM   DUAL
;

     EMPNO        SAL LAST_UPDATE_DATE               
---------- ---------- ------------------------------ 
      7934       2000 25-OCT-12 10.28.22.000000 PM   
;

EXEC :v_sal := 4000

-- 3. (Session_2) Session_1에서 수정할 같은 데이터를 수정 후 먼저 완료
var v_empno            NUMBER
var v_sal              NUMBER
var v_last_update_date VARCHAR2(1000)

COLUMN empno            format NUMBER
COLUMN sal              format NUMBER
COLUMN last_update_date format a30

BEGIN
    SELECT EMPNO,
           SAL,
           TO_CHAR(LAST_UPDATE_DATE, 'DD-MON-YY HH.MI.SSXFF AM') LAST_UPDATE_DATE
    INTO   :v_empno,
           :v_sal,
           :v_last_update_date
    FROM   EMP
    WHERE  EMPNO = 7934;
END;
/

SELECT :v_empno EMPNO,
       :v_sal SAL,
       :v_last_update_date LAST_UPDATE_DATE
FROM   DUAL
;

     EMPNO        SAL LAST_UPDATE_DATE               
---------- ---------- ------------------------------ 
      7934       2000 25-OCT-12 10.28.22.000000 PM   
;

EXEC :v_sal := 3000

UPDATE EMP
SET    SAL = :v_sal,
       LAST_UPDATE_DATE = SYSTIMESTAMP
WHERE  EMPNO            = :v_empno
AND    LAST_UPDATE_DATE = :v_last_update_date
;

COMMIT;

SELECT EMPNO,
       SAL,
       TO_CHAR(LAST_UPDATE_DATE, 'DD-MON-YY HH.MI.SSXFF AM') LAST_UPDATE_DATE
FROM   EMP
WHERE  EMPNO = 7934
;

     EMPNO        SAL LAST_UPDATE_DATE             
---------- ---------- -----------------------------
      7934       3000 25-OCT-12 10.45.52.435180 PM 
;

-- 4. (Session_1) 수정작업 완료 후 COMMIT 하려고 하나 실패함
UPDATE EMP
SET    SAL = :v_sal,
       LAST_UPDATE_DATE = SYSTIMESTAMP
WHERE  EMPNO            = :v_empno
AND    LAST_UPDATE_DATE = :v_last_update_date
;

0 rows updated.
;

3-2) 체크섬을 이용한 낙관적 락킹

• 이것은, 버전 컬럼 방법과 유사하지만, '가상'의 버전 컬럼을 계산하기 위해 자체적으로 기초 데이터를 사용함.
• 이에 대해 오라클 11g 릴리즈 2 PL/QL Supplied Packages Guide에는 다음과 같이 설명되어 있다.

• 해시 또는 체섬을 계산하는 방법 중 4가지를 소개하면 아래와 같다.

• 이 중 ORA_HASH 방식에 대해 테스트 한 결과이다.

-- 0. 테스트 데이터 생성
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;
/

-- 1. ORA_HASH 사용하여 데이터 확인
var deptno NUMBER
var dname  VARCHAR2(1000)
var loc    VARCHAR2(1000)
var hash   NUMBER

COLUMN DNAME  format a20
COLUMN LOC    format a20


BEGIN
    SELECT DEPTNO,
           DNAME,
           LOC,
           ORA_HASH(DNAME || '/' || LOC) HASH
    INTO   :DEPTNO,
           :DNAME,
           :LOC,
           :HASH
    FROM   DEPT
    WHERE  DEPTNO = 10;
END;
/

SELECT :DEPTNO DEPTNO,
       :DNAME DNAME,
       :LOC LOC,
       :HASH HASH
FROM   DUAL
;

    DEPTNO DNAME                LOC                        HASH 
---------- -------------------- -------------------- ---------- 
        10 ACCOUNTING           NEW YORK              401273349 
;

-- 2. ORA_HASH  사용하여 UPDATE #1(UPDATE 가능)
EXEC :dname := lower(:dname);

UPDATE DEPT
SET    DNAME = :DNAME
WHERE  DEPTNO = :DEPTNO
AND    ORA_HASH(DNAME || '/' || LOC) = :HASH
;

1 row updated.

SELECT D.*,
       ORA_HASH(DNAME || '/' || LOC) HASH
FROM   DEPT D
WHERE  DEPTNO = :DEPTNO
;

    DEPTNO DNAME                LOC                        HASH 
---------- -------------------- -------------------- ---------- 
        10 accounting           NEW YORK             2818855829 
;


-- 3. ORA_HASH  사용하여 UPDATE #2(UPDATE 불가)
UPDATE DEPT
SET    DNAME = :DNAME
WHERE  DEPTNO = :DEPTNO
AND    ORA_HASH(DNAME || '/' || LOC) = :HASH
;

0 rows updated.

-- 4. 11g 버전
ALTER TABLE DEPT ADD HASH AS ORA_HASH(DNAME || '/' || LOC);

SELECT *
FROM   DEPT D
WHERE  DEPTNO = :DEPTNO
;

    DEPTNO DNAME                LOC                        HASH 
---------- -------------------- -------------------- ---------- 
        10 accounting           NEW YORK             2818855829 
;

4) 낙관적 락킹인가, 비관적 락킹인가?

• 필자인 토마스 카이트의 의견

• 비관적 vs 낙관적
  • 비관적 락킹보다 낙관적 락킹을 더 선호함. 그 이유는 트랜잭션이 살아 있는 동안 한 개의 커넥션을 독점하는 것은 치러야 할 대가가 너무 크기 때문.
  • 비관적 락킹을 사용하고 있는 시스템에 낙관적 락킹을 사용해야 한다면, ORA_HASH 방식을 더 선호함. 그 이유는 경량적이고 비 간섭적이기 때문.

• 버전 컬럼 낙관적 락킹 vs 체크섬 낙관적 락킹
  • 체크섬 보다는 버전 컬럼을 더 선호함.
  • 그 이유는, CPU 측면에서 보면 해시보다 경제적이고, LONG LONG ROW, CLOB, BLOB 처럼 규모가 큰 컬럼 처리 시, 잠재적으로 일어날지 모를 문제 발생 안함.

5) 블로킹

• 블로킹(blocking)은 한 세션이 다른 세션이 요구하는 자원에 대한 락을 보유하고 있을 떄 발생.
• 락을 요구한 세션은 블로킹이 되며, 락을 보유한 세션이 해당 자원을 포기할 때까지 다른 세션은 멈추게 됨.
• DB에서 흔하게 블로킹 되는 DML 문은 INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE 5개이며, 아래에 그 내용을 간략히 기술함.

5-1) Insert 블로킹

• Insert가 블로킹되는 경우는 몇 가지가 존재하며, 가장 흔한 시나리오는 테이블에 기본키 또는 유일성 제약조건이 걸려 있고 두 세션이 동일한 값을 가진 로우를 Insert하는 경우임.
• 아래에서는 기본키에 Session_1에서 Insert 후 Commit 하기 전에, Session_2에서도 동일한 값으로 Insert시 유일성 제약조건에 위배되도록 Trigger를 설정하는 방식을 설명함.

-- 1. (Session_1) 샘플 테이블 및 트리거 생성
CREATE TABLE DEMO (X INT PRIMARY KEY);

CREATE OR REPLACE TRIGGER DEMO_BIFER
BEFORE INSERT ON DEMO
FOR EACH ROW
DECLARE
    l_lock_id     NUMBER;
    resource_busy EXCEPTION;
    PRAGMA        EXCEPTION_INIT (resource_busy, -54);
BEGIN
    l_lock_id := dbms_utility.get_hash_value(to_char(:new.x), 0, 1024);

    IF(DBMS_LOCK.REQUEST(ID                => l_lock_id,
                         LOCKMODE          => DBMS_LOCK.X_MODE,
                         TIMEOUT           => 0,
                         RELEASE_ON_COMMIT => TRUE) NOT IN (0, 4)) THEN
        RAISE resource_busy;
    END IF;
END;
/

Trigger created.

-- 2. (Session_1) 데이터 생성
INSERT INTO demo(x) VALUES(1);

-- 3. (Session_2) AUTONOMOUS_TRANSACTION 사용
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO demo(x) VALUES(1);
    COMMIT;
END;
/

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "APPS.DEMO_BIFER", line 12
ORA-04088: error during execution of trigger 'USER.DEMO_BIFER'
ORA-06512: at line 4 

• 위의 방식을 설명하면, Insert시 'dbms_utility.get_hash_value' 펑션을 통해 1,024개의 서로 다른 lock_ID 중 하나로 기본키를 해시하는 해시테이블을 생성함.
• 이후 lock_ID에 근거해 DBMS_LOCK.REQUEST를 사용하여, 타임아웃을 0으로 주고, 배타적으로 락이 걸리도록 lock_ID를 요구함.
• 만약, 처음 Insert 이후 추가적인 lock_ID를 요구하게 되면 타임아웃이 되거나 실패하여, 'ORA-00054: resource busy' 오류를 발생시킴.
• 이 메세지를 통해 Exception을 할 경우 블로킹을 막을 수 있음.

5-2) Merge, Update, Delete 블로킹

• 해당 DML 구문에 대한 블로킹을 피하는 방법은, 'SELECT FOR UPDATE NOWAIT'를 사용할 경우 회피 가능함.

6) 데드락

• 데드락(deadlock)은 두 개의 세션이 상대방이 원하는 자원을 서로 보유하고 있을 때 일어남.
• 테스트 Case는 아래와 같음

-- 0. 테스트 데이터 생성
DROP TABLE XSOFT_A;
DROP TABLE XSOFT_B;


CREATE TABLE XSOFT_A (A_1 NUMBER);
CREATE TABLE XSOFT_B (B_1 NUMBER);

INSERT INTO XSOFT_A VALUES(1);
INSERT INTO XSOFT_B VALUES(2);

COMMIT;

-- 1. (Session_1) 테이블 A를 수정함
UPDATE XSOFT_A
SET    A_1 = 10
;

-- 2. (Session_2) 테이블 B를 수정함
UPDATE XSOFT_B
SET    B_1 = 10
;

-- 3. (Session_2) 테이블 A를 수정함
UPDATE XSOFT_A
SET    A_1 = 10
;

-- 4. (Session_3) Lock Monitering
@LOCK
;

(Node)H-Sid (Node)W-Sid Lock Status  W-Time Lock Type H L-Mode R L-Mode LOCKED_OBJ                       UserName SID/SER#     S TR/w/b MODULE                  PGM      W_T WAIT_EVENT                 
----------- ----------- ----------- ------- --------- -------- -------- -------------------------------- -------- ------------ - ------ ----------------------- ---- ------- ------------------------- 
(1)9898     ▽          holding          24 TX        Ex       None                                      APPS     9898,102     I DI/F/F SQL*Plus                sqlp      24 SQL*Net message from clie  
            (1)9831     waiting           6 TX        None     Ex       XSOFT_A(T)                       APPS     9831,86      A DI/F/F SQL*Plus                sqlp       6 enq: TX - row lock conten  
;

-- 4. (Session_1) 테이블 A를 수정함
UPDATE XSOFT_B
SET    B_1 = 10
;

-- 5. (Session_2) 확인
UPDATE XSOFT_A
SET    A_1 = 10
;

UPDATE XSOFT_A
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource 
;

-- 4. (Session_3) Lock Monitering
(Node)H-Sid (Node)W-Sid Lock Status  W-Time Lock Type H L-Mode R L-Mode LOCKED_OBJ                       UserName SID/SER#     S TR/w/b MODULE                  PGM      W_T WAIT_EVENT                 
----------- ----------- ----------- ------- --------- -------- -------- -------------------------------- -------- ------------ - ------ ----------------------- ---- ------- ------------------------- 
(1)9831     ▽          holding          42 TX        Ex       None     XSOFT_A(T)                       APPS     9831,86      I DI/F/F SQL*Plus                sqlp      42 SQL*Net message from clie  
            (1)9898     waiting          45 TX        None     Ex       XSOFT_B(T)                       APPS     9898,102     A DI/F/F SQL*Plus                sqlp      45 enq: TX - row lock conten  
;

• 오라클은 데드락이 매우 드물게 발생하는 것으로 생각해서, 데드락이 발생할 때마다, 서버에 트레이스 파일을 생성함.
• 오라클 데이터베이스에서 데드락이 발생하는 가장 큰 원인은 인덱스가 없는 참조키 때문임.
  • 부모 테이블의 기본키를 수정한다면, 자식 테이블은 참조키에 대한 인덱스가 없어 락이 걸림.
  • 부모 테이블의 로우를 삭제하면, 자식 테이블 전체에 락이 걸림(참조키에 대한 인덱스가 없음)
  • 부모 테이블로 merge를 하면 자식 테이블 전체에 락이 걸림(참조키에 대한 인덱스가 없음)
    (이것은 오라클 9i와 10g에서만 적용되고, 11g 릴리즈 1 이후로는 적용되지 않음)

• 참조키에 인덱스가 필요없는 경우는 다음과 같다.
  • 부모 테이블의 로우를 삭제하지 않음.
  • 부모 테이블의 유일키/기본키 값을 수정하지 않음.
  • 부모에서 자식으로 조인 연산을 하지 않음

7) 락 상승

• 락 상승(lock escalation)이 발생할 때 시스템은 락의 granularity를 감소시킴.
  락 상승은 락을 부족한 자원으로 간주하고 오버헤드를 피하기 위한 수단으로 생각하는 데이터베이스에서 자주 사용됨.
• 오라클은 결코 락을 상승시키지 않지만, 락 변환(lock convension)을 실행함.
• 오라클은 가능한 한 가장 낮은 수준에서 락을 취하고, 필요하다면 좀 더 제한적인 락으로 전환함.
• 예를들어 FOR UPDATE 절을 이용해 테이블로부터 로우를 조회하면 2개의 락이 만들어짐.
  • 한 개는 로우를 위한 락.
  • 나머지 락은 테이블 자체에 걸리는 ROW SHARE TABLE 락.
• 오라클은 락이 한 개든 수백만 개든 오버헤드는 동일함.

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2012년 11월 03일
• 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컨설팅에서 번역한 'Expert Oracle Database'를 참고하였습니다.*