26. SQL 응용
절차형 SQL 작성
Stored Procedure
개념
일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
DB에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 모듈
리턴값이 없거나, 하나 또는 여러 개의 리턴값을 가질 수 있음
장 & 단점
장점
DB 보안 향상
네트워크 소요 시간 절감
절차적 기능 구현
개발 업무 구분
단점
낮은 처리 성능
유지 보수 어려움
Trigger
개념
테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업
특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램
유형
행 트리거
테이블 안의 영향을 받은 행 각각에 대해 실행
데이터가 변화가 생길 때마다 실행
변경 전 또는 변경 후의 레코드(rows)는 OLD, NEW 라는 가상 줄 변수를 이용해 사용 가능
FOR EACH ROW 옵션 사용
문장 트리거
- INSERT, UPDATE, DELETE 문에 대해 단 한 번만 실행
실행 시기
BEFORE : 이벤트 전
AFTER : 이벤트 후
사용자 정의 함수
개념
프로시저와 사용자 정의 함수 모두 호출하게 되면 미리 정의해 놓은 기능을 수행하는 모듈
프로그램 로직을 도와주는 역할
파라미터는 입력 파라미터만 가능하고 리턴값이 하나
병행제어와 회복
병행제어
개념
여러 트랜잭션들이 동시에 실행되면서도 DB의 일관성을 유지할 수 있게 하는 기법
동시에 여러 개의 트랜잭션을 병행 수행할 때, 트랜잭션들이 DB의 일관성을 파괴하지 않도록 트랜잭션 간의 상호작용을 제어하는 것
목적
DB의 공유도 최대화
시스템 활용도 최대화
응답시간 최소화
단위 시간 당 트랜잭션 처리건수 최대화
DB 일관성 유지
미수행 시 문제점
갱신 분실 | Lost Update- 두 개 이상의 트랜잭션이 같은 자료를 공유하여 갱신할 때 갱신 결과의일부가 없어지는 현상
비완료 의존성 | Uncommited Dependency- 하나의 트랜잭션 수행이 실패한 후 회복하기 전에 다른 트랜잭션이 실패한 갱신 결과를 참조하는 현상
모순성 | Inconsistency- 두 개의 트랜잭션이 병행 수행될 때 원치 않는 자료를 이용함으로써 발생하는 문제
연쇄 복귀 | Cascading Rollback- 병행 수행되던 트랜잭션들 중 어느 하나에 문제가 생겨 Rollback하는 경우 다른 트랜잭션도 함께 Rollback되는 현상
기법
로킹 | Locking
트랜잭션이 어떤 데이터에 접근하고자 할 때 로킹 수행
로킹이 되어 있는 데이터에는 다른 트랜잭션이 접근할 수 없음
로킹 단위 : 필드, 레코드, 파일, DB 모두 로킹 단위가 될 수 있음
| 구분 | 로크수 | 병행성 | 오버헤드 |
|---|---|---|---|
| 로킹 단위가 큼 | 적어짐 | 낮아짐 | 감소 |
| 로킹 단위가 작음 | 많아짐 | 높아짐 | 증가 |
2단계 로킹 규약 | Two-Phase Locking Protocol
Lock과 Unlock이 동시에 이루어지면 일관성이 보장되지 않으므로 Lock만 가능한 단계와 Unlock만 가능한 단계를 구분
확장 단계 : 새로운 Lock은 가능하고 Unlock은 불가능
축소 단계 : Unlock은 가능하고 새로운 Lock은 불가능
직렬 가능성을 보장
교착상태가 발생할 수 있음
타임 스탬프 | Time Stamp
데이터에 접근하는 시간을 미리 정하여서 정해진 시간의 순서대로 데이터에 접근하여 수행
직렬 가능성을 보장
교착상태가 발생하지 않음
연쇄 복귀(Cascading Rollback)을 초래할 수 있음
낙관적 병행 제어 | Optimistic Concurrency Control
트랜잭션 수행 동안은 어떤 검사도 하지 않고, 트랜잭션 종료 시에 일괄적으로 검사
트랜잭션 수행 동안 그 트랜잭션을 위해 유지되는 데이터 항목들의 지역 사본에 대해서만 갱신
트랜잭션 종료 시에 동시성을 위한 트랜잭션 직렬화가 검증되면 일시에 DB로 반영
다중 버전 병행 제어 | Multi-version Concurrency Control
여러 버전의 타임 스탬프를 비교하여 스케쥴 상 직렬 가능성이 보장되는 타임 스탬프를 선택
충돌이 발생할 경우 복귀 수행, 연쇄 복귀 발생 가능성
회복 | DB Recovery
개념
- 트랜잭션들을 수행하는 도중 장애로 인해 손상된 DB를 손상되기 이전의 정상적인 상태로 복구시키는 작업
유형
트랜잭션 장애 : 트랜잭션의 실행 시 논리적인 오류로 발생할 수 있는 에러 상황
시스템 장애 : H / W 시스템 자체에서 발생할 수 있는 에러 상황
미디어 장애 : 디스크 자체의 손상으로 발생할 수 있는 에러 상황
UNDO & REDO
UNDO : 트랜잭션 로그를 이용하여 오류와 관련된 모든 변경을 취소하여 복구 수행
REDO : 트랜잭션 로그를 이용하여 오류가 발생한 트랜잭션을 재실행하여 복구 수행
로그 파일
트랜잭션이 반영한 모든 데이터의 변경사항을 DB에 기록하기 전에 미리 기록해 두는 별도의 파일
안전한 하드디스크에 저장되며 전원과 관계없이 기록이 존재
로그 파일을 이용한 복구
- 로그 파일에 트랜잭션의 시작(START)과 종료(COMMIT)가 있는 경우 REDO 수행
- 로그 파일에 트랜잭션의 시작(START)은 있고 종료(COMMIT)가 없는 경우 UNDO 수행
회복 기법
로그 기반 회복법
지연 갱신 회복 기법 | Deferred Update
- 트랜잭션의 부분 완료 상태에선 변경 내용을 로그 파일에만 저장
- 커밋이 발생하기 전까진 DB에 기록하지 않음
- 중간에 장애가 생기더라도 DB에 기록되지 않았으므로 UNDO가 필요 없음(미실행된 로그 폐기)
즉시 갱신 회복 기법 | Immediate Update
- 트랜잭션 수행 도중에도 변경 내용을 즉시 DB에 기록
- 커밋 발생 이전의 갱신은 원자성이 보장되지 않는 미완료 갱신이므로 장애 발생 시 UNDO 필요
검사점 회복 기법 | Checkpoint Recovery
- 장애 발생 시 검사점 이전에 처리된 트랜잭션은 회복에서 제외하고 검사점 이후에 처리된 트랜잭션은 회복 작업 수행
그림자 페이징 회복 기법 | Shadow Paging Recovery
트랜잭션이 실행되는 메모리상의 Current Page Table과 하드디스크의 Shadow Page Table 이용
트랜잭션 시작시점에 Current Page Table과 동일한 Shadow Page Table 생성
트랜잭션이 성공적으로 완료될 경우 Shadow Page Table 삭제
트랜잭션이 실패할 경우 Shadow Page Table을 Current Page Table로 함
미디어 회복 기법 | Media Recovery
디스크와 같은 비휘발성 저장 장치가 손상되는 장애 발생을 대비한 회복 기법
DB 내용을 백업, 미러링, RAID 등을 통해 별도의 물리적 저장장치에 덤프
미디어 장애 시 가장 최근 덤프로 복구하고 로그 파일을 참조해 덤프 이후의 작업 REDO
ARIES 회복 기법 | Algorithms for Recovery and Isolation Exploiting Semantics
- REDO 중 Repeating history
- 붕괴가 발생했을 때의 DB 상태를 복구하기 위하여 붕괴 발생 이전에 수행했던 모든 연산을 다시 한 번 수행
- UNDO 중 Logging
- UNDO를 할 때에도 로깅을 함으로써 회복을 수행하는 도중에 실패하여 회복을 다시 시작할 때에 이미 완료된 UNDO 연산은 반복하지 않음
주요 3단계
분석 단계
- 붕괴가 발생한 시점에 REDO가 시작되어야 하는 로그의 위치를 결정
REDO 단계
- REDO 시작 위치의 로그로부터 로그가 끝날 때까지 REDO를 수행
- REDO 된 로그 레코드의 리스트를 관리하여 불필요한 REDO 연산이 수행되지 않도록 함
UNDO 단계
- 로그를 역순으로 읽으면서 진행 트랜잭션의 연산을 역순으로 UNDO