[MSSQL]MS-SQL 5일차 - Backup / Restore / Lock / Block / Cursor

### 백업 본 설정 및 복구(*** MDF  파일 삭제 된 후 마지막 Tailog--트랜잭션 로그 백업 필히 작업 후 복구 작업 시작)

** 복구 모델 전체 / 단순(트랜잭션 로그는 백업 안됨--noarchive mode)

drop database zDB

go

create database zDB

go

use zdb

go

create table T1 (id int)

go

-- 0. 백업폴더 생성

insert into T1 values (10),(20),(30)

select * from T1

go

-- 1. 전체백업

insert into T1 values (40),(50),(60)

select * from T1

go

-- 2. 차등백업

insert into T1 values (70),(80)

select * from T1

go

-- 3. 로그백업

insert into T1 values (90),(100)

select * from T1

go

-- 4. 차등백업

insert into T1 values (200),(300)

select * from T1

go

-- 장애

 - 데이터베이스 오프라인

 alter database zDB set offline

 - zDB.mdf 삭제

 - 데이터베이스 온라인

alter database zDB set online

-- Tail-Log 백업 ********

 backup log zDB to disk='c:\drive\zDB_last3.bak'

with no_truncate, init --(비상시백업,백업매체초기화)

-- 복원

-- 1. 기존 백업본을 통한 복원 (옵션: With norecovery)

--> 복원중 표시되고, 뒤에 복원을 또 할 수 있음.

-- 2. Tail-Log 백업본을 통한 복원 (옵션: With recovery)

--> 복원 완료를 의미, 뒤에 복원을 할 수 없음.

-- 확인

use zDB

select * from T1

## Shrink 테스트

ShrinkLog.sql

 ### 잠금의 단위

 

 - RID : 테이블에 있는 한 행을 잠그기 위한 행 ID입니다. 

 - 키(Key) : 인덱스에 있는 행 잠금입니다. 

 - 페이지(Page) : 8킬로바이트(KB) 데이터 페이지 또는 인덱스 페이지입니다. 

 - 익스텐트(Extent) : 인접한 여덟 개의 데이터 페이지 또는 인덱스 페이지 그룹입니다. 

 - 테이블(Table) : 모든 데이터와 인덱스가 포함된 전체 테이블입니다.  

 - 데이터베이스(DB) : 데이터베이스입니다. 

 ### 잠금의 종류

 - 공유(S) : (Shared Lock) SELECT 문처럼 데이터를 변경하거나 

업데이트하지 않는 작업(읽기 전용 작업)에 사용합니다. 

공유 잠금을 형성하여 다른 트랜잭션에 의해 현재의 데이터가 

데이터가 변경되지 못하도록 합니다.  

 - 업데이트(U) : (Update Lock) 데이터를 UPDATE 하기 위해 

Exclusive Lock을 형성하기 전에 미리 걸어주는 잠금입니다. 

즉, "곧 변경할테니까 접근하지 마라!" 라는 의미로 보시면 됩니다.  

 - 단독(X) : (Exclusive Lock)  INSERT, UPDATE, DELETE와 같은 

데이터 수정 작업에 사용합니다. 

여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 

이루어지지 못하게 합니다.

데이터 읽기 조차도 못하게 됩니다. 

 - 내재 : (Intent Lock)  잠금 계층 구조를 만드는 데 사용합니다. 

내재된 잠금의 종류에는 내재된 공유(IS) 잠금, 

내재된 단독(IX) 잠금, 공유 및 내재된 단독(SIX) 잠금이 있습니다. 

예를 들어 레코드에 공유잠금(S)이 형성되어 있으면 

이 레코드를 포함하는 테이블은 내재된 공유잠금(IS)를 형성하여 

"레코드에 공유 잠금이 형성되어 있으니 너희들은 나에게 

단독 잠금(Exclusive Lock) 걸면 안돼!" 라고 알려주는 것입니다. 

 - 스키마 : (Schema Lock)  테이블의 스키마에 종속되는 작업이 실행될 때 사용합니다. 

스키마 잠금의 종류에는 스키마 수정(Sch-M)과 스키마 안전성(Sch-S) 

두 가지가 있습니다.  

 - 대량 업데이트(BU) : (Bulk Update Lock) 데이터를 테이블로 

대량 복사하는 경우와 TABLOCK 참고가 지정된 경우 사용합니다. 

 ### 고립화 수준(Isolation Level) 

 - 아직 UPDATE가 완료 되지 않았기 때문에 UPDATE 되기전 

잘못된 데이터를 읽을 위험이 있으므로 이를 방지하기 위함입니다. 

하지만 원한다면 UPDATE 작업이 완료되지 않은 상태의 데이터를 

다른 세션에서 읽을 수 있도록 할 수 있습니다.

 - 트랜잭션의 고립화 수준이란 여러개의 트랜잭션이 동시에 수행 될 때 

잠금 충돌이 발생 하면 이를 어떻게 처리할지에 대한 문제라고 보시면 됩니다. 

고립화 수준을 어떻게 하느냐에 따라 다른 결과를 볼수 있게 됩니다.

 - 두 세션의 잠금 현상을 이용해보도록 하겠습니다. 

한 세션이 UPDATE 중일 다른 세션은 이 UPDATE 작업이 완료 될때까지 

그 데이터를 대상으로 검색을 할 수가 없었습니다. 

왜냐하면 현재 진행중인 UPDATE 작업이 COMMIT 될 지 

ROLLBACK 될 지 아직 모르기 때문에 완벽하지 않은 데이터를 보는것을 

방지하도록 하기 위해서입니다. 

하지만 다음과 같이 트랜잭션의 고립화 수준을 별도로 지정하게 되면 

결과는 달라집니다.

  --> SET TRANSACTION ISOLATION LEVEL 

   READ UNCOMMITTED

   GO 

 

 ### 고립화 수준(Isolation Level)의 종류

 - 고립화 수준은 낮을 수록 성능은 좋아집니다. 

하지만 데이터의 무결성은 깨어질 가능성이 높습니다. 

트랜잭션의 고립화 수준에는 다음과 같이 4가지 종류가 있습니다.

 1) READ UNCOMMITTED

  : Trouble

 - DirtyRead

 - NonRepeatable Read

 - Phantom Read

고립화 수준에서 가장 낮은 수준입니다. 

즉, DIrty Page를 읽어오게 되는 것입니다. 

2) READ COMMITTED (Default)

: Trouble

 - NonRepeatable Read

 - Phantom Read

  Dirty Page를 읽는, 즉 DIrty Read를 방지합니다. 

  별도의 고립화 수준을 설정하지 않았거나 

READ COMMITTED를 설정하게 되면 

  COMMIT 하지 않은 트랜잭션이 이용한 자원에 대해 

트랜잭션이 완료되어야 

(= 잠금이 해제되어야) 데이터를 읽을 수 있습니다.

3) REPEATABLE READ

: Trouble

 - Phantom Read

SQL Server는 기본적으로 같은 값을 반복적으로 가져 올 경우 이전 값과 

항상 같다는 것을 보장하지 못합니다. 

이러한 것을 Nonrepeatable Read라고 하는데 이러한 문제는 

한 트랜잭션 내에서 같은 SQL문을 여러번 사용할 경우 내부적으로 

공유 잠금(S)를 트랜잭션 종료시까지 유지하지 않기 때문입니다. 

REPEATABLE READ 고립화 수준은 이러한 문제를 해결할 수 있는 고립화 수준입니다. 

트랜잭션이 완료될 때까지 공유 잠금을 유지하여 여러번 같은 데이터를 읽어도 

항상 같은 값이 얻어지도록 합니다.

주의해야할 사항은 REPEATABLE READ가 설정되면 

원래 읽었던 데이터에 대하여서는 항상 같은 값이 유지 되지만 

새로 추가되는 데이터를 막을 수는 없습니다. 

예를 들어 특정 조건에 만족한 레코드가 5개였으면 

이 5개의 레코드는 언제든 같은 값을 보이게 되지만 

다른 트랜잭션에 의해 이 조건을 만족하는 새로운 레코드가 추가되면 

원래 없었던 새로운 레코드가 보이게 됩니다. 

이러한 것을 Pantom Read라고 합니다.

4) SERIALIZABLE

: Trouble

 - Nothing

가장 높은 고립화 수준으로 REPEATABLE READ 

고립화 수준이 막지 못하는 Phantom Read까지 방지해줍니다. 

완벽한 데이터베이스의 일관성은 유지해 주지만 

잠금을 유지하기 위하여 오버헤드는 증가하게 됩니다.

### 잠금크기와 관련된 잠금힌트

1) ROWLock : 공유 행 잠금

2) PAGLock : 공유 페이지 잠금

3) TABLock : 공유 테이블 잠금

4) TABLockX : 단독(Exclusive) 테이블 잠금

### 그밖의 잠금힌트

1) XLock : 단독(Exclusive)잠금 지정

2) UpdLock : 업데이트 잠금 지정

3) ReadPast : 잠긴 행은 건너뛰고 잠기지 않는 행들만 접근 

-> Read Commited 격리수준에서만 적용

### 커서(Cursor)에 대한 요약 정리

커서란?

- 데이터 처리는 집합단위로 이루어지기도 하지만 조건에 따라서는 행 단위로 처리하기도 한다.

- 행 단위의 데이터 처리시 커서를 사용한다.

커서의 장점

- 데이터를 행 단위로 처리할 수 있음.

커서의 단점

- 데이터 처리 속도가 느려짐.(SQL문에 비해 느림)

1.1 커서의 종류

- 서버커서(Server Cursor), 클라이언트 커서(Client Cursor)

서버커서(Server Cursor)

- T-SQL 커서와 API 커서로 나뉨

T-SQL 커서 란?

- 결과 집합을 만드는 T-SQL스크립트, 트리거, 저장 프로시저에서 사용

- 변수로 한 열을 할당받는다.(변수의 크기는 열의 데이터 타입과 데이터를 모두 받을 수 있을 만큼 선언)

API 커서란?

- OLEDB, ODBC, ADO를 이용하여 SQL 문의 결과 집합에서 커서 매핑.

클라이언트 커서란?

- ODBC에서 지원하는 커서, 결과 집합을 클라이언트 캐쉬에 저장(읽기전용)

1.2 커서의 작업

- DECLARE를 이용한 커서 선언

- OPEN을 이용한 커서 열기

- FETCH를 이용한 데이터 불러오기

- CLOSE를 이용한 커서 닫기

- DEALLOCATE를 사용ㅇ한 커서 선언 제거

1.3 커서의 ANSI 문법

- Declare 커서 이름 [ INSENSITIVE ] [ SCROLL ] CURSOR

   FOR SELECT 구문

   [ FOR { READONLY | UPDATE [ OF 컬럼명 [......N]] } ]

- INSENSITIVE : 커서에서 사용할 데이터를 임시로 복사해 주는 커서를 정의

                커서에서 정의된 데이터는 tempDB에 저장됨.

                잠금이 생김, 동시성 저하.

- SCROLL : 모든 인출 옵션 사용이 가능

           인출 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)

           인출 옵션이란? 데이터를 불러올 행을 이동하는 방식

- SELECT 구문 : 일반적인 select 구문 형식을 따름

- READ ONLY : 커서를 통한 데이터 변경 및 삭제를 방지, DELETE, UPDATE 기능을 무시.

1.4 커서의 T-SQL 문법

- DECLARE 커서 이름 CURSOR [ LOCAL | GLOBAL ]

 [ FORWARD_ONLY | SCROLL ]

 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

 [ TYPE_WARING ]

 FOR SELECT 구문

  [ FOR UPDATE [ OF 컬럼명 [ ,......N]]]

- LOCAL: 커서의 범위를 로컬로 지정(로컬변수)

- GLOBAL: 커서의 범위를 전역을 지정(전역변수)

- FORWARD_ONLY: "전진만 있되 후퇴는 없도다.", 유일한 명령 옵션은 FETCH_NEXT

- SCROLL: 모든 인출 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)를 사용하도록 지정

- STATIC: 수정이 안됨(임시커서)

- KEYSET: 커서에 포함되는 행과 멤버와 순서가 고정되도록 지정.

- DYNAMIC: 인출할 때 마다 행의 데이터 값과 순서, 멤버가 변경됨, 동적 커서

- FAST_FORWARD: 성능 최적화가 설정된 FORWARD_ONLY, READ_ONLY 커서를 지정.

- READ_ONLY: 데이터에 대한 변경 및 삭제 방지, UPDATE, DELETE 구문의 WHERE CURRENT OF 절에서 이키를 사용할 수 없음

- SCROLL_LOCKS: 위치 지정 업데이트나 삭제가 성공하도록 지정

-- Declare 구문지정

DECLARE YearPlan_Cur CURSOR FOR

  SELECT CustCode, WkCnt, DeptCode, Week, Day, SMethodYN, DaN

  FROM RM_YearPlan

  WHERE WkCnt = '1'

-- OPEN 구문

OPEN YearPlan_Cur

-- FETCH 구문

FETCH NEXT FROM YearPlan_Cur

WHILE @@FETCH_STATUS = 0

 BEGIN

  FETCH NEXT FROM YearPlan_Cur

 END

-- CLOSE 구문

CLOSE YearPlan_Cur

-- 커서 해제

DEALLOCATE YearPlan_Cur

Cursor.sql