행위

ORACLE 락 정보

DB CAFE

1 오라클 LOCK

https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1340

1.1 DML Locks

  1. 데이터 보호
  2. 테이블 락은 은 전체 테이블을 잠그는 반면 컬럼락은 선택한 행을 잠금.

1.1.1 DML락은

  1. 데이터락 이라고도 하는 DML락은 여러 사용자가 동시에 액세스하는 데이터의 무결성을 보장.
  2. 예를 들어, DML락은 두 고객이 온라인 서점에서 구할 수 있는 마지막 책을 구매하는 것을 방지합니다.
  3. DML락은 동시에 충돌하는 DML 또는 DDL 작업의 파괴적인 간섭을 방지.
1.1.1.1 Row Locks (TX)
  1. TX 락이라고도 하는 ROW락은 테이블의 단일 행에 대한 잠금입니다.
  2. 트랜잭션은 INSERT, UPDATE, DELETE, MERGE 또는 SELECT ... FOR UPDATE 문에 의해 수정된 각 행에 대해 행 잠금을 획득합니다.
  3. ROW락은 트랜잭션이 커밋되거나 롤백될 때까지 존재합니다.
  4. ROW락은 주로 두 트랜잭션이 동일한 행을 수정하지 못하도록 하는 대기열 메커니즘 역할을 합니다.
  5. 데이터베이스는 항상 배타 모드에서 수정된 행을 잠그므로 잠금을 유지하는 트랜잭션이 커밋하거나 롤백할 때까지 다른 트랜잭션이 행을 수정할 수 없습니다.
  6. ROW락은 가능한 가장 정밀한 그레인 잠금을 제공하므로 최상의 동시성과 처리량을 제공합니다.
  7. 데이터베이스 인스턴스 오류로 인해 트랜잭션이 종료되면 블록 수준 복구는 전체 트랜잭션이 복구되기 전에 행을 사용할 수 있도록 합니다.
1.1.1.2 Table Locks (TM)
  1. TM락 이라고도 하는 테이블 락은 INSERT, UPDATE, DELETE, MERGE, FOR UPDATE 절이 있는 SELECT 또는 LOCK TABLE 문에 의해 테이블이 수정될 때 트랜잭션에 의해 획득됩니다.
  2. DML 작업은 트랜잭션을 대신하여 테이블에 대한 DML 액세스를 예약하고 트랜잭션과 충돌할 DDL 작업을 방지하기 위해 테이블 잠금이 필요합니다.
  • Row Share (RS)
    • 하위 공유 테이블 잠금(SS)이라고도 하는 이 잠금은 테이블에 대한 잠금을 보유하고 있는 트랜잭션이 테이블의 잠긴 행을 갖고 있으며 이를 업데이트할 의도임을 나타냅니다. 행 공유 잠금은 테이블 잠금의 최소 제한 모드로 테이블에 대해 가장 높은 수준의 동시성을 제공합니다.
  • Row Exclusive Table Lock (RX)
    • 하위 배타적 테이블 잠금(SX)이라고도 하는 이 잠금은 일반적으로 잠금을 보유하고 있는 트랜잭션이 테이블 행을 업데이트했거나 SELECT ... FOR UPDATE를 실행했음을 나타냅니다. SX 잠금을 사용하면 다른 트랜잭션이 동일한 테이블에서 동시에 행을 쿼리, 삽입, 업데이트, 삭제 또는 잠글 수 있습니다. 따라서 SX 잠금을 사용하면 여러 트랜잭션이 동일한 테이블에 대해 동시 SX 및 하위 공유 테이블 잠금을 얻을 수 있습니다.
  • Share Table Lock (S)
    • 트랜잭션에 의해 유지되는 공유 테이블 잠금은 다른 트랜잭션이 테이블을 쿼리하도록 허용하지만(SELECT ... FOR UPDATE를 사용하지 않고) 단일 트랜잭션이 공유 테이블 잠금을 유지하는 경우에만 업데이트가 허용됩니다. 여러 트랜잭션이 공유 테이블 잠금을 동시에 보유할 수 있으므로 이 잠금을 보유하는 것만으로는 트랜잭션이 테이블을 수정할 수 있는지 확인하기에 충분하지 않습니다.
  • Share Row Exclusive Table Lock (SRX)
    • SSX(share-subexclusive table lock)라고도 하는 이 잠금은 공유 테이블 잠금보다 더 제한적입니다. 한 번에 하나의 트랜잭션만 주어진 테이블에서 SSX 잠금을 획득할 수 있습니다. 트랜잭션이 보유한 SSX 잠금은 다른 트랜잭션이 테이블을 쿼리할 수 있도록 허용하지만(SELECT ... FOR UPDATE 제외) 테이블을 업데이트할 수는 없습니다.
  • Exclusive Table Lock (X)
    • 이 잠금은 가장 제한적이며 다른 트랜잭션이 모든 유형의 DML 문을 수행하거나 테이블에 모든 유형의 잠금을 설정하는 것을 금지합니다.

1.2 DDL Locks

  • 테이블 및 뷰의 사전 정의와 같은 스키마 개체의 구조를 보호
  1. 데이터 딕셔너리(DDL) 잠금은 진행 중인 DDL 작업이 개체에 대해 작동하거나 개체를 참조하는 동안 스키마 개체의 정의를 보호합니다.
  2. DDL 작업 중에는 수정되거나 참조되는 개별 스키마 개체만 잠깁니다. 데이터베이스는 전체 데이터 사전을 잠그지 않습니다.
  3. Oracle은 DDL 락을 요구하는 DDL 트랜잭션을 대신하여 자동으로 DDL 락을 획득합니다.
  4. 사용자는 DDL 잠금을 명시적으로 요청할 수 없습니다.
    1. 예를 들어, 사용자가 저장 프로시저를 생성하면 Oracle Database는 프로시저 정의에서 참조하는 모든 스키마 객체에 대한 DDL 잠금을 자동으로 획득합니다.
  5. DDL 잠금은 프로시저 컴파일이 완료되기 전에 이러한 개체가 변경되거나 삭제되는 것을 방지합니다.

1.2.1 Exclusive DDL Locks

  1. 배타적 DDL 락은 다른 세션이 DDL 또는 DML 잠금을 획득하는 것을 방지합니다.
  2. "DDL 잠금 공유"에 설명된 작업을 제외한 대부분의 DDL 작업에는 동일한 스키마 개체를 수정하거나 참조할 수 있는 다른 DDL 작업과의 파괴적인 간섭을 방지하기 위해 리소스에 대한 배타적 DDL 잠금이 필요합니다.
    1. 예를 들어 DROP TABLE은 ALTER TABLE이 테이블에 열을 추가하는 동안 테이블을 삭제할 수 없으며 그 반대의 경우도 마찬가지입니다.
  3. 배타적 DDL 락은 DDL 문 실행 및 자동 커밋 기간 동안 지속됩니다.
  4. 배타적 DDL 잠금을 획득하는 동안 다른 작업에 의해 스키마 개체에 대해 다른 DDL 잠금이 유지되면 획득은 이전 DDL 잠금이 해제될 때까지 기다렸다가 계속 진행됩니다.

1.2.2 Share DDL Locks

  1. 리소스에 대한 공유 DDL락은 충돌하는 DDL 작업에 대한 파괴적인 간섭을 방지하지만 유사한 DDL 작업에 대한 데이터 동시성을 허용합니다.
    1. 예를 들어, CREATE PROCEDURE 문이 실행될 때 포함하는 트랜잭션은 참조된 모든 테이블에 대한 공유 DDL 잠금을 획득합니다.
    2. 다른 트랜잭션은 동일한 테이블을 참조하는 프로시저를 동시에 생성하고 동일한 테이블에서 동시 공유 DDL 잠금을 획득할 수 있지만 어떤 트랜잭션도 참조된 테이블에서 배타적 DDL 잠금을 획득할 수 없습니다.
  2. 공유 DDL 락은 DDL 문 실행 및 자동 커밋 기간 동안 지속됩니다.
  3. 따라서 공유 DDL 락을 보유하는 트랜잭션은 참조된 스키마 개체의 정의가 트랜잭션 동안 일정하게 유지되도록 보장됩니다.

1.2.3 Breakable Parse Locks

  1. 구문 분석 락은 참조하는 각 스키마 개체에 대해 SQL 문 또는 PL/SQL 프로그램 단위에 의해 유지됩니다.
  2. 참조된 개체가 변경되거나 삭제된 경우 연결된 공유 SQL 영역이 무효화될 수 있도록 구문 분석 락이 획득됩니다.
  3. 구문 분석 락은 DDL 작업을 허용하지 않고 충돌하는 DDL 작업을 허용하도록 해제될 수 있기 때문에 깨질 수 있는(Breakable ) 구문 분석 락이라고 합니다.
    1. SQL 문 실행의 구문 분석 단계에서 공유 풀에서 구문 분석 락이 획득됩니다. 잠금은 해당 명령문의 공유 SQL 영역이 공유 풀에 남아 있는 동안 유지됩니다.

1.3 System Locks

  1. 데이터 파일과 같은 내부 데이터베이스 구조를 보호
  2. 래치, 뮤텍스 및 내부 락은 완전 자동임.
  3. 사용자가 발생 또는 기간을 제어할 수 없기 때문에 사용자가 액세스할 수 없습니다.

1.3.1 래치 LATCH

  1. 래치는 공유 데이터 구조, 개체 및 파일에 대한 다중 사용자 액세스를 조정하는 간단한 저수준 직렬화 메커니즘입니다.
  2. 래치는 여러 프로세스에서 액세스할 때 공유 메모리 리소스가 손상되지 않도록 보호합니다.
    1. 특히 래치는 다음 상황에서 데이터 구조를 보호합니다.
      1. 여러 세션에 의한 동시 수정
      2. 다른 세션에서 수정하는 동안 한 세션에서 읽음
      3. 액세스 중 메모리 할당 해제(에이징 아웃)
  3. 일반적으로 단일 래치가 SGA의 여러 개체를 보호합니다.
    1. 예를 들어, DBWn 및 LGWR과 같은 백그라운드 프로세스는 데이터 구조를 생성하기 위해 공유 풀에서 메모리를 할당합니다.
    2. 이 메모리를 할당하기 위해 이러한 프로세스는 액세스를 직렬화하는 공유 풀 래치를 사용하여 두 프로세스가 공유 풀을 동시에 검사하거나 수정하려고 시도하는 것을 방지합니다.
  4. 메모리가 할당된 후 다른 프로세스는 구문 분석에 필요한 라이브러리 캐시와 같은 공유 풀 영역에 액세스해야 할 수 있습니다.
    1. 이 경우 프로세스는 전체 공유 풀이 아니라 라이브러리 캐시만 래치합니다.
  5. 행 잠금과 같은 대기열에 넣는 래치와 달리 래치는 세션이 대기열에 들어가는 것을 허용하지 않습니다.
  6. 래치를 사용할 수 있게 되면 래치를 요청한 첫 번째 세션이 이에 대한 단독 액세스를 얻습니다.
  7. 래치 회전은 프로세스가 루프에서 래치를 반복적으로 요청할 때 발생하는 반면 래치 슬립은 프로세스가 래치 요청을 갱신하기 전에 CPU를 해제할 때 발생합니다.
  8. 일반적으로 Oracle 프로세스는 데이터 구조를 조작하거나 보는 동안 매우 짧은 시간 동안 래치를 획득합니다.
    1. 예를 들어, 단일 직원의 급여 업데이트를 처리하는 동안 데이터베이스는 수천 개의 래치를 획득하고 해제할 수 있습니다. 래치 구현은 특히 프로세스가 래치를 기다리는지 여부와 대기 시간과 관련하여 운영 체제에 따라 다릅니다.
  9. 래칭의 증가는 동시성의 감소를 의미합니다.
    1. 예를 들어, 과도한 하드 구문 분석 작업은 라이브러리 캐시 래치에 대한 경합을 생성합니다.
  10. V$LATCH 뷰 에는 각 래치가 요청되고 대기한 횟수를 포함하여 각 래치에 대한 자세한 래치 사용 통계가 포함됩니다.

1.3.2 뮤텍스 Mutexe

  1. 상호 배제 개체(뮤텍스)는 동시 프로세스에서 액세스할 때 메모리의 개체가 노후화되거나 손상되는 것을 방지하는 저수준 메커니즘입니다.
  2. 뮤텍스는 래치와 유사하지만 래치는 일반적으로 개체 그룹을 보호하는 반면 뮤텍스는 단일 개체를 보호합니다.
  3. 뮤텍스는 다음과 같은 몇 가지 이점을 제공합니다.
    1. 뮤텍스는 경합의 가능성을 줄일 수 있습니다.
    2. 래치는 여러 개체를 보호하기 때문에 프로세스가 이러한 개체에 동시에 액세스하려고 할 때 병목 현상이 발생할 수 있습니다. 뮤텍스는 그룹이 아닌 개별 개체에 대한 액세스를 직렬화하여 가용성을 높입니다.
    3. 뮤텍스는 래치보다 적은 메모리를 사용합니다.
    4. 공유 모드에 있을 때 뮤텍스는 여러 세션에서 동시 참조를 허용합니다.

1.3.3 Internal Lock

  1. 내부 잠금은 래치 및 뮤텍스보다 더 높은 수준의 복잡한 메커니즘이며 다양한 용도로 사용됩니다.
  2. 데이터베이스는 다음 유형의 내부 잠금을 사용합니다.
    1. 사전 캐시 잠금
      1. 이러한 잠금은 지속 시간이 매우 짧고 항목이 수정되거나 사용되는 동안 사전 캐시의 항목에 유지됩니다. 구문 분석 중인 명령문이 일치하지 않는 개체 정의를 보지 않도록 보장합니다. 사전 캐시 잠금은 공유되거나 배타적일 수 있습니다. 공유 잠금은 구문 분석이 완료되면 해제되지만 배타적 잠금은 DDL 작업이 완료되면 해제됩니다.
    1. 파일 및 로그 관리 잠금
      1. 이러한 잠금은 다양한 파일을 보호합니다. 예를 들어 내부 잠금은 한 번에 하나의 프로세스만 변경할 수 있도록 제어 파일을 보호합니다. 또 다른 잠금은 온라인 리두 로그 파일의 사용 및 보관을 조정합니다. 여러 인스턴스가 데이터베이스를 공유 모드로 마운트하거나 한 인스턴스가 단독 모드로 데이터베이스를 마운트하도록 데이터 파일이 잠겨 있습니다. 파일 및 로그 잠금은 파일의 상태를 나타내기 때문에 이러한 잠금은 반드시 오랫동안 유지됩니다.
    1. 테이블스페이스 및 세그먼트 잠금 실행 취소
      1. 이러한 잠금은 테이블스페이스를 보호하고 세그먼트를 실행 취소합니다. 예를 들어, 데이터베이스에 액세스하는 모든 인스턴스는 테이블스페이스가 온라인인지 오프라인인지에 동의해야 합니다. 실행 취소 세그먼트는 잠겨 있으므로 하나의 데이터베이스 인스턴스만 세그먼트에 쓸 수 있습니다.

2 LOCK과 F/K

  1. Oracle Database는 종속 외래 키와 관련하여 상위 키의 동시성 제어를 최대화합니다.
  2. 힙 구성 테이블에서 잠금 동작은 외래 키 열의 인덱싱에 따라 다릅니다.
  3. 외래 키가 인덱싱되지 않으면 자식 테이블이 더 자주 잠기고 교착 상태가 발생하며 동시성이 감소합니다.
  4. 이러한 이유로 Oracle은 일치하는 고유 키 또는 기본 키가 업데이트되거나 삭제되지 않는 경우를 제외하고 대부분의 경우 외래 키를 인덱싱할 것을 권장합니다.
    1. 인덱스 구성 테이블 및 테이블 클러스터와 같은 힙이 아닌 데이터 구조에는 추가 고려 사항이 적용됩니다.

2.1 LOCK 과 and Unindexed F/K

  1. 다음 조건이 모두 충족되면 데이터베이스는 자식 테이블에 대한 전체 테이블 잠금을 획득합니다.
    1. 자식 테이블의 외래 키 열에 인덱스가 없습니다.
    2. 세션은 상위 테이블의 기본 키를 수정하거나(예: 행 삭제 또는 기본 키 속성 수정) 행을 상위 테이블에 병합합니다. 상위 테이블에 대한 삽입은 하위 테이블에 대한 테이블 잠금을 획득하지 않습니다.

cncpt308.gif

  1. 그림 9-3에서 데이터베이스는 부서 60의 기본 키 수정 동안 직원에 대한 전체 테이블 잠금을 획득합니다.
  2. 이 잠금을 사용하면 다른 세션에서 직원 테이블을 쿼리할 수 있지만 업데이트할 수는 없습니다.
  3. 예를 들어 직원 전화번호는 업데이트할 수 없습니다. 직원에 대한 테이블 잠금은 부서 테이블의 기본 키 수정이 완료된 직후 해제됩니다.
  4. 부서의 여러 행이 기본 키 수정을 거치면 직원에 대한 테이블 잠금이 얻어지고 부서에서 수정된 각 행에 대해 한 번씩 해제됩니다.

2.2 LOCK 과 and indexed F/K

  1. 다음 조건이 모두 참인 경우 데이터베이스는 자식 테이블에 대한 전체 테이블 잠금을 획득하지 않습니다.
    1. 자식 테이블의 외래 키 열이 인덱싱됩니다.
    2. 세션은 상위 테이블의 기본 키를 수정하거나(예: 행 삭제 또는 기본 키 속성 수정) 행을 상위 테이블에 병합합니다.
  2. 상위 테이블에 대한 잠금은 트랜잭션이 독점 테이블 잠금을 획득하는 것을 방지하지만 기본 키 수정 동안 상위 또는 하위 테이블에 대한 DML을 방지하지 않습니다.
    1. 이 상황은 기본 키 수정이 상위 테이블에서 발생하는 동안 업데이트가 하위 테이블에서 발생하는 경우에 선호됩니다.

3 LOCK 걸린 개체 확인 및 LOCK 해제

3.1 lock 걸린 개체 확인

/* 12C 부터 WITH절에 함수,프로시져 사용 가능 
WITH FUNCTION DBADM.FN_SEC_TO_TIME(P_SEC NUMBER) RETURN VARCHAR2
AS
    V_TIME VARCHAR2(10);
BEGIN
    IF P_SEC IS NULL THEN
        V_TIME := NULL;
    ELSE
        V_TIME := TO_CHAR(TRUNC(P_SEC/3600), 'FM900')
                  ||':'||TO_CHAR(TRUNC(MOD(P_SEC, 3600) / 60), 'FM00')
                  ||':'||TO_CHAR(TRUNC(MOD(P_SEC, 60  )     ), 'FM00');
    END IF;
    
    RETURN V_TIME;
END;
*/

SELECT /*+ ordered */
                      LEVEL
                          lvl,
                      NVL (s.blocking_session, s.sid)
                          lck_hold,
                      CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END
                          lck_wait,
                      s.username,
                      s.osuser,
                      s.status,
                      l.TYPE
                          TYPE,
                      fn_sec_to_time (l.ctime)
                          lock_tm,
                      DECODE (lmode,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          HOLD,
                      o.name
                          object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo#
                                --, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 )  slot#
                                ,
                      l.id1,
                      l.id2,
                      DECODE (l.request,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          REQUEST,
                      s.event,
                      s.machine,
                      s.module,
                      s.program,
                      s.sql_id,
                      s.blocking_session,
                      l.ctime,
                      o.obj#,
                      t.name
                          TYPE_NM,
                      t.id1_tag,
                      t.id2_tag,
                      s.sid,
                      s.serial#,
                         'ALTER SYSTEM KILL SESSION '''
                      || S.sid
                      || ','
                      || S.serial#
                      || ',@'
                      || S.INST_ID
                      || ''';'  CMD_KILL,
                         'ALTER SYSTEM DISCONNECT SESSION '''
                      || S.sid
                      || ','
                      || S.serial#
                      || ''' IMMEDIATE;'  CMD_DISCONNECT
                 FROM GV$lock l,
                      GV$session s,
                      sys.obj$ o,
                      GV$lock_type t
                WHERE     1 = 1
                      AND s.TYPE != 'BACKGROUND'
                      --  and s.username = 'EP_APP'
                      --  and s.sid = 4179
                      AND l.sid = s.sid
                      AND l.TYPE IN ('TM', 'TX', 'UL')
                      AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1)
                      AND t.TYPE = l.TYPE
           CONNECT BY     PRIOR s.sid = s.blocking_session
                      AND PRIOR l.id1 = l.id1
                      AND PRIOR l.id2 = l.id2
           START WITH s.blocking_session IS NULL
    ORDER SIBLINGS BY lock_tm DESC, lck_hold
--order by lck_hold, l.ctime desc, l.sid
;

3.2 해당 sid 와 serial 번호로 락걸린 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'
 ;

3.3 락걸린 OBJECT 세션 KILL

select 'ALTER SYSTEM KILL SESSION '''||a.sid||','||serial#||''';' SESS_KILL --세션 종료     
     , 'ALTER SYSTEM DISCONNECT SESSION '''|| a.SID|| ','|| a.SERIAL#|| ',@' || INST_ID || '''' || ' IMMEDIATE;' PS_KILL   -- 프로세스 KILL 
  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' -- DML락 , TX = 트랜잭션 
   and c.owner='OOADM'  -- OWNER명 
--   and c.object_name = 'TB_SA_BIZPLC_M'; -- 테이블명

alter system  kill session '45,74[,@1]' [IMMEDIATE]; -- []는 생략가능 , @1은 RAC일때 1번 인스턴스

3.3.1 DBA용 락 세션 KILL 뷰

CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS_KILL
AS
    SELECT B.SID
         , C.SERIAL#
         ,    'EXEC rdsadmin.rdsadmin_util.kill('
           || B.SID
           || ','
           || C.SERIAL#
           || ');'                       AWS_KILL_CMD              --     , B.ADDR
         , 'ALTER SYSTEM KILL SESSION '''||b.sid||','||c.serial#||''';' SESS_KILL --세션 종료     
         , 'ALTER SYSTEM DISCONNECT SESSION '''|| b.SID|| ','|| c.SERIAL#|| ',@' || b.INST_ID || '''' || ' IMMEDIATE;' PS_KILL   -- 프로세스 KILL 

         , B.BLOCK         -- 0 - The lock is not blocking any other processes
         -- 1 - The lock is blocking other processes
         -- 2 - The lock is not blocking any blocked processes on the local node, but it may or may not be blocking processes on remote nodes. This value is used only in Oracle Real Application Clusters (Oracle RAC) configurations (not in single instance configurations).
         --     , B.KADDR
         , C.STATUS
         , FN_SEC_TO_TIME (B.CTIME)      CTIME -- Time since current mode was granted
         , B.TYPE                                          -- TM - DML enqueue
         -- TX - Transaction enqueue
         -- UL - User supplied
         , B.LMODE                                     -- 세션이 잠금을 유지하는 잠금 모드 :
         -- 0 - 없음
         -- 1 - 널 (NULL)
         -- 2 - 행-S (SS)
         -- 3 - 행-X (SX)
         -- 4 - share (S)
         -- 5 - S / Row-X (SSX)
         -- 6 - exclusive (X)
         , B.REQUEST                                 -- 프로세스가 잠금을 요청하는 잠금 모드 :
         -- 0 - none
         -- 1 - null (NULL)
         -- 2 - row-S (SS)
         -- 3 - row-X (SX)
         -- 4 - share (S)
         -- 5 - S/Row-X (SSX)
         -- 6 - exclusive (X)
         , B.ID1
         , B.ID2
         , (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OBJECT_ID = B.ID1)    TBL1
         , (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OBJECT_ID = B.ID2)    TBL2                --     , C.OSUSER
         , C.MACHINE
         , C.PROGRAM
      --          D.SQL_TEXT
      FROM V$LOCK B 
 LEFT JOIN V$SESSION C 
        ON B.SID = C.SID
     --        LEFT JOIN V$SQLTEXT D ON C.SQL_ID = D.SQL_ID
     WHERE B.TYPE IN ('TM', 'TX')
--   AND B.SID IN (160,796,743,190,842)
;

4 락 발생 사용자 및 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
   ;

5 락트리(lock tree) 보기

SELECT level,
       LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       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;

또는

SELECT s.blocking_session
     , TO_NUMBER (s.sid)               Waiting_Session
     , s.event
     , s.seconds_in_wait
     , P.pid
     , P.spid                          "ServerPID"
     , s.process                       "ClientPID"
     , s.username
     , s.program
     , s.machine
     , s.osuser
     , s.sql_id
     , SUBSTR (sq.sql_text, 1, 75)     SQL
  FROM v$sql sq
     , v$session s
     , v$process P
 WHERE     s.event LIKE 'enq: TX%'
       AND s.paddr = p.addr
       AND s.sql_address = sq.address
       AND s.sql_hash_value = sq.hash_value
       AND s.sql_id = sq.sql_id
       AND s.sql_child_number = sq.child_number
UNION ALL
SELECT s.blocking_session
     , TO_NUMBER (s.sid)               Waiting_Session
     , s.event
     , s.seconds_in_wait
     , p.pid
     , p.spid                          "ServerPID"
     , s.process                       "ClientPID"
     , s.username
     , s.program
     , s.machine
     , s.osuser
     , s.sql_id
     , SUBSTR (sq.sql_text, 1, 75)     SQL
  FROM v$sql sq
     , v$session s
     , v$process p
 WHERE  s.sid IN (SELECT DISTINCT blocking_session
                    FROM v$session
                   WHERE event LIKE 'enq:TX%')
       AND s.paddr = p.addr
       AND s.sql_address = sq.address(+)
       AND s.sql_hash_value = sq.hash_value(+)
       AND s.sql_id = sq.sql_id(+)
       AND s.sql_child_number = sq.child_number(+)
ORDER BY 1 NULLS FIRST, 2

6 테이블 별 LOCK 확인

SELECT A.SID
     , A.SERIAL#
     , SUBSTRB(A.USERNAME, 1, 16) AS USERNAME
     , SUBSTRB(A.MACHINE, 1, 30) AS MACHINE
     , A.TERMINAL
     , A.OSUSER
     , A.PROGRAM
     , SUBSTRB(TO_CHAR(A.LOGON_TIME, 'MM/DD HH24:MI:SS'), 1, 14) AS LOGON_TIME
     , SUBSTRB(C.OBJECT_NAME, 1, 58) AS 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 LIKE UPPER('&테이블명');

7 Lock을 잡고있는 세션과 기다리는 세션 조회

SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW
     , B.SID
     , B.SERIAL# AS SER#
     , SUBSTR(B.MACHINE, 1, 10) AS MACHINE
     , SUBSTR(B.PROGRAM, 1, 15) AS PROGRAM
     , SUBSTR(A.OBJECT_NAME, 1, 17) AS OBJ_NAME
     , SUBSTR(B.STATUS, 1, 1) AS S
     , DECODE(B.COMMAND,  0, NULL,  2, 'INSERT',  6, 'UPDATE',  7, 'DELETE',  B.COMMAND) AS SQLCMD
     , B.PROCESS AS PGM_PSS
  FROM V$SESSION B
     , (SELECT    A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME
           FROM      V$LOCK A
                , DBA_OBJECTS B
           WHERE     A.ID1 = B.OBJECT_ID(+)
           GROUP BY  A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME)) A

 WHERE B.SID = A.SID AND B.TADDR IS NOT NULL;

8 작업 중인 데이터베이스 트랜잭션 조회

SELECT S.SID
     , S.SERIAL#
     , S.STATUS
     , S.OSUSER
     , S.USERNAME
     , T.STATUS
     , T.START_TIME
  FROM V$SESSION S
     , V$TRANSACTION T
     , DBA_ROLLBACK_SEGS R
 WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.SEGMENT_ID;

9 락 발생시 같이 실행된 세션/SQL 조회

SELECT /*+ ORDERED */ A.SADDR, A.SID, A.USER_NAME, A.ADDRESS, A.HASH_VALUE, A.SQL_ID
       , to_single_byte(A.SQL_TEXT) -- 멀티바이트 오류때문에 to_single_byte 함수 사용
       , A.SQL_TEXT
       , A.LAST_SQL_ACTIVE_TIME, A.SQL_EXEC_ID, A.CURSOR_TYPE, A.CHILD_ADDRESS, A.CON_ID
   FROM V$OPEN_CURSOR A
   JOIN V$SESSION B
     ON B.SID = A.SID
    AND B.SQL_ID = :SQL_ID     
  ORDER BY A.LAST_SQL_ACTIVE_TIME DESC 
  ;

10 잠금 발생 유형 조회

SELECT A.SID
     , DECODE(A.TYPE
            , 'MR', 'MEDIA RECOVERY'
            , 'RT', 'REDO THREAD'
            , 'UN', 'USER_NAME'
            , 'TX', 'TRANSACTION'
            , 'TM', 'DML'
            , 'UL', 'PL/SQL USER LOCK'
            , 'DX', 'DISTRIBUTED XACTION'
            , 'CF', 'CONTROL FILE'
            , 'IS', 'INSTANCE STATE'
            , 'FS', 'FILE SET'
            , 'IR', 'INSTANCE RECOVERY'
            , 'FS', 'FILE SET'
            , 'ST', 'DISK SPACE TRANSACTION'
            , 'TS', 'TEMP SEGMENT'
            , 'IV', 'LIBRARY CACHE INVAILDATION'
            , 'LS', 'LOG START OR SWITCH'
            , 'RW', 'ROW WAIT'
            , 'SQ', 'SEQUENCE NUMBER'
            , 'TE', 'EXTEND TABLE'
            , 'TT', 'TEMP TABLE'
            , A.TYPE
                )
            AS "LOCK_TYPE"
     , DECODE(A.LMODE
            , 0, 'NONE'
            , 1, 'NULL'
            , 2, 'ROW-S(SS)'
            , 3, 'ROW-X(SX)'
            , 4, 'SHARE'
            , 5, 'S/ROW-X(SSX)'
            , 6, 'EXCLUSIVE'
            , TO_CHAR(A.LMODE)
                )
            AS "MODE_HELD"
     , DECODE(A.REQUEST
            , 0, 'NONE'
            , 1, 'NULL'
            , 2, 'ROW-S(SS)'
            , 3, 'ROW-X(SX)'
            , 4, 'SHARE'
            , 5, 'S/ROW-X(SSX)'
            , 6, 'EXCLUSIVE'
            , TO_CHAR(A.REQUEST)
                )
            AS "MODE_REQUESTED"
     , TO_CHAR(A.ID1) AS "LOCK_ID1"
     , TO_CHAR(A.ID2) AS "LOCK_ID2"
     , DECODE(BLOCK,  0, 'NOT BLOCKING',  1, 'BLOCKING',  2, 'GLOBAL',  TO_CHAR(BLOCK)) AS "BLOCKING_OTHERS"

  FROM V$LOCK A WHERE (ID1, ID2) IN (SELECT B.ID1, ID2

                         FROM      V$LOCK B
                         WHERE     B.ID1 = A.ID1);

11 잠금 상태 오브젝트 조회

SELECT A.SESSION_ID
     , B.SERIAL#
     , A.OS_USER_NAME
     , A.ORACLE_USERNAME
     , C.OBJECT_NAME
     , A.LOCKED_MODE
     , A.XIDUSN

  FROM V$LOCKED_OBJECT A

     , V$SESSION B
     , DBA_OBJECTS C

 WHERE A.OBJECT_ID = C.OBJECT_ID AND A.SESSION_ID = B.SID;

12 LOCK 잠금 SQL 구문 조회

SELECT B.USERNAME AS USERNAME
     , C.SID AS SID
     , C.OWNER AS OBJECT_OWNER
     , C.OBJECT AS OBJECT
     , B.LOCKWAIT
     , A.PIECE
     , A.SQL_TEXT AS SQL
  FROM V$SQLTEXT A
     , V$SESSION B
     , V$ACCESS C
 WHERE A.ADDRESS = B.SQL_ADDRESS 
   AND A.HASH_VALUE = B.SQL_HASH_VALUE 
   AND B.SID = C.SID AND C.OWNER!= 'SYS';

13 해당 테이블의 세션을 제거하는 쿼리 KILL / DISCONNECT

/*

   특정 테이블이 락을 발생하고 있으면 세션을 찾아서 중단시킨다.

*/ 
-- KILL
SELECT 'ALTER SYSTEM KILL SESSION '''|| S.SID||','||S.SERIAL# ||''';' KILL_CMD
     ,  'ALTER SYSTEM DISCONNECT SESSION '''|| S.SID||','||S.SERIAL# ||'''POST_TRANSACTION/IMMEDIATE;' DISCONNCT_CMD
  FROM V$LOCK L
     , DBA_OBJECTS O
     , V$SESSION S 
 WHERE L.ID1 = O.OBJECT_ID 
   AND S.SID = L.SID 
   AND O.OWNER = 'ESTDBA' 
   AND O.OBJECT_NAME = 'TMP_GSYM2'

-- DISCONNECT
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''|| S.SID||','||S.SERIAL# ||'''POST_TRANSACTION/IMMEDIATE;' 
  FROM V$LOCK L, DBA_OBJECTS O
     , V$SESSION S 
 WHERE L.ID1 = O.OBJECT_ID 
   AND S.SID = L.SID 
   AND O.OWNER = 'ESTDBA' 
   AND O.OBJECT_NAME = 'TMP_GSYM2'

14 Blocking Lock Session 확인

SELECT B.BLOCKING_SESSION AS BLOCKING_SESSION_SID
     , C.SID AS LOCK_SESSION_SID
     , C.OWNER AS OBJECT_OWNER
     , C.OBJECT AS OBJECT
     , B.LOCKWAIT
     , A.PIECE
     , A.SQL_TEXT AS SQL
  FROM V$SQLTEXT A
     , V$SESSION B
     , V$ACCESS C
 WHERE A.ADDRESS = B.SQL_ADDRESS 
   AND A.HASH_VALUE = B.SQL_HASH_VALUE 
   AND B.SID = C.SID 
   AND B.BLOCKING_SESSION IS NOT NULL 
   AND C.OWNER NOT IN ('SYS', 'PUBLIC') 
   AND C.OBJECT NOT IN ('TOAD_PLAN_TABLE') ORDER BY A.PIECE;