일반 USER가 임의적으로 LOCK을 발생시키고, 관리자가 LOCK을 찾아내는 법

일반 USER가 임의적으로 LOCK을 발생시키고, 관리자가 LOCK을 찾아내는 법

 

• LOCK 발생시키기.

   

1. 우선 SYS의 SID를 확인해 놓는다. (SYS의 SID는 142이다.)

select *

from v$session

where username is not null and

username not in ('SYSMAN','DBSNMP');

 

1. Sqlplus로 User (SCOTT & HR) 접속하여 SID를 확인한다. (SID No.144 : HR, SID NO.146: SCOTT)

 

1. SCOTT이 HR에게 자신의 DEPT 테이블에 관해서 아래와 같은 권한을 할당한다.

 

1. HR은 SCOTT의 DEPT 테이블을 아래와 같이 UPDATE 한다.

 

1. SCOTT이 다시 DEPT 테이블에 관해서 아래와 같이 UPDATE를 시도한다.

(scott에서 업데이트를 하려고 해도 HR이 해당 정보를 변경한 후, commit이나 rollback을 하지 않아서 SCOTT에서 LOCK이 걸려있다.)

 

• 문제인식

이러한 사실을 SCOTT은 모르고, 업데이트가 이루어지지 않는 이유로 ADMINISTRATOR에게 질의를 한다. 이제!! Administrator는 위 문제를 해결해야만 한다.

 

 

해결하기

 

1. LOCK과 관련된 객체들을 찾는 일련의 행동들을 한다.

(방법1)   select * from v$lock where type in ('TX', 'TM'); TX: Transaction 잠금. commit이나 rollback을 수행할 때 까지 유지된다. P283 참조.

(방법2) SELECT logon_time,user#,username,osuser,machine,program FROM v$session WHERE SID IN(SELECT DISTINCT SID FROM v$lock where type='TM');

 

 

 

select distinct sid from v$lock where type in ('TX','TM');

 

 

select * from v$session where sid in (select distinct sid from v$lock where type in ('TX','TM'));

 

(위 그림에서 LOCKWAIT 컬럼을 살펴보게 되면 어느 사용자가 '대기 중' 상태인지 확인 가능하다 (=SCOTT)

 

위에서 ID1을 찾기 위해서 아래와 같이 join을 함

SELECT * FROM v$session A JOIN (SELECT DISTINCT SID FROM v$lock WHERE TYPE IN ('TX','TM') )B ON A.sid= B.SID;

SELECT logon_time,user#,username,osuser,machine,program FROM v$session WHERE SID IN(SELECT DISTINCT SID FROM v$lock where type='TM'); --좀더 (중요)필요한 정보들만 찾아온다.

 

이제 LOCK이 걸린 객체가 무엇인지 찾아본다.

 

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID IN (select id1 from v$lock where type='TM');

(SCOTT 소유의 DEPT 테이블)

 

 

이제 어떠한 LOCK이 걸려있는지 알아온다.

SELECT * FROM V$LOCKED_OBJECT;

LOCKED_MODE SELECT~FOR UPDATE에 의한 LOCK INSERT에 의한 LOCK Update에 의한 LOCK Delete에 의한 LOCK

 

 

 

이제 아래 두 스크립트를 돌려서 대기 (WAITING) 중인 객체를 알아온다.

 

--STEP1 C:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utllockt.sql 스크립트를 SQLPLUS에서 실행한다.   SQL> @ C:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utllockt.sql

--STEP2 C:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\catblock.sql 스크립트도 실행   SQL>@C:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\catblock.sql

--STEP3   SELECT B.USERNAME "USERNAME", C.SID "SID", C.OBJECT "OBJECT", 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' AND B.SID=146; --대기 중인 SID 146번 객체에 대한 정보를 알아 왔다. (146은 SCOTT)

--SETP4이제 누구 떄문인지 알아온다. SELECT * FROM DBA_WAITERS WHERE WAITING_SESSION=146 --HOLDING SESSION 컬럼을 살펴보면 144번 떄문에 대기중임을 알 수 있다.(144 는 HR)   SELECT * FROM DBA_BLOCKERS;   SELECT * FROM V$SESSION WHERE SID=144;

--STEP5 이제 144번을 죽인다 (144: SID 36 : SERIAL#) ALTER SYSTEM KILL SESSION '144,36';     그림과 같이 HR의 SESSION이 종료되어 진다. 그리고 대기 상태에 있던 SCOTT은 LOCK(잠금,대기)가 풀려 update가 실행된다.