by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_락킹 이슈




락킹 이슈(Locking Issue)

Lost Update

  • lost update는 고질적인 데이터베이스 문제며, 실제로 모든 다중 사용자 컴퓨터 환경에서 발생한다.
    1. Session1의 트랜잭션이 로컬 메모리에서 한 로우 데이터를 검색하고 그것을 최종 사용자 USER1에게 보여준다.
    2. Session2에서 다른 트랜잭션이 같은 로우를 검색하고 다른 최종 사용자 User2에게 그 데이터를 보여준다.
    3. User1은 애플리케이션을 사용해서 해당 로우를 변경한 후 데이터베이를 수정하고 커밋한다. Session1 트랜잭션은 이제 완료되었다.
    4. User2 또한 해당 로우를 변경하고 데이터베이스를 수정하고 커밋한다. Session2의 트랜잭션도 이제 완료되었다.
  • 위 과정을 +lost updat*라고 한다. 왜냐하면 3단계에서 변경한 내용을 읽어버리기 때문이다.
    • User1이 데이터를 변경하기 이전에 User2가 동일한 쿼리를 수행한다.
    • User1은 데이터를 수정하고 확인 메세지를 받고 데이터도 확인한다.
    • User2는 이전 데이터를 수정하고 확인 메세지를 받는다. 이와 같이 3번 과정의 데이터는 Lost 된다.

비관적 락킹

  • 사용자가 화면에 있는 값을 변경하기 바로 전에 실행되는 방법이다.
  • 로우를 선택하고 수정할 의사를 보이면 그 즉시 로우에 락을 걸고 변경하고 커밋할 때까지 계속된다.
    • 커넥션이 유지되는 환경에서만 유용하다.
SQL> select empno, ename, sal from emp where deptno = 10;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300

MILLER 로우를 선택했다고 가정한다. 바인드 콜을 Simulate 하기 위해서 다음 명령을 수행한다.

SQL> variable empno number
SQL> variable ename varchar2(20)
SQL> variable sal number
SQL> exec :empno := 7934; :ename := 'MILLER'; :sal := 1300;

PL/SQL procedure successfully completed

데이터 값을 확인한 후에 SELECT FOR UPDATE NOWAIT를 사용하여 로우에 락은 건다.

SQL> select empno, ename, sal
  2  from emp
  3  where empno = :empno
  4  and ename = :ename
  5  and sal = :sal
  6  for update nowait;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300
Note. 'decode(column, :bind_varibale ,1) = 1'은 'where (column = :bind_variable OR (column is NULL and :bind_variable is NULL)과 같다.
  • 다른 세션이 수정하는 것을 막기 위해 로우에 락을 걸고 동일한 로우를 다시 쿼리한다. 이 접근 방식을 비관적 락킹(pessimistic locking) 이라고 한다.
    • 이 로우는 다른 세션이 수정하지 못하도록(읽기는 아님) 락이 걸릴 것이다.
    • 다른 사용자가 MILLER를 수정하는 중이라면 +ORA-00054 resource busy+라는 오류가 날 것이다.
    • 만일 데이터를 선택하고 수정한다는 의사를 표시하는 사이에 어떤 사람이 벌써 로우를 변경했다면 '0'건의 로우를 리턴할 것이다. 이 데이터는 더 이상 최신 데이터가 아니라는 것을 의미한다. lost update를 피하기 위해서 다시 조회한 후 수정한다.
SQL> update emp
  2  set ename = :ename, sal = :sal
  3  where empno = :empno;

1 row updated.

SQL> commit;

Commit complete.
  • 초기에 로우를 읽고 락을 건 사이에 데이터가 변경되지 않았다는 것을 확인했기 때문에 누군가 변경해 놓은 것을 덮어쓰는 일은 불가능하다.

낙관적 락킹

  • 낙관적 락킹(optimistic locking)은 수정하기 바로 전까지 모든 락킹을 미루는 방법으로 락을 획득하지 않고 화면 정보를 변경한다.
  • 다른 사용자가 데이터를 변경하지 않을 거라 낙관적으로 생각하므로 수정 작업을 제대로 했는지 알 수 있는 마지막 순간까지 기다린다.
  • 이 방법은 사용자가 로우를 수정했는데 데이터가 변경되었다는 것을 알면 사용자는 다시 수정해야 한다.
    Update table
    Set column1 = :new_column1, column2 = :new_column2, ....
    Where primary_key = :primary_key
    And column1 = :old_column1
    And column2 = :old_column2
    ...
    
  • 위 UPDATE는 사실상 lost update를 피할 수 있지만 다른 세션이 해당 로우의 UPDATE를 완료할 때까지 블로킹될 가능성은 있다.
  • 만일 모든 애플리케이션이 낙관적 락킹을 사용한다면, UPDATE를 있는 그래로 쓰는 것도 괜찮다.
  • 낙관적 락킹을 구현할 수 있는 방법은 여러가지가 있다.
  • 우리는 다음과 같은 2가지를 통해서 알아보도록 한다.
    • 로우의 *버전*을 말해주는 특정 컬럼을 사용하는 법. 이 특정 컬럼은 데이터베이스 트리거나 Application 코드에 의해 유지된다.
    • 원래의 데이터를 이용하여 계산되는 체크섬 또는 해시값 이용하는 법.

버전 컬럼을 이용한 낙관적 락킹(Optimistic Locking Using a Version Column)

  • lost update를 방지하기 위하여 하나의 컬럼을 추가해서 테이블을 만든다.
  • 추가되는 컬럼은 NUMBER나 DATE/TIMESTAMP으로 한다.
  • 기존의 dept 테이블을 이용하여 복제 dept_1 테이블을 만든다.
    SQL> create table dept_1
      2  (deptno number(2),
      3  dname varchar2(14),
      4  loc varchar2(13),
      5  last_mod timestamp with time zone default systimestamp not null,
      6  constraint dept_1_pk primary key(deptno)
      7  );
    
    Table created.
    
  • 데이터를 입력한다.
    SQL> insert into dept_1( deptno, dname, loc )
      2  select deptno, dname, loc
      3  from dept;
    
    4 rows created.
    
    SQL> commit;
    
    Commit complete.
    
  • deptno가 '10'인 데이터를 검색한다. 그리고 해당 시간을 저장한다.
    SQL> variable deptno number
    SQL> variable dname varchar2(14)
    SQL> variable loc varchar2(13)
    SQL> variable last_mod varchar2(50)
    SQL> begin
      2  :deptno := 10;
      3  select dname, loc, last_mod
      4  into :dname,:loc,:last_mod
      5  from dept_1
      6  where deptno = :deptno;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
  • 검색한 데이터를 조회해 본다.
    SQL> select :deptno dno, :dname dname, :loc loc, :last_mod lm
      2  from dual;
    
     DNO DNAME      LOC	     LM
    ---- ---------- ------------ -----------------------------------
      10 ACCOUNTING NEW YORK     22-OCT-14 08.25.00.875694 AM +09:00
    
  • deptno가 '10'인 데이터를 검색하고 검색 당시의 시간을 조건으로 주어서 수정한다.
    SQL> update dept_1
      2  set dname = initcap(:dname),
      3      last_mod = systimestamp
      4  where deptno = :deptno
      5      and last_mod = to_timestamp_tz(:last_mod);
    
    1 row updated.
    
  • 기존에 열고 있던 화면에서 수정하면 데이터는 수정되지 않는다.
    SQL> update dept_1
      2  set dname = upper(:dname),
      3      last_mod = systimestamp
      4  where deptno = :deptno
      5     and last_mod = to_timestamp_tz(:last_mod);
    
    0 rows updated.
    

Optimistic Locking Using a Checksum

  • 오라클이제공해 주는 해쉬 또는 체크섬을 이용해서 계산하는 방식이다.
    한 방향 해쉬 함수는 가변 길이 입력 문자열인 데이터를 받아서 고정 길이의 해시값으로 불리는 출력 문자열로 변환한다. 해시값은 입력 데이터의 유일한 식별자의 역활을 한다. 데이터가 변경되었는지 확인할 때 해시값을 사용할 수 있다.
    한 방향 해쉬 함수는 한 방향으로 작용하는 해시 함수이다. 입력 데이터로부터 해시값을 계산하기는 쉬우나 해시값으로부터 데이터를 역으로 만들기는 어렵다.
  • 아래는 오라클이 제공하는 기능들이다.
    • OWA_OPT_LOCK.CHECKSUM : 이 방법은 오라클 8i 버전 8.1.5 이후 버전에서 이용 가능하다. 주어진 문자열로 16비트 체크섬을 리턴하는 함수와 주어진 ROWID로 해당 로우의 16비트 체크섬을 계산하고 동시에 락을 거는 함수도 있다. 충동 가능성은 65,536개 문자열 중 하나다.
    • DBMS_OBFUSCATION_TOOLKIT.MD5 : 이 방법은 오라클 8.1.7 이후 버전에서 이용 가능하다. 128비트 메세지 다이제스트
      (message digest)를 계산한다. 충돌 확률은 약 1/3.4028E+38(매우 낮음)이다.
    • DBMS_CRYTO.HASH : 이 방법은 오라클 10g 릴리즈 1 이후 버전에서 이용 가능하다. SHA-1(Secure Hash Algorithm 1) 또는 MD4/MD5 메세지 다이제스트를 계산할 수 있다. 필자는 SHA-1 알고리즘을 사용하기를 권한다.
    • ORA_HASH : 이 방법은 10g 릴리지 1 이후 버전에서 이용 가능하다. ORA_HASH는 한 개의 varchar2 타입의 함수 인자와 선택적으로 입력이 가능한 리턴값을 제어하는 또 다른 한 쌍의 함수 인자를 취하는 오라클 내장 함수다. 리턴값은 숫자다. (기본값은 0과 4294964295 사이에 있는 숫자)
Note 대부분 프로그래밍 언어는 해시와 체크섬 함수 그룹을 갖고 있기 때문에 데이터베이스 외부에서 이런 함수를 마음대로 사용할 수 있다. 그러나 오라클이 제공하는 데이터베이스 해시 또는 체크섬 함수를 사용한다면, 새로운 언어 또는 새로운 접근 방식으로 개발할 필요가 있을 때 이식성을 증가시킬 수 있다.
  • 기존에 사용하던 컬럼은 제거한다.
    SQL> alter table dept_1 drop column last_mod;
    
    Table altered.
    
  • dpetno가 '10'인 데이터를 검색하고 ORA_HASH 함수를 이용해서 해시를 계산한다.
    SQL> variable deptno number
    SQL> variable dname varchar2(14)
    SQL> variable loc varchar2(13)
    SQL> variable hash number
    SQL> begin
      2  select deptno, dname, loc,
      3  ora_hash(dname || '/' || loc) hash
      4  into :deptno, :dname, :loc, :hash
      5  from dept_1
      6  where deptno = 10;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
       :DEPTNO :DNAME        :LOC         :HASH
    ---------- ------------- ------------ ----------
            10 ACCOUNTING    NEW YORK     401273349
    
  • 해시는 단순히 숫자이다. 수정을 위해서 사용될 것이다.
    SQL> exec :dname := lower(:dname);
    PL/SQL procedure successfully completed.
    
    SQL> update dept_1
    2    set dname = :dname
    3    where deptno = :deptno
    4    and ora_hash(dname || '/' || loc) = :hash
    5    /
    1 row updated.
    
    SQL> select dept_1.*,
    2    ora_hash(dname|| '/' || loc) hash
    3    from dept_1
    4    where deptno = :deptno ;
    
        DEPTNO DNAME          LOC                 HASH
    ---------- -------------- ------------- ----------
            10 accounting     NEW YORK      2818855829
    
  • 다시 검색해보면 hash 값이 변경되어 있는 것을 확인할 수 있다.
    SQL> update dept_1
    2    set dname = :dname
    3    where deptno = :deptno
    4    and ora_hash(dname || '/' || loc) = :hash
    5    /
    
    0 rows updated.
    
  • 변경 전의 hash 값을 이용하여 수정하면 hash 값이 일치하지 않아서 수정되지 않는 것을 확인할 수 있다.
  • 해시 기반의 접근 방식이 제대로 작동하기 위해서는 모든 Application이 해시를 계산할 때 동일한 접근 방식을 사용하도록 보장해서 한다.
  • 일반적으로 사용하기 쉽도록 테이블에 가상 컬럼을 추가(oracle 11g release 이상 버전)하거나 컬럼을 추가한 뷰를 사용할 것을 제안한다.
  • 컬럼을 추가하여 사용하는 방법은 아래와 같다.
    SQL> alter table dept_1
    2    add hash as
    3    (ora_hash(dname || '/' || loc) );
    
    SQL> select *
    2    from dept_1
    3    where deptno =:deptno
    .....
    
  • 해쉬 혹은 체크섬을 사용하는 방식은 CPU를 많이 소비하기 때문에 자원이 부족한 시스템에서는 고려해야 한다.

낙관적 락킹인가, 비관적 락킹인가?

  • 오라클은 비관적 락킹이 낙관적 락킹에 비해서 더 많은 이점을 가지고 있다. 그러나 클라이언트/서버 connection처럼 데이터베이스에 대한 상태 기반의 connection을 요구한다. 따라서 최근에 같은 추세에서는 반영하기 힘들다.
  • 필자는 타임스탬프 컬럼을 이용한 버전 컬럼 방식을 주로 사용한다.
  • 해시 체크섬 ORA_HASH 방식은 데이터베이스에 독립적이며, 특히 외부에서도 가능하다. 데이터베이스가 아닌 미들웨어에서 수행하게 된다면 CPU 사용률과 네트워크 전송 측면에서 높은 자원 사용률이라는 부담을 줄 것이다.

    Blocking

    *Blokcing은 하나의 세션이 락을 잡고 있을 때 다른 세션이 요청을 할 때 발생한다. 결과적으로 요청된 자원은 블락킹 된다. 다른 세션이 "hang" 하기 전까지 블락 될 것이다.

  • 모든 경우에 Blocking은 피할 수 없다.
  • INSERT, UPDATE,DELETE, MERGE, SELECT FOR UPDATE에 데이터가 블락될 것이다.
Blocked Inserts
  • 기본 키나 Unique 제약조건을 가지고 있는 테이블에 같은 데이터를 넣을 경우 한 쪽이 commit이나 rollback을 하기 전까지 블락될 것이다.
  • 다른 경우는 참조키를 가지고 있는 경우에 자식 테이블에 데이터를 입력할 경우 만일 부모 테이블이 입력 삭제될 경우 블락될 것이다.
  • Blocked INSERT는 일련번호를 기본 키에 사용함으로서 피할 수 있다. 만일 Sequence 를 사용하지 못하는 경우라면 DBMS_LOCK 패키지를 사용하여 구현 할 수 있다.
  • 먼저 기본키를 가지고 있는 테이블과 트리거를 생성한다.
  • 트리거는 DBMS_UTILITY.GET_HASH_VALUE를 사용하고 다른 세션에서 같은 데이터 값을 입력한다.
    Note 트리거를 성공적으로 컴파일 하기 위해서는 DBMS_LOCK을 실행할 수 있는 권한을 가지고 있어야 한다.
    SQL> create table demo ( x int primary key );
    
    Table created.
    
SQL> create or replace trigger demo_bifer
before insert on demo
for each row
declare
l_lock_id number;
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
l_lock_id :=
dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 );
if ( dbms_lock.request
( id => l_lock_id,
lockmode => dbms_lock.x_mode,
timeout => 0,
release_on_commit => TRUE ) <> 0 )
then
raise resource_busy;
end if;
end; 

Trigger created.
  • A 세션에서 데이터 입력
    SQL> insert into demo values ( 1 );
    
    1 row created.
    
  • B 세션에서 데이터 입력
    SQL> insert into demo values ( 1 );
    insert into demo values ( 1 )
                *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified
    ORA-06512: at "UBACK.DEMO_BIFER", line 14
    ORA-04088: error during execution of trigger 'UBACK.DEMO_BIFER'
    
  • 트리거에 의해서 기본키 제약조건으로 인하여 입력할 때 위와 같은 에러를 보여주는 것을 확인할 수 있다.
Blocked Merges, Updates, and Deletes
  • UPDATE 이슈를 피하기 위해서는 SELECT FOR UPDATE NOWAIT 쿼리를 이용한다.
    • 쿼리가 실행한 이후에 데이터가 변하지 않게 해준다.
    • 로우에 락을 건다. (블락된 UPDATE DELETE 로부터 막아준다.)
  • 비관적 락킹은 데이터를 짧은 순간에 수정하는 경우에 사용한다.
  • 낙관적 락킹은 데이터베이스 안에 데이터를 즉시 수정하기 전에 사용한다.

Deadlocks

  • 데드락은 두 개의 세션이 상대방이 원하는 자원을 서로 보유하고 있을 때 일어난다.
    Session A Session B
    Table A 수정 Table B 수정
    Blocking Table A 수정
    Table B 수정 Deadlock
  • 오라클은 두 세션 중에 하나를 *희생물*로 선택하여 그 세션을 롤백한다.
  • 예를 들어 세션 B가 테이블 A를 수정하려고 하면 다음과 같은 오류와 함께 롤백될 수 있다.
    update a set x = x+1
    *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource
    
  • 세션 A는 Blocking 상태로 남아있다.
  • 데드락은 매우 드물게 발생하고 발생할 경우 서버에 트레이스 파일을 생성하여준다.
  • 아래는 트레이스 파일의 내용이다.
    *** 2005-04-25 15:53:01.455
    *** ACTION NAME:() 2005-04-25 15:53:01.455
    *** MODULE NAME:(SQL*Plus) 2005-04-25 15:53:01.455
    *** SERVICE NAME:(SYS$USERS) 2005-04-25 15:53:01.455
    *** SESSION ID:(145.208) 2005-04-25 15:53:01.455
    DEADLOCK DETECTED
    Current SQL statement for this session:
    update a set x = 1
    The following deadlock is not an ORACLE error. It is a
    deadlock due to user error in the design of an application
    or from issuing incorrect ad-hoc SQL. The following
    information may aid in determining the deadlock:...
    
  • 데드락은 거의 발생하지 않지만 발생하는 가장 큰 원인은 인덱스가 없는 참조키 때문이다.
  • 다음과 같은 시나리오에서 부모 테이블을 변경한 후 자식 테이블에 락을 건다.
    • 부모 테이블의 기본키를 수정한다면 자식 테이블은 참조키에 대한 인덱스가 없어 락이 걸릴 것이다.
    • 부모 테이블의 로우를 삭제하면 자식 테이블 전체에 락이 걸릴 것이다. (참조키에 대한 인덱스가 없음)
    • 부모 테이블로 merge 하면 자식 테이블 전체에 락이 걸릴 것이다. (참조키에 대한 인덱스가 없음) 이것은 오라클 11g 릴리즈 1 이후로는 적용되지 않는다.
  • 한 쌍의 테이블을 다음과 같이 설정한다.
    SQL> create table p ( x int primary key );
    
    Table created.
    
    SQL> create table c ( x references p );
    
    Table created.
    
    SQL> insert into p values ( 1 );
    
    1 row created.
    
    SQL> insert into p values ( 2 );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
SQL> insert into c values ( 2 );

1 row created.
  • 위와 같이 하면 다른 세션은 즉시 블로킹 된다.
  • 다른 세션에서도 테이블 C에 DELETE, INSERT, UPDATE를 할 수 없다.
    SQL> delete from p where x = 1;
    
    
  • 아래와 같은 경우 EMP가 DEPT 테이블에 참조키를 가지고 있고 DEPTNO에 인덱스가 없다면 DEPT를 수정하는 동안 EMP 테이블 전체에 락이 걸릴 것이다.
    SQL> update dept set deptno=:1,dname=:2,loc=:3 where rowid=:4
      2  ;
    
  • 다음 예제는 테이블 C에서 인덱스가 없는 참조키를 찾는 스크립트 사용법을 보여준다.
    SQL> column columns format a30 word_wrapped
    SQL> column tablename format a15 word_wrapped
    SQL> column constraint_name format a15 word_wrapped
    SQL> select table_name, constraint_name,
    cname1 || nvl2(cname2,','||cname2,null) ||
    nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
    nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
    nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
    columns
    from ( select b.table_name,
    b.constraint_name,
    max(decode( position, 1, column_name, null )) cname1,
    max(decode( position, 2, column_name, null )) cname2,
    max(decode( position, 3, column_name, null )) cname3,
    max(decode( position, 4, column_name, null )) cname4,
    max(decode( position, 5, column_name, null )) cname5,
    max(decode( position, 6, column_name, null )) cname6,
    max(decode( position, 7, column_name, null )) cname7,
    max(decode( position, 8, column_name, null )) cname8,
    count(*) col_cnt
    from (select substr(table_name,1,30) table_name,
    substr(constraint_name,1,30) constraint_name,
    substr(column_name,1,30) column_name,
    position
    from user_cons_columns ) a,
    user_constraints b
    where a.constraint_name = b.constraint_name
    and b.constraint_type = 'R'
    group by b.table_name, b.constraint_name
    ) cons
    where col_cnt > ALL
    ( select count(*)
    from user_ind_columns i
    where i.table_name = cons.table_name
    and i.column_name in (cname1, cname2, cname3, cname4,
    cname5, cname6, cname7, cname8 )
    and i.column_position <= cons.col_cnt
    group by i.index_name
    )
    / TABLE_NAME                 CONSTRAINT_NAME COLUMNS
    ---------------------------- --------------- ------------------------
    C                            SYS_C00108609   X
    
  • 참조키에 인덱스가 필요없을 때는 다음과 같은 경우이다.
    • 부모 테이블의 로우를 삭제하지 않는다.
    • 부모 테이블의 유일키/기본키 값을 수정하지 않는다.
    • 부모에서 자식으로 조인 연산을 하지 않는다.
락 상승
  • 락 상승이 발생할 때 시스템은 락의 granularity를 감소시킨다.
    Note 오라클은 절대 락을 상승시키는 일이 없다.
  • 오라클은 락을 상승시키지는 않지만, 종종 락 상승과 혼동하는 용어인 락 변환(lock conversion) 또는 락 촉진(lock promotion)을 실행한다.
    Note 락 변환과 락 촉진이라는 용어는 동의어다. 오라클은 그 과정을 락 변환이라고 한다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.