[Oracle] Oracle Lock Test

TX Lock Test (DML)

• 여러 세션이 동일 로우를 변경하는 경우(mode=6)
• 여러 세션이 Unique Key 충돌을 일으키는 경우(mode=4)
• 여러 세션이 비트맵 인덱스(Bitmap Index) 충돌을 일으키는 경우(mode=4)

 

COMMANDS

-- 현재 세션의 SESSION ID 확인
SELECT SID
FROM V$SESSION
WHERE USERNAME=USER;

-- LOCK 걸린 SESSION 확인
 SELECT D.OBJECT_NAME,
        L.SESSION_ID,
        S.SERIAL#,
        S.MACHINE,
        S.PROGRAM,
        S.LOGON_TIME
 FROM DBA_OBJECTS D,
      V$LOCKED_OBJECT L,
      V$SESSION S
 WHERE L.SESSION_ID = S.SID
 AND D.OBJECT_ID = L.OBJECT_ID
 ORDER BY S.LOGON_TIME;

 

LMODE / REQUEST

0. NONE

1. NO LOCK (NULL)

2. ROW SHARE (SS)

3. ROW EXCLUSIVE (SX)

4. SHARE (S)

5. SHARE ROW EXCLUSIVE (SSX)

6. EXCLUSIVE (X)

 

---

 

CASE # 1

• 1번 세션 (SID=59) 에서 특정 Row Update, No Commit
• 2번 세션 (SID=7) 에서 1번 세션과 같은 Row Update
• 3번 세션 (SID=69) 에서 해당 테이블에 Insert

 

• 1번 세션 -> ID =1 인 Row Update

(SID=59)
UPDATE SCOTT.DUMMY1 
SET CUSTOMER = 1 
WHERE ID = 1;

1 rows Updated.

• 2번 세션 -> 1번 세션과 같은 ID =1 인 Row Update

(SID=7)
UPDATE SCOTT.DUMMY1 
SET CUSTOMER = 2
WHERE ID = 1;

...                    ## Lock 발생

• 3번 세션 -> 해당 테이블에 Insert

(SID=69)
INSERT INTO SCOTT.DUMMY1(ID,CUSTOMER) VALUES(101,12345); 
1 row created.

## 같은 TABLE 다른 ROW에는 접근 가능한 것을 알 수 있다.

• 현재 LOCK 상태 조회

SELECT S.USERNAME, 
       L.SID, 
       S.SERIAL#, 
       L.TYPE, 
       L.ID1, 
       L.ID2, 
       L.LMODE, 
       L.REQUEST, 
       L.BLOCK 
FROM V$LOCK L, 
     V$SESSION S 
WHERE L.SID = S.SID 
AND S.USERNAME = USER;

+ )

BLOCK = 1 → BLOCKER

REQUEST >0 → BLOCKEE

• TX Lock의 발생 원인 조회

SELECT SID, SEQ#, EVENT, STATE, SECONDS_IN_WAIT, P1, P2, P3 
FROM V$SESSION_WAIT 
WHERE EVENT LIKE 'enq: %';

 

• 해결 방법

1. 락 걸린 테이블 찾기

SELECT  DO.OBJECT_NAME 
      , DO.OWNER 
      , DO.OBJECT_TYPE 
      , VO.XIDUSN 
      , VO.SESSION_ID 
      , VO.LOCKED_MODE 
   FROM V$LOCKED_OBJECT VO 
      , DBA_OBJECTS DO 
  WHERE VO.OBJECT_ID = DO.OBJECT_ID;

2. 해당하는 SID와 SERIAL 번호를 구한다

SELECT A.SID, A.SERIAL# 
  FROM V$SESSION A 
     , V$LOCK B 
     , DBA_OBJECTS C 
 WHERE A.SID         = B.SID 
   AND B.ID1         = C.OBJECT_ID 
   AND B.TYPE        = 'TM' 
   AND C.OBJECT_NAME = 'DUMMY1';

3. SID와 SERIAL 번호로 LOCK의 원인이 되는 세션을 해제한다

ALTER SYSTEM KILL SESSION '59, 10009';

 

---

 

CASE # 2

enq: TX - row lock contention

• 1번 세션 (SID=59) 에서 특정 Row Update, No Commit
• 2번 세션 (SID=7) 에서 1번 세션과 같은 Row Update
• 3번 세션 (SID=69) 에서 1,2번 세션과 같은 Row Update

 

• 1번 세션 -> ID =1 인 Row Update

(SID=59)
UPDATE SCOTT.DUMMY1 
SET CUSTOMER = 1 
WHERE ID = 1;

1 rows Updated.

• 2번 세션 -> 1번 세션과 같은 ID =1 인 Row Update

(SID=7)
UPDATE SCOTT.DUMMY1 
SET CUSTOMER = 2
WHERE ID = 1;

...                    ## Lock 발생

• 3번 세션 -> 1,2번 세션과 같은 Row Update

(SID=69)
UPDATE SCOTT.DUMMY1
SET CUSTOMER = 3
WHERE ID = 1;

...                    ## Lock 발생

• 현재 LOCK 상태 조회

SELECT S.USERNAME, 
       L.SID, 
       S.SERIAL#, 
       L.TYPE, 
       L.ID1, 
       L.ID2, 
       L.LMODE, 
       L.REQUEST, 
       L.BLOCK 
FROM V$LOCK L, 
     V$SESSION S 
WHERE L.SID = S.SID 
AND S.USERNAME = USER;

• TX Lock의 발생 원인 조회

SELECT SID, SEQ#, EVENT, STATE, SECONDS_IN_WAIT, P1, P2, P3 
FROM V$SESSION_WAIT 
WHERE EVENT LIKE 'enq: %';

 

• 해결 방법

1. 락 걸린 테이블 찾기

SELECT  DO.OBJECT_NAME 
      , DO.OWNER 
      , DO.OBJECT_TYPE 
      , VO.XIDUSN 
      , VO.SESSION_ID 
      , VO.LOCKED_MODE 
   FROM V$LOCKED_OBJECT VO 
      , DBA_OBJECTS DO 
  WHERE VO.OBJECT_ID = DO.OBJECT_ID;

2. 해당하는 SID와 SERIAL 번호를 구한다

SELECT A.SID, A.SERIAL# 
  FROM V$SESSION A 
     , V$LOCK B 
     , DBA_OBJECTS C 
 WHERE A.SID         = B.SID 
   AND B.ID1         = C.OBJECT_ID 
   AND B.TYPE        = 'TM' 
   AND C.OBJECT_NAME = 'DUMMY1';

3. SID와 SERIAL 번호로 LOCK의 원인이 되는 세션을 해제한다

ALTER SYSTEM KILL SESSION '59, 10009';

 

---

 

CASE #3

여러 세션이 Unique Key 충돌을 일으키는 경우(mode=4)

-- 인덱스 생성
 CREATE UNIQUE INDEX TEST_IDX ON SCOTT.DUMMY1(ID);

 SELECT *
 FROM USER_INDEXES
 WHERE TABLE_NAME = 'DUMMY1';

-- 여러 세션에서 같은 로우에 데이터 삽입

• 1번 세션 -> ID = 102 인 Row Insert

(SID=62)
 INSERT INTO SCOTT.DUMMY1
 VALUES(102,1);

• 2번 세션 -> ID = 102 인 Row Insert

(SID=64)
SQL> INSERT INTO SCOTT.DUMMY1
  2  VALUES(102,1);

...                            ## LOCK 발생

• 3번 세션 -> ID = 102 인 Row Insert

(SID=72)
SQL> INSERT INTO SCOTT.DUMMY1
  2  VALUES(102,1);

...                            ## LOCK 발생

• 현재 LOCK 상태 조회

 SELECT SID,TYPE,LMODE,REQUEST,BLOCK
 FROM V$LOCK
 WHERE SID IN
 (
    64,
    72,
    62
 )
 AND TYPE = 'TX';

 

-- REQUEST = 4 => SHARED 모드로 TX LOCK을 획득하기 위해 대기

• TX Lock의 발생 원인 조회

 SELECT SID,SEQ#,EVENT,P1,P2,P3,WAIT_CLASS#
 FROM V$SESSION_WAIT
 WHERE SID IN
 (
    64,
    72,
    62
 );

 

---

 

CASE #4

여러 세션이 비트맵 인덱스(Bitmap Index) 충돌을 일으키는 경우(mode=4)

 

(SID=62)
-- 인덱스 생성
 SELECT *
 FROM USER_INDEXES;

 CREATE BITMAP INDEX TEST_BITMAP_IDX
 ON SCOTT.DUMMY1(ID);

• 2번 세션 -> ID = 102 인 Row Insert

(SID=64)
SQL> INSERT INTO SCOTT.DUMMY1
  2  VALUES(102,1);

1 row created.

• 3번 세션 -> ID = 102 인 Row Insert

(SID=72)
SQL> INSERT INTO SCOTT.DUMMY1
  2  VALUES(102,1);

...                            ## LOCK 발생

• 현재 LOCK 상태 조회

 SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK
 FROM V$LOCK
 WHERE SID IN
 (
    62,
    64,
    72
 )
 AND TYPE = 'TX';

 

• TX Lock의 발생 원인 조회

 SELECT SID,SEQ#,EVENT,P1,P2,P3,WAIT_CLASS#
 FROM V$SESSION_WAIT
 WHERE SID IN
 (
    62,
    64,
    72
 );