제 19 장 (Oracle) 오라클에서 동시성 제어 (MVCC)

Presentation on theme: "제 19 장 (Oracle) 오라클에서 동시성 제어 (MVCC)"— Presentation transcript:

1 제 19 장 (Oracle) 오라클에서 동시성 제어 (MVCC)

2 02. 동시성 제어(concurrency control)
트랜잭션이 동시에 수행될 때, 일관성을 해치지 않도록 트랜잭션의 데이터 접근을 제어하는 DBMS의 기능 표 8-3 트랜잭션의 읽기(read)/쓰기(write) 시나리오 트랜잭션1 트랜잭션2 발생 문제 동시접근 [상황 1] 읽기 읽음(읽기만 하면 아무 문제가 없음) 허용 [상황 2] 쓰기 오손 읽기, 반복불가능 읽기, 유령 데이터 읽기 허용 혹은 불가 선택 [상황 3] 갱신손실(절대 허용하면 안 됨) 허용불가(LOCK을 이용)

3 2. 락 갱신손실 문제를 해결하려면 상대방 트랜잭션이 데이터를 사용하는지 여부를 알 수 있는 규칙이 필요함
데이터를 수정 중이라는 사실을 알리는 방법의 잠금장치임

4 2.1 락의 개념 그림 8-9 락을 이용한 갱신손실 문제 해결 TI T2 버퍼의 데이터 값 LOCK(X)
A=read_item(X); ① A=A-100; X=1000 (wait... 대기) write_item(A->X); ② UNLOCK(X); X=900 B=read_item(X); ③ B=B+100; write_item(B->X); ④ UNLOCK(X) 트랜잭션 T1 버퍼(BUFFER) 트랜잭션 T2 X ③ ④ ① ② A B DBMS X 1000 데이터베이스 그림 8-9 락을 이용한 갱신손실 문제 해결

5 2.1 락의 개념 [작업 설명] 한 개의 데이터에 두 개의 트랜잭션이 접근하여 갱신하는 작업

6 2.1 락의 개념 [시나리오] 두 트랜잭션을 동시에 실행 : 교재 445쪽 참고 트랜잭션 T1 트랜잭션 T2
SET TRANSACTION NAME 'T1'; SELECT * FROM Book WHERE bookid=1; UPDATE Book SET price=7100 SET TRANSACTION NAME 'T2'; UPDATE Book SET price=price+100 COMMIT; ...(대기 상태)...

7 2.2 락의 유형 락은 트랜잭션이 읽기를 할 때 사용하는 락인 공유락(LS, shared lock)과 읽고 쓰기를 할 때 사용하는 배타락(LX, exclusive lock)으로 나뉨 공유락과 배타락을 사용하는 규칙 데이터에 락이 걸려있지 않으면 트랜잭션은 데이터에 락을 걸 수 있음 트랜잭션이 데이터 X를 읽기만 할 경우 LS(X)를 요청하고, 읽거나 쓰기를 할 경우 LX(X)를 요청 다른 트랜잭션이 데이터에 LS(X)을 걸어둔 경우, LS(X)의 요청은 허용하고 LX(X)는 허용하지 않음 다른 트랜잭션이 데이터에 LX(X)을 걸어둔 경우, LS(X)와 LX(X) 모두 허용하지 않음 트랜잭션이 락을 허용받지 못하면 대기 상태가 됨 표 8-4 락 호환행렬 요청 상태 LS 상태 LX 상태 LS 요청 허용 대기 LX 요청

8 2.3 2단계 락킹 (2 phase locking) 정의 : 락을 걸고 해제하는 시점에 제한을 두지 않으면 두 개의 트랜잭션이 동시에 실행될 때 데이터의 일관성이 깨질 수 있어 이를 방지하는 방법 확장단계(Growing phase, Expanding phase) : 트랜잭션이 필요한 락을 획득하는 단계로, 이 단계에서는 이 미 획득한 락을 해제하지 않음 수축단계(Shrinking phase) : 트랜잭션이 락을 해제하는 단계로, 이 단계에서는 새로운 락을 획득하지 않음 [작업 설명] 두 개의 데이터에 두 개의 트랜잭션이 접근하여 갱신하는 작업 [문제 발생] 락을 사용하되 2단계 락킹 기법을 사용하지 않을 경우[교재 447쪽] [문제 해결] 2단계 락킹 기법을 사용할 경우[교재448쪽]

9 2.3 2단계 락킹 [문제 발생] 락을 사용하되 2단계 락킹 기법을 사용하지 않을 경우[교재 447쪽] 트랜잭션T1
A, B 값 LX(A) t1=read_item(A); t1=t1-100; A=write_item(t1); UN(A) A=900 B=1000 t2=read_item(A); t2=t2*1.1; A=write_item(t2); LX(B) t2=read_item(B); B=write_item(t2); UN(B) A=990 B=1100 t1=read_item(B); t1=t1+100; B=write_item(t1); B=1200 /* A+B=2190 이므로 일관성 제약조건에 위배됨 */

10 2.3 2단계 락킹 [문제 해결] 2단계 락킹 기법을 사용할 경우[교재448쪽] 트랜잭션T1 트랜잭션T2 A, B 값
LX(A) t1=read_item(A); t1=t1-100; A=write_item(t1); A=900 B=1000 …(대기상태)… LX(B) t1=read_item(B); t1=t1+100; B=write_item(t1); UN(A) UN(B) B=1100 t2=read_item(A); t2=t2*1.1; A=write_item(t2); t2=read_item(B); B=write_item(t2); A=990 B=1210 /* A+B=2200 이므로 일관성 제약조건을 지킴 */

11 2.4 데드락(deadlock) 정의 : 두 개 이상의 트랜잭션이 각각 자신의 데이터에 대하여 락을 획득하고 상대방 데이터에 대하여 락을 요청하면 무한 대기 상태에 빠질 수 있는 현상. 교착상태라고도 함 [작업 설명] 두 개의 데이터에 두 개의 트랜잭션이 접근하여 갱신하는 작업 [문제 발생] SQL Server에서 데드락 발생[교재 450쪽] [문제 해결] 데드락 해결 일반적으로 데드락이 발생하면 DBMS는 T1 혹은 T2의 작업 중 하나를 강제로 중지시킴. 그 결 과 나머지 트랜잭션은 정상적으로 실행됨. 이때 중지시키는 트랜잭션에서 변경한 데이터는 원 래 상태로 되돌려 놓음

12 2.4 데드락 트랜잭션 T1 트랜잭션 T2 SET TRANSACTION NAME ‘T1’; UPDATE Book
SET price=price+100 WHERE bookid=1; SET TRANSACTION NAME ‘T2’; SET price=price*1.1 WHERE bookid=2; …(대기상태)… COMMIT;

13 03. 트랜잭션 고립 수준 트랜잭션 고립 수준 명령어(transaction isolation level instruction)
DBMS는 트랜잭션을 동시에 실행시키면서 락보다 좀 더 완화된 방법으로 문제를 해결하기 위해 제공하는 명령어 표 8-5 트랜잭션 고립 수준 명령어와 발생 현상 문제 고립 수준 오손 읽기 반복불가능 읽기 유령데이터 읽기 READ UNCOMMITTED 가능 READ COMMITTED 불가능 REPEATABLE READ SERIALIZABLE

14 3.1 READ UNCOMMITTED(Level = 0)
고립 수준이 가장 낮은 명령어로, 자신의 데이터에 아무런 공유락을 걸지 않음 (배타락은 갱신손실 문제 때문에 걸어야 한다). 또한 다른 트랜잭션에 공유락과 배타락이 걸린 데이터를 대기하지 않고 읽음. 심지어 다른 트랜잭션이 COMMIT하지 않은 데이터도 읽을 수 있음. 그 때문에 오손(dirty) 페이지의 데이터를 읽게 됨. 이 명령어는 SELECT 질의의 대상이 되는 테이블에 대해서 락을 설정하지 않은 것(NOLOCK)과 같음 표 8-6 READ UNCOMMITTED 모드 요약 모드 READ UNCOMMITTED LOCK SELECT 문 - 공유락 걸지 않음 UPDATE 문 - 배타락 설정 다른 트랜잭션의 공유락과 배타락이 걸린 데이터를 읽음 SQL 문 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 문제점 오손 읽기, 반복불가능 읽기, 유령데이터 읽기 표 8-4 락 호환행렬 요청 상태 LS 상태 LX 상태 LS 요청 허용 LX 요청 대기

15 3.2 READ COMMITTED(Level=1)
오손(dirty) 페이지의 참조를 피하기 위해 자신의 데이터를 읽는 동안 공유락을 걸지만 트랜잭션이 끝나기 전에라도 해지 가능 다른 트랜잭션 데이터는 락 호환성 규칙에 따라 진행. 이 옵션은 오라클의 기본 설정으로 아무런 설정을 하지 않으면 READ COMMITTED 방식으로 수행됨 표 8-7 READ COMMITTED 모드 요약 모드 READ COMMITTED LOCK SELECT 문 - 공유락을 걸고 끝나면 바로 해지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 SQL 문 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 문제점 반복불가능 읽기, 유령데이터 읽기 표 8-4 락 호환행렬 요청 상태 LS 상태 LX 상태 LS 요청 허용 대기 LX 요청

16 3.3 REPEATABLE READ(Level=2)
자신의 데이터에 설정된 공유락과 배타락을 트랜잭션이 종료할 때까지 유지하여 다른 트랜잭션이 자신의 데이터를 갱신(UPDATE)할 수 없도록 함 다른 트랜잭션 데이터는 락 호환성 규칙에 따라 진행한다. 다른 고립화 수준에 비해 데이터의 동시성(concurrency)이 낮아 특별하지 않은 상황이라면 사용하지 않는 것이 좋음 표 8-8 REPEATABLE READ 모드 요약 모드 REPEATABLE READ LOCK SELECT 문 - 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 SQL 문 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 문제점 유령데이터 읽기 표 8-4 락 호환행렬 요청 상태 LS 상태 LX 상태 LS 요청 허용 대기 LX 요청

17 3.4 SERIALIZABLE(Level=3)
고립 수준이 가장 높은 명령어로, 실행 중인 트랜잭션은 다른 트랜잭션으로부터 완벽하게 분리됨 데이터 집합에 범위를 지어 잠금을 설정할 수 있기 때문에 다른 사용자가 데이터를 변경하려고 할 때 트랜잭션을 완벽하게 분리할 수 있음. 이 명령어는 네 가지 고립화 수준 중 제한이 가장 심하고 데이터의 동시성도 낮음. SELECT 질의의 대상이 되는 테이블에 미리 배타락을 설정한 것과 같은 효과를 냄 표 8-9 SERIALIZABLE 모드 요약 모드 SERIALIZABLE LOCK SELECT 문 - 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 인덱스에 공유락을 설정하여 다른 트랜잭션의 INSERT 문이 금지됨 SQL 문 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 문제점 없음 표 8-4 락 호환행렬 : REPEATABLE READ와 동일함. 추가로 인덱스 노드에 공유락 사용함.

18 4. 트랜잭션 고립 수준과 락킹 기법 실습 1. 갱신 손실 문제 방지 실험 트랜잭션 T1 READ COMMITTED 모드
SELECT * FROM trantest WHERE empID = 'A002'; UPDATE trantest SET empName = '임꺽정' WHERE empID = 'A002'; UPDATE trantest SET empName = '강만중' --Wait; COMMIT;

19 READ COMMITTED 모드 (기본 모드)
4. 트랜잭션 고립 수준과 락킹 기법 실습 2. 오손 읽기 문제 방지 실험 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; SELECT name FROM member where id = 2; Wait COMMIT;

20 READ COMMITTED 모드 (기본 모드)
4. 트랜잭션 고립 수준과 락킹 기법 실습 3. 반복불가능 읽기 문제 발생 실험 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM member where id = 2; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; COMMIT; SELECT name FROM member where id = 2 /* 앞의 결과와 다름 */

21 READ COMMITTED 모드 (기본 모드)
4. 트랜잭션 고립 수준과 락킹 기법 실습 4. 반복불가능 읽기 문제 방지 실험 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM member where id = 2 FOR UPDATE; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; Wait SELECT name FROM member where id = 2 /* 앞의 결과와 다름 */ COMMIT;

22 READ COMMITTED 모드 (기본 모드)
4. 트랜잭션 고립 수준과 락킹 기법 실습 5. 유령데이터 읽기 문제 발생 실험 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DELETE FROM member WHERE id =2; COMMIT; SELECT Name FROM member WHERE id BETWEEN 1 AND 3; insert into member values (2,'johyeon','1234',

23 READ COMMITTED 모드 (기본 모드)
4. 트랜잭션 고립 수준과 락킹 기법 실습 6. 유령데이터 읽기 문제 방지를 위한 고립 수준 상향 명령어 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; DELETE FROM member WHERE id =2; COMMIT; SELECT Name FROM member WHERE id BETWEEN 1 AND 3; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into member values (2,'johyeon','1234', Wait;