락킹 이슈(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

h6.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

• 데드락은 두 개의 세션이 상대방이 원하는 자원을 서로 보유하고 있을 때 일어난다.

• 오라클은 두 세션 중에 하나를 희생물로 선택하여 그 세션을 롤백한다.
• 예를 들어 세션 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를 감소시킨다.
• 오라클은 절대 락을 상승시키는 일이 없다.
• 오라클은 락을 상승시키지는 않지만, 종종 락 상승과 혼동하는 용어인 락 변환(lock conversion) 또는 락 촉진(lock promotion)을 실행한다.
• 락 변환과 락 촉진이라는 용어는 동의어다. 오라클은 그 과정을 락 변환이라고 한다.