학습목표 학습목표 본 장은 동시성 제어와 잠금(lock) 등 효과적인 트랜잭션 관리 기법 과 필요한 명령을 다룬다. 또한 데이터베이스의 장애에 대비하여 안전한 데이터의 관리를 위한 백업과 복원 기법, 서로 다른 DBMS 간이나 다른 서버 사이의 데이터 교환을 위한 데이터.

Presentation on theme: "학습목표 학습목표 본 장은 동시성 제어와 잠금(lock) 등 효과적인 트랜잭션 관리 기법 과 필요한 명령을 다룬다. 또한 데이터베이스의 장애에 대비하여 안전한 데이터의 관리를 위한 백업과 복원 기법, 서로 다른 DBMS 간이나 다른 서버 사이의 데이터 교환을 위한 데이터."— Presentation transcript:

1 학습목표 학습목표 본 장은 동시성 제어와 잠금(lock) 등 효과적인 트랜잭션 관리 기법 과 필요한 명령을 다룬다. 또한 데이터베이스의 장애에 대비하여 안전한 데이터의 관리를 위한 백업과 복원 기법, 서로 다른 DBMS 간이나 다른 서버 사이의 데이터 교환을 위한 데이터 내보내기와 가져오기 기법도 다룬다.

2 트랜잭션 관리 1. 트랜잭션 관리 1.1 트랜잭션의 개념 개념 : 하나 이상의 작업이 모여 이루어진 하나의 논리적인 작업 단위로서 하나의 트랜잭션은 한 번에 수행된다. 트랜잭션의 예 : 아래의 4가지 작업은 서로 상관관계가 있는 작업이다. 그러므로 이 작업 과정 중에 오류가 생기면 전체 작업을 취소하도록 관리되어야 한다. 이것을 트랜잭션이라 한다. 1. 홍길동의 계좌를 조회하여 100만원이 있는지 확인한다. 2. 홍길동의 계좌에서 100만원을 뺀다. 3. 이순신의 계좌에 100만원을 더한다. 4. 이순신의 계좌에 잔액을 확인한다.

3 트랜잭션 관리 1.1 트랜잭션의 개념 -계속 트랜잭션의 조건 트랜잭션 관리 기법 조건 의미 원자성
1.1 트랜잭션의 개념 -계속 트랜잭션의 조건 조건 의미 원자성 트랜잭션은 더 이상 나눌 수 없는 단위로 구성되어야 한다. 일관성 트랜잭션이 수행된 후 모든 데이터는 무결성을 유지해야 한다. 격리성 현재 변경 작업이 진행 중인 트랜잭션의 대상 데이터는 잠금 상태(lock)가 되어 다른 트랜잭션에 의해 접근이 허용되어서는 안 된다. 트랜잭션 관리 기법 기법 의미 동시성 제어 다수 사용자에 의한 요청된 여러 트랜잭션들의 상호작용을 제어하여 각 트랜잭션들이 독립적으로 수행되는 것처럼 관리하는 기법 회복 예고 없는 시스템 장애로 인해 정상적인 종료를 하지 못한 트랜잭션들을 회복기능을 이용하여 데이터의 일관성을 유지하는 기법.

4 트랜잭션 관리 1.2 트랜잭션의 종류 자동 커밋 트랜잭션 SQL문 단위로 트랜잭션을 관리하는 방법
1.2 트랜잭션의 종류 자동 커밋 트랜잭션 SQL문 단위로 트랜잭션을 관리하는 방법 명령이 성공하면 자동 commit 처리 SQL-Server의 기본 트랜잭션 처리 모드 명시적 트랜잭션 트랜잭션의 시작과 끝을 명시하는 방법 BEGIN TRAN문, COMMIT TRAN문, ROLLBACK TRAN문 등 이용 rollback이나 commit 이 되지 않으면 계속 잠금(lock) 상태에 있음

5 트랜잭션 관리 1.3 트랜잭션 구문 BEGIN TRAN 명시적 트랜잭션의 시작 위치를 의미한다. COMMIT TRAN
1.3 트랜잭션 구문 BEGIN TRAN 명시적 트랜잭션의 시작 위치를 의미한다. COMMIT TRAN 데이터 변경 작업의 결과를 실제 데이터베이스에 적용하도록 하는 구문이다. COMMIT 된 후의 데이터베이스는 변경 전으로 되돌릴 수 없다. ROLLBACK TRAN 데이터의 변경 작업을 취소하고  데이터베이스의 내용은 트랜잭션작업 이전의 상태로 되돌린다.  여기서 전체 트랜잭션의 내용을 취소하지 않기 위해 특정 지점에 저장점(SAVE POINT)을 지정하면 저장점이 있는 부분까지만 트랜잭션을 취소할 수 있다. SAVE TRAN 저장점을 지정하기 위한 구문으로 지정된 저장점까지 rollback으로 트랜잭션을 취소할 수 있다. <참고: 저장점> 트랜잭션의 특정 부분을 지정해 두는 것으로 필요 시에 저장점까지 commit 이나 rollback 할 수 있는 효율적인 기능

6 트랜잭션 관리 1.3 트랜잭션 구문 - 계속 트랜잭션 구문(commit, rollback, save point 문) 을 이용한 예 ① delete 상품2           ---> 자동 commit 되어 ‘상품2’ 테이블 삭제 작업 실행 ② begin tran tr_start            > 명시적 트랜잭션 시작 ③ insert into 상품2(상품코드, 소코드, 색상코드, 사이즈, 재고수량)                 values('P1','S1','C1', 'XL',10)        > 레코드 삽입 ④ insert into 상품2(상품코드, 소코드, 색상코드, 사이즈, 재고수량)         values('P2', 'S2','C2','L',20)          > 레코드 삽입 ⑤ commit tran         > ③번, ④번 삽입문 실행  ⑥ begin tran tr_start          ---> 명시적 트랜잭션 시작 ⑦ save  tran save_point_1         --> 저장점 지정 ⑧ insert into 상품2(상품코드, 소코드, 색상코드, 사이즈, 재고수량)        values('P3','S3','C31', 'XL',30)          > 레코드 삽입 ⑨ rollback tran save_point_1           > ⑦번 위치까지 rollback, 즉 ⑧번 작업 취소

7 트랜잭션 관리 1.4 잠금(Lock) 개념 : 트랜잭션이 실행될 때 대상 데이터에 대해 다른 트랜잭션의 접근을 막는 기법
개념 : 트랜잭션이 실행될 때 대상 데이터에 대해 다른 트랜잭션의 접근을 막는 기법 특징 : 트랜잭션의 무결성 및 데이터베이스의 일관성을 유지 잠금이 수행되는 동안 다른 트랜잭션은 기다리게 되므로 서로 충돌하지 않고 고유한 데이터를 액세스할 수 있게 된다.  여러 명의 사용자가 동일한 데이터에 접근하려고 할 때 데이터베이스는 동시성 제어를 하게 된다. SQL-Server의 잠금의 대상 잠금의 대상 의미 데이터베이스 데이터베이스 스키마를 변경할 때 사용 테이블 가장 잠금이 많이 사용되는 대상으로 데이터 전체와 인덱스를 포함하여 잠금을 수행 익스텐트 인접한 데이터 페이지 8개 또는 인덱스 페이지 그룹을 잠금 페이지 특정 페이지의 데이터 또는 인덱스 키들을 잠금 키 인덱스의 행 단위의 잠금 행 또는 행 식별자 특정 테이블의 한 행에 대해 잠금 (정확히는 행 식별자를 이용한 행의 잠금)

8 commit 되지 않은 데이터를 읽어 올 수 있는 수준으로 이때는 잘못된 데이터를 가져올 가능성이 있다
트랜잭션 관리 1.4 잠금(Lock) - 계속 SQL-Server의 잠금 수준 단계 종류 내용 성능 무결성 0단계 READ UNCOMMITTED commit 되지 않은 데이터를 읽어 올 수 있는 수준으로 이때는 잘못된 데이터를 가져올 가능성이 있다 . 1단계 READ COMMITTED commit 되지 않은 데이터에  잠금을 걸어 다른 사용자가 읽지 못하게 하는 것, SQL Server의 기본 잠금 수준이다. 2단계 REPEATABLE READ 다른 트랜잭션의 commit되지 않은 결과를 가져오지 않지만 삽입된 행은 계속 읽어오므로 처음 질의 결과와 나중 질의 결과가 다를 수 있다. 3단계 SERIALIZABLE 전체 트랜잭션이 끝날 때 까지 계속 잠금 상태가 되도록 하는 것 높음 낮음 낮음 높음 잠금 정의 명령 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -> 다른 수준의 잠금이 지정되면 먼저 지정된 수준은 해제됨

9 트랜잭션 관리 1.5 교착상태(DeadLock)
특징 : 교착상태가 발생하면 대상 데이터를 처리하려는 트랜잭션들이 계속 늘어나 서버의 리소스를 많이 사용하고 수행속도가 현저히 떨어진다. 교착상태기 일어나면 SQL-Server는 손실이 가장 적은 트랜잭션을 포기하고 그 트랜잭션을 RollBack 한다. 교착상태를 완전히 없앨 수는 없다. 해결책 트랜잭션 내의 개별 작업들이 같은 순서로 데이터에 접근 트랜잭션의 단위를 짧게 유지 트랜잭션끼리의 상호 작용 금지 불필요한 잠금의 사용 금지

10 2. 데이터의 백업과 복원 백업과 복원 2.1 백업의 개념 2.2 백업의 종류
2.1 백업의 개념 개념 : 데이터베이스 복사본과 주기적으로 데이터와 트랜잭션에 수행되는 변경 작업에 대한 기록인 로그들을 저장해 두어 데이터베이스에 문제가 발생하면 손쉽게 데이터베이스를 복원하도록 하는 원리 2.2 백업의 종류 종류 내용 비고 전체백업 데이터베이스 전체를 백업, 많은 시간 소요하지만 주기적으로 꼭 해야 함 차등백업 데이터베이스의 변경된 부분만 백업 전체백업 필요 트랜잭션 로그백업 데이터베이스에 수행된 변경 작업에 대한 로그파일을 이용한 백업 파일그룹백업 특정 파일 그룹에 대해서만 백업 데이터파일 백업 특정 파일 그룹 안에 있는 데이터 파일에 대해서만 백업

11 2. 데이터의 백업과 복원 백업과 복원 2.3 백업 실습(전체 백업) 전체 백업을 위한 준비 OFF-Line으로 만들기
2.3 백업 실습(전체 백업) 전체 백업을 위한 준비 OFF-Line으로 만들기 ON-Line 인 상태에서 사용 중인 데이터 파일은 백업 불가능 현재 데이터베이스에 접근 중인 사용자가 있을 때는 OFF-Line 할 수 없다. SQL-Server의 백업기능을 이용하지 않고 바로 디스크의 데이터베이스 파일을 복사해도 된다. 사용 중인 데이터베이스 OFF-Line 하기 엔터프라이즈 관리자의 ‘트리’ 창에서 대상 데이터베이스 선택 마우스 오른쪽 눌러 단축메뉴에서 ‘모든 작업’ 선택 하위메뉴의 ‘오프라인 시키기’ 선택

12 백업과 복원 2.3 백업 실습(전체백업) - 계속 1. 백업 초기 화면 2. 백업 방법 선택

13 백업과 복원 2.3 백업 실습(전체백업) - 계속 3. 백업 저장 위치 지정 4. 백업 완료

14 백업과 복원 2.3 백업 실습(트랜잭션 로그백업) 1. 트랜잭션 로그 백업 초기 화면 2. 로그 백업 마법사 선택

15 백업과 복원 2.3 백업 실습(트랜잭션 로그백업) - 계속 1. 로그 백업 마법사 화면 2. 백업 데이터베이스 지정하기

16 백업과 복원 2.3 백업 실습(트랜잭션 로그백업) - 계속 3. 백업 유형 선택 ->트랜잭션 로그 선택
2.3 백업 실습(트랜잭션 로그백업) - 계속 3. 백업 유형 선택 ->트랜잭션 로그 선택 4. 백업 저장 위치 지정 -> 파일 선택 및 위치 지정

17 백업과 복원 2.3 백업 실습(트랜잭션 로그백업) - 계속 5. 백업 예약 창 -> 일정 에 체크
2.3 백업 실습(트랜잭션 로그백업) - 계속 5. 백업 예약 창 -> 일정 에 체크 -> 변경 버튼 누름 6. 백업 일정 편집 -> 일정 변경 후 되풀이 선택

18 백업과 복원 2.3 백업 실습(트랜잭션 로그백업) - 계속 7. 백업 완료

19 백업과 복원 2.4 복원 실습(전체 복원) 1. 복원 초기 화면

20 백업과 복원 2.4 복원 실습(전체 복원) - 계속 2. 복원할 대상 선택 3. 복원 완료

21 3. 데이터 가져오기와 내보내기 가져오기와 내보내기 개념 : 서로 다른 데이터베이스 서버 간의 데이터 변환 서비스 및
IMPORT / EXPORT 서비스 대상 : 데이터 파일, 스프레드 시트 파일, 텍스트 파일 작업 방식 원본의 복사 뷰 및 SQL문의 실행에 의한 가져오기 및 내보내기 SQL-Server는 가져오기 / 내보내기 마법사 지원 SQL-Server ORACLE ACCESS TEXT-file SpreadSheet

22 가져오기와 내보내기 3.1 데이터 내보내기 실습 1. 시작-> 프로그램 -> MicroSoft SQL Server -> 데이터 가져오기 및 내보내기를 선택

23 3.1 데이터 내보내기 실습- 계속 가져오기와 내보내기
3.1 데이터 내보내기 실습- 계속 2. 내보내기 할 데이터 원본의 형식 선택(‘Microsoft OLE DB Provider for SQL Server' ) -> 대상 데이터베이스 선택(‘manpower’)

24 3.1 데이터 내보내기 실습- 계속 가져오기와 내보내기
3.1 데이터 내보내기 실습- 계속 3. 저장할 대상 유형 선택(‘Microsoft Access’) ->저장할 파일 지정(미리 만들어 둔, 또는 기존의 ‘test_mdb’ 선택)

25 3.1 데이터 내보내기 실습- 계속 가져오기와 내보내기
3.1 데이터 내보내기 실습- 계속 4. 내보내기 방법 지정(‘원본 데이터베이스에서 테이블 및 뷰 복사’ 선택)

26 가져오기와 내보내기 3.1 데이터 내보내기 실습- 계속 5. 내보내기 할 테이블 이나 뷰 선택 (여기서는 ‘모두 선택’ 선택)

27 3.1 데이터 내보내기 실습- 계속 가져오기와 내보내기
3.1 데이터 내보내기 실습- 계속 6. 내보내기 할 시점이나 저장 여부 선택 (여기서는 ‘즉시 실행’ 선택)

28 3.1 데이터 내보내기 실습- 계속 가져오기와 내보내기 7. 내보내기 완료 후 결과 확인
3.1 데이터 내보내기 실습- 계속 7. 내보내기 완료 후 결과 확인 -> Access 데이터베이스 실행 후 ‘test.mdb’ 열어서 ‘manpower’ 데이터베이스의 모든 테이블이 들어왔는지 확인

29 가져오기와 내보내기 3.2 데이터 가져오기 실습 가져오기 할 대상 데이터(여기서는 MS-EXCEL의 시트파일 'Sales2004.xls’ 에서 ‘탁자출고현황’과 ‘출하가격표’ 두 개의 시트이다)

30 3.2 데이터 가져오기 실습 - 계속 가져오기와 내보내기 2. 가져오기 대상 종류 지정(‘MicroSoft Excel’)
3.2 데이터 가져오기 실습 - 계속 2. 가져오기 대상 종류 지정(‘MicroSoft Excel’) 3. 가져오기 할 파일 지정(‘sales2004.xls’)

31 3.2 데이터 가져오기 실습 - 계속 가져오기와 내보내기
3.2 데이터 가져오기 실습 - 계속 4. 가져와서 저장할 대상 지정(‘MicroSoft OLE DB SQL Server’) 5. 가져올 방법 지정(‘원본의 테이블 및 뷰 복사’)

32 3.2 데이터 가져오기 실습 - 계속 가져오기와 내보내기 6. 가져올 대상 시트 선택(‘출하가격표’ 와 ‘탁자출고현황’)
3.2 데이터 가져오기 실습 - 계속 6. 가져올 대상 시트 선택(‘출하가격표’ 와 ‘탁자출고현황’) 7. 가져오기 할 시점 지정(‘즉시 실행’)

33 3.2 데이터 가져오기 실습 - 계속 가져오기와 내보내기 8. 가져오기 진행 및 결과 메세지
3.2 데이터 가져오기 실습 - 계속 8. 가져오기 진행 및 결과 메세지 9. 엔터프라이즈 관리자 화면에서 결과 확인

34 3.2 데이터 가져오기 실습 - 계속 가져오기와 내보내기 10. ‘탁자출고현황’ 테이블 상태
3.2 데이터 가져오기 실습 - 계속 10. ‘탁자출고현황’ 테이블 상태 공백 셀은 <NULL> 상태로 변환 항목의 이름(필드 명에 해당하는 것)들도 데이터 값으로 처리 열에 따른 필드명은 F1, F2, F3,,,로 변환 여러 셀에 걸쳐 입력되어 있던 제목은 하나의 데이터로 변환

35 답: 커밋(commit), 롤백(rollback), 저장점(save point)
단원 요약 단원요약 1 ★ 한 번에 수행되는 한 단위의 작업을 이라 한다. 여러 트랜잭션의 수행을 위해 데이터베이스는 잠금(Lock) 이라는 기법을 이용해 를 한다. ? ? 답: 트랜잭션. 병행성 제어 단원요약 2 ★ 트랜잭션의 전체 또는 일부를 데이터베이스에 적용하는 구문은 이고 취소하는 구문은 이다. 효율적인 트랜잭션의 관리를 위해 트랜잭션의 특정 위치를 지정하여 관리하는 것을 라고 한다. ? ? ? 답: 커밋(commit), 롤백(rollback), 저장점(save point)

36 답: 전체백업, 트랜잭션 로그백업, 데이터 내보내기/가져오기
단원 요약 단원요약 3 ★ 커밋(commit)이 끝나지 않는 트랜잭션에 대해 잠금이 계속되어 그 데이터를 사용하려는 다른 트랜잭션들이 무한정 기다리는 상태가 되는 것을 이라고 한다. ? 답: 교착상태(deadlock) 단원요약 4 ★ 백업의 종류는 반드시 주기적으로 수행해야 하는 이 있고 변경된 부분의 로그만을 대상으로 하는 이 있다. 데이터베이스 서버 간, 또는 다른 유형의 데이터를 변환을 하는 기능을 라고 한다. ? ? ? 답: 전체백업, 트랜잭션 로그백업, 데이터 내보내기/가져오기