[오라클]3-3단계 Lock의 경합

 

락 (LOCK) DML문을 실행하면 해당 트랜잭션에 의해 발생한 데이터가 다른 사용자에 의해 변경이 발생하지 않도록 LOCK(잠금현상)을 발생시킵니다. 그 이유는 진행중인 트랜잭션의 작업이 완료될 때 까지는 다른 사용자들로부터 보호되어야 하기 때문입니다. 이러한 현상은 COMMIT 또는 ROLLBACK문이 실행되면 자동적으로 해제가 됩니다. 다음 내용은 잠금현상의 주요 특징입니다 . - 데이터의 검색이 일관되게 해 줍니다. - DML문장이 실행될 때 ROLLBACK을 위해 오라클 서버가 변경 전 데이터와 변경 후 데이터를 모두 보관합니다. - 테이블을 직접 변경하고 있는 세션에서 만 변경중인 데이터를 확인할 수 있으며 다른 세션에서는 변경 중인 데이터를 확인할 수 없습니다. - 어떤 사용자가 변경 중인 행을 다른 사용자가 변경할 수 없습니다. 변경 중인 사용자에 의해 COMMIT 또는 ROLLBACK문이 실행된 후 변경할 수 있습니다. LOCK의 종류 오라클 사에서 제공하는 락의 종류는 행 -레벨 락과 테이블-레벨 락, 두 가지 종류가 있으며 데이터베이스를 설치하면 기본적으로 행-레벨 락이 기본 모드입니다. 다음은 현재 사용하고 있는 데이터베이스가 어떤 락 -모드인지 확인하는 방법입니다. SQL > show parameter always_content SQL > show parameter row_locking NAME TYPE VALUE row_locking string always ß 행-레벨 락 모드 ROW_LOCKING = INTENT ß 테이블-레벨 락 모드 1) ROW-LEVEL의 LOCK 행 -레벨 락이란 SELECT~FOR UPDATE, UPDATE, INSERT, DELETE문에 의해 해당 행에만 발생하는 락 종류입니다. 다음 예제를 따라 해 보십시오. <세션1> SQL > SELECT * FROM emp WHERE empno = 7844; SQL > UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844; SQL> <세션2> SQL > UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844; ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 ROW-LEVEL LOCK 발생 <세션1> SQL > COMMIT; <세션2> SQL> ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK 해제 또는 <세션1> SQL > SELECT * FROM emp WHERE empno = 7844 FOR UPDATE; ß SELECT ~ FOR UPDATE 문장은 SELECT문이지만 LOCK이 발생합니다. 검색되는 ROW들은 앞으로 변경되어질 행으로 미리 LOCK을 설정합니다. SQL > <세션2> SQL > UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844; ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 ROW-LEVEL LOCK 발생 <세션1> SQL > ROLLBACK; <세션2> SQL > ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK이 해제되고 문장이 실행됨 2) TABLE-LEVEL의 LOCK 변경중인 데이터가 특정 행들이 아닌 테이블 전체인 경우에는 테이블 -레벨 락이 발생합니다. (DML문이 실행되었지만 WHERE 조건절이 없는 경우) 오라클 사에서 제공하는 테이블-레벨 락에는 3가지 종류가 있습니다. - S LOCK : 테이블 전체 행에 설정되는 락 종류이며 마스터-디테일(Master-Detail) 관계 또는 부모-자식(Paraent-Child) 관계를 가진 2개의 테이블에서 부모 테이블이 변경될 때 자식 테이블에 발생하는 테이블-레벨 락 입니다. 또는 다음과 같은 명령어에 의해 수동으로 발생시킬 수 있습니다. < 문법 > LOCK TABLE [테이블명] IN SHARE MODE; - SRX LOCK : S 락과 같은 경우에 발생하며, 차이점은 부모 테이블이 생성될 때 자식 테이블의 FOREIGN-KEY 컬럼에 대한 부모 테이블의 컬럼이 ON DELETE CASCADE 절에 의해 생성되어 있고 FOREIGN-KEY 컬럼에 인덱스가 생성되어 있지 않은 경우입니다.( 다음 페이지에서 자세히 소개됩니다.) 다음과 같은 명령어에 의해 수동으로 발생시킬 수 있습니다 . < 문법 > LOCK TABLE [테이블명] IN SHARE ROW EXCLUSIVE MODE; - X LOCK : 가장 강력한 범위의 테이블-레벨 락 입니다. X 락이 발생하면 어떤 종류의 다른 락을 발생시킬 수 없습니다. < 문법 > LOCK TABLE [테이블명] IN EXCLUSIVE MODE; <세션1> SQL > DELETE emp; ß SQL > ß 전체 테이블을 삭제하기 때문에 TABLE-LEVEL LOCK이 발생 SQL > <세션2> SQL > UPDATE emp SET sal = sal * 1.1 WHERE deptno = 20; ß Waiting 발생 : 세션1에서 삭제 중인 행이기 때문에 TABLE-LEVEL LOCK 발생 <세션1> SQL > COMMIT; <세션2> 0 행이 갱신되었습니다. ß 세션1에서 COMMIT이 실행되었기 때문에 EMP 테이블에는 ROW가 존재하지 않으므로 에러가 발생합니다. 또는 SQL > LOCK TABLE emp IN EXCLUSIVE MODE; ß TABLE-LEVEL LOCK을 유발시키는 명령어 SQL > UPDATE emp SET sal = sal * 1.1 WHERE empno = 7954; SQL > ß 하나의 행 만 변경하지만 TABLE-LEVEL LOCK 발생 <세션2> SQL > UPDATE emp SET sal = sal * 1.1 WHERE empno = 7954; ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 TABLE-LEVEL LOCK 발생 <세션1> SQL > COMMIT; <세션2> SQL > ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK 해제 S 락과 SRX 락의 튜닝 위 그림을 보면 , 마스터-디테일(Master-Detail) 관계 또는 부모-자식(Paraent-Child) 관계를 가진 2개의 테이블이 있습니다. 부서정보 테이블에 부서번호 컬럼은 식별키(Primary Key)이고 사원정보 테이블에 부서번호 컬럼은 부서정보 테이블의 부서번호 컬럼을 참조하는 외부키 (Foreign-Key) 입니다. SQL > CREATE TABLE DEPT (DEPTNO NUMBER(2) PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ); SQL > CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) REFERENCES DEPT (DEPTNO) ); 이와 같은 , 부모-자식 관계를 가진 2개의 테이블에서 부모 테이블의 컬럼이 변경될 때 자식 테이블에 테이블-레벨 락이 발생합니다. SQL > DELETE FROM dept WHERE deptno = 10; SQL > 테이블 -레벨의 락이 발생하는 이유는 2개의 테이블에 저장되어 있는 공통 컬럼은 서로 참조하는 관계를 가지고 있기 때문에 부모 테이블에 저장되어 있지 않은 컬럼 값이 자식 테이블에 존재할 수 없기 때문입니다. 그래서, 부모 테이블에 대해 변경이 발생하면 자식 테이블 전체도 S 테이블 락을 발생하는 것 입니다. 문제점은 자식 테이블의 경우 , 실제적인 DML문이 아닌 부모 테이블로 인해 테이블-레벨 락이 발생했기 때문에 락이 해제될 때 까지 무작정 기다려야 한다는 점입니다. 자식 테이블에 대해 더 이상의 DML문을 수행할 수 없는 문제가 발생하게 됩니다. 이런 문제를 해결하기 위한 유일한 방법은 자식 테이블의 외부키 (Foreign-Key) 컬럼에 인덱스를 생성하는 방법입니다. SQL > CREATE INDEX I_emp_deptno ON dept(deptno); 외부키 컬럼에 인덱스를 생성하면 S 락 또는 SRX 락이 발생할 때 자식 테이블에 대해 테이블-레벨 락을 발생시키지 않고 인덱스에 락을 발생시키기 때문에 자식 테이블에 대해 계속적인 DML 작업을 수행할 수 있게 됩니다. 즉 , 이런 문제로 인한 대기상태를 피할 수 있으므로 성능이 개선될 수 있습니다. 락 모니터링 데이터베이스를 사용하다 보면 개발자에 의해 실행되는 수 많은 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