PowerPoint 프레젠테이션

Size: px

Start display at page:

Download "PowerPoint 프레젠테이션"

우민 강
5 years ago
Views:

1 IT CookBook, SQL Server 로배우는데이터베이스개론과실습 [ 강의교안이용안내 ] 본강의교안의저작권은한빛아카데미 에있습니다. 이자료를무단으로전제하거나배포할경우저작권법 136 조에의거하여최고 5 년이하의징역또는 5 천만원이하의벌금에처할수있고이를병과 ( 倂科 ) 할수도있습니다.

2 Chapter8. 트랜잭션, 동시성제어, 회복 SQL Server 로배우는데이터베이스개론과실습

3 1. 트랜잭션 2. 동시성제어 3. 트랜잭션고립수준 4. 회복

4 트랜잭션의개념을이해하고데이터베이스에서왜필요한지알아본다. 트랜잭션실행시동시성제어가필요한이유를알아보고락킹을이용한동시성제어기법에대해알아본다. 락킹보다완화된방법으로트랜잭션의동시성을높이는트랜잭션고립수준에대해알아본다. 데이터베이스시스템에문제가생길경우복구하는방법을알아본다.

5 01. 트랜잭션 트랜잭션의개념 트랜잭션의성질 트랜잭션과 DBMS

6 1.1 트랜잭션 트랜잭션 (transaction) 은 DBMS 에서데이터를다루는논리적인작업의단위다. 데이터베이스에서트랜잭션을정의하는이유 데이터베이스에서데이터를다룰때장애가일어나는경우가있다. 트랜잭션은장애시데이터를복구하는작업의단위가된다. 데이터베이스에서여러작업이동시에같은데이터를다룰때가있다. 트랜잭션은이작업을서로분리하는단위가된다. 트랜잭션은전체가수행되거나또는전혀수행되지않아야한다 (all or nothing). EX) 은행업무를보는데 A 계좌 ( 박지성 ) 에서 B 계좌 ( 김연아 ) 로 10,000 원을이체할경우 BEGIN 1 A 계좌 ( 박지성 ) 에서 10,000원을인출하는 UPDATE 문 2 B 계좌 ( 김연아 ) 에 10,000원을입금하는 UPDATE 문 END

7 1.1 트랜잭션 BEGIN TRANSACTION 1 /* 박지성계좌를읽어온다 */ 2 /* 김연아계좌를읽어온다 */ /* 잔고확인 */ 3 /* 예금인출박지성 */ UPDATE Customer SET balance=balance WHERE name= 박지성 ; 주기억장치박지성트랜잭션김연아 버퍼 /* 예금입금김연아 */ UPDATE Customer SET balance=balance WHERE name= 김연아 ; 1 DBMS COMMIT /* 부분완료 */ 5 /* 박지성계좌를기록한다 */ 6 /* 김연아계좌를기록한다 */ 데이터베이스 박지성 김연아 COMMIT TRANSACTION (a) 계좌이체트랜잭션 (b) 트랜잭션수행과정 그림 8-1 계좌이체트랜잭션과수행과정

8 1.1 트랜잭션 트랜젝션수행과정 1 A 계좌 ( 박지성 ) 의값을하드디스크 ( 데이터베이스 ) 에서주기억장치버퍼로읽어온다 2 B 계좌 ( 김연아 ) 의값을하드디스크 ( 데이터베이스 ) 에서주기억장치버퍼로읽어온다. 3 A 계좌 ( 박지성 ) 에서 10,000원을인출한값을저장한다. 4 B 계좌 ( 김연아 ) 에 10,000원을입금한값을저장한다. 5 A 계좌 ( 박지성 ) 의값을주기억장치버퍼에서하드디스크 ( 데이터베이스 ) 에기록한다. 6 B 계좌 ( 김연아 ) 의값을주기억장치버퍼에서하드디스크 ( 데이터베이스 ) 에기록한다. 트랜잭션의종료 (COMMIT) 를알리는방법 [ 방법 1] COMMIT-5-6 [ 방법 2] COMMIT DBMS는사용자에게빠른응답성을보장하기위해 [ 방법 1] 을선택한다. 시작 (begin) 수행중 (active) 1234 부분완료 (partially committed) 버퍼내용기록 56 완료 (commit) 그림 8-2 트랜잭션의수행과정

9 1.2 트랜잭션의성질 표 8-1 트랜잭션과프로그램의차이점 구분트랜잭션프로그램 프로그램구조 BEGIN TRANSACTION... COMMIT TRANSACTION main() {... } 다루는데이터데이터베이스저장된데이터파일에저장된데이터 번역기 DBMS 컴파일러 성질원자성, 일관성, 고립성, 지속성 - 트랜잭션 프로그램 DBMS 데이터베이스 파일 그림 8-3 컴퓨터시스템내의트랜잭션과프로그램

10 1.2 트랜잭션의성질 트랜잭션의 ACID 성질 원자성 (Atomicity) : 트랜잭션에포함된작업은전부수행되거나아니면전부수행되지않아야 (all or nothing) 한다. 일관성 (Consistency) : 트랜잭션을수행하기전이나수행한후나데이터베이스는항상일관된상태를유지해야한다. 고립성 (Isolation) : 수행중인트랜잭션에다른트랜잭션이끼어들어변경중인데이터값을훼손하는일이없어야한다. 지속성 (Durability) : 수행을성공적으로완료한트랜잭션은변경한데이터를영구히저장해야한다.

11 1.2.1 원자성 원자성 (Atomicity) 이란트랜잭션이원자처럼더이상쪼개지지않는하나의프로그 램단위로동작해야한다는의미다. 즉일부만수행되는일이없도록전부수행하 거나아예수행하지않아야 (all or nothing) 하는성질이다. 표 8-3 트랜잭션제거명령어 (TCL) 명령어문법설명 BEGIN BEGIN { TRAN TRANSACTION } 트랜잭션의시작을표시 COMMIT COMMIT { TRAN TRANSACTION } 트랜잭션의종료를표시 ROLLBACK SAVE ROLLBACK { TRAN TRANSACTION } [ <savepoint> ] SAVE { TRAN TRANSACTION } { <savepoint> } 트랜잭션을전체혹은 <savepoint> 까지무효화시킴 <savepoint> 를만듬

12 1.2.1 원자성 다음은 T-SQL 에서트랜잭션을선언하고수행하는예다. BEGIN TRANSACTION INSERT INTO Book(bookid, bookname) VALUES (100, 'Database'); SAVEPOINT a; INSERT INTO Customer(custid, name) VALUES (100, ' 홍길동 '); SAVEPOINT b; INSERT INTO Orders(orderid, custid, bookid) VALUES (100, 100, 100);... /* a로롤백하면 Customer와 Orders 테이블에삽입은없던일이된다 */ IF (...) ROLLBACK to a;... /* b로롤백하면 b부터현재까지작업은없던일이된다 */ IF (...) ROLLBACK to b;... /* ROLLBACK 명령문을수행하면트랜잭션작업전체가없던일이된다 */ IF (...) ROLLBACK;... /* 이제까지진행한작업을데이터베이스에반영한다 */ COMMIT;

13 1.2.2 일관성 트랜잭션은데이터베이스의일관성 Consistency 을유지해야한다. 일관성은테이블 이생성될때 CREATE 문과 ALTER 문의무결성제약조건을통해명시된다. 계좌이체 (1 만원이체 ) 트랜잭션실행중 데이터베이스 (consistent) 데이터베이스 (inconsistent) 데이터베이스 (consistent) A 계좌 + B 계좌 = 20 만원 A 계좌 + B 계좌 = 19 만원 A 계좌 + B 계좌 = 20 만원 그림 8-4 데이터베이스변경중과변경후의일관성

14 1.2.3 고립성 데이터베이스는공유가목적이기때문에여러트랜잭션이동시에수행된다. 동시에수행되는트랜잭션은상호존재를모르고독립적으로수행되는데, 이를고립성 Isolation이라고한다. 고립성을유지하기위해서는트랜잭션이변경중인임시데이터를다른트랜잭션이읽고쓸때제어가필요하다. t1 시간트랜잭션 1 트랜잭션 2 트랜잭션 3 트랜잭션 4 테이블 A 테이블 B 테이블 C 테이블 D 데이터베이스 그림 8-5 트랜잭션의동시수행과데이터공유

15 1.2.4 지속성 트랜잭션이정상적으로완료 (commit) 혹은부분완료 (partial commit) 한데이터는 DBMS가책임지고데이터베이스에기록한다. 이러한성질을트랜잭션의지속성 (Durability) 이라고한다. DBMS 복구시스템은트랜잭션이작업한내용을수시로로그 (log) 데이터베이스에기록하였다가문제가발생하면로그파일을이용하여복구작업을수행한다. 시작 (begin) 수행중 (active) 부분완료 (partially committed) 버퍼내용기록 완료 (commit) 실패 (failed) 작업취소 취소 (aborted) 그림 8-6 트랜잭션의상태도 부분완료 (partially committed) : 트랜잭션수행은완료되었지만변경내용이데이터베이스에기록되었는지확실하지않은상태다. 이상태에서는 DBMS가최종적으로변경내용을데이터베이스에기록해야완료 (committed) 상태가된다. 만약시스템내부문제혹은시스템다운등으로 DBMS가변경내용을데이터베이스에기록하지못하면실패 (failed) 상태가된다. 실패 (failed) : 트랜잭션을중간에중단하였거나, 부분완료상태에서변경내용을데이터베이스에저장하지못한상태를말한다. 실패상태에서 DBMS는트랜잭션이수행한작업을모두원상복구시킨다.

16 1.3 트랜잭션과 DBMS DBMS는원자성을유지하기위해회복 ( 복구 ) 관리자프로그램을작동시킨다. DBMS는일관성을유지하기위해무결성제약조건을활용한다. DBMS는고립성을유지하기위해일관성을유지하는것과마찬가지로동시성제어알고리즘을작동시킨다. DBMS는지속성을유지하기위해회복관리자프로그램을이용한다. 트랜잭션의성질원자성일관성고립성지속성 DBMS 의기능 무결성제약조건 (SQL 문 ) 동시성제어 (LOCKING) 회복 (LOG DB) 그림 8-7 트랜잭션의성질과 DBMS 의기능

17 02. 동시성제어 갱신손실문제 락

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

19 2.1 갱신손실문제 갱신손실 (lost update) 문제는두개의트랜잭션이한개의데이터를동시에갱신 (update) 할때발생한다. 갱신손실문제는데이터베이스에서절대발생하면안되 는현상이다. [ 작업설명 ] 한개의데이터에두개의트랜잭션이접근하여갱신하는작업 [ 시나리오 ] 두개의트랜잭션이동시에작업을진행 [ 문제발생 ] 갱신손실 T2 는잘못된데이터로작업하여잘못된결과를만든다음 T1 의갱신작업을무효화하고덧쓰기를수행한것 이다. T1 의갱신이손실된갱신손실 (lost update) 문제가발생한것이다.

20 2.1 갱신손실문제 트랜잭션 TI 트랜잭션 T2 버퍼의데이터값 A=read_item(X); 1 A=A-100; X=1000 B=read_item(X); 2 B=B+100; X=1000 write_item(a->x); 3 X=900 write_item(b->x); 4 X=1100 트랜잭션 T1 버퍼 (BUFFER) 트랜잭션 T2 A 1 X B DBMS X 1000 데이터베이스 그림 8-8 갱신손실문제발생시나리오

21 2.2 락 갱신손실문제를해결하려면상대방트랜잭션이데이터를사용하는지여부를알 수있는규칙이필요하다. 즉자신이데이터를수정중이라는사실을알리면된다. 알리는방법으로락 (lock) 이라는잠금장치를사용한다.

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

23 2.2.1 락의개념 [ 작업설명 ] 한개의데이터에두개의트랜잭션이접근하여갱신하는작업 트랜잭션 T1 트랜잭션 T2 BEGIN TRAN; SELECT * FROM Book WHERE bookid=1; BEGIN TRAN; SELECT * FROM Book WHERE bookid=1; UPDATE SET WHERE Book price=7100 bookid=1; UPDATE SET WHERE Book price=price+100 bookid=1; SELECT * FROM Book WHERE bookid=1; COMMIT; SELECT * FROM Book WHERE bookid=1; COMMIT;

24 2.2.1 락의개념 [ 시나리오 ] SQL Server 에서두개의갱신트랜잭션을동시에실행 그림 8-10 SQL Server 에서두개의트랜잭션을동시에실행시키는화면

25 트랜잭션 T1 트랜잭션 T2 BEGIN TRAN; USE Madang; SELECT * FROM Book WHERE bookid=1; UPDATE Book SET price=7100 WHERE bookid=1; BEGIN TRAN; Use Madang; SELECT * FROM Book WHERE bookid=1; UPDATE Book SET price=price+100 WHERE bookid=1;...( 대기상태 )... SELECT * FROM Book WHERE bookid=1; COMMIT; / * COMMIT 과동시에결과가나타남 */ SELECT * FROM Book WHERE bookid=1; COMMIT;

26 2.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 요청대기대기

27 단계락킹 락을사용하면갱신손실문제를해결할수있다. 하지만락을걸고해제하는시점에제한을두지않으면두개의트랜잭션이동시에실행될때데이터의일관성이깨질수있다. 이것을방지하기위하여 2단계락킹 (2 phase locking) 기법을사용한다. 확장단계 (Growing phase, Expanding phase) : 트랜잭션이필요한락을획득하는단계로, 이단계에서는이미 획득한락을해제하지않는다. 수축단계 (Shrinking phase) : 트랜잭션이락을해제하는단계로, 이단계에서는새로운락을획득하지않는다. [ 작업설명 ] 두개의데이터에두개의트랜잭션이접근하여갱신하는작업

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

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

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

31 2.4 데드락 BEGIN TRAN; USE Madang; UPDATE Book SET price=price+100 WHERE bookid=1; UPDATE Book SET price=price+100 WHERE bookid=2; ( 대기상태 ) 트랜잭션 T1 BEGIN TRAN; USE Madang; UPDATE Book SET price=price*1.1 WHERE bookid=2; UPDATE Book SET price=price*1.1 WHERE bookid=1; ( 대기상태 ) 트랜잭션 T2

32 03. 트랜잭션고립수준 트랜잭션동시실행문제 트랜잭션고립수준명령어 트랜잭션고립수준실습

33 3.1.1 오손읽기 오손읽기 (dirty read) 는읽기작업을하는트랜잭션 1이쓰기작업을하는트랜잭션 2가작업한중간데이터를읽기때문에생기는문제다. 작업중인트랜잭션 2가어떤이유에서작업을철회 (ROLLBACK) 할경우트랜잭션 1은무효가된데이터를읽게되고잘못된결과를도출한다. 이현상을오손읽기라고한다. 오손읽기문제를이해하기위하여다음과같은실습테이블을생성해보자. /* 실습테이블생성 */ USE Madang; Drop TABLE Users; CREATE TABLE Users ( id INTEGER, name VARCHAR(20), age INTEGER); INSERT INTO Users VALUES (1, 'HONG GILDONG', 30); SELECT * FROM Users;

34 3.1.1 오손읽기 [ 작업설명 ] 두개의트랜잭션을동시에실행 트랜잭션 T1, T2 가동시에실행된다. T1 은읽기만하고 T2 는쓰기를한다. T1 은 T2 가변경한데이터를읽어와 작업하는데, T2 가작업중철회 (ROLLBACK) 를하게되었다. [ 문제발생 ] 오손읽기 T2가변경한데이터를 T1이읽은후어떤원인으로인하여 T2가스스로철회 (ROLLBACK) 를하게되었다. 철회를하면 T2의작업은없던일이된다. T1은 T2가종료하지않은상태에서 T2가변경한데이터를보고작업을하게된것이다. 아래는트랜잭션 T2가홍길동의나이를 30에서 21로변경한후철회 (ROLLBACK) 하여, 트랜잭션 T1에게오류를발생시킨예다.

35 T1( 읽는트랜잭션 ) READ UNCOMMITTED 모드 T2( 쓰는트랜잭션 ) READ COMMITTED 모드 BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM Users WHERE id=1; BEGIN TRAN; USE Madang; UPDATE Users SET age=21 WHERE id=1; SELECT * FROM Users WHERE id=1; SELECT * FROM Users WHERE id=1; ROLLBACK SELECT * FROM Users WHERE id=1; COMMIT;

36 3.1.2 반복불가능읽기 반복불가능읽기 (non-repeatable read) 는트랜잭션 1이데이터를읽고트랜잭션 2 가데이터를쓰고 ( 갱신, UPDATE) 트랜잭션 1이다시한번데이터를읽을때생기는문제다. 즉, 트랜잭션 1이읽기작업을다시한번반복할경우이전의결과와다른결과가나오는현상을반복불가능읽기라고한다. [ 작업설명 ] 두개의트랜잭션을동시에실행 트랜잭션 T1, T2 가동시에실행된다. T1 은읽기만하고 T2 는쓰기 ( 갱신, UPDATE) 를한다. T1 은데이터를읽고 작업을한후, T2 가변경한데이터를다시한번읽어와작업을한다. [ 문제발생 ] 반복불가능읽기 T1이데이터를읽고작업하던중 T2가데이터를변경하였다. T1은변경한데이터를보고다시한번작업을하였다. 오손읽기와달리이번에는 T2가 COMMIT을했기때문에틀린데이터는아니다. 그런데 T1 입장에서는같은 SQL 문이다른결과를도출한다.

37 3.1.2 반복불가능읽기 T1( 읽는트랜잭션 ) READ COMMITTED 모드 T2( 쓰는트랜잭션 ) READ COMMITTED 모드 BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT * FROM Users WHERE id=1; BEGIN TRAN; USE Madang; UPDATE Users SET age=21 WHERE id=1; COMMIT; SELECT * FROM Users WHERE id=1; SELECT * FROM Users WHERE id=1;

38 3.1.3 유령데이터읽기 유령데이터읽기 (phantom read) 는트랜잭션 1이데이터를읽고트랜잭션 2가데이터를쓰고 ( 삽입, INSERT) 트랜잭션 1이다시한번데이터를읽을때생기는문제다. 즉, 트랜잭션 1이읽기작업을다시한번반복할경우이전에없던데이터 ( 유령데이터 ) 가나타나는현상을유령데이터읽기라고한다. [ 작업설명 ] 두개의트랜잭션을동시에실행 트랜잭션 T1 은읽기만하고 T2 는쓰기 ( 삽입, INSERT) 를한다. T1 은데이터를읽고작업을한후, T2 가변경한 데이터를다시한번읽어와작업을한다. [ 문제발생 ] 유령데이터읽기 이번에는 T1이 T2가새로운데이터를삽입한사실을모르고작업을한다. T2가 COMMIT을했기때문에틀린데이터는아니다. 그러나 T1 입장에서는새로운데이터가반영되어반복불가능읽기와마찬가지로같은 SQL 문이다른결과를도출한다. 유령데이터읽기는반복불가능읽기와비슷하지만없던데이터가삽입되었기때문에다르게구분한다.

39 3.1.3 유령데이터읽기 T1( 읽는트랜잭션 ) READ COMMITTED 모드 T2( 쓰는트랜잭션 ) READ COMMITTED 모드 BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT * FROM Users WHERE age BETWEEN 10 AND 30; BEGIN TRAN; USE Madang; INSERT INTO Users VALUES (3, Bob, 27 ); COMMIT; SELECT * FROM Users WHERE age BETWEEN 10 AND 30; SELECT * FROM Users WHERE age BETWEEN 10 AND 30; COMMIT;

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

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

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

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

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

45 3.3.1 반복불가능읽기문제와방지를위한명령어 반복불가능읽기문제 트랜잭션 T1 READ COMMITTED 모드 ( 기본모드 ) 트랜잭션 T2 READ COMMITTED 모드 ( 기본모드 ) BEGIN TRAN; USE Madang; SELECT SUM(price) 총액 FROM Book; BEGIN TRAN; USE Madang; SELECT bookid, bookname, publisher, price FROM Book WHERE bookid=1; SELECT SUM(price) 총액 FROM Book; UPDATE Book SET price=price+500 WHERE bookid=1; SELECT SUM(price) 총액 FROM Book; COMMIT; SELECT SUM(price) 총액 FROM Book; /* 앞의결과와다름 */ COMMIT;

46 3.3.1 반복불가능읽기문제와방지를위한명령어 반복불가능읽기문제를방지하기위한명령어 -REPEATABLE READ 모드 트랜잭션 T1 REPEATABLE READ 모드 트랜잭션 T2 READ COMMITTED 모드 ( 기본모드 ) BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT SUM(price) 총액 FROM Book; BEGIN TRAN; USE Madang; SELECT bookid, bookname, publisher, price FROM Book WHERE bookid=1; SELECT SUM(price) 총액 FROM Book; /* 여기까지실행해본후진행 */ UPDATE Book SET price=price+500 WHERE bookid=1; ( 쿼리를실행하는중...) /* 대기상태가됨, T1이 COMMIT하면실행됨 */ SELECT SUM(price) 총액 FROM Book; /* 앞의결과와같음 */ COMMIT; SELECT SUM(price) 총액 FROM Book; COMMIT;

47 3.3.2 유령데이터읽기문제와방지를위한명령어 유령데이터읽기문제 트랜잭션 T1 REPEATABLE READ 모드 트랜잭션 T2 READ COMMITTED 모드 ( 기본모드 ) BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT SUM(price) 총액 FROM Book; BEGIN TRAN; USE Madang; SELECT SUM(price) 총액 FROM Book; INSERT INTO Book VALUES (11, 테스트, 테스트출판사, 5500); SELECT SUM(price) 총액 FROM Book; COMMIT; SELECT SUM(price) 총액 FROM Book; /* 앞의결과와다름 */ COMMIT;

48 3.3.2 유령데이터읽기문제와방지를위한명령어 유령데이터읽기문제를방지하기위한명령어 - SERIALIZABLE 모드 트랜잭션 T1 SERIALIZABLE 모드 트랜잭션 T2 READ COMMITTED 모드 ( 기본모드 ) BEGIN TRAN; USE Madang; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT SUM(price) 총액 FROM Book; BEGIN TRAN; USE Madang; SELECT SUM(price) 총액 FROM Book; /* 여기까지실행해본후진행 */ INSERT INTO Book VALUES (11, 테스트, 테스트출판사,5500); ( 쿼리를실행하는중...) /* 대기상태가됨, T1 이 COMMIT 하면실행됨 */ SELECT SUM(price) 총액 FROM Book; /* 앞의결과와같음 */ COMMIT; SELECT SUM(price) 총액 FROM Book; COMMIT;

49 04. 회복 트랜잭션과회복 로그파일 로그파일을이용한회복 체크포인트를이용한회복

50 04. 회복 회복 (recovery) 은데이터베이스에장애가발생했을때데이터베이스를일관성있 는상태로되돌리는 DBMS 의기능이다. 데이터베이스시스템에서발생할수있는장애의유형은다음과같다. 시스템충돌 : 하드웨어혹은소프트웨어의오류로인하여주기억장치가손실되는것을말한다. 주기억장치에상주하여처리중인프로그램과데이터의일부혹은전부가손실된다. 미디어장애 : 헤드의충돌이나읽기장애에의하여보조기억장치의일부데이터가손실되는것을말한다. 보조기억장치에저장중인데이터의일부혹은전부가손실된다. 응용소프트웨어오류 : 데이터베이스에접근하는소프트웨어의논리적인오류로트랜잭션의수행이실패하는것을말한다. 자연재해 : 화재, 홍수, 지진, 정전등에의해컴퓨터시스템이손상되는것을말한다. 부주의혹은태업sabotage : 운영자나사용자의부주의로데이터가손실되거나의도적인손상을입는것을말한다.

Customer SET balance=balance+10000 WHERE name= 김연아 ; 1 DBMS 2 5 6 COMMIT /* 부분완료 */ 5 /* 박지성계좌를기록한다 */

51 4.1 트랜잭션과회복 BEGIN TRANSACTION 1 /* 박지성계좌를읽어온다 */ 2 /* 김연아계좌를읽어온다 */ /* 잔고확인 */ 3 /* 예금인출박지성 */ UPDATE Customer SET balance=balance WHERE name= 박지성 ; 주기억장치박지성트랜잭션김연아 버퍼 /* 예금입금김연아 */ UPDATE Customer SET balance=balance WHERE name= 김연아 ; 1 DBMS COMMIT /* 부분완료 */ 5 /* 박지성계좌를기록한다 */ 6 /* 김연아계좌를기록한다 */ 데이터베이스 박지성 김연아 COMMIT TRANSACTION (a) 계좌이체트랜잭션 (b) 트랜잭션수행과정 그림 8-11 계좌이체트랜잭션과수행과정

52 4.1 트랜잭션과회복 BEGIN TRANSACTION 1 /* 박지성계좌를읽어온다 */ 2 /* 김연아계좌를읽어온다 */ /* 잔고확인 */ 3 /* 예금인출박지성 */ UPDATE Customer SET balance=balance-100 WHERE name= 박지성 ; 4 /* 예금입금김연아 */ UPDATE Customer SET balance=balance+100 WHERE name= 김연아 ; COMMIT /* 부분완료 */ 5 /* 박지성계좌를기록한다 */ 6 /* 김연아계좌를기록한다 */ 부분완료 (partially committed) 시작 (begin) A 수행중 (active) B-1 C-1 D-1 완료후실패 실패 (failed) 완료전실패 B-2 D-2 C-2 COMMIT TRANSACTION (a) 계좌이체트랜잭션 그림 8-12 트랜잭션수행과상태도 완료 (committed) (b) 트랜잭션상태도 취소 (aborted)

53 4.2 로그파일 DBMS는트랜잭션이수행중이거나수행이종료된후발생하는데이터베이스손실을방지하기위해트랜잭션의데이터베이스기록을추적하는로그파일 (log file) 을사용한다. 로그파일은트랜잭션이반영한모든데이터의변경사항을데이터베이스에기록하기전에미리기록해두는별도의데이터베이스다. 안전한하드디스크에저장되며전원과관계없이기록이남아있다. 로그파일에저장된로그의구조는다음과같다. < 트랜잭션번호, 로그의타입, 데이터항목이름, 수정전값, 수정후값 > 로그의타입 은트랜잭션의연산타입으로 START, INSERT, UPDATE, DELETE, ABORT, COMMIT 등이있다. 수정전값 은데이터의변경전값을나타내고, 수정후값 은연산의결과로변경된값을나타낸다. <T1, START> <T1, UPDATE, Customer( 박지성 ).balance, , 90000> <T1, UPDATE, Customer( 김연아 ).balance, , > <T1, COMMIT>

54 4.2 로그파일 BEGIN TRANSACTION 1 /* 박지성계좌를읽어온다 */ 2 /* 김연아계좌를읽어온다 */ /* 잔고확인 */ 3 /* 예금인출박지성 */ UPDATE Customer SET balance=balance WHERE name= 박지성 ; 주기억장치박지성트랜잭션김연아 버퍼 /* 예금입금김연아 */ UPDATE Customer SET balance=balance WHERE name= 김연아 ; 1 DBMS COMMIT /* 부분완료 */ 5 /* 박지성계좌를기록한다 */ 6 /* 김연아계좌를기록한다 */ 데이터베이스 박지성 김연아 트랜잭션로그파일 COMMIT TRANSACTION (a) 계좌이체트랜잭션 (b) 트랜잭션수행과정 그림 8-13 트랜잭션수행과로그파일

55 4.3 로그파일을이용한회복 데이터의변경기록을저장해둔로그파일을이용하면시스템장애도복구할수 있다. 아래두개의트랜잭션이실행된다고하자. 편의상트랜잭션의연산 SELECT, UPDATE 는 read_item( ), write_item( ) 으로대체한다. 트랜잭션은각각데이터 A, B, C, D 를읽거나쓰는작업을진행한다. 데이터 (A, B, C, D) 의초깃값은 (100, 200, 300, 400) 이다. 트랜잭션 T1 트랜잭션 T2 read_item(a); A=A+10; read_item(b); B=B+10; write_item(b); read_item(c); C=C+10; write_item(c); write_item(a); read_item(a); A=A+10; write_item(a); read_item(d); D=D+10; read_item(b) B=B+10; write_item(b); write_item(d);

56 4.3 로그파일을이용한회복 트랜잭션이 T1 T2 순으로실행된다면다음과같은로그파일이생성된다. 로그번호 로그레코드 [T1, START] [T1, UPDATE, B, 200, 210] [T1, UPDATE, C, 300, 310] [T1, UPDATE, A, 100, 110] [T1, COMMIT] [T2, START] [T2, UPDATE, A, 110, 120] [T2, UPDATE. B, 210, 220] [T2, UPDATE, D, 400, 410] [T2, COMMIT]

57 4.3 로그파일을이용한회복 시스템운영중장애가발생하여시스템이다시가동되었을때 DBMS는로그파일을먼저살펴본다. DBMS는트랜잭션이종료되었는지혹은중단되었는지여부를판단하여종료된트랜잭션은종료를확정하기위하여재실행 (REDO) 을진행하고, 중단된트랜잭션은없던일로되돌리기위해취소 (UNDO) 를진행한다. 트랜잭션의재실행 (REDO) 장애가발생한후시스템을다시가동을했을때, 로그파일에트랜잭션의시작 (START) 이있고종료 (COMMIT) 가있는경우다. COMMIT 연산이로그에있다는것은트랜잭션이모두완료되었다는의미다. 다만변경내용이버퍼에서데이터베이스에기록되지않았을가능성이있다. 따라서로그를보면서트랜잭션이변경한내용을데이터베이스에다시기록하는과정이필요하다. 이과정을 REDO라고한다. 트랜잭션의취소 (UNDO) 장애가발생한후시스템을다시가동했을때, 로그파일에트랜잭션의시작 (START) 만있고종료 (COMMIT) 가없는경우다. COMMIT 연산이로그에보이지않는다는것은트랜잭션이완료되지못했다는의미로, 트랜잭션이한일을모두취소해야한다. 이경우완료하지못했지만버퍼의변경내용이데이터베이스에기록되어있을가능성이있기때문에로그를보면서트랜잭션이변경한내용을데이터베이스에서원상복구시켜야한다. 이과정을 UNDO라고한다.

58 4.3 로그파일을이용한회복 즉시갱신방법즉시갱신 (immediate update) 은 갱신데이터 로그, 버퍼 데이터베이스 작업이부분완료전에동시에진행될수있으며, 부분완료가되면갱신데이터는로그에기록이끝난상태다. 지연갱신방법 지연갱신 (deferred update) 은 갱신데이터 로그 가끝난후부분완료를하고 버 퍼 데이터베이스 작업이진행되는방법이다.

59 4.3 로그파일을이용한회복 표 8-10 트랜잭션로그와회복방법 ( 즉시갱신방법 ) 로그번호작업결과 i =0 아무작업도필요없음 T1 과 T2 가수행을시작하지않았음 1 <= i <= 4 UNDO(T1) : T1을취소 T1이 i까지생성한로그레코드를이용하여데이터베이스항목을되돌림 5 <= i <= 9 REDO(T1) : T1을재수행 1부터 4까지 T1이생성한로그레코드를이용하여데이터베이스항목값을기록함 UNDO(T2) : T2를취소 T2가 5부터 i까지생성한로그레코드를이용하여데이터베이스항목을되돌림 10 REDO(T1) : T1을재수행 REDO(T2) : T2를재수행 T1을수행하지않은것과같음 T1은수행이완료됨 T2는수행하지않은것과같음 T1, T2는수행이완료됨

60 4.3 로그파일을이용한회복 표 8-11 트랜잭션로그와회복방법 ( 지연갱신방법 ) 로그번호작업결과 i =0 아무작업도필요없음 T1 과 T2 가수행을시작하지않았음 1 <= i <= 4 T1 : 아무작업도필요없음 T1 을수행하지않은것과같음 5 <= i <= 9 REDO(T1) : T1을재수행 1부터 4까지 T1이생성한로그레코드를이용하여데이터베이스항목값을기록함 T2 : 아무작업도필요없음 10 REDO(T1) : T1을재수행 REDO(T2) : T2를재수행 T1 은수행이완료됨 T2 는수행하지않은것과같음 T1, T2 는수행이완료됨

61 4.4 체크포인트를이용한회복 로그를이용한회복은시스템에장애가일어났을때어느시점까지되돌아가야하는지알수없다. 트랜잭션이많은응용의경우하루이상되돌아가서복구하는것은사실상불가능하다. 회복시많은양의로그를검색하고갱신하는시간을줄이기위하여몇십분단위로데이터베이스와트랜잭션로그파일을동기화한후동기화한시점을로그파일에기록해두는방법혹은그시점을체크포인트 (checkpoint, 혹은검사점 ) 라고한다. 체크포인트시점에는다음과같은작업을진행한다. 주기억장치의로그레코드를모두하드디스크의로그파일에저장한다. 버퍼에있는변경된내용을하드디스크의데이터베이스에저장한다. 체크포인트를로그파일에표시한다.

62 4.4 체크포인트를이용한회복 체크포인트가있으면로그를이용한회복기법은좀더간단해진다. 체크포인트이전에 [COMMIT] 기록이있는경우 아무작업이필요없다. 로그에체크포인트가나타나는시점은이미변경내용이데이터베이스에모두기록된 후이기때문이다. 체크포인트이후에 [COMMIT] 기록이있는경우 REDO(T) 를진행한다. 체크포인트이후에변경내용이데이터베이스에반영되지않았으므로 REDO 를진행한다. 체크포인트이후에 [COMMIT] 기록이없는경우 즉시갱신방법을사용했다면 UNDO(T) 를진행한다. 버퍼의내용이반영됐을수도있기때문에원상복구시켜야한다. 반면지연갱신방법을사용했다면아무것도할필요가없다. 지연갱신방법은 [COMMIT] 이전에는버퍼의내용을데이터베이스에반영하지않기때문이다.

63 4.4 체크포인트를이용한회복 즉시갱신방법을사용했다면 T2, T3 는아무작업이필요없고, T4, T5 는 REDO, T1, T6 는 UNDO 가필요하다. 지연갱신방법을사용했다면 T2, T3 는아무작업이 필요없고, T4, T5 는 REDO 가필요하다. T1, T6 는아무작업이필요없다. 트랜잭션 T1 트랜잭션 T2 트랜잭션 T3 트랜잭션 T4 트랜잭션 T5 트랜잭션 T6 시간 t 체크포인트시스템장애 그림 8-15 트랜잭션로그기록과체크포인트

64 4.4 체크포인트를이용한회복 트랜잭션 T1, T2, T3 가동시에실행된후다음과같이로그기록을남겼다. 즉시갱 신기법을사용하여회복을한다면 REDO(T2), UNDO(T3) 가진행된다. T1 에대해 서는아무작업이필요없다. 로그번호 로그레코드 [T1, START] [T1, UPDATE, B, 200, 120] [T1, UPDATE, C, 300, 310] [T2, START] [T2, UPDATE, A, 110, 120] [T1, UPDATE, A, 120, 110] [T1, COMMIT] [T2, UPDATE. B, 120, 220] [CHECKPOINT] [T3, START] [T3, UPDATE, A, 110, 120] [T2, UPDATE, D, 400, 410] [T2, COMMIT] [T3, UPDATE. B, 220, 230] ~~ 시스템장애 ~~ 그림 8-16 체크포인트가포함된로그기록

65 요약 1. 트랜잭션의상태도 2. 트랜잭션의성질 3. 동시성제어 4. 갱신손실 5. 락 6. 2단계락킹 7. 데드락 8. 트랜잭션동시실행문제 9. 트랜잭션고립수준명령어 10. 로그파일을이용한회복 11. 회복을위한로그기록방법 12. 체크포인트

66 SQL Server 로배우는데이터베이스개론과실습

<4D F736F F F696E74202D2039C0E52DC6AEB7A3C0E8BCC72E BC8A3C8AF20B8F0B5E55D>

<4D F736F F F696E74202D2039C0E52DC6AEB7A3C0E8BCC72E BC8A3C8AF20B8F0B5E55D> 트랜잭션 (transaction) 항공기예약, 은행, 신용카드처리, 대형할인점등에서는대규모데이터베이스를수백, 수천명이상의사용자들이동시에접근함 많은사용자들이동시에데이터베이스의서로다른부분또는동일한부분을접근하면서데이터베이스를사용함 동시성제어 (concurrency control) 동시에수행되는트랜잭션들이데이터베이스에미치는영향은이들을순차적으로수행하였을때데이터베이스에미치는영향과같도록보장