[Oracle] Lock Process Session Kill

오라클 LOCK 걸린 경우 Session Kill 하는 방법

일반적으로 LOCK은 다음과 같이 해결합니다.
아래의 쿼리를 system 계정에서 실행합니다.

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',
'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', /* Non 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);

위의 Query를 실행시키면 다음과 같은 내용이 출력된다.

SID LOCK_TYPE   MODE_HELD  MODE_REQUE   LOCK_ID1   LOCK_ID2
---   ---------------       ----------        ----------               --------            ------------------
5    Transaction   Exclusive        None                262172         90
6    Transaction   None              Exclusive          262172         90
9    Transaction   None              Exclusive          262172         90

SID 6과 9는 SID 5가 걸고 있는 Lock이 풀리기를 기다리고 있음을 알 수 있다.
SID 5번을 kill 하고 싶다면 다음과 같이 하면 됩니다.

SELECT sid, serial#, username from v$session where sid = ?? (여기에서는 5)

위의 값을 가지고 다음과 같이 세션을 Kill 하면 됩니다.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#'; (위의 쿼리로 나온 값)

참고로 다음과 같은 사항이 도움이 될까 하여 적어 봅니다..

* 다음 Query는 Lock과 관련된 테이블을 출력해 준다.

column username format a10
column lockwait format a10
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';
/

위의 Query를 실행하면 다음과 같은 결과가 출력된다.

USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT
--------------- --- ------------- -------------- ----------
SQL
---------------------------------------------------------------
LTO2 6 LTO EMP C3D320F4
update lto.emp set empno =25 where empno=7788
LTO3 9 LTO EMP C3D320C8
delete from lto.emp where empno=7788
LTO 5 LTO DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')

여기서는 USERNAME에 나와있는 유저가 OBJECT에 나와있는 테이블을 수정하려고 함을 나타낸다. LT02, LT03는 LT0가 Commit, Rollback 하기를 기다리고 있음을 알 수 있다. 하지만 여기에는 가장 최근의 DML 명령 하나만 나와있기 때문에 여기 나온 명령이 반드시 Lock을 걸고 있는 명령이라고 단정 지을 수는 없다.

관련된 프로세스
---------------
다음 Query를 실행해 보면 프로세스와 관련된 정보를 얻을 수 있다.

column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS';
/

위의 Query를 실행하면 다음과 같은 결과가 출력된다.

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT
USER ID ID SPID SPID
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8

 

이 때는 다음과 같이 조치한다.

1. LTO에게 Commit, Rollback 할 것을 요구한다.
2. SQLDBA>ALTER SYSTEM KILL SESSION '5,31';
3. %kill -9 17309 (유닉스상의 Shadown Process)
stop/id= (PROC SPID=SESS SPID on vms running single task)

여기서 SYS 유저는 제외시켰는데 필요하다면 Query의 Where 조건에서 s.username != 'SYS' 부분을 삭제하면 된다.

CATBLOCK.SQL & UTLLOCKT.SQL
---------------------------
$ORACLE_HOME/rdbms/admin 디렉토리에 있는 스크립트 가운데 catblock.sql과 utlockt.sql을 사용하여서 Lock 상황을 쉽게 파악할 수 있다. 이들은 다음과 같이 실행한다.

%cd $ORACLE_HOME/rdbms/admin
%sqldba lmode=y (svrmgrl
SVRMGR>connect internal
SVRMGR>@catblock

결과는 다음 Query 문으로 알아본다.

column waiting_session format a8
select lpad(' ',3*(level-1)) || waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;

위의 Query에 의한 출력은 다음과 같다.

WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
5 None
6 Transaction Exclusive Exclusive 262172 90
9 Transaction Exclusive Exclusive 262172 90

여기서 Session 6, Session 9가 Session 5를 기다리고 있음을 알 수 있다.