| |
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을 좀더 키워주거나, 사이에 콜되는 리모트 서비스를 튜닝하여 수행시간을 줄여야 한다.
|