599
yesterday 1,456
visitor 24,809,376
8

오라클 락 해제, 조회

조회 수 460204 추천 수 0 2011.11.01 15:18:28

오라클 LOCK 의 종류

 

1. 대표적인 두가지 유형의 LOCK

(1).DML LOCK
- DML Lock은 여러 사용자에 의해 동시에 액세스 되고 있는
데이터의 무결성을 보장하기 위한 것이다.이들 lock은 DML 작업과 ,
DDL 작업을 동시에 충돌시키는 파괴적인 작업를 예방한다.
- DML 문은 최하 두개의 LOCK을 설정하게 된다
(SHARED LOCK(TM), EXCLUSIVE LOCK(TX) )

● 테이블 레벨 잠금(TM)
- 테이블 레벨 잠금(TM 유형)은 테이블을 수정하는 모든 DML 트랜잭션에 대해
설정된다.
- INSERT,UPDATE,DELETE, SELECT..FOR UPDATE,LCOK TABLE
- 테이블 잠금은 DDL 작업이 트랜잭션과 충돌하지 못하도록 막는다.

 

트랜잭션 1 트랜잭션 2
--------------------------------------------------------------------
SQL > update s_emp
set salary=salary*1.1; SQL> DROP TABLE S_EMP;
100 rows updated. ERROR at line 1:
ORA-00054 : resource busy and
acquire with NOWAIT specified

● 행 레벨 잠금(TX)
- insert,update,delete,select ...for update 에 의해 수정된 각 행에 대해
자동으로 행 레벨의 lock(TX)이 설정된다.
- row level lock은 다른 어떠한 사용자도 동시에 동일한 행을 수정할 수
없도록 보장된다. 따라서 다른 사용자에 의해 수정되었지만 아직 커밋되지
않은 행을 사용자가 수정할 위험이 없다.
(2)DDL LOCK
- DDL Lock은 객체가 지속적인 DDL 작업에 의해 활동 하거나 참조되는 동안
스키마 객체의 정의를 보호한다. 오라클 서버는 동일한 스키마 객체를 수정하거나
참조할지도 모르는 다른 DDL 작업을 막기위해 DDL Lock 을 자동으로 설정한다.


2. 테이블 잠금 모드(TABLE LOCK MODE)
- DML 트랜잭션에 의해 점유(획득)되는 2개의 TABLE 잠금 모드가 있으며
이들은 DML 트랜잭션에 대해 오라클서버가 자동으로 할당으로 테이블 잠금 모드이다.
- 테이블 잠금 모드의 제한성에 따라, 동일한 테이블에서 다른 테이블 잠금(LOCK)들이
점유될 수 있는 모드가 결정된다.

(1) 행 독점(ROW EXCLUSIVE,RX)
- 이 모드는 다른 트랜잭션들이 동일한 테이블의 다른 행을 동시에
INSERT,UPDATE,DELETE하거나 잠글수 있도록 허용하고
- 다른 트랜잭션들이 독점 읽기 또는 쓰기를 위해 테이블을 수동으로 잠그는 것을
막는다.

(2) 행 공유 ( ROW SHARE,RS)
- SELECT ....FOR UPDATE 문을 사용하는 질의 동안 행을 잠그도록 선택할 수 있다.
- 이 모드는 다른 트랜잭션들이 동일한 테이블의 다른 행을 동시에 질의,삽입,갱신,
삭제하거나 잠글 수 있도록 허용하고
- 다른 트랜잭션들이 독점쓰기 액세스를 위해 테이블을 수동으로 잠그는 것을 막는다.

(3) 공유 행 독점 (SHARED ROW EXCLUSIVE,SRX)
- 이것은 획득된 수동 공유 잠금 모드와 DML 문을 막는 훨씬 높은 레벨의 잠금이다.
- 암시적으로 공유 행 독점 잠금(LOCK)을 얻은 SQL문은 다시 참조 무결성과 연관된다.
다음 상황에서 부모테이블로부터 삭제할 때 자식 테이블에
공유 행 독점 잠금이 필요하다.

트랜잭션1 트랜잭션2
------------------------------------------------------------------
SQL> DELETE FROM S_DEPT SQL> UPDATE S_EMP
WHERE ID =60; SET SALARY = SALARY*1.1
1 rows deleted. WHERE ID = 28477;
Transaction 2 waits.


SQL> COMMIT;
Commit complete. 1 rows updated.

(4) 독점 (EXCLUSIVE , X )
- 이것은 최고 레벨의 테이블 잠금(LOCK) 이기 때문에, 가장 제한적인 모드로서
다른 트랜잭션이 테이블을 질의하는 것만 허용한다.
- 모든 유형의 DML과 수동 잠금 모드를 허용하지 않는다.

트랜잭션1 트랜잭션2
----------------------------------------------------------------
SQL > LOCK TABLE S_DEPT IN
EXCLUSIVE MODE;
Table Locked. SQL > SELECT * FROM S_DEPT
FOR UPDATE;
Transaction 2 waits.

(5) LOCK 문장으로 수동으로 LOCK 획득하기
SQL > LOCK TABLE TABLE_NAME IN ( ROW SHARE ) MOME;
(ROW EXCLUSIVE)
(SHARE UPDATE)
(SHARE)
(SHARE ROW EXCLUSIVE)
(EXCLUSIVE)

 

3. LOCK 의 감시 및 진단
(1) V$LOCK

중요컬럼 설명
----------------------------------------------------------------------
* SID *세션 ID ( 라고 생각됩니다. 순수한 저자의 생각)
* TYPE *TYPE 의 대표적인 유형으로 TX,TM 이 있다.
* ID1 *TYPE이 TX일 경우에는 롤백 세그먼트 번호 및 슬롯번호
TYPE이 TM일 경우에는 수정되고 있는 테이블의 ID(오브젝트 ID)
* LMODE *LOCK MODE로서 TX일 경우 6, TM일 경우 3, 0일 경우 lock 을
획득하기 위해 기다리고 있는 것임
* REQUEST *획득하려고 하는 lock의 mode를 나타내며 0이 아닐경우 lock을
획득하기 위해 대기하고 있는 것이다.

(2) V$LOCKED_OBJECT
중요컬럼 설명
---------------------------------------------------------------------
* XIDUSN * 롤백 세그먼트 번호,값이 0이라면 해당 SESSION_ID는
XIDUSN이 0이외의 다른 값을 갖고 있는 SESSION_ID에 의해 점유
되고 있는 잠금을 요청하면서 기다리고 있는 중이다.
* OBJECT_ID * 수정되고 있는 객체의 ID
* SESSION_ID * 객체를 잠그는 세션 ID

 

4. DEAD LOCK
(1) DEAD LOCK은 2 개 이상의 트랜잭션이 각각 서로가 lock을 건 데이터를 기다리고
있을 때 발생한다.


트랜잭션1 트랜잭션2
------------------------------------------------------------------------------
SQL> udpate s_emp SQL> update s_emp
set salary=salary*1.1 set salary=salary*1.1
where id = 24877; where id=24876;
1 rows updated. 1 rows updated.
-------------------------------------------------------------------------------
SQL> update s_emp SQL> update s_emp
set salary=salary*1.1 set salary=salary*1.1
where id=24876; where id=24877;
Transaction 1 waits. Transaction 2 waits.
--------------------------------------------------------------------------------
ORA-00060: deadlock
detected while
waiting for resource

- 트랜잭션 1에서 두번째 update문이 deadlock 을 탐지하여 오라클 서버는
그 문장을 롤백하고 메시지를 반환한다.
- deadlock을 박생시킨 문장은 롤백되지만 트랜잭션은 롤백되지 않는다.

 

4. Lock 의 type (V$lock에서의 "type"컬럼에 올수 있는 것들)
* MR(Media Recovery , 매체 복구)
* RT(Redo Thread, 리두 쓰레드)
* UN(User Name, 사용자명)
* UL(PL/SQL User Lock , PL/SQL 사용자 잠금(lock))
* DX(Distributed Xaction,분산 활동)
* CF(Control File,컨트롤 화일)
* IS(Instance State,인스턴스 상태)
* FS(File Set,파일 세트)
* IR(Instance Recovery,인스턴스 복구)
* ST(Disk Space Transaction,디스크 공간 트랜잭션)
* TS(Temp Segment,임시세그먼트)
* IV(Libary Cache Invalidation , 라이브러리 캐쉬 무효)
* LS(Log Start or Switch,로그 시작 또는 로그 스위치)
* RW(Row Wait, 행 대기)
* SQ(Sequence Number, 시퀀스 번호)
* TE(Extend Table,테이블 확장)
* TT(Temp Table,임시 테이블)
* TX(Transaction:행레벨 잠금 )
* TM(테이블 레벨 잠금)

 

======================================================================================================================================
======================================================================================================================================

 

출처 : http://blog.naver.com/anova91?Redirect=Log&logNo=50001516818

 

오라클 LOCK 처리 관련 SQL
                         
                                                    작성자 : 오범석
                                                    E-MAIL : refreshman@chollian.net

/********************************************************************
    SIMPLE SCRIPT TO CHECK LOCKS ON THE SYSTEM
    -- 현재 LOCK 상태를 조회한다.
********************************************************************/

SELECT s.username,
s.sid,
l.type,
l.id1,
l.id2,
l.lmode,
l.request,
p.spid PID
FROM v$lock l,
v$session s,
v$process p
WHERE s.sid = l.sid
  AND p.addr = s.paddr
  AND s.username is not null
ORDER BY id1, s.sid, request;



/*************************************************************************************
현재 LOCK 상태와 세션 문자열을 출력한다.
다음 SQL을 실행하여 Lock Requested 가 있다면 현재 wait 상태이다.
wait상태를 풀기 위해 현재 LOCK을 HELD하고 있는 SESSION을 찾아 KILL해야 한다.
찾는 방법은 다음과 같다.
세션별로 Lock Requested가 있는 쿼리의 문장과 Lock Requested가 없는 문장을 비교하여 
쿼리문의 access object가 같다면 Lock Requested가 없는 문장의 세션을 kill 하면 된다.
세션 KILL 구문 ( ALTER SYSTEM KILL SESSION 'Session ID,Serial_NO')을 하면 된다.

참고로 $ORACLE_HOME/rdbms/admin/utllockt.sql 이라는 script가 있다.
이 script는 현재 lock에 대해 tree형식으로 출력한다.
이 script를 사용하면 쉽게 어떤 세션이 어떤 세션의 lock을 wait하는지 확인이 된다.
*************************************************************************************/

set linesize 132 pagesize 66

--break on kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a15 heading "Table Name"
column owner format a9
column Address format a18

SELECT NVL(S.USERNAME,'Internal') username,
NVL(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) tab,
DECODE(L.LMODE, 1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',null) lmode,
DECODE(L.REQUEST, 1, 'No Lock',
  2, 'Row Share',
  3, 'Row Exclusive',
  4, 'Share',
  5, 'Share Row Exclusive',
  6, 'Exclusive',null) request,
SQL.SQL_TEXT SQL_TEXT
FROM V$LOCK L,
V$SESSION S,
V$SQLTEXT SQL,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
  AND S.SQL_ADDRESS = SQL.ADDRESS
  AND S.SQL_HASH_VALUE = SQL.HASH_VALUE
  AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)
  AND U1.USER# = T1.OWNER#
  AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5;



--해당 세션의 SQL statement를 확인한다.

SELECT sqltext
FROM v$sqltext a, v$session b
WHERE a.address = b.sql_address
  AND a.hash_value = b.sql_hash_value
  AND b.sid = [세션 ID]
ORDER BY peice;

 

 

======================================================================================================================================

======================================================================================================================================

 

출처 : http://blog.naver.com/chaos78?Redirect=Log&logNo=10051065925

 

 

1. 테이블에 락걸린 정보 확인

 

SELECT B.TYPE, C.OBJECT_NAME, 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='테이블 명';

 

2. 확인 된 정보에서 SID, SERIAL# 번호를 통해서 락을 KILL 한다.

  ALTER SYSTEM KILL SESSION 'SID, SERIAL#'

  ex ) ALTER SYSTEM KILL SESSION '58, 6015';

 

 

- 락정보 확인

SELECT 
    A.SESSION_ID AS SESSION_ID,
    B.SERIAL# AS SERIAL_NO,
    A.OS_USER_NAME AS OS_USER_NAME,
    A.ORACLE_USERNAME AS ORACLE_USERNAME,
    B.STATUS AS STATUS
FROM V$LOCKED_OBJECT A, V$SESSION B
WHERE A.SESSION_ID = B.SID;

 

 

- 락 검사

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',
        'ST', 'Disk Space Transaction',
        'IR', 'Instance Recovery',
        'ST', 'Disk Space Transaction',
        'TS', 'Temp Segment',
        'IV', 'Library Cache Invalidation',
        'LS', 'Log Start or Switch',
        'RW', 'Row Wait',
        'SQ', 'Sequence Number',
        'TE', 'Extend Table',
        'TT', 'Temp Table',
        A.TYPE) LOCK_TYPE,
    DECODE(A.LMODE,
        0, 'None', /* MON LOCK EQUIVALENT */
        1, 'Null', /* N */
        2, 'Row-S (SS)', /* L */
        3, 'Row-X (SX)', /* R */
        3, 'Row-X (SX)', /* R */
        4, 'Share', /* S */
        5, 'S/Row-X (SSX)', /* C */
        6, 'Exclusive', /* X */
    TO_CHAR(A.LMODE)) MODE_HELD,
    DECODE(A.REQUEST,
        0, 'None', /* MON LOCK EQUIVALENT */
        1, 'Null', /* N */
        2, 'Row-S (SS)', /* L */
        3, 'Row-X (SX)', /* R */
        4, 'Share', /* S */
        5, 'S/Row-X (SSX)', /* C */
        6, 'Exclusive', /* X */
    TO_CHAR(A.REQUEST)) MODE_REQUESTED,
    TO_CHAR(A.ID1) LOCK_ID1,
    TO_CHAR(A.ID2) LOCK_ID2
FROM V$LOCK A
WHERE (ID1,ID2) IN
    (SELECT B.ID1, B.ID2 FROM V$LOCK B
     WHERE B.ID1=A.ID1
        AND B.ID2=A.ID2 AND B.REQUEST>0)

 

 

<오락클 락 계정 확인>
SQL> select username, account_status from dba_users;

 

<오라클 락 계정 해제>
SQL> alter 계정 account unlock;

 

 

'헬로마켓'과 함께하는 스마트한 중고 아이템 거래

https://www.hellomarket.com


1
profile

기펴^^

October 08, 2014
*.216.23.202

감사합니다. 덕분에 테이블 락 문제를 해결했습니다 ^^;;

문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수sort

Program Note 로그인 :)