락 처리 절차
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1. lock 확인
-- 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-1.락 걸린 세션 과 락을 잡은 세션
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. 락 경합 사항 보기
- 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;3. 아래 쿼리 *SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.
-- lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO' ;4. 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 프로세스아이디
5. 락걸린 테이블 찾아서 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값