전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
동시성 제어
- 많은 사람들이 동시에 엑세스 하거나 데이터를 변경하도록 하기 위해 DB 가 제공하는 기능
- 오라클의 락, 래치, 뮤텍스 등
오라클의 멀티버저닝 기능
- 오라클은 동시성 제어를 위해 락 이외에 다중 버전 기능을 제공
- 읽은 시점을 기준으로 일관된 결과를 반황
- 읽기가 쓰기를 블로킹 하지 않음
- 기본적으로 문장 수준에서 적용 되며, 트렌젝션 수준으로 적용되도록 변경 가능
트렌젝션 고립 수준
- 트렌젝션의 목적은 하나의 일관된 상태에서 다음의 일관된 상태로 바꾸는 것
- ISO 표준은 다양한 레벨의 트렌젝션 고립 수준을 정의하고 있다
- ANSI/ISO 는 아래와 같이 네 가지 고립 수준을 정의한다
- Read Uncommited
- Read Commited
- Repeatable Read
- Serializable
트렌젝션 고립 수준 별로 발생 가능한 "현상" 들
- Dirty Read - Commit 되지 않은 변경 중인 데이터가 다른 세션에서 조회 가능한 현상
- Non Repeatable Read - 한 트렌젝션에서 같은 행을 두 번 이상 읽을 때 결과값이 다를 수 있음
- Phantom Read - 한 트렌젝션에서 동일한 쿼리를 두 번 이상 수행할 때 없던 행이 조회 가능
Read Uncommited
발생 가능한 현상
- Dirty read 현상 허용: Commit 되지 않은 다른 사용자가 변경 한 데이터를 조회 가능하다
- Non Repeatable Read 현상 허용: 하나의 트렌젝션에서 같은 로우를 두 번 이상 읽을 때 결과가 달라질 수 있다
- Phantom Read 현상 허용: 하나의 트렌젝션에서 같은 쿼리를 두 번 이상 수행했을 때 없던 로우가 조회 가능하다
- 기본적으로 Non-Blocking 읽기(변경중인 데이터를 다른 사용자가 조회)를 허용하기 위하여 Dirty Read 수준을 허용 하는 것
- 다중 사용자 환경에서 Dirty Read 는 위험한 현상이므로 일반적인 상용 DB 에서는 이 단계의 고립 수준은 잘 쓰이지 않는다
Read Commited
발생 가능한 현상
- Non Repeatable Read 현상 허용: 하나의 트렌젝션에서 같은 로우를 두 번 이상 읽을 때 결과가 달라질 수 있다
- Phantom Read 현상 허용: 하나의 트렌젝션에서 같은 쿼리를 두 번 이상 수행했을 때 없던 로우가 조회 가능하다
- DBMS 의 가장 보편적인 읽기 수준이다.
- 일반적인 DB 는 Uncommit 된 변경사항을 읽게 하지 않기 위해 해당 로우에 대한 Blocking 을 시도한다.
- 이로 인해 문장 단위로 커밋 하는 잘못된 습관을 가질 수 있다
- 다른 변경하는 세션의 커밋을 기준으로 없던 결과값이 나오는 것은 Dirty Read 와 동일하다
- 이 없던 결과를 읽기 위해 심지어 대기까지 한다
h5.Repeatable Read
발생 가능한 현상
- Phantom Read 현상 허용: 하나의 트렌젝션에서 같은 쿼리를 두 번 이상 수행했을 때 없던 로우가 조회 가능하다
- 시점에 따라 일관성 있는 결과를 얻기 위하여 조회/변경 작업에 모두 락을 획득하는 수준
- Lost update 를 방지하기 위해 이 레벨의 고립 수준을 사용한다
- 어떤 한 시점을 기준으로 일관성 있는 결과를 반환한다
- 커넥션 풀을 사용하는 3 티어 환경에서는 사용 불가능하다
- 데드락이 더 빈번하게 발생 가능하다 - 변경 <-> 변경 세션 간 데드락 뿐 아니라 조회 <-> 변경 세션간 데드락도 발생
Serializable
- Dirty read/Non Repeatable read/Phantom Read 중 아무 현상도 허용하지 않는 고립 수준이다.
- 트렌젝션을 시작 하는 순간을 기준으로 일관된 결과 제공한다
- 가장 높은 단계의 고립 수준을 제공
- 데이터를 변경하는 다른 사용자가 전혀 없는 환경에서 작업 하는 것 처럼 보임
오라클의 Serializable 모드
- 아래와 같은 방법으로 변경 가능하다(alter system 은 불가능)
SQL> alter session set isolation_level=serializable;
- 트렌젝션이 시작 된 이후에 다른 세션이 변경 한 로우를 내가 업데이트 하려고 할 때 ORA-08177 에러를 발생시킨다.
SQL> create table se_test (a number, b number);
Table created.
SQL> insert into se_test select level, level*10 from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
SQL>
------------ Session A -------------- ------------ Session B --------------
SQL> alter session set isolation_level=serializable;
Session altered.
SQL> update se_test set a=1000 where b=10;
1 row updated.
SQL> select * from se_test where b=10;
A B
---------- ----------
1 10
SQL> commit;
Commit complete.
SQL> select * from se_test where b=10;
A B
---------- ----------
1 10
SQL> update se_test set a=3000 where b=10;
update se_test set a=3000 where b=10
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> commit;
Commit complete.
SQL> select * from se_test where b=10;
A B
---------- ----------
1000 10
SQL> update se_test set a=3000 where b=10;
1 row updated.
SQL>
SQL> select * from se_test where b=10;
A B
---------- ----------
1000 10
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from se_test where b=10;
A B
---------- ----------
3000 10
SQL>
오라클은 낙관적 입장을 취한다
- 트렌젝션이 업데이트 하는 행을 다른 트렌젝션이 업데이트 하지 않을 것이라고 가정한다
- 이는 빠른 트렌젝션 처리를 하는 OLTP 에서는 대부분 유효하다
- 트렌젝션이 시작 한 이후로 커밋 된 변경분까지도 감지하지 못하기 때문에 사용에 주의 필요
- Serializable 고립 수준은 다음과 같은 상황에서 사용하는 것이 효과적이다
- 누군가 같은 데이터를 변경 할 가능성이 매우 낮을 때
- 트렌젝션 수준의 읽기 일관성이 필요할 때
- 트렌젝션 수행 시간이 짧을 때
- Serializable 고립 수준을 다르게 구현한 많은 시스템에서는 공유 읽기 락에 의한 교착 상태와 블로킹 현상이 많다고 함
- 오라클은 블로킹 현상이 없는 대신에 ORA-08177 에러를 발생 시키지만 타 시스템의 데드락과 블로킹만큼 빈번하지는 않다고 함
- 표 7-7 의 내용은 참고로만...
4 가지 ANSI 고립 수준과 발생 가능한(허용되는) 현상은 아래 표 참조
Read Only
- 변경을 허용하지 않는 것 빼고는 Serializable 와 유사함
- ORA-01877 오류는 만나지 않지만 ORA-01555 snapshot too old 에러는 발생할 수 있음
- Undo 를 충분히 할당하자
- Read only 트렌젝션을 정작 어떻게 설정하는지는 나와있지 않다
오라클의 트렌젝션 고립 수준 정리
- 명시적으로 Read Commited 와 Serializable 수준을 지원 한다.
- 기본 시스템 설정 수준은 Read Commited
- SQL 표준 정의 상 읽기 일관성을 지원하는 수준은 Repeatable Read 이다
- SQL 표준 정의 상 Non Blocking Read 를 구현하는 수준은 Read Uncommited 이다
- 오라클은 Read Commited 모드에서 읽기 일관성 및 Non blocking Read 가 가능하다
멀티비전 읽기 읽관성의 문제점
Case 1 - DW system 에서 발생 가능한 실수
- 소스 테이블에 마지막 변경 시점을 컬럼에 저장하는 트리거 구현
- DW 에 초기 데이터 적재 직전 SYSDATE 를 변수에 저장하고(ex. 오전 9시 정각)
- SELECT * FROM TABLE; 과 같은 구문으로 초기 데이터 적재 시작
- 10시에 변경분을 반영하기 위해 변경 시점 컬럼 참조하여 9시 이후로 변경된 데이터를 모두 타겟 테이블과 Merge
- 이 때 Non blocking 을 구현하는 오라클과 같은 시스템은 로직에 결함이 발생 가능하다
- 9시에 다른 세션에서 변경이 진행 중인 데이터 (ex 8:50 에 Update --> 9:10 에 Commit) 는
- 초기 적재 시 변경 전 값으로 가져온다(읽기 일관성)
- 위 데이터의 변경 시점은 8:50 분 이므로 10시에 변경 분 반영 시에도 추출되지 않는다
- 위 로직을 보완하기 위해 v$transaction 뷰에서 가장 오래된 트렌젝션 시작 시점을 기준으로 추출하라고 예시되어 있음
- 추출하는 테이블 관련 트렌젝션이 아니라 다른 테이블 트렌젝션까지 같이 조회 되므로 단순 응용이 가능할지?
읽기 블록 과다
- 335 ~ 338 Page 예시 참조
- 데이터 변경이 빈번하게 발생할 수 있는 테이블은 그렇지 않은 데이블에 비해 읽기 블록이 더 많이 발생한다
- 자신의 트렌젝션(Serializable) 혹은 문장(Read Commited) 수행 시점의 블록을 찾기 위해 Undo Segment 를 추가로 탐색 하기 때문
- 자세한 내용은 Undo 관련 챕터에서 다룰 예정
쓰기 일관성
변경(Update) 를 위한 두 가지 모드 사용
- 오라클은 업데이트 작업을 위해 두 가지 모드의 블록 읽기를 수행한다
- Update t set x = x+1 where y=5;
1. 변경할 행을 찾을 때 : Consistent 읽기
2. 실제 대상 행을 업데이트 할 때 : Current 읽기
SQL> create table t ( X int, y int ) ;
Table created.
SQL> insert into t values ( 1, 1 ) ;
1 row created.
SQL> commit;
Commit complete.
----------- Session A ------------- ----------- Session B ------------
SQL> update t set x=x+1 ;
1 row updated.
SQL> update t set x=x+1 where x=1;
... 락 대기 중 ...
SQL> commit;
Commit complete.
SQL>
0 rows updated.
SQL>
- Where 절의 조건은 Consistent 데이터인 x=1 을 찾았으므로 Session A 에 의한 락을 대기
- 실제 업데이트는 Commit 이 끝난 후 Current 데이터에 대해 수행
발생 가능한 상황
- Update 한 문장을 수행하는 동안 다른 세션이 Where 조건절에 부합하는 새 레코드를 추가 시 Where 절의 Consistent 모드 읽기가 새 레코드를 볼 수 없으므로 Update 적용 되지 않음
- Update 한 문장을 수행하는 동안 다른 세션이 Where 조건절에 부합하는 조건 값을 변경 시
- Update 한 문장을 수행하는 동안 다른 세션이 Where 조건절에 부합하는 변경 값을 변경 시
- 다른 세션의 Commit 시점을 기준으로 Curent 블록을 읽어 SELECT FOR UPDATE 모드로 재 시작 한다고 함
그러나 "이런 경우에는 또 어떻게 될까?" 에 대한 의문이 꼬리에 꼬리를 물고 계속되는데,
변경 작업을 다시 진행하려고 SELECf FOR UPDATE 모드 (update할 때처럼 consistent 모드로 읽고 current 모드로 블록을 다시 얻음)
읽기를 시작했는데 시작할 때 분명 Y=5 였던 로우가 블록의 현재 버전을 얻고 보니 Y= l1로 바뀌었다는 사실을 발견하면 그때는 또 어떻게 해야 할까?
그 SELECF FOR UPDATE 문장 도 재시작할 것이고，그런 순환이 반복될 수 있다.
재 시작 매커니즘
재 시작 매커니즘을 확인 할 방법은?
- 테스트 테이블 및 트리거 구현
SQL> create or replace trigger t_bufer
before update on t for each row
begin
dbms_output.put_line('old.x = '||:old.x||', old.y = '||:old.y);
dbms_output.put_line('new.x = '||:new.x||', new.y = '||:new.y);
end;
/
Trigger created.
SQL>
{code:xm;}
\\
* 테이블 Row 수정 테스트
{code_xml}
SQL> create table t ( X int, y int ) ;
Table created.
SQL> insert into t values ( 1, 1 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace trigger t_bufer
before update on t for each row
begin
dbms_output.put_line('old.x = '||:old.x||', old.y = '||:old.y);
dbms_output.put_line('new.x = '||:new.x||', new.y = '||:new.y);
end;
/
Trigger created.
SQL>
------------- Session A ------------- -------------- Session B ---------------
SQL> set serveroutput on;
SQL> update t set x = x+1;
old.x = 1, old.y = 1
new.x = 2, new.y = 1
1 row updated.
SQL> set serveroutput on;
SQL> update t set x = x+1 where x > 0;
... 락 대기 중 ...
SQL> commit;
Commit complete.
SQL> old.x = 1, old.y = 1
new.x = 2, new.y = 1
old.x = 2, old.y = 1
new.x = 3, new.y = 1
1 row updated.
트리거가 두 번 실행 되었다
- 한 번은 변경하려고 한 Row 의 읽기 일관성 버전에서(x=1) 작동해서 old, new 값을 출력
- 다른 한 번은 실제 Update 한 커런트 버전에서(x=2) 작동해서 old, new 값을 출력
- 트리거가 실행을 마칠 때 까지 - 트리거가 :new 값을 변경할 수 있으므로 - 커런트 블록을 가져올 수 없다
- Update 에 의한 재 시작 매커니즘은 Where 절에서 참조하는 컬럼들의 Consistent 값과 Current 값이 달라졌을 경우이다
트리거 자체도 재 시작을 유발하는 요인 중 하나가 될 수 있다
-------- Session A ------------ --------- Session B -----------
SQL> rollback;
Rollback complete.
SQL>
SQL> update t set x=x-1;
old.x = 2, old.y = 1
new.x = 1, new.y = 1
1 row updated.
SQL> commit;
Commit complete.
SQL> update t set x = x+1;
old.x = 1, old.y = 1
new.x = 2, new.y = 1
1 row updated.
SQL> update t set x = x+1 where y > 0;
... 락 대기 중 ...
SQL> commit;
Commit complete.
SQL> old.x = 1, old.y = 1
new.x = 2, new.y = 1
old.x = 2, old.y = 1
new.x = 3, new.y = 1
1 row updated.
SQL>
- where y > 0 조건으로 수행했는데도(Where 절의 Consistent 값과 Current 값이 다르지 않음에도) 트리거가 두번 수행되었다
- 트리거에서 :OLD 와 :NEW 를 참조하였는지도 재시작을 결정하는 요인이다
-------- Session A ------------ --------- Session B -----------
SQL> rollback;
Rollback complete.
SQL>
SQL> update t set x=x-1;
old.x = 2, old.y = 1
new.x = 1, new.y = 1
1 row updated.
SQL> commit;
Commit complete.
SQL> drop trigger t_bufer;
Trigger dropped.
SQL> create or replace trigger t_bufer
before update on t for each row
begin
dbms_output.put_line(', old.y = '||:old.y);
dbms_output.put_line(', new.y = '||:new.y);
end;
/
Trigger created.
SQL> update t set x = x+1;
, old.y = 1
, new.y = 1
1 row updated.
SQL>
SQL> update t set x = x+1 where y > 0;
... 락 대기 중 ...
SQL> commit;
Commit complete.
SQL>
, old.y = 1
, new.y = 1
1 row updated.
SQL> select * from t;
X Y
---------- ----------
3 1
SQL>
- 위와같이 old.x, new.x 를 트리거에서 제거하면 재시작은 발생하지 않는다
Update 의 재 시작 매커니즘의 정리
- Where 절의 조건은 Consistent 모드 데이터를 탐색
- Set 절의 변경 데이터는 Current 모드 데이터를 변경
- Where 절에서 검색한 데이터의 Consistent 와 Current 가 다를 경우에는 재 시작 매커니즘이 작동한다.
- Trigger 의 :old, :new 에 대한 참조도 재 시작 매커니즘을 유발하는 요인이다.
재 시작 매커니즘에 대한 저자의 결론
- 트렌젝션 성격이 아닌 다른 작업을 트리거에서 처리하면 재 시작 매커니즘에 의한 부작용이 생길 수 있다.
- UTL_FILE 등의 플랫 파일 기록 작업 이나 Autonomus 트렌젝션 함수, 메일 발송 등 되돌릴 수 없는 행위를 트리거와 연동 시킬 경우 재 시작 매커니즘에 의해 불필요한 작업이 발생할 수 있다.
전체 정리
- 각 고립 수준 별 매커니즘을 잘 알고 활용하자
- 동시성과 일관성은 Trade off 관계이다(상용 DBMS 는 어느정도 한계를 극복했다- 오라클의 멀티버저닝 등)
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4021
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.