Lock session 확인 2010 년 01 월 27 일 DB 기술지원팀 문서정보 프로젝트명 Lock session 확인 서브시스템명 버전 1.0 문서명 작성일 2011-01-31 작성자 최종수정일 2011-01-31 문서번호 재개정이력 일자내용수정인버전 문서배포이력 발신자수신자배포목적일자비고 유니원아이앤씨 기술문서.
Table of Contents 1 오라클의 LOCK 형태... 3 1.1 DML LOCK... 3 1.2 DDL LOCK... 3 2 LOCK 의감시및진단... 3 2.1 v$lock... 3 2.2 v$locked_object... 4 2.3 DEAD LOCK... 4 3 LOCK 문제... 4 3.1 문제발생원인... 4 3.2 Lock session 확인... 5 3.3 해결방법... 5 4 TX LOCK TEST... 5 5 LOCK 의 TYPE... 7.
1 오라클의 LOCK 형태 1.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 작업이트랜잭션과충돌하지못하도록막습니다. 행레벨잠금 (TX) - insert, update, delete, select, for update 에의해수정된각행에대해자동으로행레벨의 lock(tx) 이설정됩니다. - row level lock은다른어떠한사용자도동시에동일한행을수정할수없도록보장됩니다. 따라서다른사용자에의해수정되었지만아직커밋되지않은행을사용자가수정할위험이없습니다. - 처음 DML operation에대해 COMMIT 또는 ROLLBACK때까지 lock을잡고있는것을말합니다. Queuing mechanism을사용하기때문에다른 session에서는 transaction이완전히끝날때까지기다리는것입니다. 1.2 DDL LOCK - DDL Lock은객체가지속적인 DDL 작업에의해활동하거나참조되는동안스키마객체의정의를보호한다. 오라클서버는동일한스키마객체를수정하거나참조할지도모르는다른 DDL 작업을막기위해 DDL Lock 을자동으로설정한다. 2 LOCK 의감시및진단 2.1 v$lock SQL> select * from v$lock; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ----------------------- ---------------------- -------- ----- --------- ---------- ---------- ------------ ------------ ------------- 00000003A3CDB5E0 00000003A3CDB600 165 XR 4 0 1 0 698522 0 00000003A3CDB678 00000003A3CDB698 165 CF 0 0 2 0 698522 0 3/7
- v$lock 컬럼설명 1) TY: TYPE의대표적인유형으로 TX, TM 이있습니다. 2) ID1: TYPE이 TX일경우에는롤백세그먼트번호및슬롯번호, TYPE이 TM일경우에는수정되고있는테이블의 ID( 오브젝트 ID) 3) LMODE: LOCK MODE로서 TX일경우 6, TM일경우 3, 0일경우 lock 을획득하기위해기다리고있는것입니다. 4) REQUEST: 획득하려고하는 lock의 mode를나타내며 0이아닐경우 lock을획득하기위해대기하고있는것입니다. 2.2 v$locked_object - v$locked_object 컬럼설명 1) XIDUSN: 롤백세그먼트번호, 값이 0이라면해당 SESSION_ID는 XIDUSN이 0이외의다른값을갖고있는 SESSION_ID에의해점유되고있는잠금을요청하면서기다리고있는중입니다. 2) OBJECT_ID: 수정되고있는객체의 ID입니다. 3) SESSION_ID: 객체를잠그는세션 ID입니다. 2.3 DEAD LOCK DEAD LOCK은 2 개이상의트랜잭션이각각서로가 lock을건데이터를기다리고있을때발생합니다. 트렌젝션 1, 2 SQL> udpate s_emp SQL> udpate s_emp ORA-00060: deadlock detected while waiting for resource 트랜잭션 1에서두번째 update문이 deadlock 을탐지하여오라클서버는그문장을롤백하고메시지를반환합니다. deadlock을발생시킨문장은롤백되지만트랜잭션은롤백되지않습니다. 3 LOCK 문제 3.1 문제발생원인 대부분의원인은 Application 의잘못된설계에기인합니다. 4/7
3.2 Lock session 확인 TX lock을 holding하는모든 session을보여줍니다. SQL> select * from v$lock where type='tx' and lmode>0; TX lock을 waiting하는모든 session을보여줍니다. SQL> select * from v$lock where type='tx' and request>0; 3.3 해결방법 Lock Holder에게 Commit, Rollback을요청합니다. Lock Holder의세션을 Kill 시킵니다. SQL> ALTER SYSTEM KILL SESSION sid,serial# ; 부득이한경우, OS 명령어 kill 사용 $> kill -9 12733 유닉스 Shadow Process (Server Process) 를 Kill 시키는것은바람직하지않습니다. 4 TX LOCK TEST <scott 1> SQL> conn scott/tiger SQL> update dept set loc= SEOUL where deptno=10; <scott 2> SQL> conn scott/tiger SQL> update dept set loc= BUSAN where deptno=10; - HANG 발생 <scott 3> SQL> conn scott/tiger SQL> delete from dept where deptno=10; - HANG 발생 <SYS> Holding session과 Waiting session 찾기 - Holding session을찾는실행명령문 $> vi hoding.sql SELECT DECODE(request,0,'Holder: ','Waiter: ') sid sess,id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request / SQL>@hoding.sql 5/7
Sess ID1 ID2 LMODE REQUEST TY ------------------------------- ----------- ------------ ---------------- -------------- ---------- Holder: 8 327721 211 6 0 TX Waiter: 9 327721 211 0 6 TX Waiter: 12 327721 211 0 6 TX 위에서 session 9와 12가 session 8의 Transaction이종료되기를기다리고있는것을알수있습니 다. - Waiting session 상세정보를출력하는실행명령문 $> vi lock_objects.sql column username format a10 column lockwait format a20 column sql_text format a80 column object_owner format a14 column object format a15 select b.username username, c.sid sid, c.owner object_owner, c.object object, b.lockwait, a.sql_text 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' / SQL>@lock_objects USERNAME SID OBJECT_OWNER OBJECT SQL -------------- -------- ------------------- ---------- --------------------------------------------- SCOTT 12 SCOTT DEPT delete from dept where deptno=10 SCOTT 9 SCOTT DEPT update dept set loc= BUSAN where deptno=10 위에서보면 SID 9 와 12가 DEPT에대한 DML Operation 중에 Waiting 하고있다는것을알수있습니다. - Lock 해결 : 1) SID 8을 COMMIT or ROLLBACK 을시킨다. 2) SQL> alter system kill session 8, 16 ; 3) OS 명령어 kill 사용 : kill -9 12733 - Holding session 의 SQL 문확인 6/7
SID USER_NAME SQL_TEXT ---------- ------------------------------ ------------------------------------------------------------ 8 SCOTT update dept set loc= SEOUL where deptno=10; 9 SCOTT update dept set loc= BUSAN where deptno=10; 12 SCOTT delete from dept where deptno=10; - Holding session의 Process ID 확인 SQL> select a.pid, a.spid, b.sid, b.serial# from v$process a, v$session b Where a.addr=b.paddr and b.sid=8 PID SPID SID SERIAL# --------- ---------- ------------ --------------- 10 12733 8 16 5 LOCK 의 TYPE V$lock에서의 "type" 컬럼에올수있는 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: 테이블레벨잠금 7/7