DB Lock 감지 및 분석 방법
작성자 관리자 작성시간 2008-01-18 16:26:20
 

1. lock_sqlt.sql
col usr        format a15      heading 'USR(DB/OS)'
col sid        format 9999    heading 'SID'
col ser        format 9999999  heading 'Serial#'
col status      format a8      heading 'Status'
col ty          format a4      heading 'Type'
col held        format a4      heading 'Held'
col req        format a4      heading 'Wait'
col id1        format 9999999  heading 'Lock ID1'
col id2        format 9999999  heading 'Lock ID2'
col ctime      format 99999    heading 'RT(s)'
col program    format a15      heading 'Program'
col process    format a7      heading 'CliPID'
col spid        format a7      heading 'OraPID'
col sql_text    format a120    heading 'SQL Statement'

set pages 10000 lin 120

select /*+ rule */
        substr(s.username||'/'||s.osuser,1,20) usr,
        s.sid sid, s.serial# ser,
        s.status,
        l.type ty,
        decode(lmode,1,'NONE',2,'SS',3,'SX',4,'S',5,'SSX',6,'X') held,
        decode(request,1,'NONE',2,'SS',3,'SX',4,'S',5,'SSX',6,'X') req,
        id1, id2, l.ctime,
        substr(s.program,1,15) program, s.process, p.spid,
        t.sql_text
from  v$sqltext t , v$process p, v$session s, v$lock l
where l.sid    = s.sid
  and s.paddr  = p.addr
  and s.username is not null
  and l.type in ('TX', 'DX')
  and t.address (+) = s.sql_address
  and t.hash_value (+) = s.sql_hash_value
order by id1, id2, held, s.sid
/

2. 출력되는 컬럼 설명
USR(DB/OS)  DB유저명과 Client 프로그램을 수행한 OS 유저명
SID    세션 ID. Lock을 건 session을 죽이고 싶다면 alter system kill session 'sid, seiral#';
Serial#        세션내의 순차번호. 위의 SQL문 등에 필요하다.
STATUS          오라클에서 해당 SQL문 현상 수행중(ACTIVE)인지 수행이 끝났는지(INACTIVE) 상태
Type            LOCK 타입
Held            LOCK 타입이 TX이고, Held가 X이면 Lock을 잡고 있는 세션
Wait            LOCK 타입이 TX이고, Wait가 X이면 Lock이 풀리기를 기다리는 세션
Lock ID1        LOCK 구분 ID1
Lock ID2 LOCK 구분 ID2 동일한 LOCK인지를 구별할 수 있는 LOCK 고유 번호
RT(s)  수행한 시간
Program        클라이언트의 프로그램명(tmax의 서버프로세스나 jeus의 dbpool 등)
CliPID  클라이언트 프로세스의 PID
OraPID  오라클서버프로세스의 PID
SQL Statement  마지막 수행한 SQL문 (주의, 꼭 Lock을 건 SQL문이 아닐 수도 있다.)
                즉, Update를 해서 lock을 건 후, select 문을 수행한다면, 이 컬럼에는 select문이 나온다.

< SQL Statement가 잘못나오는 예제 >
SCOTT/tmadm        54    2658 I  TX  X          655395  102140  1291 sqlplus@RC      29741  11638
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
-> sqlplus에서 update문을 수행하여 lock을 걸었는데, 실제 조회시 PROCEDURE 콜 루틴이 보임.
이는 sqlplus에서 User SQL(update)을 수행 후 내부적으로 위의 프로시져를 콜하기 때문에,
마지막 수행된 프로시져콜 문이 나오게 된다.



3. 실제 테스트를 통한 수행 결과 및 Lock 분석

< nonxa(sample1) 에서 lock 걸구, 분산트랜젝션 2개(xa1, xa2)가 waiting 일때 >

USR(DB/OS)        SID  Serial# STATUS  Type Held Wait Lock ID1 Lock ID2  RT(s) Program        CliPID  OraPID
--------------- ----- -------- -------- ---- ---- ---- -------- -------- ------ --------------- ------- -------
SQL Statement
----------------------------------------------------------------------
--------------------------------------------------
SCOTT/tmadm      608      308 ACTIVE  DX  X              483        0      5 xa2@RCNW4 (TNS  25688  17324
update lock_test  set b='ann' where a=1

SCOTT/tmadm        24    1934 ACTIVE  DX  X              484        0      7 xa1@RCNW4 (TNS  25686  17290
update lock_test  set b='aaa' where a=1

SCOTT/tmadm        35    2581 INACTIVE TX  X          5242959    54516    12 sample1@RCNW4 ( 25685  17281


SCOTT/tmadm        24    1934 ACTIVE  TX        X    5242959    54516      7 xa1@RCNW4 (TNS  25686  17290
update lock_test  set b='aaa' where a=1

SCOTT/tmadm      608      308 ACTIVE  TX        X    5242959    54516      5 xa2@RCNW4 (TNS  25688  17324
update lock_test  set b='ann' where a=1

- Type이 TX인 것 중, Held가 X인 것이 lock을 잡고 있는 놈이고, Wait가 X인 것이 lock을 Waiting하는 것이다.
- 아울러, 어떤 Lock을 waiting하는지는 Lock ID1,2가 같은지를 따져보면 된다.
- 즉 sample1이 Lock ID1,2가 5242959, 54516인 Lock을 잡고 있고, xa1, xa2가 같은 Lock을 Waiting 하고 있다.
- nonxa가 lock을 건 경우, nonxa에서 수행한 sql문은 나오지 않는다.
- xa1과 xa2가 분산트랜젝션이므로 각각 Type이 DX인 정보가 하나씩 더 나옴을 알 수 있다.

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

< xa1이 lock을 걸고, sample1, xa2 순으로 LOCK Waiting >
SCOTT/tmadm      608      423 ACTIVE  DX  X              482        0    10 xa2@RCNW4 (TNS  25845  18957
update lock_test  set b='ann' where a=1

SCOTT/tmadm        35    2591 INACTIVE TX  X          2228308  105554    16 xa1@RCNW4 (TNS  25842  18912
update lock_test  set b='aaa' where a=1

SCOTT/tmadm        39      157 ACTIVE  TX        X    2228308  105554    12 sample1@RCNW4 ( 25844  18925
update lock_test  set b='ppp' where a=1

SCOTT/tmadm      608      423 ACTIVE  TX        X    2228308  105554    10 xa2@RCNW4 (TNS  25845  18957
update lock_test  set b='ann' where a=1

- XA가 Lock을 건 경우, xa에서 수행한 sql문을 확인할 수 있다.
- 단, Lock을 건 XA 프로그램(xa1)이 분산트랜젝션인지에 대한 정보(type = DX)는 나오지 않는다.
- 즉, Lock을 건 프로그램의 SQL문이 보이면, DX 정보가 없어도 그 프로그램은 XA프로그램이다.

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

< xa1이 lock을 걸고, xa2가 LOCK Waiting >

USR(DB/OS)        SID  Serial# STATUS  Type Held Wait Lock ID1 Lock ID2  RT(s) Program        CliPID  OraPID
--------------- ----- -------- -------- ---- ---- ---- -------- -------- ------ --------------- ------- -------
SQL Statement
----------------------------------------------------------------------
--------------------------------------------------
SCOTT/tmadm        26    2471 ACTIVE  DX  X              484        0      6 xa2@RCNW4 (TNS  25949  19959
update lock_test  set b='ann' where a=1

SCOTT/tmadm        39      172 INACTIVE TX  X          655435  102144      9 xa1@RCNW4 (TNS  25948  19948
update lock_test  set b='aaa' where a=1

SCOTT/tmadm        26    2471 ACTIVE  TX        X      655435  102144      6 xa2@RCNW4 (TNS  25949  19959
update lock_test  set b='ann' where a=1

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

< xa1이 lock을 걸고,  xa2, sample1 순으로 LOCK Waiting >
USR(DB/OS)        SID  Serial# STATUS  Type Held Wait Lock ID1 Lock ID2  RT(s) Program        CliPID  OraPID
--------------- ----- -------- -------- ---- ---- ---- -------- -------- ------ --------------- ------- -------
SQL Statement
----------------------------------------------------------------------
--------------------------------------------------
SCOTT/tmadm      441        5 ACTIVE  DX  X              484        0    13 xa2@RCNW4 (TNS  26142  22868
update lock_test  set b='ann' where a=1

SCOTT/tmadm      210        5 INACTIVE TX  X          4980816    54322    28 xa1@RCNW4 (TNS  26141  22723
update lock_test  set b='aaa' where a=1

SCOTT/tmadm      441        5 ACTIVE  TX        X    4980816    54322    13 xa2@RCNW4 (TNS  26142  22868
update lock_test  set b='ann' where a=1

SCOTT/tmadm      546        5 ACTIVE  TX        X    4980816    54322      7 sample1@RCNW4 ( 26154  22890
update lock_test  set b='ppp' where a=1


4. Lock을 건 세션 강제로 죽이기
1) Oracle session 죽이기
alter system kill session 'sid, seiral#'
마지막 예제에서 두번째 로우인 xa1이 Lock을 걸고 있고 이 프로그램에 대한 session을 kill하고자 한다면,
sys나 system으로 로긴 후 다음 SQL을 보낸다.
alter system kill session '210,5';

< 주의 >
위와 같이 조치한 경우, 일반적으로 오라클 서버프로세스도 종료되나, 경우에 따라 오라클 서버프로세스는 떠있고,
오라클내부에서 세션정보만 없어지는 경우가 있음
그 경우 tmax등에서 서비스 콜을 하면 ORA-00028: your session has been kill 같은 에러가 발생됨.
그래서 될 수 있으면 Oracle Server 프로세스를 찾아 kill하는 것이 더 좋음.

2) Oracle Server Process Kill
세션 정보 중 OraPID 값을 찾아 OS에서 kill -9 OraPID 하면 됨.
마지막 예제에서 kill -9 22723 하면 됨.
오라클 서버 프로세스를 Kill하게 되면, Oracle은 했던 작업을 rollback하고, session을 삭제함
단, rollback 시간이 수초에서 수분이 걸리기도 하므로, v$session 정보에 해당 세션이 계속 나타나기도 함.

3) client program 종료
가장 명쾌한 세션 clear 방법으로 세션정보중 Program과 CliPID로 해당 client process를 확인하여
kill -9 CliPID 또는 tmax의 경우 tmdown -i -S Program(티맥스서버) 로 client 프로그램을 종료
이 경우, Oracle 서버 프로세스가 자동 죽게 되고, 하던 작업을 rollback하고, 세션정보도 Clear 함.

4) 정리
tmax의 경우 3번 방법이 가장 안전하고,
JEUS의 경우 pool을 사용함으로 커넥션별로 kill할 수가 없음으로 Oracle Process를 찾아 Kill하는 것이 좋고,
3), 2) 모두 실패한 경우 1)번을 수행하는 것이 좋습니다.


5. Lock 관련 오라클 에러
1) ora 2049
NonXA의 경우, Lock waiting시 무한 대기지만, XA의 경우 60초(DISTRIBUTED_LOCK_TIMEOUT : 히드파라메터)만큼 기다리다가
타임아웃에러(2049)를 발생시키면서 기다리는 것을 종료한다.
이 경우, 위의 lock_sqlt.sql을 수행하여 어떤 세션이 Lock을 잡고 있는지를 확인하여 조치한다.

2) ora 1591
분산트랜젝션 2PC 작업 도중, prepare만 받고 commit이나 rollback을 못받는 경우, 일정시간 동안 기다리다가
commit이나 rollback이 없으면 해당 트랜젝션을 보류(pending) 시키게 되는데,
이 경우 해당 테이블에 대한 또다른 작업(select, insert, update, delete 등등)을 하면, 데이타가 불확실하기 때문에
1591에러를 받게 됨.
조치는 dba_2pc_pending view에서 prepared 상태인 tx를 찾아 강제 commit 또는 rollback 해주어야 함.
$ sqlplus system/manager
SQL> select local_tran_id, global_tran_id, state from dba_2pc_pending where state='prepared';
LOCAL_TRAN_ID GLOBAL_TRAN_ID        STATE 
------------- ---------------------- --------
1.8.238      V817LOC.WORLD.89f6eafb prepared

SQL> rollback force '1.8.238';
또는
SQL> commit force '1.8.238';

JEUS의 경우는 JEUS를 비정상적으로 내렸다 올리거나 강제로 sync 명령을 내리면 미처리된 분산트랜젝션을 처리해준다.
즉, commit이나 rollback을 보내줌으로 pending된 트랜젝션이 처리가 됨.

3) xa trace file에서 xa_prepare, xa_commit, xa_rollback시에 -4번 에러
Oracle Session TimeOut이 발생한 경우.
OPENINFO 절에 주는 SessTM이 있는데,
XA서비스 콜을 종료하고(xa_end) 또다른 XA서비스 콜(xa_start)하는 사이
또는 XA서비스 콜을 종료하고(xa_end) xa_prepare 등이 들어오기까지의 idle time이 SessTM을 초과하게 되면
-4번 에러가 발생하고, 오라클은 내부적으로 rollback을 수행하게 된다.
조치는 SessTM을 좀더 키워주거나, 사이에 콜되는 리모트 서비스를 튜닝하여 수행시간을 줄여야 한다.


목록 | 입력 | 수정 | 답변 | 삭제