오라클 lock 조회

[송지환]

오라클 LOCK 조회 및 KILL 방법

멜번초이 2011. 11. 30. 14:18

프로젝트에서 LOCK 이 자주 걸리는 문제가 발생하여 연구하던 중에 아래의 sql 문을 이용하여 lock 세션을 찾아서 관리할 수 있다는 것을 알았다. 그러나 이 SQL 은 System 유저만이 사용할 수 있다는  제약이 있다.

LOCK 이 발생하게 되면 프로그램이 동작하다가 멈추고 기다리게 된다. 원인을 파악하는데 시간이 허비하게 되는데 알고 보니 테이블에 LOCK이 걸린 것이었다면 허탈함을 금할 수 없다.  자주 LOCK 이 걸리는 테이블이라면 SELECT 할 때 WAIT 타임을 1 정도로 주면 1초동안 LOCK 이 풀리기를 대기하다가 오류 처리되므로 개발자가 쉽게 LOCK 걸린 상황을 인지할 수 있다.

-- 락걸린 테이블 확인
SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,
        VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE
FROM    V$LOCKED_OBJECT VO, DBA_OBJECTS DO
WHERE   VO.OBJECT_ID = DO.OBJECT_ID;

-- 해당 테이블에 LOCK 이 걸렸는지.
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
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 IN ('TB_CO_GENO');

-- 락발생 사용자와 SQL, OBJECT 조회
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
                A.PROGRAM, B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM V$LOCKED_OBJECT X, V$SESSION A, V$SQLTEXT B, DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND
X.OBJECT_ID = D.OBJECT_ID AND A.SQL_ADDRESS = B.ADDRESS
ORDER BY B.ADDRESS, B.PIECE;

-- 현재 접속자의 SQL 분석
SELECT DISTINCT A.SID, A.SERIAL#,
       A.MACHINE, A.TERMINAL, A.PROGRAM,
       B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM  V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE

-- 락 세션 죽이기
  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 = 'TB_CO_GENO'
  
SID SERIAL#
--- -------
5   1
6   1

2. 다음 명령으로 SESSION들을 KILL한다.  ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
SQL> alter system kill session '5, 1';
SQL> alter system kill session '6, 1';


-- 락 세션 죽이는 sql 문
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
       A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'
FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
WHERE  X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME;

자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기 때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가 Release 되기를 기다리게 되는 경우도 있다.
3. Transaction을 직접 처리하는 경우 (@Transaction을 사용하지 않고 @Override 를 사용) commit 이나 rollback 하지 않고 바로 빠져 나가는 경우에도 lock 이 발생될 수 있다. 거래가 바로 빠져 나가는 경우는 exception 이 발생했으나 catch 를 제대로 하지 못 한 경우가 흔하다.


다음은 <http://www.medianart.com/288> 에서 퍼온 글입니다. 내가 궁금하던 것을 잘 정리해 놓으셨군요~

LOCK의 유형
   데이타의 concurrency를 보장하기 위해 오라클은 lock과 transaction을 사용한다.
   Lock은 같은 자원을 access하는 사용자들 사이에 상호간에 해를 끼치는 것을 예방하기  
   위해서 사용되는 메카니즘이다.
   Lock의 종류
      - Exclusive : lock이 걸린 자원의 공유를 허용하지 않는다.
      - Share : 자원에 대해 수행되는 명령의 유형에 따라 lock된 자원의 공유되는 것을  허용한다.
   오라클에서의 Lock의 일반적인 범주
      - Data or DML (row locks TX and table locks TM) : 동시에 다중의 사용자에 의해
        access되는 테이블 데이타의 보호를 위해 사용된다.
      - Dictionary or DDL(TD) : 트랜잭션에서 access되는 테이블과 같은 Object의 정의를
        보호하기 위해서 사용한다.
      - Internal and Latches (RT, MR) : SGA 영역에서의 내부적인 데이타베이스와 메모리
        구조를 보호하기 위해 사용한다.
   1. TABLE LOCKS
      테이블의 특정한 row를 수정하는 문장은 항상 그러한 row에 대해 exclusive row lock
      을 획득하고 테이블 lock을 전유한다.
      (1) Row Share Table Locks (RS)
          - row를 lock 시키고 lock된 테이블을 UPDATE 할 목적이다.
          - 모든 row를 SELECT 하려는 다른 트랜잭션을 허용한다.
          - 동일 테이블에서 lock 되지 않은 row를 INSERT, UPDATE, DELETE 하는 다른  트랜잭션을 허용한다.
          - 테이블에 대한 ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE lock
            을 획득하려는 다른 트랜잭션을 허용한다.
          - EXCLUSIVE 모드에서의 테이블 locking으로부터 다른 트랜잭션을 예방한다.
      (2) Row Exclusive Table Locks (RX)
          - row를 lock 시키고 테이블에서 row를 변화시킨다.
          - 모든 row를 SELECT 하려는 다른 트랜잭션을 허용한다.
          - 동일 테이블에서 lock 되지 않은 row를 INSERT, UPDATE, DELETE 하는 다른
            트랜잭션을 허용한다.
          - 테이블에 대한 ROW SHARE, ROW EXCLUSIVE lock을 획득하려는 다른 트랜잭션을
            허용한다.
          - SHARE, EXCLUSIVE, SHARE ROW EXCLUSIVE 모드에서의 테이블 locking으로부터
            다른 트랜잭션을 예방한다.
      (3) Share Table Locks (S)
          - 다른 트랜잭션을 제공하지 않는 테이블에서 row를 INSERT, UPDATE, DELETE
            하는 것이 SHARE lock을 홀딩한다.
          - 동일 테이블에서 지정된 row를 QUERY 또는 LOCK 하려는 다른 트랜잭션을  허용한다.
          - 테이블에 대해 더 나아가 SHARE lock을 얻으려는 다른 트랜잭션을 허용한다.
          - EXCLUSIVE 또는 SHARE ROW EXCLUSIVE 모드로 테이블을 locking 하는 것으로
            부터 다른 트랜잭션을 보호한다.
      (4) Share Row Exclusive Table Locks (SRX)
          - 테이블에 row를 INSERT, UPDATE, DELETE 한다.
          - 동일 테이블에서 지정된 row를 QUERY 또는 LOCK 하려는 다른 트랜잭션을   허용한다.
          - SHARE, EXCLUSIVE 또는 SHARE ROW EXCLUSIVE 모드로 테이블을 locking 하는 것으로
            부터 다른 트랜잭션을 보호한다.
      (5) Exclusive Table Locks (S)
          - 테이블에 row를 INSERT, UPDATE, DELETE 한다.
          - 동일 테이블에서 지정된 row를 QUERY하는 것에 대해서만 다른 트랜잭션을
            허용한다.
          - 어떠한 lock 문장을 내리는 것으로부터 다른 트랜잭션을 보호한다.
   2. DICTIONARY LOCKS
      Dictionary lock은 Object에 대한 DDL 명령이 수행되는 동안 Object의 정의를  보호한다.
   3. INTERNAL LOCKS 과 LATCHES
      Latches 와 Internal lock은 메모리 구조를 보호하기 위한 메카니즘이다.
      - Latches 는 SGA 영역에 있는 shared data structure를 보호하기 위한 low-level    연속 메카니즘이다.
      - Internallock은 data dictionary entry를 보호하고 database file, tablespace,  rollback segment를 lock한다.
      Latches 와 Internal locks는 데이타베이스 사용자에 의해 컨트롤 될 수 없다.

LOCK에 대한 모니터
   lock contention 과 병목현상은 시스템의 효능을 저하시킨다.
   1. 모니터 방법
      (1) SQL*DBA 모니터 lock display
      (2) V$LOCK view
      (3) utllockt.sql 이라는 스크립트 화일의 내용을 확인
          (lock이 된 자원을 기다리는 사용자가 있는지를 보여줌)
   2. SQL*DBA lock monitor 를 사용한 모니터 방법
      Lock 모니터의 Resource ID 1 필드는 DBA_OBJECTS 에서의 object_id 이다.
      Lock 모니터의 특정한 Resource ID 1 과 일치하는 테이블명을 찾기 위해서는   다음 Query를 사용한다.
      SQL> SELECT owner, object_id, object_name, object_type
           FROM dba_objects
           WHERE object_id = resource id #;
      ▶ LOCK monitor의 컬럼들
      Username : 연결된 사용자의 명
      Session ID : 사용자 연결을 식별하기 위해 오라클에서 제공되는 ID
      Serial Number : 세션의 시리얼 번호로 각각의 세션을 유일한 번호로 지정하기 위해  Session ID를 함께 사용한다.
      Lock Type : TM은 데이타 조작에 대해 dictionary lock을 표시한다.
                  Resource ID 1 필드는 lock이 걸린 테이블의 ID이다.
                  테이블명을 찾아보려면 DBA_OBJECTS 쿼리를 사용한다.
                  TM lock은 트랜잭션 lock이다.
                  지시하는 row는 lock이 걸리거나 lock 되기 위해 요청된다.
      Resource ID 1 : TM 형태의 lock에 대해 DBA_OBJECTS view로 찾은 테이블 ID를 보여준다. 다른 값은 무시될 수 있다.
      Resource ID 2 : 내부적으로 lock의 형태를 식별하기 위해 사용한다.
      Mode Held : 자원을 쥐고 있는 lock의 모드를 나타낸다.
      Mode Requested : 자원에게 요청된 lock의 모드를 나타낸다.-- row lock 조회(멀티)

select event

       , instance_number

       , sample_time

       , sql_id 

       , session_id

       , session_serial#

       , blocking_inst_id

       , blocking_session

       , blocking_session_serial# 

from DBA_HIST_ACTIVE_SESS_HISTORY

WHERE to_char(sample_time, 'YYYY/MM/DD HH24:MI') LIKE '2020/12/11%'   

and event like '%row lock contention%'

order by sample_time;

-- 테이블 lock

-- 특정 테이블에 lock 여부 조회

select 

    a.sid

  , a.serial#

  , 'alter system kill session '''||a.sid||','||a.serial#||''''||';' as aaa

  , a.status

  , a.process

  , a.username

  , a.osuser

  , c.object_name

  , round(a.SECONDS_IN_WAIT/60,2)  as wait_분

  , round(a.SECONDS_IN_WAIT/60/60,2)  as wait_시

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 = '테이블명'

;

-- DML lock 조회

-- Insert/update/delete 수행시 걸린 lock 조회

select

    a.session_id

  , b.serial#

  , 'alter system kill session '''||a.session_id||','||b.serial#||''''||';' as aaa

  , a.os_user_name

  , a.oracle_username

  , b.status

  , round(b.SECONDS_IN_WAIT/60,2)  as wait_분

  , round(b.SECONDS_IN_WAIT/60/60,2)  as wait_시

from v$locked_object a, v$session b

where a.session_id = b.sid

;

-- 현재 세션 중 Active 상태 조회

SELECT

    sid,

    serial#,

    module,

    program,

    status,

    sql_id,

    event

FROM

    v$session

WHERE 1=1

    and type <> 'BACKGROUND'

    AND status = 'ACTIVE';

-- 쿼리 수행상태 조회

-- 현재 세션이 active 상태인 수행중인 쿼리 조회

select 

    a.sid

  , a.serial#

  , 'alter system kill session '''||a.sid||','||a.serial#||''''||';' as aaa

  , a.status

  --, a.process

  , a.username

  --, a.osuser

  , round(a.SECONDS_IN_WAIT/60,2)  as wait_분

  , round(a.SECONDS_IN_WAIT/60/60,2)  as wait_시

  , b.sql_text

  , c.program

from

  v$session a, v$sqlarea b, v$process c

where 1=1

  and a.sql_hash_value = b.hash_value

  and a.sql_address = b.address

  and a.paddr = c.addr

  and a.status = 'ACTIVE'

  and a.type <> 'BACKGROUND'

  and a.username = '계정명'  

;

-- TX Lock 경합 상황 모니터링

select sid, type, id1, id2, lmode, request, block

         , to_char(trunc(id1/power(2,16))) USN

         , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT

         , id2 SQN

  from v$lock

 where TYPE = 'TX' ;

-- TX Lock의 발생 원인 조회     

-- 특히 이벤트명이 enq: TX - row lock contention일 때는 Lock 모드에 따라 그 발생원인을 판단

select sid, seq#, event, state, seconds_in_wait

       , p1

       -- enq: TX - row lock contention일 때 lock타입

       , chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65536) as lock_type

       -- enq: TX - row lock contention일 때 lock모드

       ,decode(to_char(bitand(p1, 65536)), 0, 'None'

                                    , 1, 'Null'

                                    , 2, 'RS'   -- Row-Shared

                                    , 3, 'RX'   -- Row-Exclusive

                                    , 4, 'S'    -- Shared

                                    , 5, 'SRX'  -- Shared-Row-Exclusive

                                    , 6, 'X'    -- Exclusive

        ) lock_mode      

        , p2, p3

        -- Undo 세그먼트 번호

        ,trunc(p2/power(2,16))  as undo_segment

        -- 트랜젝션 테이블 슬롯번호

        ,bitand(p2, to_number('ffff', 'xxxx')) + 0  as slot_number

        -- 트랜젝션 슬롯 Wrap 시퀀스

        ,p3        as slot_wrap_sequence

  from v$session_wait

 where 1=1

--   and event like 'enq: TX%' 

   and sid = 821;

-- DML간 테이블 Lock 조회

select l.session_id SID 

   , (case when lock_type = 'Transaction' then 'TX'

           when lock_type = 'DML' then 'TM' end) TYPE

   , mode_held

   , mode_requested mode_reqd

   , (case when lock_type = 'Transaction' then

                to_char(trunc(lock_id1/power(2,16)))

           when lock_type='DML' then

                (select object_name from dba_objects

                 where object_id = l.lock_id1)

      end) "USN/Table"

   , (case when lock_type = 'Transaction' then

                to_number(lock_id1) end) "SQN"

   , (case when blocking_others = 'Blocking' then ' <<<<<' end) Blocking

    from dba_lock l       

   where lock_type in ('Transaction', 'DML')

   order by session_id, lock_type, lock_id1, lock_id2 ; 

-- 이벤트 상황 조회

select event, wait_time, seconds_in_wait, state

  from v$session_wait

 --where sid = 821 ;   

<Lock 모드간 호환성(Compatibility)>

- ○ : 호환된다는 의미

	Null	RS	RX	S	SRX	X
Null	○	○	○	○	○	○
RS	○	○	○	○	○
RX	○	○	○
S	○	○		○
SRX	○	○
X	○

(의미)

-- RS : Row Share(또는 SS : sub share)

select for update 문을 위해 로우 Lock을 설정하려면 먼저 획득해야 함

-- RX : Row Exclusive(또는 SX : sub exclusive)

insert, update, delete, merge 문을 위해 로우 Lock을 설정하려면 먼저 획득해야 함

-- S : Share

-- SRX : share row exclusive(또는 SSX : share/sub exclusive)

-- X : exclusive

* 선행 트랜젝션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜젝션은 대기하거나 작업을 포기해야 한다

* RS, RX 간에는 어떤 조합으로도 호환이 되므로 select for update나 DML문 수행시 이들간에 테이블 Lock에 의한 경합은 절대 발생하지 않는다

* 다만, 같은 로우를 갱신하려 할 때 로우 Lock에 의한 경합은 발생   

-- LOCK TYPE이 TX인 USER 및 Lmode 조회

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,

    round(s.SECONDS_IN_WAIT/60,2)  as wait_분,

    round(s.SECONDS_IN_WAIT/60/60,2)  as wait_시

FROM

    v$lock     l,

    v$session  s

WHERE

        l.sid = s.sid

    AND l.type = 'TX' 

  --AND S.USERNAME = '유저이름';

REQUEST => 0 : 락을 보유, 작업이 끝나지 않은 상태, 

           4, 6 :  락으로 인해 대기중인 상태, 작업이 끝나기 전에는 같은 로우를 시도하려는 유저들

※참고 mode(4) : Unique Key 충돌을 일으키는 경우, mode(6) : 여러 세션이 동일 로우를 변경하는 경우

-- 해당 sid 와 serial 번호로 lock 걸린 object name 을 확인

SELECT A.SID

     , A.SERIAL#

     , object_name

     , A.SID || ', ' || A.SERIAL# AS KILL_TASK

  FROM V$SESSION A

 INNER JOIN V$LOCK B

    ON A.SID = B.SID

 INNER JOIN DBA_OBJECTS C

    ON B.ID1 = C.OBJECT_ID

 WHERE B.TYPE  = 'TM'

 ;

-- 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인

SELECT DISTINCT T1.SESSION_ID

     , T2.SERIAL#

     , T4.OBJECT_NAME

     , T2.MACHINE

     , T2.TERMINAL

     , T2.PROGRAM

     , T3.ADDRESS

     , T3.PIECE

     , T3.SQL_TEXT

  FROM V$LOCKED_OBJECT T1

     , V$SESSION T2

     , V$SQLTEXT T3

     , DBA_OBJECTS T4

 WHERE 1=1

   AND T1.SESSION_ID = T2.SID

   AND T1.OBJECT_ID = T4.OBJECT_ID

   AND T2.SQL_ADDRESS = T3.ADDRESS

   ORDER BY T3.ADDRESS, T3.PIECE

   ;

-- 락을 좀더 자세히 확인할 수 있는 쿼리

SELECT

    a.sid,

    a.serial#,

    a.username,

    a.process,

    b.object_name,

    decode(c.lmode, 2, 'RS', 3, 'RX',

           4, 'S', 5, 'SRX', 8,

           'X', 'NO')                                      "TABLE LOCK",

    decode(a.command, 2, 'INSERT', 3, 'SELECT',

           6, 'UPDATE', 7, 'DELETE', 12,

           'DROPTABLE', 26, 'LOCK TABLE', 'UNKNOWN')         "SQL",

    decode(a.lockwait, NULL, 'NO WAIT', 'WAIT')             "STATUS"

FROM

    v$session    a,

    dba_objects  b,

    v$lock       c

WHERE

        a.sid = c.sid

    AND b.object_id = c.id1

    AND c.type = 'TM';

-- 간단하게 블록킹 및 WAITNG SID 조회

SELECT

    'USERNAME : '

    || (

        SELECT

            username

        FROM

            v$session

        WHERE

            sid = a.sid

    )

    || ', SID : '

    || a.sid     AS blocking,

    ' IS WAITING ',

    'USERNAME : '

    || (

        SELECT

            username

        FROM

            v$session

        WHERE

            sid = b.sid

    )

    || ', SID : '

    || b.sid     AS blocked

FROM

    v$lock  a,

    v$lock  b

WHERE

        a.id1 = b.id1

    AND a.id2 = b.id2

    AND a.block = 1

    AND b.request > 0;

-- V$SESSION_WAIT : TX Lock의 발생 원인 조회

SELECT

    sid,

    seq#,

    event,

    state,

    seconds_in_wait,

    p1,

    p2,

    p3

FROM

    v$session_wait

WHERE

    event LIKE 'enq: TX%';

-- V$TRANSACTION : TRANSACTION 조회

SELECT

    xidusn,

    xidslot,

    xidsqn,

    to_char(xidusn, 'XXXX')

    || to_char(xidslot, 'XXXX')

    || to_char(xidsqn, 'XXXX')       to_hex_xid,

    xid                              AS 트랜잭션id

FROM

    v$transaction;

-- SID별 실행 쿼리 추출

SELECT

    a.username,

    a.program,

    a.machine,

    a.module,

    b.spid,

    a.sid,

    a.serial#,

    a.status,

    c.sql_text

FROM

    v$session  a,

    v$process  b,

    v$sql      c

WHERE

    a.sid IN (

        SELECT

            sid

        FROM

            v$lock

        WHERE

            type = 'TX'

    ) -- sid 입력 

 AND B.ADDR = A.PADDR 

 AND A.SQL_HASH_VALUE = C.HASH_VALUE(+) 

 AND A.SQL_ADDRESS = C.ADDRESS(+);

-- Waiting session 확인

-- "BLOCKING_SESSION이 NULL이 아닌 것이 Waiting session이다."

SYS@orcl2> SELECT SID, SERIAL#, USERNAME, BLOCKING_SESSION

           FROM V$SESSION

           WHERE BLOCKING_SESSION IS NOT NULL;

       SID    SERIAL# USERNAME                       BLOCKING_SESSION

---------- ---------- ------------------------------ ----------------

        19         145 SCOTT                                        27           

-- Blocking session 확인

-- 현재 lock을 잡고 있는 정보

SYS@orcl2> SELECT SID, SERIAL#, USERNAME

           FROM V$SESSION

           WHERE SID IN (SELECT BLOCKING_SESSION

                         FROM V$SESSION

                         WHERE BLOCKING_SESSION IS NOT NULL);

       SID    SERIAL# USERNAME

---------- ---------- ------------------------------

        27        91 SCOTT                         

-- 자세하게 볼 수 있다.

SYS@orcl2> select *

           from v$session

           where sid in(27, 145);

-- lock된 object를 확인

/*

0 - NONE: lock requested but not yet obtained

1 - NULL

2 - ROWS_S (SS): Row Share Lock

3 - ROW_X (SX): Row Exclusive Table Lock

4 - SHARE (S): Share Table Lock

5 - S/ROW-X (SSX): Share Row Exclusive Table Lock

6 - Exclusive (X): Exclusive Table Lock

*/

SYS@orcl2> SELECT session_id, object_id, oracle_username, locked_mode

           FROM v$locked_object ;

SESSION_ID  OBJECT_ID ORACLE_USERNAME                LOCKED_MODE

---------- ---------- ------------------------------ -----------

        19      73181 ALLEN                                    3

        27      73181 SCOTT   

-- lock 종류 확인        

SYS@orcl2> SELECT session_id, owner, name, mode_held

                  FROM dba_dml_locks ;

SESSION_ID OWNER                          NAME                           MODE_HELD

---------- ------------------------------ ------------------------------ -------------

        27 SCOTT                          EMP                            Row-X (SX)

        19 SCOTT                          EMP                            Row-X (SX)

-- "event" : lock 원인 확인

-- "SECONDS_IN_WAIT" : lock 걸려 waiting 하는 시간

SYS@orcl2> SELECT sid, serial#, username, blocking_session, event, seconds_in_wait

           FROM v$session

           WHERE username in ('SCOTT','ALLEN') ;

SID SERIAL# USERNAME BLOCKING_SESSION    EVENT                           SECONDS_IN_WAIT

---------- ---------- ----------------- ------------------------------   -------------------

19     145 ALLEN           27            enq: TX - row lock contention   298

27      91 SCOTT                         SQL*Net message from client     1431        

-- lock 걸린 파일 블록정보 확인

SYS@orcl2> SELECT username, row_wait_obj#, row_wait_file#, row_wait_block#,

                          row_wait_row#, sql_id

        FROM v$session

        WHERE blocking_session IS NOT NULL ;

USERNAME                  ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# SQL_ID

------------------------- ------------- -------------- --------------- ------------- -------------

ALLEN                             73181              4             151             1 8fnkr5kmx5xmv

-- lock 걸린 row 확인

SYS@orcl2> SELECT empno, ename, sal

        FROM scott.emp

        WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,73181,4,151,8) ;

     EMPNO ENAME             SAL

---------- ---------- ----------

      7839 KING             5500

-- lock 잡고 있는 놈, lock 때문에 대기하는 놈 조회

SELECT

    decode(status, 'INACTIVE', username

                               || ' '

                               || sid

                               || ','

                               || serial#, 'lock')      AS holder,

    decode(status, 'ACTIVE', username

                             || ' '

                             || sid

                             || ','

                             || serial#, 'lock')      AS waiter,

    sid,

    serial#,

    status

FROM

    (

        SELECT

            level                                                  AS le,

            nvl(s.username, '(oracle)')                             AS username,

            s.osuser,

            s.sid,

            s.serial#,

            s.lockwait,

            s.module,

            s.machine,

            s.status,

            s.program,

            to_char(s.logon_time, 'DD-MON-YYYY HH24:MI:SS')        AS logon_time

        FROM

            v$session s

        WHERE

            level > 1

            OR EXISTS (

                SELECT

                    1

                FROM

                    v$session

                WHERE

                    blocking_session = s.sid

            )

        CONNECT BY

            PRIOR s.sid = s.blocking_session

        START WITH s.blocking_session IS NULL

    );

HOLDER                    WAITER                           SID    SERIAL# STATUS

------------------------- ------------------------- ---------- ---------- --------

SCOTT 27,91               lock                              27         91 INACTIVE

lock                      ALLEN 19,145                      19        145 ACTIVE