락 처리 절차
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 락 처리 절차[편집]
1.1 락 모니터링[편집]
1.1.1 락 모니터링 뷰 생성[편집]
-- DROP VIEW RTIS_DBA.VW_DBA_LOCKS;
CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS
(
LVL,
LCK_HOLD,
LCK_WAIT,
USERNAME,
OSUSER,
STATUS,
TYPE,
LOCK_TM,
HOLD,
OBJECT,
ID1,
ID2,
REQUEST,
EVENT,
MACHINE,
MODULE,
PROGRAM,
SQL_ID,
BLOCKING_SESSION,
CTIME,
OBJ#,
TYPE_NM,
ID1_TAG,
ID2_TAG,
SID,
SERIAL#,
CMD_KILL
)
BEQUEATH DEFINER
AS
SELECT /*+ ordered */
LEVEL
lvl,
NVL (s.blocking_session, s.sid)
lck_hold,
CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END
lck_wait,
s.username,
s.osuser,
s.status,
l.TYPE
TYPE,
fn_sec_to_time (l.ctime)
lock_tm,
DECODE (lmode,
0, 'NONE',
1, 'NULL',
2, 'row-S(SS)',
3, 'row-X(SX)',
4, 'share(S)',
5, 'S/Row-X(SSX)',
6, 'exclusive(X)')
HOLD,
o.name
object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo#
--, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 ) slot#
,
l.id1,
l.id2,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'row-S(SS)',
3, 'row-X(SX)',
4, 'share(S)',
5, 'S/Row-X(SSX)',
6, 'exclusive(X)')
REQUEST,
s.event,
s.machine,
s.module,
s.program,
s.sql_id,
s.blocking_session,
l.ctime,
o.obj#,
t.name
TYPE_NM,
t.id1_tag,
t.id2_tag,
s.sid,
s.serial#,
'ALTER SYTEM KILL SESSION '''
|| TO_CHAR (S.SID)
|| ','
|| TO_CHAR (S.SERIAL#)
|| ''';'
CMD_KILL
FROM v$lock l,
v$session s,
sys.obj$ o,
v$lock_type t
WHERE 1 = 1
AND s.TYPE != 'BACKGROUND'
-- and s.username = 'EP_APP'
-- and s.sid = 4179
AND l.sid = s.sid
AND l.TYPE IN ('TM', 'TX', 'UL')
AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1)
AND t.TYPE = l.TYPE
CONNECT BY PRIOR s.sid = s.blocking_session
AND PRIOR l.id1 = l.id1
AND PRIOR l.id2 = l.id2
START WITH s.blocking_session IS NULL
ORDER SIBLINGS BY lock_tm DESC, lck_hold
--order by lck_hold, l.ctime desc, l.sid;;
;
COMMENT ON TABLE RTIS_DBA.VW_DBA_LOCKS IS 'DBA_락_확인';
COMMENT ON COLUMN RTIS_DBA.VW_DBA_LOCKS.LVL IS '레벨';
1.2 lock 확인[편집]
1.2.1 lock 걸린 SERIAL_NO,SESSION_ID, OBJECT[편집]
SELECT DISTINCT t1.session_id AS session_id
,t2.serial# AS serial_no
,t1.os_user_name AS os_user_name
,t1.oracle_username AS oracle_username
,t2.status AS status
,t3.object_name
,DECODE( locked_mode
,2, 'ROW SHARE'
,3, 'ROW EXCLUSIVE'
,4, 'SHARE'
,5, 'SHARE ROW EXCLUSIVE'
,6, 'EXCLUSIVE'
,'UNKNOWN'
) lock_mode
FROM v$locked_object t1
, v$session t2
, dba_objects t3
WHERE t1.session_id = t2.SID
AND t1.object_id = t3.object_id;
1.2.2 락 걸린 세션 과 락을 잡은 세션[편집]
- RAC 환경
SELECT blocking_session, blocked_session, script
FROM (SELECT DISTINCT s1.username
|| '@' || s1.machine || ' ( INST='
|| s1.inst_id || ' SID=' || s1.sid
|| ' ET=' || s1.last_call_et || 'sn. STATUS='
|| s1.status || ' EVENT=' || s1.event
|| ' ACTION= ' || s1.action || ' PROGRAM='
|| s1.program || ' MODULE=' || s1.module
|| ')' blocking_session
, s2.username
|| '@' || s2.machine || ' ( INST='
|| s2.inst_id || ' SID=' || s2.sid
|| ' ET=' || s2.last_call_et || 'sn. STATUS='
|| s2.status || ' EVENT=' || s2.event
|| ' ACTION= ' || s2.action || ' PROGRAM='
|| s2.program || ' MODULE=' || s2.module
|| ')' blocked_session
, DECODE (s1.TYPE
, 'USER', 'alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;'
, NULL) script
, COUNT (*) OVER (PARTITION BY s1.inst_id, s1.sid) blocked_cnt
FROM gv$lock l1
, gv$session s1
, gv$lock l2
, gv$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2)
ORDER BY blocked_cnt DESC;
- 락 owner(가해자) 0 , 락 waiter(피해자) 6
SELECT S.USERNAME,
L.SID,
L.ID1,
TRUNC(L.ID1 / POWER(2, 16)) RBS,
BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
L.ID2 SEQ,
L.LMODE,
L.REQUEST
FROM V$LOCK L,
V$SESSION S
WHERE L.SID = S.SID
AND L.TYPE = 'TX'
AND S.USERNAME = USER;
@ REQUEST 0 : 락을 보유
@ REQUEST 6 : 락으로 인해 대기중인 상태
1.2.3 락 경합 사항 보기[편집]
- V$LOCK 셀프조인 모니터링
SELECT (SELECT USERNAME
FROM V$SESSION
WHERE SID = A.SID) BLOCKER,
A.SID,
' is blocking ',
(SELECT USERNAME
FROM V$SESSION
WHERE SID = B.SID) BLOCKER,
B.SID
FROM V$LOCK A,
V$LOCK B
WHERE A.ID1 = B.ID1
AND A.ID2 = B.ID2
AND A.BLOCK = 1 -- Lock을 잡고 있는 세션
AND B.REQUEST > 0 ; -- Lock으로 인해 대기하는 세션
2. session_id를 *SESSION_ID*에 넣어 락 sql 찾기
-- lock 걸린 sql
SELECT b.username username
,c.SID AS session_id
,c.owner object_owner
,c.OBJECT OBJECT
,b.lockwait
,a.sql_text SQL
,piece
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'
and c.SID = '&SESSION_ID'
ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;
1.3 세션 kill/discount/OS process kill[편집]
- SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.
- lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO[,@1]' ; --@1안 rac일경우 1번 인스턴스
- lock SESSION discount
alter system discount session 'SESSION_ID ,SERIAL_NO[,@1]' immediate; --@1안 rac일경우 1번 인스턴스
- OS Process Kill
3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다. * 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!
-- lock 걸린 PROCESS ID 찾기
SELECT DISTINCT s.username "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'
-- unix에 로그인 하여 process kill
kill -9 프로세스아이디
1.4 락 걸린 테이블 찾아서 kill[편집]
-- 1) 헤당 테이블 찾기
SELECT * FROM v$lock
WHERE id1 = (SELECT object_id
FROM all_objects
WHERE owner ='오너명'
AND object_name ='테이블명'
)
-- 2) 죽이기
SELECT * FROM v$session
WHERE sid = 찾은 sid값