본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++


LOCK 문제 확인과 해결 방법 
- 응용프로그램들의 기능이 복잡해져가면서 Lock 문제가 빈번하게 발생함에 따라
이를 해결하는 것이 매우 중요한 일이 되었다. 도대체 어떠한 프로그램이 Lock
을 발생시켜 데이타베이스를 Hangup 상태에 이르게 하는가를 알아내는 것은
쉬운 일이 아니지만 지금부터 이 문제를 해결하는 방법에 대하여 알아보도록
한다.

SQLDBA MONITOR SCREENS [V7.2 이하]
----------------------
$ TERM=vt100;export TERM
$ sqldba
[ connect internal ] -> [ key-0(ins key) ]

[ MONITOR SESSION ]
=========================================================================
Session Serial Process Status Username Lock Current
ID Number ID Waited Statement
-------------------------------------------------------------------------
6 35 28 ACTIVE LTO2 C2D2B3B4 UPDATE
8 70 19 INACTIVE SYSTEM SELECT
12 15 25 INACTIVE LTO INSERT
14 17 27 ACTIVE LTO3 C2D2B438 DELETE
15 30 26 ACTIVE SYS UNKNOWN
=========================================================================
- Session Id : SID - Session Identifier
- Serial Nr : Session Serial Nmber. Session 의 Object 별로 지정된다.
- Process Id : V$PROCESS에서 PID(Oracle Process Identifier)
- Status : Session의 상태
- Username : Oracle Username
- Lock waited: 대기중인 Lock Address

여기서 가장 중요한 컬럼은 Lock Waited 이다. 여기에 무엇인가 기록이 되어
있다면 이 세션은 특정 리소스를 얻기 위하여 대기 중인 상태임을 알 수 있다.
위의 예에서 LT02, LT03이 그 경우에 해당된다.

[ MONITOR LOCK ]
========================================================================
Session Serial Lock Resource Resource Mode Mode
Username ID Number Type ID 1 ID 2 Held Requested
------------------------------------------------------------------------
LTO2 6 35 TM 2294 0 RX NONE
LTO2 6 35 TM 2295 0 RX NONE
LTO2 6 35 TX 262167 87 NONE X
LTO2 6 35 TX 327682 90 X NONE
LTO2 12 15 TM 2294 0 RX NONE
LTO 12 15 TM 2295 0 RX NONE
LTO 12 15 TX 262167 87 X NONE
LTO3 14 17 TM 2294 0 RX NONE
LTO3 14 17 TM 2295 0 RX NONE
LTO3 14 17 TX 262167 87 NONE X
LTO3 14 17 TX 196636 87 X NONE
========================================================================
- Username : Oracle Username.
- Session Id : SID - Session Identifier.
- Serial Nr : 앞의 설명 참조

===============+==========================+============================
Type of Lock | Resource ID1 | Resource ID2
---------------+--------------------------+----------------------------
TX(Transaction)| 롤백 세그먼트 번호와 슬롯 | Wrap Number를 10진수로 변환한
| 번호를 10진수로 변환한 값 | 값(롤백슬롯이 재사용된 횟수)
---------------+--------------------------+----------------------------
TM(Table Locks)| 수정된 테이블의 Object ID | 항상 0
---------------+--------------------------+----------------------------
RW(Row Wait) | File #, Block #를 10진수 | 블럭내의 Row를 10진수로 변환
| 로 변환한 값 (V6) | 한 값
---------------+--------------------------+----------------------------
UL(User-Defined| Oracle7 Concepts Manual Ch10, Oracle7 Admin Guide
Locks) | Appendix B 81 의 리스트 참조
---------------+-------------------------------------------------------
Mode Held | 걸고 있는 Lock Mode
---------------+-------------------------------------------------------
Mode Requested | 요구한 Lock Mode
=======================================================================

* 다음 사용자들은 Lock을 기다리고 있는 상태이다.
LTO2 6 35 TX 262167 87 NONE X
LTO3 4 17 TX 262167 87 NONE X

* 이들이 기다리고 있는 Lock은 다음과 같다.
LTO 12 15 TM 2294 0 RX NONE
LTO 12 15 TM 2295 0 RX NONE
LTO 12 15 TX 262167 87 X NONE

TX Lock의 경우, 동일한 리소스에 대한 Lock을 요구하는 경우 각각의 ID1,ID2
는 동일한 값을 갖는다. 하나의 데이타 블럭 내에는 그 블럭을 사용하고 있는
트랜잭션에 대한정보가 들어있는데 만약 이 트랜잭션이 Commit, Rollback되지
않으면 다른 트랜잭션들은 그 트랜잭션이 잡고 있는 리소스의 Lock이 해제되기를
기다리게 된다.
하나의 트랜잭션이 여러 개의 테이블에 대해서 작업을 하고 있는 경우에는
Lock과 연관된 리소스가 어떤 것인지를 쉽게 파악하기가 힘들다. 이 때에는
Monitor Session과 Monitor Table을 모두 보아야지만 확실하게 알 수 있다.

[ MONITOR SESSION ]
========================================================================
Session Serial Process Lock Current
ID Number ID Status Username Waited Statement
------------------------------------------------------------------------
5 31 19 INACTIVE LTO INSERT
6 43 25 ACTIVE LTO2 C3D320F4 UPDATE
9 1 26 ACTIVE LTO3 C3D320C8 DELETE
========================================================================

[ MONITOR TABLE ]
========================================================================
Session ID Schema Name Table Name
------------------------------------------------------------------------
5 LTO DEPT
6 LTO EMP
9 LTO EMP
------------------------------------------------------------------------

* 위의 경우를 보면 Session 6와 Session 9가 LTO.EMP을 사용하려고 기다리고
있음을 알 수 있다.

해결방법
---------
대부분의 Lock 문제는 응용프로그램의 잘못된 설계 때문에 발생하게 된다.
이를 해결하기 위해서는 다음과 같은 방법으로 Lock을 Release 시켜야 한다.

1. Lock Holder에게 Commit, Rollback을 요청한다.
2. Lock Holder의 세션을 Kill시킨다.

SQLDBA>ALTER SYSTEM KILL SESSION 'sid,serial#';

3. 유닉스 Shadow Process(Server Process)를 Kill 시키는 것은 바람직하지
않다. 특히 MTS 환경 하에서는 각별한 주의가 필요하다.

4. 2PC Pending 트랜잭션인 경우는 Rollback Force, Commit Force로 해결
한다.

LOCK 문제를 일으키는 SQL 명령 찾기
----------------------------------
* 다음 Query는 Lock과 관련된 트랜잭션을 출력해준다.

column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
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)
/


( 위의 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이 풀리기를 기다리고 있음을 알 수 있다.

* 다음 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
SQLDBA>connect internal
SQLDBA>@catblock
SQLDBA>@catproc

결과는 다음 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를 기다리고 있음을 알 수 있다.

Lock & Hanging 문제를 추정하는 방법
-----------------------------------
프로그램 상에서 어느 부분이 Lock, Hanging 문제를 일으키는지 알아내기가
여의치 않을때 다음과 같은 방법을 사용해 보기 바란다.

1. init.ora의 sql_trace=ture로 세팅하면 연관된 SQL 명령이 출력될
것이다.
2. OS 상에서도 Process 상태를 점검하여 본다.
3. OS 상의 Debugging 기능을 사용하거나 만약 가능하다면 oradbx를 사용한다.
Platform 에 따라서 없을 수도 있다.
4. 여러가지 Monitoring 방법과 Locking/Blocking 스크립트를 이용한다.

PROCESS STACKS 확인
-------------------
때로는 Hanging Process나 리소스를 점유하고 있는 Process의 Process Stack을
점검해 보는 것이 문제 해결에 도움이 되는 경우가 있다.

1. OS Debugging 프로그램을 이용하여 Hangup 되기 전의 마지막 Call을 확인
한다.

ex)%truss -p

2. oradbx(오라클 debugging 프로그램)은 Support,Development 시에만 사용
된다.

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"
from 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

만약 oradbx가 없다면 다음과 같이 해서 만들어 준다.

%cd $ORACLE_HOME/rdbms/lib
%make -f oracle.mk oradbx

LTO Process가 무엇을 하고 있는지 알고 싶으면 Process Stack을 보면 알수
있다.

ps -ef | grep 17312
usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713(DESCRIPTION=(LOCAL=YE
type
debug 17312 (이 유저의 oracle shadow process)
dump stack
dump procstat

위에서 생성된 트레이스 화일(user_dump_dest 에 생성됨)을 이용하면 Hanging
문제를 해결하는데 큰 도움이 된다.

자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황
에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE
LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기
때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가
Release 되기를 기다리게 되는 경우도 있다.

Ex)
create table test (a number) initrans 1 maxtrans 1;

SYSTEM: insert into test values (5); /* completed */
SCOTT: insert into SYSTEM.test values (10); /* Scott waits */

SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- ----- ------------- ----------- ---------- -------- --------
7 System Transaction Exclusive None 196639 54
10 Scott Transaction None Share 196639 54

from oracle

laalaal~