|
데이터베이스를 사용하다 보면 개발자에 의해 실행되는 수 많은 SQL문들이 반복적으로 락을
설정하고 또 해제하게 됩니다 . 많은 사용자들이 동시에 테이블을 입력, 수정, 삭제, 조회하게 되면 서로 걸고 걸리는 락 관계가 성립되게 됩니다.
그렇다면 , 개발자가 어떤 테이블을 조작하려는데 누군가가 락을 발생시킨 상태라면 더 이상작업을 진행시키지 못하게 되고, 락을 발생시킨 사용자가 COMMIT 또는 ROLLBACK 문을 수행할 때 까지 무작정 대기해야 하는 문제가 발생하게 됩니다.
이런 경우 , 락을 유발시킨 사용자가 누구인지 알 수 있다면 락 해제를 요청할 수 있을 것이고 또한, 어떤 사용자가 어떤 SQL문을 언제 실행하여 어떤 테이블, 어떤 행에 어떤 종류의 락을 유발하고 있는지도 알 수 있다면 보다 효과적으로 락을 관리할 수 있을 것입니다.
위 그림을 보면 , 세션번호가 10번인 사용자가 EMP 테이블의 2번째 행을 UPDATE문으로 변경하고 있습니다. 아직 COMMIT문을 실행하지 않은 상태에서 세션번호가 20번인 사용자가 같은 EMP 테이블의 2번째 행을 UPDATE 하려고 합니다. 이런 경우가 발생하면 2번째 사용자의 UPDATE문은 실행되지 못하고 대기상태에 빠지게 됩니다. 첫 번째 사용자가 트랜잭션을 빠르게 종료 시킨다면 문제가 없겠지만 그렇지 못한 경우에는 무한정 대기상태에 빠지게 됩니다.
이런 경우 , 오라클사에서 제공하는 UTLLOCKT.SQL 스크립트를 실행하면 어떤 세션에서, 어떤 종류의 락을, 어떤 문장으로 실행했고 그 문장으로 인해 대기상태에 빠진 세션은 어떤 정보을 가지고 있는지를 상세히 제공해 줍니다.
| SQL > |
CONNECT system/manager |
| SQL > |
@$HOME/rdbms/admin/utllockt.sql
USERNAME SID OBJECT SQL |
|
SCOTT 10 EMP update emp set sal = sal * 1.1
where empno = 7934
SCOTT 20 EMP update emp set sal = sal * 1.1
where empno = 7934 |
ß 분석결과를 보면, 10번 세션번호를 가진 사용자에 의해 UPDATE문이 실행되었고 행-레벨의 락으로 인해 20번 세션번호를 가진 사용자의 세션이 대기상태에 있는 것을 확인할 수 있습니다.
락의 원인이 분석되었으면 신속히 락을 해제해야 하는데 락을 해제하는 방법은 2가지가 있습니다. 첫 번째 방법은 락을 유발한 세션에서 COMMIT 또는 ROLLBACK문을 실행하는 방법이고 두 번째 방법은 ALTER SYSTEM KILL SESSION 명령어로 강제로 세션을 종료시키는 방법입니다. 대부분의 경우는 COMMIT, ROLLBACK 문장에 의해 자동적으로 락을 해제하게 되고 데드-락(Dead-Lock)과 같이 더 이상 락을 해제할 수 없는 상황에는 ALTER SYSTEM KILL SESSION 명령어를 사용하게 됩니다.
< 문법 > SELECT sid, serial# FROM v$session;
ALTER SYSTEM KILL SESSION ‘[sid], [serial#]'; | |
자 ~ 다음 예제를 따라 해 보십시오.
실습을 위하여 3개의 세션(session)을 Open하십시오. 2개 세션은 SCOTT 사용자로 나머지 하나는 “/as sysdba”로 접속합니다.
1) 첫 번째 SCOTT Session에서 한 사원의 월급을 변경(Update) 합니다.
먼저 , DEPT, EMP 테이블을 새롭게 생성하고 다음과 같이 제약조건을 설정하십시오
|
$ sqlplus scott/tiger
SCOTT(1).SQL> @$ORACLE_HOME/sqlplus/demo/demobld
$ sqlplus scott/tiger
SCOTT(1).SQL> alter table dept
add constraint dept_deptno_pk primary key(deptno) ;
SCOTT(1).SQL> alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno);
SCOTT(1).SQL> update emp
set sal = sal * 1.1
where empno = 7934 ; ß |
UPDATE문에 의해 LOCK이 발생합니다
2) SYS Sesseion에서 데이터베이스의 Lock 상태를 확인해 봅니다.
|
$sqlplus “/as sysdba” |
| SYS.SQL> |
select username, sid
from v$session
where username = ‘SCOTT'; |
ß 현재 Lock을 유발하고 있는 사용자명과 Session-ID를 분석하십시오
<질문> 사용자명은 ?
세션 ID 는 ?
| SYS.SQL> |
SYS.SQL> select sid, type, id1, lmode, request
from v$lock
where sid = <Session ID>; |
ß 조금 전 분석된 Session-ID
이번에는 Lock이 발생한 Object가 어떤 것 인지 알아 봅시다.
| SYS.SQL> |
select object_name
from dba_objects
where object_id in ( ID1 컬럼의 값 ); |
ß 위 문장 V$LOCK을 실행했을 때 해당 SID의 TYPE 컬럼이 ‘TM' lock이 걸린 행의
ID1 컬럼의 값을 괄호 안에 지정하십시오.
OBJECT_NAME
EMP ß EMP 테이블이 DML문에 의해 Lock 발생하고 있습니다.
다음과 같이 V$LOCKED_OBJECT 자료사전을 통해서도 알 수 있습니다.
SYS.SQL> col ORACLE_USERNAME format a10
SYS.SQL> col OS_USER_NAME format a12
SYS.SQL> select OBJECT_ID, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE
from v$locked_object
OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE | |
5285 7 SCOTT dba9i101 3
OBJECT_ID : Lock이 유발된 객체번호
SESSION_ID : Lock을 유발한 사용자의 세션번호
ORACLE_USER : Lock을 유발한 사용자명
OS_USER_NAME : Lock을 유발한 사용자의 O/S 계정
LOCKED_MODE : 1 SELECT ~ FOR UPDATE에 의한 Lock
2 INSERT에 의한 Lock
3 UPDATE에 의한 Lock
4 DELETE에 의한 Lock | |
3) 두번째 SCOTT Session 에서 EMP 테이블을 삭제합니다… 삭제가 될까요 ?
|
$ sqlplus scott/tiger |
| SCOTT(2).SQL> |
drop table emp;
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified |
ß 이 문장이 실행되기 위해서는 Exclusive Table Lock이 필요한데, 첫번째 SCOTT Session이 EMP 테이블에 대해 Row Exclusive Table Lock 잡고 있기 때문에 DROP 문장은 실행될 수 없습니다.
두번째 SCOTT Session 에서 EMP 테이블의 다른 행을 변경해 봅시다.
| SCOTT(2).SQL> |
update emp
set sal = sal * 1.1
where empno = 7902 ; |
ß SYS Session애서 현재 데이터베이스에 어떤 Lock이 발생했는지 알아봅시다.
| SCOTT(2).SQL> |
select username, sid
from v$session
where username = ‘SCOTT' ;
USERNAME SID |
|
SCOTT 7
SCOTT 9 새롭게 Lock이 발생한 세션 |
<질문> 사용자명은 ? 세션 ID 는 ?
| SYS.SQL> |
select sid, type, id1, lmode, request
from v$lock
where sid = <Session ID>; ß 조금 전 분석된 Session-ID |
| SYS.SQL> |
select OBJECT_ID, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE
from v$locked_object;
OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE |
|
5285 7 SCOTT dba9i101 3
5285 9 SCOTT dba9i101 3 ß 새로운 Lock 정보
두번째 SCOTT session 에서 다음과 같이 UPDATE문을 실행해 보십시오.
SCOTT(2).SQL> rollback;
SCOTT(2).SQL> update emp set sal = sal * 1.1 where empno = 7934 ;
이미 SCOTT(1) Session에서 UPDATE문을 진행하고 있기 때문에 Lock이 발생되어 있기 때문에 변경할 수 없습니다.( Waiting 상태 ) |
SYS Session 에서 어떤 lock이 발생했는지 알아봅시다.
| SYS.SQL> |
select sid, type, id1, lmode, request
from v$lock
where request <> 0; ß request <> 0 이면 waiting 중인 Lock정보를 검색합니다.
SID TY ID1 LMODE REQUEST |
------------------------------------------------
9 TX 131077 0 6
ß 세션ID 9번이 현재 Waiting 상태임을 알 수 있습니다.
또는 , 다음과 같은 방법으로 Waiting 중인 세션을 분석할 수 있습니다.
SYS.SQL> SQL> select XIDUSN, OBJECT_ID, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE
from v$locked_object
where XIDUSN = 0 ; ß XIDUSN = 0 이면 waiting 중 세션
XIDUSN OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE
---------- ---------- ---------- ---------- ------------ -----------
0 5285 9 SCOTT dba9i101 3
이번에는 어떤 사용자의 어떤 세션에서 어떤 Object에 대해 어떤 SQL문을 실행하여 Lock이 발생하였는지를 분석하는 방법입니다.
SYS.SQL> @$ORACLE_HOME/rdbms/admin/utllockt.sql
SYS.SQL> col username format a8
SYS.SQL> col sid format 999
SYS.SQL> col object format a12
SYS.SQL> col sql format a50
SYS.SQL> 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';
USERNAME SID OBJECT SQL
-------- ---- ------------ --------------------------------------------------
SCOTT 9 EMP update emp set sal = sal * 1.1
where empno = 7934
SCOTT 9 EMP update emp set sal = sal * 1.1
where empno = 7934
결과를 보고 lock을 잡고 있는 Session을 강제로 Kill하면 Lock이 풀리게 됨으로 다음 트랜잭션은 실행됩니다.
SQL> select sid, serial#, username from v$session where sid = ???;
SID SERIAL# USERNAME
---- ---------- --------
9 81 SCOTT
SQL> alter system kill session ‘9, 81' ;
ß 위 문장에서 분석된 Session-ID와 Seria-No로 Kill하십시오.
SCOTT(2) Session으로 이동해서 Enter-Key를 눌러 보십시오.
이 세션은 강제로 Kill 되었습니다..
SCOTT(2).SQL> update emp
set sal = sal * 1.1
where empno = 7934 ; ß 새로운 SQL문 작성 후 Enter
ERROR at line 1:
ORA-00028: your session has been killed
때로는 , 특정 사용자가 실행한 트랜잭션이 계속해서 Lock을 유발하고 있을 때 DBA는 해당 작업을 분석하여 강제로 세션을 종료시켜야 합니다.
< 잠깐 > 기타 관련사전
1) DBA_WAITERS
이 자료사전은 다른 사용자의 DML문이 실행된 후 락이 발생한 행에 대해 또 다른 사용자가 DML문을 실행하게 되면 대기상태에 빠지게 되는데, 이러한 경우, 대기하는 사용자의 세션 정보를 제공합니다. 이 자료사전을 참조하기 전에 반드시 CATBLOCK.SQL 스크립트를 실행해야 합니다.
< 세션-1 >
SQL> @$HOME/rdbms/admin/catblock.sql
SQL> connect scott/tiger
SQL> update dept set loc = 'aa';
<세션-2>
$ sqlplus scott/tiger
SQL> update dept set loc = 'aa';
< 세션-1 >
SQL> SELECT WAITING_SESSION HOLDING_SESSION LOCK_TYPE, LOCK_ID1, LOCK_ID2
FROM DBA_WAITERS;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE LOCK_ID1 LOCK_ID2
--------------- --------------- ----------------------------------
10 9 Transaction 131076 229
2) DBA_BLOCKERS : 현재 락을 유발하고 있는 모든 세션정보를 알 수 있습니다.
| SQL > |
SELECT * FROM DBA_BLOCKERS;
HOLDING_SESSION
9 | | 
락 (LOCK)
